Introduction
Power BI allows you to schedule a Dataset refresh through the power BI portal (https://app.powerbi.com).
If you expect the dataset to refresh as soon as the new records are inserted in the underlying tables then you do not want the ability to trigger a dataset refresh from another source, this blog will help you with how to do that. We are using Powershell to do the same.
Pre-requisites
Datasets in Power BI Premium, Premium per user, and Power BI Embedded
GroupId and DatasetId are required
The required permission scope is Dataset.ReadWrite.All
The number of refreshes is limited to the general limitations of API-based refreshes for both Pro and Premium datasets.
Register an application
Go to https://portal.azure.com and sign in using your Office 365 account. Navigate to App Registration and create a new app by providing a name.
Note down the Client ID and Tenant ID; this will be useful in the later steps. Then, create a client secret and note it down
Provide API Permissions by choosing Power BI and choose “Read and write all datasets”
Add Service Principal to Power BI Workspace
Add the newly created Server Principal to the Power BI workspace.
Note that API access is subject to your Power BI subscription. It is recommended that you use a Power BI Premium subscription for the same.
PowerShell Script
Set Variables
You will need the following parameters for this PowerShell script
Tenant ID - Obtained during the app creation step
Client ID - Obtained during the app creation step
Client secret - Obtained during the app creation step
Group ID - Screenshot below
Dataset ID - Screenshot below
Set the PowerShell variables
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$clientid = "7XXXXXXXXXXXXXXXXXXXXXXXXa4c9"
$clientsecret = "XbQXXXXXXXXXXXXXXXXXXXMIaPo"
$scope = "https://analysis.windows.net/powerbi/api/.default"
$tenantid = "06dXXXXXXXXXXXXXXXXXXXXXXXXXXXd75"
$groupID = “XXXXXXXXXXXXXXXXXXX”
$dataset = “ZSER54XXXXX”
Obtain Access Token
The code below will return the access token.
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/x-www-form-urlencoded")
$body = "client_id=$clientid&client_secret=$clientsecret&scope=$scope&grant_type=client_credentials"
$response = Invoke-RestMethod "https://login.microsoftonline.com/$tenantid/oauth2/v2.0/token" -Method 'POST' -Headers $headers -Body $body
$token = $response.access_token
$token = "Bearer $token"
Refresh the dataset
Below block of code will trigger the Dataset
$authHeader = @{
'Content-Type'='application/json'
'Authorization'= $token
}
$groupsPath = ""
if ($groupID -eq "me") {
$groupsPath = "myorg"
} else {
$groupsPath = "myorg/groups/$groupID"
}
$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST
Get the status of Dataset
This Code helps to return the status of Dataset
$statusURL = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
$response = Invoke-RestMethod $statusURL -Method 'GET' -Headers $authHeader
$status = $response.value[0].status
Testing
The final step is to run the PowerShell script, ensure that it runs, and dataset gets refreshed.
Result
You can run the script in Powershell
You can check the dataset refresh history to ensure a successful refresh.
Comments