How to Connect Power BI to Serverless Azure Synapse Analytics

January 29, 2024
Here's a solution we created for accessing Azure Data Lake Storage Gen 2 data from Power BI using Azure Synapse Analytics.

By: Mason Prewett, Dean Jurecic, and Mark Seaman

Introduction

Azure Synapse Analytics (Synapse) is a powerful tool that makes connecting to data in Azure Data Lake Storage Gen2 (ADLS) as easy as traditional data sources like SQL Server. This article outlines the details of a solution that stages data in ADLS, and then uses Synapse serverless views to structure this data and serve it to a Power BI report.

Below is a diagram that shows the details of our Synapse and Power BI solution.
 

 
As shown above, Synapse is used to do the following two operations in this solution:

  • Ingest data from ADLS and write it to a staging ADLS location
  • Serve ADLS data to a Power BI Report

Synapse serverless views are used to virtualize ADLS data and make it easier to query and connect to with reporting tools.
 

Data Ingestion & Storage

Spark notebooks in Synapse are a useful resource for data ingestion. As outlined in our solution diagram below, it uses a Spark notebook to load data from a Microsoft public Azure storage account (ADLS) into our own staging Azure storage account (ADLS).
 

 
Our solution sources a public dataset provided by Microsoft on US population by zip code. This dataset is hosted in Azure blob storage and the code to access it is provided in the Microsoft documentation.

Here is our full PySpark code for data ingestion in this notebook:

 

 

 
After executing this notebook, a delta table is written to our staging data lake location as shown below.
 

 

Creating a Synapse View

Now that the data is staged in our own storage account, Synapse serverless views make it easy to access this data just like in SQL Server. Views can be used to explore and ensure the quality of the ADLS data.
 

 
To create a Synapse serverless view, open the Synapse workspace and create a new SQL script. Select the database where this view should be created. This will be used later in the Power BI connection.

The view to connect to our staged data was defined as follows:

 
An external data source should be created first that points to the staged data lake container. This is then referenced in the OPENROWSET of the view to let Synapse know the location of the ADLS data. We did some data analysis to determine the appropriate data type limits for the column definitions in the WITH clause.

Pro Tip: The column definitions in the WITH clause are important because Synapse will attempt to infer the schema if the WITH clause is left out and will set the data types to non-optimal values.
 
Example: all varchar data types are set to VARCHAR(8000).

Now this view can be used to query the ADLS staged data, which can also be used in Power BI connection.
 

 

Connecting to the Synapse View Using Power BI

It is very easy to connect to the Synapse view using Power BI Desktop to create shareable reports with a wide variety of visualization options.
 

 
First, open Power BI Desktop and create a new report. Then select “Get Data” and “More…” from the ribbon.
 

 
Enter “synapse” in the search box, then select “Azure Synapse Analytics SQL”.
 

 
Next, determine the SQL endpoint for the Synapse workspace. To do this, click on the “Manage” tab on the left navigation of Synapse Studio. Click on “SQL Pools” and then click on the “Built-in” pool.
 

 
Now copy the value of the “Workspace SQL endpoint”.
 

 
Enter the SQL endpoint into the “Server” textbox, and database name in the “Database” textbox. In most cases, leave Data Connectivity Mode set to “Import” and click OK. You will be prompted to enter the credentials for this connection.
 

 
Next, a list of views will be shown that exist in the database that was entered. Choose the view(s) that you would like to use for the report, then click “Load”.
 

 
The selected views will then be loaded into Power BI Desktop. From here you can select the appropriate fields and visuals to create reports like the example below:
 

 

Thanks for Reading! Does Your Company Need Help With Power BI or Azure Synapse Analytics?

Does your company need help getting the most out of Power BI or Synapse? We’ve helped many companies maximize their investments in these technologies. Check out these two pages below for information on our consulting services:

 

Keep Your Data Analytics Knowledge Sharp

Get the latest Key2 content and more delivered right to your inbox!
 

 
 

Related Content
GitHub Source Control Integration with Azure Synapse Workspace

GitHub Source Control Integration with Azure Synapse Workspace

By: Syed Islam   GitHub source control integration with Azure Synapse workspace allows data professionals to manage scripts, notebooks, and pipelines in a version-controlled environment. This integration provides a centralized repository where teams can...

Microsoft Fabric – Starting a Trial

Microsoft Fabric – Starting a Trial

By: Mason Prewett, Dean Jurecic, and Mark Seaman Two Ways to Start Fabric Trial Fabric, the new AI-powered analytics platform by Microsoft, hosts resources in a workspace similar to Power BI. A new Fabric workspace can be created for this or an existing Power BI...

How to Use Power BI Analyze in Excel

How to Use Power BI Analyze in Excel

By: Dean Jurecic   After you’ve connected to a data source and have carefully selected the right visuals, formatting, and other elements for your report and shown it to the intended users, you will often hear something like the following: “This looks great, but I...