[spacer] [spacer] [spacer]

Sign up with your email address to be the first to know about new publications

[mc4wp_form id="4946"] [spacer]

Power BI and Azure Synapse Analytics Serverless

Combine Power BI and Azure Synapse Analytics Serverless to get timely actionable insights. In this blog post, you will create a Power BI report using the on-demand form of Azure Synapse Analytics. Get the best performance from the engine and pay only per usage!

In my previous post on Power BI and Azure Synapse Analytics, I highlighted how easy it is to build Power BI reports in Azure Synapse Analytics workspaces.  

In the previous post, I used a provisioned database. Today, we’ll explore the serverless (on-demand) offering and connect to it using Power BI. 

The tutorial below has three sections: 

  • Create External Tables 
  • Create Power BI dataset 
  • Create Power BI report 

Why should you query the information in a Data Lake using Azure Synapse Analytics external tables and not directly Power BI? 

I believe that are two key reasons for doing this: 

  • Power BI only supports import mode when using information from your Data Lake. This means that you have limitations on the size of the dataset (either Power BI Pro or Power BI Premium). Import mode also makes it difficult to display recent or near real-time changes.
  • Using the engine of Azure Synapse Analytics will bring great performance and big data capabilities to your reporting solutions, and it is fully integrated with your Azure data lake store.

Pre-requirements 

For this example, we are going to be using 2 different AdventureWorks parquet files that you can download from this link

  • Sales_SalesOrderHeader_20200723.parquet 
  • Sales_SalesTerritory_20200723.parquet 

Create external tables 

First, create external tables in your Azure Synapse Analytics SQL serverless pool. You can create them without writing any lines of codes. 

Since Azure Synapse Analytics on-demand does not store any data in the database, we’ll create a link definition between your Data Lake and the database. This takes advantage of the Synapse Analytics engine (Massively parallel processing system). 

Create external table Sales Order Header 

Create external table Sales Order Header

Azure Synapse Analytics can create the external table for us. We just need to provide the name of the database that we want to use (serverless is the default option), external table name, and the automatic option. 

Create external table

Then, you’ll be able to query the information. 

Query the information

Create external table Territories 

Now, repeat the same process for the territories file. 

Create external table territories

Territory file external table. 

Create external table SQL on demand

You can verify that the objects exist in your database by using the Data Hub. 

Data Hub

Create Power BI dataset 

Next, to create a Power BI report, you need to have a linked service (check this blog post on how to create a Power BI report) and create the datasets (connected to your serverless external tables).  

Create Power BI dataset

This screen starts the tutorial. 

Get started with Microsoft Power BI

First, select your database. 

Now, it’s time to download the Power BI template. This allows you to create a dataset. 

Download .pbids file

After that, open the report and select your datasets. 

Select datasets

In our scenario, we are going to use the direct query method that doesn’t require importing information into a Power BI report. 

Connection settings

Make sure that you create a relationship using the Territory Id column. 

Territory Id column

We’ll build the report in Azure Synapse Analytics, so you can simply publish the dataset. 

Publish dataset

Select the Azure Synapse Analytics workspace that you are using in your linked service. 

Publish to Power BI

The dataset will become available in your Azure Synapse Analytics workspace, but you still need to configure the dataset credentials. 

Configure dataset credentials

Configure the credentials for the Power BI dataset in the Power BI service. 

Next, edit the credentials. 

Edit credentials on Azure Synapse Analytics

And finally, select the authentication method to OAuth2. 

Configure Direct Query

Your dataset is ready! 

Dataset is ready

Create a Power BI Report 

Now that you have external tables and a Power BI dataset; you can start building reports using the Direct Query method to your Data Lake parquet files. 

Power BI report

Select your dataset. 

Now, you are ready to start creating Power BI Reports! 

Start creating Power BI Reports

When you save the report, it will become available in your workspace. You can also share it.  

Save a copy of report
Save your report
Az Synapse Analytics test

It will be available in Azure Synapse Analytics as well. 

Available in Azure Synapse Analytics

Summary 

You’ve seen how easy it is to create external tables and Power BI reports in Azure Synapse Analytics using a serverless option (pay per usage). 

Final Thoughts 

I’m really satisfied with delivering actionable insights from end-to-end without writing a single line of code. This will certainly enable more users to start using the platform and get value out of it. 

What’s next?

Looking ahead, I’ll be posting on some of the other features within Azure Synapse Analytics. Please leave a comment if there is anything that you would like to learn about.  

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *