Link Power BI to Azure Synapse Analytics

Author: Eric Rouach, Madeira Data Solutions

Date: October 2022 Pre-requisites:

· Basic/Intermediate understanding of Microsoft Azure eco-system

· Ability to create new resources in Microsoft Azure

· Basic knowledge of Power BI

· A Microsoft Azure subscription

· Power BI Desktop installed on your PC

· An existing Power BI Service (pro license) account

· An existing Power BI Service workspace

Azure Synapse Analytics is the perfect all-in-one-place service for creating, developing, integrating and visualize data. In this demo, we will focus on connecting Power BI to an instance of Azure Synapse Analytics. The steps we will follow:

· Create a Dedicated SQL Pool (former Azure Data Warehouse)

· Use/Create an instance of Azure Synapse Analytics

· Create a Power BI linked service

· Create a Power BI report

· Manage the Power BI report from Azure Synapse

Let's get started!

As usual, let's create a properly named Resource Group to gather all the services relevant to our task:

Then, let's create a Dedicated* SQL Pool or in other words, a data warehouse.

*We may create a Serverless SQL Pool instead but having dedicated compute resources is enough for the purpose of this demo.

In the Marketplace search bar, let's type "sql pool", hit Enter and choose the relevant service:

Let's create the resource…

… and choose a meaningful name for our SQL pool:

As you may notice, we are using a previously created server called synapse-analytics1.

Let us remember that this is a logical server that needs to be existing so that our SQL pool will have a place to connect to. In case we do not have an existing server, we may click "Create new".

Next, Let's customize the performance level:

Here we can scale the DWU (data warehouse units) according to our needs and click Apply:

For the purpose of this demo, the minimum is ok.

From the Networking tab, we may customize the firewall rules to allow or deny access to the logical server.

As you may notice from the screenshot below, the yes/no options are greyed-out since they have already been configured in our previously created server and may not be modified from here.

From the Additional settings tab, we may select our data source as a blank database, a backup or a sample database.

For the purpose of this demo let us choose the sample option which will populate the SQL pool with the AdventureWorksDW2014 data:

Let's click Review + create:

The resource deployment shall take several minutes:

Once the resource is created, we may go to the newly created SQL pool's overview page:

As you can see, the SQL pool is accessible from an existing Synapse Analytics workspace. In case no instance of Synapse Analytics can be linked to the SQL pool, a link to create one will appear at the overview page.

For the purpose of this demo, let's go ahead and launch Synapse Studio:

Once in our Synapse workspace, from the Data section, we may see the new SQL pool and its tables:

Our next goal is to connect Power BI to our Synapse workspace so that we can manage any report from it, fulfilling Synapse Analytics' very purpose:

From the Manage section, we need to create a new Power BI linked service:

We may use the search bar or use the ready-made link:

We will now be prompted for the name of an existing Power BI Service workspace:

For this demo, let's use a workspace called "Synapse Analytics":

At this point, we must publish changes in order to save the new linked service:

Then, from the Develop section, we may now see the newly created Power BI workspace and create a new dataset:

Selecting "New Power BI dataset" will open a new window for choosing the relevant SQL pool:

Selecting a SQL pool will automatically download a .pbids file and launch Power BI Desktop to open it:

We may now select the relevant tables and/or views for building our report. For the purpose of this demo, let's import the DimCustomer and DimGeography tables so we can build a simple report to visualize the count of customers broken down by country:

After creating a relationship between the tables (data model), we may create a simple visualization:

We now need to publish the report to our Power BI Service workspace:

Now, back to the Synapse workspace, we're able to view and edit the report!

From now on, any change on the report saved from within the Synapse workspace will be reflected in the Power BI Service interface and vice-versa.

Let's try to change the visual from a column chart to a map:

We can see the change is being reflected in Power BI Service:

---- Thank you! ----