How to Connect Azure Synapse Analytics to Power BI 

• Read original article here

How to Connect Azure Synapse Analytics to Power BI 
31
The first question on your mind, well that is if you are unfamiliar with Azure Synapse Analytics is probably, what is...
The first question on your mind, well that is if you are unfamiliar with Azure Synapse Analytics is probably, what is Azure Synapse?
Azure Synapse Analytics is an analytics service that enables you to bring together data integration, warehousing and big data analytics.
Using Azure Synapse Analytics, you can query data using serverless dedicated options at scale! The platform enables you to ingest, explore, prepare, transform and manage data for all your visualization and machine learning needs.
Microsoft Power BI
Understanding your data is key in enabling you to leverage insights in decision-making. Power BI is your go-to smart analytics tool that will not only keep your data secure but give you insights using data visualization leveraging on its in-built AI capabilities.
Truly bringing together Azure Synapse Analytics and Power BI will be a match made in heaven for all your data analytics needs.
Prerequisites
How then can we connect Azure Synapse Analytics and Power BI to maximize the best of both worlds? When you are done, you will be able to deploy your web application. In order to complete this tutorial, you will need:
An Azure subscription, get a free students account here
Free Power BI account, sign in here , and download the desktop application.
QuickStart – Creating an Azure Synapse Resource
Log in to the Azure Portal and create a new Azure Synapse Analytics Resource
Fill in the details as shown below, then review and create your Azure Synapse Resource.
Subscription: Your Azure Subscription here
Resource Group: create new with a unique name
Region: West Europe
Resource Group: create new with a unique name
Select Data Lake Storage Gen2: from subscription
Account Name: create new with a unique name
File system name: create new with a unique name
Once the resource is deployed, go to the workspace URL where you will be able to access the Azure Synapse Analytics Workspace
Data – Ingesting Data to Azure Synapse Analytics
In this step, we will utilize data from Kaggle. The data is of Mobile Specification and prices scrapped from Gadget360 website.
To connect the data to Power BI, you will need to load your data into a dedicated SQL Pool. Select manage on the menu, then under SQL pools create a new pool.
Once on the Azure Synapse workspace, click on ingest data and select Built-in copy task with the cadence set as run once now.
In the next step on the source, the source type will be Set up a new connection with the base URL as the data link. Set the authentication as anonymous and then go ahead and create the data source.
Under source data store and file format settings, leave all the details as default. Next, the destination, select the destination type as SQL Pool and connect to the SQL pool you just created in the Synapse workspace.
Under column mapping, ensure all the columns are assigned to the correct data type.
Under settings in the data Pipeline, choose bulk insert and leave the rest as default.
You have now successfully ingested your data. To confirm this, go to data and under tables in the new SQL pool you create, you will find the file and a list of columns it contains.
 
Visualization – Set up a connection to Power BI and visualize your data
Head back to the dashboard and click on visualize, you will then be able to create a connection to Power BI.
Under managed, go to linked services and click on the Power BI Workspace that you just created then select New Power BI dataset to connect to the SQL pool:
Select the SQL pool you created as the data source and download your data file.
Open the file you have just downloaded in your Power BI Desktop application. Select Microsoft account on the left and ensure you are signed into your Microsoft account to ease the authentication process.
Select the dataset we just created and click load to create the dataset. You can either import the dataset to copy the data into Power Bi or DirectQuery which will connect to the data source live if you are streaming.
You can go ahead and review your dataset in Power BI and once satisfied, click on publish, to publish your dataset.
Head back to Azure Synapse and click on refresh, you should see the dataset you just published there.
You have now successfully published your dataset and you can use it to build and publish reports in Azure Synapse Studio.
Summary
In simple steps, we have ingested our data to Azure Synapse, prepared it, and connected the data to Power BI for visualization. Eager to learn more about Power BI and Azure Synapse? The learning modules below will help you get going: