How to Connect Azure Synapse Analytics to Power BI

  • 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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
ODSC - Open Data Science

ODSC - Open Data Science

93K Followers

Our passion is bringing thousands of the best and brightest data scientists together under one roof for an incredible learning and networking experience.