Synchronously Refreshing a Power BI Dataset using Azure Logic Apps


By:   |   Updated: 2019-12-02   |   Comments   |   Related: More > Azure

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:

return refresh history

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.

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.

configure new action

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.

add delay step

The configuration is straight forward: enter 30 for the count and select Second from the dropdown:

wait for 30 seconds

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:

initialize variable action

Specify FinishLoop as the name, Boolean as the type and false as the initial value (keep in mind the value is case sensitive).

initialize variable config

Next we’re adding the Until loop, which can be found under Control.

until control action

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:

add finishloop variable to value

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.

rename action

Next, add the GUID of the workspace and the dataset to the action (see the previous tips on how to find these).

configure return refresh action

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.

the top param

Select the parameter and specify 1 as its value.

top parameter with value 1

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.

add parse json action

Once you click on the content cell, you can choose the result of the previous step from the list:

choose json content

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.

remove 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.

add new condition

Click on "Choose a value":

condition added

From the dynamic content list, choose the status field:

add status to condition

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:

for each loop automatically added

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:

true condition for failed

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:

check for unknown

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:

false condition for failed, with subcondition

The entire Until loop looks like this:

until loop completed

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:

test logic app

In the output, we can see the first retrieved status was Unknown, which means the Until loop had to do another iteration:

status unknown

At the top of the Until loop, we can see the loop had to do 4 iterations in total.

4 iterations

The status retrieved in the final iteration was Completed. The body has then also the optional field endTime:

refresh completed

We can verify in the Power BI service that this is accurate:

power bi service check

The one-hour difference is caused by the Power BI service showing local time zones, while the API uses UTC.

Next Steps


Last Updated: 2019-12-02


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools