Refreshing all datasets in power BI using rest API

Power BI users desire to see the real-time data; in sync with their data source, on their dashboard. Since the exact source refresh completion time cannot be assumed, instant updating of data isn’t possible by scheduling the data refresh in the Power BI service.

Among the three Power BI Connectors, choose the Direct Query mode to create the dataset and witness the real-time data. But the limitations of using the Direct Query mode may restrict the user to achieve his desire.

In this case, creating a PowerShell script to invoke the Power BI dataset refresh using Rest API, and adding the execution of this PowerShell script as a step or as a process in the source refresh job (SQL Job, Schedulers, etc.) could help.

Read through the following to refresh datasets using REST API.

1. Prerequisites
2. Registering for a Client ID
3. Configure the app in the Azure portal
4. Create the PowerShell script to invoke the Power BI Refresh
5. Execute or schedule the script as per the requirement

1. Prerequisites

1.1 User Credential:
User Credential which we use to refresh the dataset should have:

• Power BI Pro License
• Access (Admin or Member role) to the app workspaces which you want to refresh
• Tenant admin access on the Power BI Portal to register the application

1.2 Datasets
Datasets that we are going to refresh should:

• Be created either in Import mode or Mixed mode
• Get configure with the Power BI Gateway

1.3 Infrastructure
PowerShell version should be 5.0 or greater on the machine where we are going to execute the created the PowerShell script.

2. Registering for a Client ID

2.1 Sign in to Power BI:

• Open the following URL in the browser: https://dev.powerbi.com/apps
• Sign-in into the Power BI with valid credentials

2.2 Register the application
While registering, fill out the following three sections:

• Application Name:
  Give the name of Application which is easy to understand like “Power BI Refresh” or “Power BI API”.
• Application Type:
  Select “Native” as Application Type as shown in the above screenshot.
• API Access
  To perform the refresh activity, select Read and write
  • All datasets
  • Gateways
  • All workspaces

Then click on the “Register” button. It will be displayed in the Client ID section below, which we should save in a secure location.

3. Configure the app in Azure portal

• Open the below URL in the browser: https://portal.azure.com/
• Login with the credentials which we used to register the app in the above
• Follow the steps mentioned in the below image:
• All the registered apps list will be shown in the section number 3 in the above image
• Click on the app which you registered
• Choose “authentication” s and then update the redirect URL (steps 1 and 2 as shown in the image below):
• Add the Redirect URI: “urn:ietf:wg:oauth:2.0:oob” in the section numbered 2
• Save the changes

4. Create the PowerShell script to invoke the Power BI Refresh

4.1 Create Variables
Open the Windows PowerShell executable file. Create the following 3 variables:

• $clientId = “****” Client Id of the above registered app.
• $Username = “****@**.com” Username which we used above.
• $Password = “****” Password of the same user.

4.2 Generate the authorization token

function GetAuthToken
{
    $adal = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\4.6.0\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
    
    $adalforms = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\4.6.0\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
 
    [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null

    [System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null

    $redirectUri = "urn:ietf:wg:oauth:2.0:oob"

    $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

    $authority = "https://login.microsoftonline.com/common/oauth2/authorize"

    $AADcredential = [Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential]::new($UserName, $Password)

    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

    $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId,$AADcredential)

    return $authResult
}

$token = GetAuthToken

4.3 Build the REST API header with Authorization Token

$authHeader = @{
   'Content-Type'='application/json'
   'Authorization'= $token.CreateAuthorizationHeader()
   }

4.4 Format the Groups Path

$groupsPath = ""
if ($groupID -eq "me") {
    $groupsPath = "myorg"
} else {
    $groupsPath = "myorg/groups/"
} 

4.5 Fetch the Dataset information of all groups and invoke its refresh

$uril = "https://api.powerbi.com/v1.0/$groupsPath"
$restResponse1 = Invoke-RestMethod -Uri $uril -Method GET -Headers $authHeader
$x=$restResponse1.value
foreach($i in $x){
$groupID=$i.Id #workspace Id
$groupName = $i.Name #Workspace name
$groupName + "-" + $groupID
 $uri = "https://api.powerbi.com/v1.0/$groupsPath/$groupID/datasets" 
$restResponse = Invoke-RestMethod -Uri $uri -Method GET -Headers $authHeader  
 $d=$restResponse.value
foreach($j in $d){
$datasetID=$j.Id #dataset Id
$datasetName=$j.Name #dataset Name
$datasetName + "-" + $datasetID
# Refresh the dataset
$uri = "https://api.powerbi.com/v1.0/$groupsPath/$groupID/datasets/$datasetID/refreshes" 
$restResponse = Invoke-RestMethod -Uri $uri -Method POST -Headers $authHeader
Start-Sleep -s 30
}}

5. Execute or schedule the script as per the requirement

• You can integrate the above-created PowerShell script into the SSIS solution.
• Users can execute the above PowerShell script directly in the last step of the source refresh SQL job.
• Execute the PowerShell script whenever you want the data refresh.

Technovert offers a set of Digital Transformation Services including PowerBI, Data Science, Data Management, and Modernization. Get in touch with us to accelerate your digital transformation journey.