Actionable Data Analytics
Join Our Email List for Data News Sent to Your Inbox

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: 

Synapse Data Movement Process

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. 

create a new AAD group

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

 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  
  1. 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:  

Execute the solution 

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

 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 
  1. Add a Web activity to trigger the Power BI report refresh  
  1. 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. 
  1. Inside the Until activity, add a Wait activity and a Web activity to get the status of the refresh 
  1. Add a Web activity to trigger the Power BI report refresh (Optional).  
create Azure Synapse Analytics pipeline to refresh Power BI

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: 

Add a web activity to your pipeline

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. 

add an until activity

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:  

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.  

comments [ 10 ]
share
No tags 0
10 Responses
  • Picskteal
    07 . 07 . 2021

    Can the same implementation be followed for Dataflows if you want to refresh them?

    • David Alzamendi
      15 . 08 . 2021

      Hi,

      At this stage, you cannot implement this solution in data flows.

      I recommend using the Web activity in the pipelines as it will be more cost effective.

      Regards,

      David

  • James Reeves
    03 . 02 . 2022

    Hi, I have tried to implement the ADF solution from github but I get a HttpStatusCode – ‘401 : Unauthorized’ when trying to execute. I have other pipelines calling the same API via ADF so I don’t believe it to be a permission issue.

    I have seen alternate blogs that say you must get an AAD bearer token to trigger a refresh, can you clarify please?

    • David Alzamendi
      01 . 04 . 2022

      Hi James Reeves,

      I might be late for this one but it will be great to know how you overcame this issue. Let me know if you still need assistance.

      Regards,
      David

      • Satyabrata Kanungo
        29 . 08 . 2022

        Dear David,

        We are also facing same issue while calling Power BI API from Synapse, appreciate your advise please.

        Troubleshoot activity failures
        {
        “errorCode”: “2108”,
        “message”: “Error calling the endpoint ‘https://api.powerbi.com’. Response status code: ‘NA – Unknown’. More details: Exception message: ‘NA – Unknown [ClientSideException] An error occurred while sending the request.’.\r\nRequest didn’t reach the server from the client. This could happen because of an underlying issue such as network connectivity, a DNS failure, a server certificate validation or a timeout.”,
        “failureType”: “UserError”,
        “target”: “Refresh Power BI Dataset”,
        “details”: []
        }

    • Zach
      13 . 04 . 2022

      Getting this same error. Is it possible to post the resolution?

    • Jürgen
      19 . 04 . 2022

      Getting also the same Error. Can you post or send a soloution?

  • Karthik
    01 . 04 . 2022

    Hi, Do we need Power Bi premium license for configuring the Power Bi part? What exactly do you mean by admin access, is it admin access at the workspace level?

    Please, help me with the above queries. Thank you

    • David Alzamendi
      01 . 04 . 2022

      Hi Karthik,

      You can use Power BI Pro or Premium licenses. For the steps in the article:

      Access to create the Azure AD group and configure membership (temporal)
      You need Power BI admin access in the tenant for configuring the access to APIs (temporal)
      Member or Contributor access to the workspace level for the Azure AD group

      Regards,
      David

  • Cedz
    19 . 05 . 2023

    Nice Solution , however if you have DEP enabled in your workspace , the API calls are blocked and unsuccessful.

Do you want to leave a comment?

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