Scheduling Power BI Data Refresh using APIs

We have an enterprise job scheduler which is used to execute ETL jobs. The last step of the job scheduler is to refresh the Power BI data set.

Here are the steps to do it.

• Create a Service account in your Azure Active Directory.
• Assign Pro license to the Service Account.
• Add the Service Account as an Admin of the Power BI workspace.
• Members of the Power BI workspace should have permissions to edit content.
• Create an Azure Native App using https://dev.powerbi.com/apps.
  • App Type: Native
  • Redirect URL: urn:ietf:wg:oauth:2.0:oob
  • Level of access: all dataset APIs
• Using Azure portal, add the Service Account as a Owner of the Native app.
• Get the client Id of the Azure App.
• Get the datasetId and groupId for the dataset you want to refresh.
• Fill in these parameters in the script below

Here is the PowerShell script:

# An easy way to get group and dataset ID is to go to dataset settings and click on the #dataset that you’d like to refresh. Once you do, the URL in the address bar will show the #group ID and # dataset ID, in the format:# app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID}

[String]$pbiUsername = “FILL Service Account – serviceaccount@mycompany.com”
[String]$pbiPassword = ‘FILL Service Account Password’

$clientId = “FILL Client ID of the Azure Native App”

$authResponse = $null
$authUrl = “https://login.microsoftonline.com/cc318280-7c9c-4662-8a9d-f5deee4d6fb7/oauth2/token”
$body = @{
“resource” = “https://analysis.windows.net/powerbi/api”;
“client_id” = $clientId;
“grant_type” = “password”;
“username” = $pbiUsername;
“password” = $pbiPassword;
“scope” = “openid”
}

Write-Output “Getting Authentication-Token …”
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

$headers = @{
“Content-Type” = “application/json”;
“Authorization” = $authResponse.token_type + ” ” + $authResponse.access_token
}

$groupID = “FILL GROUP ID” # the ID of the group that hosts the dataset. Use “me” if this is your My Workspace
$datasetID = “FILL DATASET ID” # the ID of the dataset that hosts the dataset

$groupsPath = “myorg/groups/$groupID”

$uri = “https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes”

# Refresh the dataset

Invoke-RestMethod -Uri $uri –Method POST -Headers $headers –Verbose

# Check the refresh history

Invoke-RestMethod -Uri $uri –Method GET -Headers $headers –Verbose