Refresh PowerBI Datasets using PowerShell and Azure Runbooks

Posted on by — 36 Comments ↓

In June 2017, Microsoft announced a new set of API function to manage data refreshes in PowerBI. The new API basically allows you to trigger a refresh or retrieve the history of previously executed refreshes. The full specification can be found in the official MSDN documentation, or using this direct links: Refresh dataset and Get dataset refresh history

So besides the scheduled and manual refreshes from within the PowerBI service directly, we now have a third option to trigger refreshes but this time also from an external caller! This itself is already pretty awesome and some people already did some cool stuff leveraging the new API functions:

Charles Sterling: Running the Power BI Refresh API’s Headless
Sirui Sun: Git-Repository powerbi-powershell

The basic idea is to use object from pre-built Azure Management DLLs to generate the OAuth Access token that is necessary to use the API. This works very well locally but cannot be used in the cloud – e.g. in combination with Azure Automation Runbooks or Azure Functions where you cannot install or reference any custom DLLs.

In this blog post I will show you how you can accomplish exactly this  – create an Azure Automation Runbook to refresh your PowerBI dataset!
But first of all there are some things that you need to keep in mind:

  1. There are no service accounts in PowerBI so we will always use a “real” user
  2. you need to supply the credentials of a “real” user
  3. The user needs to have appropriate access to the dataset in order to refresh it
  4. the dataset refresh must succeed if you do it manually in PowerBI
  5. you are still limited to 8 refreshes/day through the API

OK, so lets get started. First of all we need an Azure Application which has permissions in PowerBI. The easiest way to do this is to use the navigate to https://dev.powerbi.com/apps, log in with your account and simply follow the steps on the screen. The only import thing is to select the App Type “Native app”. At the end, you will receive a ClientID and a ClientSecret – Please remember the ClientID for later use!

Next step is to create the Azure Runbook. There are plenty of tutorials out there on how to do this: My first PowerShell workflow runbook or Creating or importing a runbook in Azure Automation so I will no go into much more detail here. Besides the runbook itself you also need to create an Automation Credential to store the username and password in a secure way – here is a tutorial for this: Credential Assets in Azure Automation

Now lets take a look at the PowerShell code. Instead of using any pre-built DLLs I removed all unnecessary code and do all the communication using Invoke-RestMethod. This is a very low-level function and is part of the standard PowerShell modules so there is no need to install anything! The tricky part is to acquire an Authentication Token using username/password as it is nowhere documented (at least I could not find it) what the REST call has to look like. So I used Fiddler to track the REST calls that the pre-built DLLs use and rebuilt them using Invoke-RestMethod. This is what I came up with:

$authUrl = "https://login.windows.net/common/oauth2/token/"
$body = @{
"resource" = "https://analysis.windows.net/powerbi/api";
"client_id" = $clientId;
"grant_type" = "password";
"username" = $pbiUsername;
"password" = $pbiPassword;
"scope" = "openid"
}
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

$clientId is the ClientID of the Azure AD Application
$pbiUsername is the email address of the PowerBI user.
$pbiPassword is the password of the PowerBI user.
The $authRepsonse then contains our Authentication token which we can use to make our subsequent calls:

$restURL = "https://api.powerbi.com/v1.0/myorg/datasets/$pbiDatasetId/refreshes"
$headers = @{
"Content-Type" = "application/json";
"Authorization" = $authResponse.token_type + " " + $authResponse.access_token
}
$restResponse = Invoke-RestMethod -Uri $restURL –Method POST -Headers $headers

And that’s all you need. I wrapped everything into a PowerShell function that can be used as an Azure Runbook. The username/password is derived from an Azure Automation Credential.

The final runbook can be found here: PowerBI_Refresh_Runbook.ps1

Refresh_PowerBI_Dataset_Azure_Runbook

It takes 4 Parameters:

  1. CredentialName – the name of the Azure Automation credential that you created and which stores the PowerBI username and password
  2. ClientID – the ID of your Azure Active Directory Application which you created in the first step
  3. PBIDatasetName – the name of the PowerBI dataset that you want to refresh
  4. PBIGroupName – (optional) the name of the group/workspace in which the PowerBI dataset from 3) resides

