Refresh Power BI Reports with Azure Synapse Analytics and Azure Data Factory

If you want to refresh your Power BI reports using Azure Synapse Analytics or Azure Data Factory, don’t look any further. Download or create the solution here. 

When you refresh a Power BI report, you are actually refreshing the dataset. In this blog post, we will look at refreshing a Power BI report (dataset) in comparison to refreshing a dataset. 

The Power BI Rest APIs don’t support passing partitions that need to be refresh for Power BI Premium.

Reasons to Refresh Your Power BI Reports Using Azure Synapse or Azure Data Factory  

To begin, Power BI schedules are great, but when refresh Power BI reports, the data might not be available in the source system.  

By including the refresh process in Azure Data Factory or Synapse Analytics, you can make sure that the refresh only runs if new data is available. 

Logic data movement example: 

Pre-requirements (Access) 

Pre-requirements access to use Azure Synapse Analytics and Data Factory to refresh Power BI reports can be split into 2 main areas: 

• Azure configuration: creating a security group and adding a service principal 
• Power BI configuration: granting access to a security group  

Azure configuration 

In Azure, every time that an Azure Data Factory or Azure Synapse Analytics service is created, a new managed identity is created in Azure Active Directory (AAD). 

You are unable to give this managed identity direct access in Power BI to refresh Power BI reports. Instead, you  need to create an Azure Active Directory Group and include the managed identity as a member. 

First, create a new AAD group. 

Next, add your Azure Synapse Analytics or Azure Data Factory service names. 

Then, create the AAD group. 

Finally, the AAD group becomes available on the list. You’ve finished the configuration in Azure. 

Power BI Configuration 

In Power BI in order to allow the new group with the managed identities to refresh the reports, there are two main steps: 

1. Give the previously created AAD group access to the workspace  

2. Enable integration of APIs in the admin settings (you need admin access) 

In the admin portal, under tenant settings, grant the following access: 

At the workspace level, where you have your reports, give access to the new AAD group: 

Then, add the AAD group as described below: 

You’ll be able to see the group displayed on the list below. Now, close the windows and you have finished the configuration in Power BI. 

Download the Solution to Refresh Power BI Reports with Azure Synapse Analytics 

You can download the solution from my GitHub repository in the following link: 

Refresh Power BI Report with Azure Synapse Analytics or Azure Data Factory 

This solution complements perfectly with the following optimization solutions:  

• Pause and Resume Azure Synapse Analytics SQL Pool  
• Scale Azure Synapse Analytics SQL Pool with Azure Data Factory 
• Azure Data Factory Solution to Start and Stop VM 

Execute the solution 

First, get the Workspace and Dataset IDs from Power BI Service. 

Back in the pipeline, execute the solution using those values. 

Create Azure Synapse Analytics Pipeline to Refresh Power BI 

If you want to create a solution from scratch in Azure Synapse Analytics or Azure Data Factory to refresh Power BI reports, you need to: 

1. Create a pipeline and add parameters to refresh Power BI reports 

2. Add a Web activity to trigger the Power BI report refresh  

3. Include an Until activity to verify the status of the refresh. This step is optional, there might be cases where you want to wait until the refresh has completed. 

4. Inside the Until activity, add a Wait activity and a Web activity to get the status of the refresh 

5. Add a Web activity to trigger the Power BI report refresh (Optional).  

Create a Pipeline and Add Parameters 

First, create a pipeline and add parameters to refresh Power BI reports with Azure Synapse Analytics or Azure Data Factory. 

Add a Web Activity to Refresh the Power BI Report 

Add a web activity to your pipeline as described in the picture below: 

In the settings section, add the following dynamic expression: 

@concat('https://api.powerbi.com/v1.0/myorg/groups/',pipeline().parameters.WorkspaceId,'/datasets/',pipeline().parameters.DatasetId,'/refreshes') 

Add an Until Activity 

Note: This step is optional. There might be cases where you want to wait until the refresh is completed. 

In the settings section, add the following expression. This expression looks at the last refresh and it will run until the status is Completed or Failed. 

@or(equals(activity('Get Power BI Dataset status').output.value[0].status,'Completed'),equals(activity('Get Power BI Dataset status').output.value[0].status,'Failed')) 

Add the Wait Activity 

Click to edit the Until activity option. 

If you don’t include a Wait activity, you execute the API too many times and the cost of the solution will increase considerably. It’s possible to modify the number of seconds to wait. 

Remember that the cost of the execution is rounded up to the closest minute. For example: if the activity runs for 1 second, you will be billed for 1 minute. 

Add a Web Activity to Get the Status of the Refresh of a Power BI Report 

Add a new Web activity. This helps you get the status of the last refresh for that dataset. 

In the Settings option, include the following expression as part of the URL: 

@concat('https://api.powerbi.com/v1.0/myorg/groups/',pipeline().parameters.WorkspaceId,'/datasets/',pipeline().parameters.DatasetId,'/refreshes?$top=1') 

This gets the latest status of the Power BI dataset refreshes.  

The possible statuses are: 

• ‘Unknown’ – unknown completion state or refresh is in progress. EndTime will be empty with this status. 
• ‘Completed’ – refresh completed successfully 
• ‘Failed’ – refresh failed. serviceExceptionJson will contain the error. 
• ‘Disabled’ – refresh disabled by Selective Refresh. 

Summary  

Today, you’ve had a look at refreshing Power BI reports with Azure Synapse Analytics or Azure Data Factory.  By doing this, you’ll take your data movement process to the next level. 

Don’t forget to download the following solutions:  

• Pause and Resume Azure Synapse Analytics SQL Pool  
• Scale Azure Synapse Analytics SQL Pool with Azure Data Factory  
• Azure Data Factory Solution to Start and Stop VM 

What’s Next? 

In upcoming blog posts, we’ll continue to explore some of the features within Azure Data Services.        

Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!      

As always, please leave any comments or questions below.