Refresh Power BI Datasets with PowerShell

This post contains a PowerShell script that triggers a refresh process for three Power BI datasets. Additionally, this script is called/executed as part of automated processes via SQL Server Agent and SQL Server Integration Services (SSIS).

Given the length of the previous post regarding group email notification, as well as the option to schedule a refresh in the Power BI service, I intentionally left out details regarding dataset refresh for the report that’s driving the notification. In many cases scheduled refresh in the Power BI service may be sufficient but ideally you’d want to synchronize the data retrieval (the JSON files with refresh history) with the dataset refresh to immediately deliver the notification once the new data is available.

The Datasets to Refresh

In this example, the objective is to trigger the refresh of the following three datasets in an app workspace named “BI Administration and Monitoring”:

As an aside, if you’re responsible for administering Power BI in your organization or part of a team that shares these duties you may consider a dedicated app workspace to host various monitoring and admin solutions. For example, you could include Azure Active Directory group data, Analysis Services metadata and performance metrics (if applicable), in addition to core Power BI usage analytics, on-premises data gateway metrics, and more.

Dataset Refresh Script

The PowerShell script calls the Refresh Dataset in Group Power BI REST API via the Invoke-PowerBIRestMethod cmdlet, which is included in the Power BI Management module. In the following example, the user account of a Power BI Service Administrator is used for authentication. Alternatively, a service principal could also be used per the documentation on the Connect-PowerBIServiceAccount cmdlet.

Both the sample script below and a parameterized sample script (for use in SSIS example) have been uploaded to GitHub:

#1. Authenticate to Power BI with Power BI Service Admin account

$User = "123456@thebigcompany.com"
$PW = "theuserpw"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

#2. Get the workspace ID of the dataset(s) to be refreshed

$WSIDAdmin = Get-PowerBIWorkspace -Scope Organization -Name 'BI Administration and Monitoring' | `
    Where {$_.Type -eq "Workspace"} | ForEach {$_.Id}

#3. Get the dataset ID(s) of the datasets to be refreshed 

#Refresh History dataset
$DSIDRefresh = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "Refresh History"} | ForEach {$_.Id}

#PBI Tenant Wide Monitoring dataset
$DSIDMonitor = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "PBI Tenant-Wide Deployment"} | ForEach {$_.Id}

#Gateway Monitoring dataset
$DSIDGateway = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "Gateway Analysis"} | ForEach {$_.Id}

#4. Build dataset refresh URLs

$RefreshDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDRefresh + '/refreshes'

$MonitorDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDMonitor + '/refreshes'

$GatewayDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDGateway + '/refreshes'

#5. Execute refreshes with mail on failure

$MailFailureNotify = @{"notifyOption"="MailOnFailure"}

Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify

Invoke-PowerBIRestMethod -Url $MonitorDSURL -Method Post -Body $MailFailureNotify

Invoke-PowerBIRestMethod -Url $GatewayDSURL -Method Post -Body $MailFailureNotify

Per the comments in the script above, following authentication to the Power BI service, the workspace ID and dataset IDs are retrieved via standard Get cmdlets included in the Power BI Management module. Once these values (GUIDs) are obtained and stored in variables, separate URL strings are built for each dataset to be refreshed and these custom strings are also assigned to variables. Finally, the REST API to trigger the refresh is called by passing the respective URL variable for the given dataset.

Script Notes

• This pattern of retrieving the IDs for workspaces, datasets, and other Power BI artifacts (reports, dashboards) and then passing the values to various other cmdlets and expressions is very common.
  • The script used to retrieve dataset refresh history for the group email notification is just another example.
• The Where-Object cmdlet for type = “Workspace” is only necessary to avoid a scenario in which a workspace with the same name was created with the current Office 365 group-based workspace experience.
  • At some point later this year following GA of the new Power BI workspaces the current/legacy Office 365 group based workspaces will be migrated to the new workspace experience.
• You ‘could’ just navigate to the workspace(s) and dataset(s) in the Power BI service to obtain the IDs necessary for the script rather than retrieve the values on the fly but of course this approach would not be resilient to change.
• The refresh will still execute if you don’t supply a value for the Body parameter – you’ll just get a warning since this is a POST method (not a GET).

Dataset Refresh Automation

The dataset refresh script is only useful when it’s called from an automation/orchestration tool as part of a process impacting the data ingested by the Power BI dataset(s). Probably in the most common scenario you want the Power BI dataset(s) to be refreshed immediately following an update to the source system such as a data warehouse or reporting data marts.

From a SQL Server Agent perspective, you could create a dedicated job with a PowerShell step and call this job from the sp_start_job stored procedure. Alternatively, you could just add a Powershell step to an existing Agent job such as the following example for refresh history data:

In the example above, the Refresh History dataset is refreshed immediately following the successful execution of a PowerShell script that writes refresh history data to JSON (to be retrieved by the dataset).

The PowerShell step type in SQL Server Agent may be sufficient in some cases but it doesn’t allow for abstracting away sensitive values such as the credential used for authentication. In this case, and particularly if SSIS packages are already being used, one option is to re-factor the PowerShell script to accept parameters from a calling application and then leverage the project or package-scoped parameters in SSIS like the following example:

In the image above, an SSIS expression is created for the Arguments property of the Execute Process Task Editor. The values for three package-scoped parameters (script, user, pw) are passed into this expression to execute the refresh. As parameters in SSIS, the admin team more easily manage and secure the values.

The Execute Process Task could also be used to just execute the PowerShell script (without the custom expression and parameters) per the following blog post.

See the parameterized script on GitHub as well as a previous post that highlighted executing PowerShell from SSIS for additional details.

Coming to California

I’ll be delivering a one hour session on custom Power BI Administration solutions on April 13th at SQL Saturday 847 Orange County. If you’re around the Los Angeles area and interested in this topic or anything else I write or blog about it would be a pleasure to meet you.