This blog post is part of my “Automating Power BI deployments” series, and you can find a list of the other posts here.
What you need to know
There’s a few things you need to pay close attention to when setting the refresh schedule via the API:
- Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.
- The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index
- The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.
Initial steps
As we’ve done with all our PowerShell scripts so far, let’s create some parameters, connect to the service and retrieve the workspace and report objects:
param
(
[Parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[String]
$WorkspaceName,
[Parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[String]
$ReportName,
[Parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[String]
$Timezone,
[Parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[String]
$RefreshTime
)
#Connect to Power BI tenant
Connect-PowerBIServiceAccount | Out-Null
#Retrieve the workspace
$WorkspaceObject = (Get-PowerBIWorkspace -Scope Organization -Name $WorkspaceName)
#Retrieve the report
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $ReportName)
The API call
We’re going to use the Patch method again here (see the previous post in the series for more info on that), and set the refresh schedule for once a day at the time (and time zone) provided in the parameters. Note that we’re also setting the notification option to MailOnFailure, which means that the owner of this dataset will get an email if the refresh fails.
#API url for the refresh schedule
$ApiUrl = "groups/" + $WorkspaceObject.Id + "datasets/" + $PbiReportObject.DatasetId + "/refreshSchedule"
$ApiRequestBody = @"
{
"value": {
"days": [
"Sunday",
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday"
],
"times": [
"$RefreshTime"
],
"notifyOption": "MailOnFailure",
"localTimeZoneId": "$Timezone",
"enabled": true
}
}
"@
#Update refresh schedule
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody")
Write-Output "Data refresh schedule for report ""$ReportName"" updated successfully..." `n
Want to download the PowerShell scripts to perform these actions? Get it from my GitHub repo.
Hi there Thanks for the great post
Getting an error when trying to setup scheduled refresh on Environment USGOVHIGH
An error has occurred!!
Error Line Number : 89
Error Command : Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body (“$ApiRequestBody”)
Error Message : One or more errors occurred
Use the -Verbose switch on that line. Hopefully that will give you a more descriptive error message.
How can we set Refresh notification in the API call along with setting the schedule time
You can activate the notification, but it doesn’t look like you can set or change the list of recipients. Here’s the official doc: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/update-refresh-schedule-in-group#schedulenotifyoption