When everything is working as expected, you can create custom schedules or even create webhooks to trigger the script and refresh you PowerBI dataset! As you probably know, this is really powerful as you can now make the refresh of the PowerBI dataset part of your daily ETL job!

36 Replies to “Refresh PowerBI Datasets using PowerShell and Azure Runbooks”

  1. Very nice, Gerhard. Thanks for sharing this technique! I’ve worked with Invoke-RestMethod before and thought I’d share some alternate syntax with you. As an alternative to concatenating the parameters or repeating $headers.Add, you should also have the option of building the header and body parameters as hash tables like this:

    $headers = @{
    ‘Content-Type’ = ‘application/json’;
    ‘Authorization’ = $authHeader
    }

    $body = @{
    ‘resource’ = “https://analysis.windows.net/powerbi/api&client_id=$clientId”;
    ‘grant_type’ = ‘password’
    ‘username’ = [uri]::EscapeDataString($pbiUsername)
    ‘password’ = [uri]::EscapeDataString($pbiPassword)
    ‘scope’ = ‘openid’
    }

    I personally like this style, but I don’t think there’s any functional difference. Invoke-RestMethod probably converts the hash tables to strings when called.

    Reply ↓

  2. Hi Gerhard,
    I always liked your posts and learn many things related to PowerBI or Azure.

    I got stuck with one of the requirement from my project, which I am unable to get any solution so far.

    the issue is that we are collecting data from on-prem using ADF and storing all the data in ADLS, same pipeline uses the aggregation using ADLA and generate one file and store it back in ADLS. so far until here all looks good and works fine, where I am struggling now is that my aggregated ADLS file is consumed by Power BI reports, when I do the manual refresh its working fine at Power BI side, but when I try to implement same using ADF I am facing lots of issues, even tried azure batch with ADF custom activity but no luck… have you ever faced such scenario?

    Thanks in advance
    Regards
    Nihal

    Reply ↓

  3. Thanks this is very nice article, we have same scenario to refresh power bi dataset programmatically using ADF, we have written the code in C# and it is working fine locally and but it is not working in azure functions since it can’t load dlls that you mentioned in the above article.

    Later, we tried your approach using azure automation account (credential stored in it), and run the power shell script that was mentioned here.
    http://files.gbrueckl.at/blog/Refresh-PowerBI-Dataset-using-PowerShell_7C94/PowerBI_Refresh_Runbook.ps1

    But we are getting multi factor authentication error as below :

    error”:”interaction_required”,”error_description”:”AADSTS50079: The user is required to use multi-factor authentication.\r\nTrace ID: fa7b0f4b-c0e8-42c7-9272-aa1320770300\r\nCorrelation ID: ce7afffa-4b22-4eaf-be24-255e434e10ca\r\nTimestamp: 2018-08-08 00:16:03Z”,”error_codes”:[50079],”timestamp”:”2018-08-08 00:16:03Z”,”trace_id”:”fa7b0f4b-c0e8-42c7-9272-aa1320770300″,”correlation_id”:”ce7afffa-4b22-4eaf-be24-255e434e10ca”,”suberror”:”basic_action”} (The remote server returned an error: (400) Bad Request.).

    Can you help us if there is any fix that we can do to ignore mutli-factor authentication with above mentioned runbook powershell.

    Or is there any way that we can run C# utility in azure functions ?

    Appreciate your help.

    Reply ↓

    • Hey Sam,

      well, there is a purpose of multi-factor authentication – to require a manual step when someone is authenticating to approve the login. This can of course not work when you want to run a job as a service in an automated way.
      I had the same issue at one of my clients and the simplest solution is to create a new AAD User without multi-factore authentication set up, grant it proper permissions in Power BI and use that user’s credentials for your runbook.

      kind regards,
      -gerhard

      Reply ↓

  5. Thanks for your reply and suggestion.
    We are trying to find if there is any alternate to refresh power bi dataset in end-to-end workflow.

    Our goal is to include Power BI Data set refresh as soon as our data load is completed.We already have created c# utility to refresh power BI data set and it is running locally.

    If we want to use ADF ,how can we put this c# code into ADF pipeline (we tried to use httptrigger in azure functions, but it is failing to get access token).

    Reply ↓

  6. I am getting the following error when I use Azure function app with C# code

    Function completed (Failure, Id=82233fa-87db-41a6-ab15-6c34c8095d29, Duration=1242ms)
    Executed ‘{FullName}’ (Failed, Id={InvocationId})

    Reply ↓

  7. Thanks Gerhard.

    As the service normally allows only 8 refreshes a day under Pro, does this allow you to work around that number and increase the refresh rate of the datasets?

    Reply ↓

  8. .ps1 is the extension for powershell right ? may i know , where to run powershell?please advise. thanks

    Reply ↓

    • you can run any PowerShell script in Azure Automation in a PowerShell runbook
      If you are not familiar with those I advice to first get into those technologies before trying to apply the solution described in the blog post

      -gerhard

      Reply ↓

  9. This article is awesome! Thanks. Although it does not work for me yet it looks like it points to the right direction. Why it does not work for me… any help would be appreciated….

    if I add the Workspace name “BI Reporting” in the parameter PBIGROUPNAME and the dataset name “ERP Dataset @ AZ” in the parameter PBIDATASETNAME I get the following error:
    A Group called “BI Reporting” does not exist! (A Group called “BI Reporting” does not exist!)

    If I leave the parameter PBIGROUPNAME empty the script assumes that the workspace is “My Workspace” and then I get the error:
    A Dataset called “ERP Dataset @ AZ” does not exist! (A Dataset called “ERP Dataset @ AZ” does not exist!)

    The second error I do understand since there is no dataset “ERP Dataset @ AZ” in My Workspace. However, the first one I do not.

    I checked with the user that has access to the workspace and the dataset and if I refresh manually it works fine.

    The previous steps of the power shell script seem to execute without any issue:
    Getting Credential “credentialname” from stored Automation-Credential …
    Done!
    Getting Authentication-Token …
    Done!

    Ideas? Do I miss something?

    Thanks for any help!

    Reply ↓

    • Hi Christos,

      it could be that you need to escape/URL-encode the blank in the Group-Name
      can you try to use “BI%20Reporting”? (blank escaped by %20)

      -gerhard

      Reply ↓

      • Dear Gerhard,

        thanks for the reply. The issue was that I didn’t gave enough rights to the Application that I created in https://dev.powerbi.com/apps. Once I elevated the rights it was working as a charm.

        Furthermore, I made a few changes to the script to get the callBackUri of the webhook that I assigned to the runbook so I know when the data refresh was done. I paste the addition here in case is usefull for someone else as well.

        #1 add two more paremeters
        [parameter(Mandatory=$false)]
        [Object] $WebhookData,

        [parameter(Mandatory=$false)]
        [string] $callBackUri

        #2 Get call back URI in the beginning of the script
        $Body=(ConvertFrom-Json -InputObject $WebhookData.RequestBody)
        $callBackUri =$Body.callBackUri

        #3 Check for the dataset refresh status every 12 mins for a max of 1 hour. In my case that was enough a more generic solution would be better here though
        $i=0
        $Response=’Done’
        while($i -lt 5){
        $restResponse = Invoke-RestMethod -Uri “$restURL/$pbiDatasetId/refreshes/?`$top=1” –Method GET -Headers $headers
        $restResponse.value | select starttime,endTime,refreshType,status,id
        $Response=$restResponse.value.status
        if ($Response -eq ‘Completed’) {
        break
        }
        $i=$i+1
        Start-Sleep -s 721
        }

        #4 Send webhook pipeline reply
        $Reply= @{
        Response=$Response
        pbiGroupName=$pbiGroupName
        pbiGroupURI=$pbiGroupURI
        pbiDatasetId=$pbiDatasetId
        pbiDatasetName=$pbiDatasetName
        }
        Invoke-RestMethod -Method ‘Post’ -Uri $callBackUri -Body $Reply

        Reply ↓

  10. Hi Gerhard,

    Thanks for this post. I am too having troubles traversing through the post and putting it all together. Here is what I have done successfully

    #PowerBI
    I am the owner of the workspace and the dataset within

    #dev – App registration
    Created an app registration dev/powerbi.com/apps and noted the clientID

    #Azure Portal App registrations
    Confirmed new app registration instance in Azure Portal (same clientID above)
    azure app registration instance has a secret object (value and ID)

    #Automation Account
    Automation account has Runbook
    Automation account has Credentials object
    Credentails object has secret object
    Secret object has (user:pass / value:ID from secret object above)

    #execution
    When I run the runbook, I get errors during invoke-RestMethod
    bad request 400 Invoke-RestMethod : {“error”:”invalid_request”,”error_description”:”AADSTS90019: No tenant-identifying information found in either the request or implied by any provided credentials

    Invoke-RestMethod : The remote server returned an error: (403) Forbidden

    Reply ↓

    • could be an issue with the $authUrl – can you try to set your AAD tenant ID explicitly instead of /common/
      e.g. 

      $authUrl = "https://login.windows.net/1234-1231234-12312312-1231312-1231/oauth2/token/"
      Reply ↓

      • Hi Gerhard,

        For the bad request I found the issue. There were 2 spots in the script with syntax errors:

        #1
        “resource” = “https://analysis.windows.net/powerbi/api”;
        opening quote on http address is of the wrong fomat

        #2
        $restURL = “https://api.powerbi.com/v1.0/myorg$pbiGroupURI/datasets”
        there is a missing slash between myorg and $pbiGroupURI

        I have now fixed those both since posting the question. I now just see this error :

        Invoke-RestMethod : The remote server returned an error: (403) Forbidden.
        At line:112 char:17
        + … tResponse = Invoke-RestMethod -Uri “https://api.powerbi.com/v1.0/myor …
        +

        I will do your suggestion and let you know. Thanks again for a great post and for getting back to me so soon!

        Reply ↓

      • If I change the authUrl as you have specified, I get bad request 400. It appears the format needs to be $authUrl = “https://login.windows.net/1234-1231234-12312312-1231312-1231/oauth2/authorize/”

        Reply ↓

          • Hi Gerhard,

            No, it is not working now. I am getting forbidden 403 on the $restURL / $restResponse lines.

            Even if I hard code the values in $pbiGroupURI (i.e. /groups/1234-1243-124-1234/datasets)

            the line $restReponse = Invoke-RestMethod -Uri -$restURL -Method GET -Headers $headers fails with 403 forbidden.

            Note: if I comment out that entire section to get the group/dataset values, and just hard code the refresh call with the values, I get the same error. I’m not sure how all the authorization is passing above and the calls are failing later? If I test these calls using MS UI (try me), it works pefectly

            Reply ↓

  11. Hi @Gerhard.

    This is in response to our chat above from this April 2021.

    It doesn’t run locally because script execution is disabled by policy. The offending lines are from 58 onward. I believe the issue we had in April was because this SP/App.Registration that I had created for me, was not added to the Power BI API. If that is not true, this will not work with a script or not.

    This user (SP/App.Registration) has since been added to the PBI API. We can do deploy reports/datasets and refresh them via the API and Azure CICD.

    However, running this script still produces the error we saw in April (even after doing the same steps above as we did before)

    Here is the error I see now when we try and run it.

    Invoke-RestMethod : {“error”:”invalid_grant”,”error_description”:”AADSTS50034: The user account <> does not exist in the cddc1229-ac2a-4b97-b78a-0e5cacb5865c directory.

    I replaced the name of the SP with my name. The SP definitely exists in the tenant and it can definitely access the PBI API and do workspace/report and dataset commands.

    When you create the credentials in the automation account, do you specify the friendly name or object ID for the SP user name? I’m trying everything here.

    Any suggestions?

    Reply ↓

    • Hi Aaron,
      after reading your comment and also reviewing the code again I can say that the code only works with a real user and not with a service principal as the authentication (lines 38-46) are different if an SP is used:

      $body = @{
	"resource" = "https://analysis.windows.net/powerbi/api";
	"grant_type"    = "client_credentials"
	"client_id"     = $Credential.UserName
	"client_secret" = $Credential.GetNetworkCredential().Password
}

      can you give this a try?

      you may also want to have a look here https://insightsquest.com/2019/03/03/refresh-power-bi-datasets-with-powershell/

      as a lot of things changed since the original post, there are probably already better solutions out there

      -gerhard

      Reply ↓

      • Thanks again Gerhard. The modified code still didn’t work, but I’ll give the next post a try.

        The use case we are trying to accomplish is a partition refresh via the API and it looks like TMSL has to be used.

        Thanks for all the help. I’ll keep you posted!
        Aaron

        Reply ↓

Leave a Reply

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

*