By: Koen Verbeeck | Updated: 2019-12-02 | Comments (9) | Related: > Azure Data Factory
Problem
I’m able to refresh a Power Dataset in the Power BI service using the Power BI API. The solution is built in an Azure Logic App. However, when the action calls the API, it immediately succeeds without giving an indication if the refresh actually succeeded or not. Is it possible to do a synchronous refresh, where the status of the refresh is returned?
Solution
Once a dataset is published to the Power BI service, you’ll typically want to refresh the dataset periodically with the latest data. An option is to trigger the refresh right after the data warehouse (or other data store) has been reloaded. This can be done through the Power BI API. The tips Refresh Power BI Dataset from Azure Data Factory - Part 1 and Refresh Power BI Dataset using Azure Logic Apps – Part 2 explain in detail how you can set up a Logic Apps custom connector to the Power BI API. This connector allows you to call the various Power BI API functions, one of them is the dataset refresh. However, if you call the API you only get a notification if the actual call to the API has succeeded or not (which is almost instantly). You do not get a notification if the dataset has actually refreshed correctly.
In this tip, we build further on the solution build in those two previous tips. We will implement a polling mechanism to verify if the dataset has refreshed successfully. If you want to follow along, please make sure the custom connector has been set up in your own environment.
Synchronously Refreshing a Dataset in Power BI
As described in the previous tips, the dataset is refreshed using the custom connector since Logic Apps didn’t support it natively. Luckily, a recent blog post on the Power BI blog announced the (preview) availability of the "Refresh a dataset" action in Microsoft Flow. Since Flow is built upon Logic Apps, this means it’s available there as well. Unfortunately, this new action is asynchronous. There is another task though in the custom connector which will assist us in retrieving the status of the dataset refresh:
Using this task, we can retrieve the latest refresh history of the dataset and verify if it is still running or if it has completed yet. Let’s start with a new Azure Logic App, triggered by an HTTP Request. Add a new action, search for Power BI and add the new Refresh a dataset action.
After adding the action, you’ll need to log into the Power BI service. Next, you can choose the workspace and the dataset from the dropdowns.
Before we start polling for the refresh status, we’ll wait for 30 seconds, just to make sure the refresh has actually started and we’re not fetching the status of a previous refresh. This can be achieved with the Delay action, which is part of the Schedule connector.
The configuration is straight forward: enter 30 for the count and select Second from the dropdown:
To have a polling mechanism, we need a loop. Inside this loop, we’ll fetch the status of the refresh and check it against some conditions. Looping in a Logic App can be achieved using the Until action. This loop will continue looping until a certain condition is met. We’re going to use a Boolean variable called FinishLoop to control the loop. Once this variable equals true, the loop can stop.
The first step is to add the action called Initialize Variable:
Specify FinishLoop as the name, Boolean as the type and false as the initial value (keep in mind the value is case sensitive).
Next we’re adding the Until loop, which can be found under Control.
When the loop is added, configure it to stop when FinishLoop equals true. When you click in the cell for value, you can choose the variable from the pop-up:
The condition should look like this:
Inside the Until loop, add a new action. Search for your custom Power BI connector and the task aptly named "Returns the refresh history of the specified dataset from the specified workspace.". Since the name of an action in a Logic App can only be 80 chars longs, you need to modify the name into something shorter.
Next, add the GUID of the workspace and the dataset to the action (see the previous tips on how to find these).
To only fetch the status of the latest refresh, we need to add a parameter. Click on the cell displaying "add new parameter". This will pop-up a list of possible parameters, of which there is only one: the top parameter.
Select the parameter and specify 1 as its value.
This action will call the Power BI API and get the most recent refresh status of the dataset. The result will be returned as an HTTP message in JSON format. You can find the exact details in the documentation. Inside the JSON body of the message, we need to extract the status field. To do this, we need to parse the JSON first, which can be done using the Parse JSON action, which can be found under Data Operations.
Once you click on the content cell, you can choose the result of the previous step from the list:
We also need to specify the schema. From the documentation, I choose the sample response of a failed refresh, since this contains all possible items:
{ "value": [ { "refreshType":"ViaApi", "startTime":"2017-06-13T09:25:43.153Z", "endTime":"2017-06-13T09:31:43.153Z", "serviceExceptionJson":"{\"errorCode\":\"ModelRefreshFailed_CredentialsNotSpecified\"}", "status":"Failed", "requestId":"11bf290a-346b-48b7-8973-c5df149337ff" } ] }
Click on "Use sample payload to generate schema", copy paste the JSON into the box and click on Done:
In the generated schema, go to required fields list and remove endTime and serviceExceptionJson since they are both optional fields.
Now we’re going to inspect the status field using a condition. Add a new step and add the Condition action which can be found under Control.
Click on "Choose a value":
From the dynamic content list, choose the status field:
Since this field needs to be extracted from the parsed JSON body from the previous step, the Logic App will automatically add a for each loop to loop over all the possible fields of the JSON:
As you can see in the screenshot, the value field (which is the parsed JSON) is used as the input for the loop. In the condition, we’re going to compare the value of the status field against the string "Failed". If the refresh has failed, we can stop the Until loop. If the condition is true, we’ll send an email saying the refresh has failed and we will set the variable FinishLoop to true. To learn more how to send an email with Logic Apps, check out the tip Azure Data Factory Pipeline Email Notification – Part 1. Setting the variable can be done with the Set Variable action. The "true" branch of the condition will look like this:
If the status is not equal to "Failed", we can check if the status equals "Unknown" with another condition. The status Unknown signifies the refresh is still going on. This condition takes up the following configuration:
If the status is not unknown (and also not failed), the refresh has succeeded (or it is disabled but I assume you’ll want to refresh a dataset enabled for refreshing). This means the loop can stop as well. If it is equal to unknown, the loop needs to continue. First, we’ll add another delay step where we’ll wait again for 30 seconds. After that, we’re setting the variable FinishLoop to false. In the other branch of the condition (the refresh has succeeded), we’ll be setting the variable to true.
Both branches look like this:
The entire Until loop looks like this:
Keep in mind the Until loop has by default a maximum of 60 iterations configured. If the refresh of your dataset takes a long time, you might want to wait longer in the delay step to avoid hitting this limit.
Now we can test the Logic App:
In the output, we can see the first retrieved status was Unknown, which means the Until loop had to do another iteration:
At the top of the Until loop, we can see the loop had to do 4 iterations in total.
The status retrieved in the final iteration was Completed. The body has then also the optional field endTime:
We can verify in the Power BI service that this is accurate:
The one-hour difference is caused by the Power BI service showing local time zones, while the API uses UTC.
Next Steps
- If you haven’t already, check out the tips Refresh Power BI Dataset from Azure Data Factory - Part 1 and Refresh Power BI Dataset using Azure Logic Apps – Part 2 to learn more about how to configure the Logic App custom connector for Power BI.
- All possible refresh status are explained in the documentation: Datasets - Get Refresh History.
- More documentation:
- An introduction to Power BI datasets.
- You can find more Azure tips in this overview.
- More Power BI tips can be found here.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-12-02
