So today, we have an amazing topic to discuss on triggering refresh on Power BI Dataset in a workspace using Rest API.
In many Business scenarios, the Power BI reports needs to be Triggered/refreshed once the batch load is completed every day.
The batch load finish time can be dynamic and cannot be believed to finish every day on a specified time. So, it brings the dependency of the Power BI report refresh on the batch completion.
There are two ways that can be used to trigger the refresh on the reports.
- Power BI provides the API that can be used to refresh the report’s Dataset for a particular workspace using the POST HTTP request. The HTTP request can be sent using the WEB activity through ADF/Synapse pipeline.
- You can leverage the Logic App to trigger the report’s refresh (we will see how to trigger the dataset using Logic App in our next blog).
Here I am taking only one dataset Id in a workspace to trigger. However, in real time there could be more than one workspace and Dataset Id that needs to be triggered.
Using API from the Pipeline.
Here we are going to refresh all the Datasets one by one. To achieve this, we have the API to trigger the refresh. In the below URL you need to edit the workspace ID and Dataset ID.
API :
https://api.powerbi.com/v1.0/myorg/groups/<Workspace ID>/datasets/<Dataset ID>/refreshes
First search the web activity and drag that into the canvas. I have named the Activity and kept the retry count as 2( you can keep as per your requirement) and the retry interval is set to 30 seconds.
It is always good to have retry logic so that if activity fails to run in the first attempt due to any reason, then it will try for it again.
You need to put this URL in the pipeline web activity and since we need to trigger the dataset we need to use the POST HTTP request type.
Now click on the Settings tab to configure the activity. First paste the refresh API URL in the field and select the Method as POST.
Since we have used the POST Method it expects the values to be passed. We are not passing anything to the URL we will just keep a dummy empty value in the Body so that it won’t throw error for any values expectation.
The next step is to authenticate when calling the endpoint i.e. Power BI. There are many options to authenticate, you can choose any one of the following and authenticate. I am going to use the System Assigned Identity as I have my ADF/Synapse workspace added to the Power BI workspace with the permission to refresh the dataset.
Once you select the Authentication method as System Assigned Managed Identity it will ask the resource to which you want to authenticate then you can use the Power BI API resource URL.
Resource URL: https://analysis.windows.net/powerbi/api
Now we are ready to trigger and send the refresh request to the Dataset.
Let’s check the status of the refresh: You can check the refresh status of the Dataset by switching the HTTP request from POST to GET. You can see the refresh Type is “ViaApi” and the status as “Completed”.
Note: For multiple Datasets and workspaces you can store the Ids in a SQL table or in a file storage and pass each of the Ids using the ForEach Activity.
