Refresh Power BI datasets in certain workspaces using Powershell and API

[jayjay1]

Hi, I have managed to piece together the following code from a couple of different articles online to refresh all datasets within my organisations Power BI tenant however this takes over an hour to run unfortunately and by the time it gets to the datasets I am interested in the token authorisation expires and the datasets do not refresh. 

Is it possible to adjust the following code so only datasets in certain workspaces can be refreshed, say that only contain the words "Global Performance"? 

I have tried different Where-Object clauses but nothing seems to work and it goes ahead and refreshes all datasets. I'd rather not hard-code the workspace id which does work but there are a large number of them created by my team. 

Thank you

$clientId = " " #App registered in Azure: Power BI Dataset Refresh

function GetAuthToken
{
if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
}

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

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

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

$User = " " #user and password information to be input here
$PWord = ConvertTo-SecureString -String " " -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord

$AADCredential = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential" -ArgumentList $credential.UserName,$credential.Password

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

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

return $authResult
}

# Get the auth token from AAD
$token = GetAuthToken

# Building Rest API header with authorization token
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$token.CreateAuthorizationHeader()
}

<#
$body = @{

'notifyOption' = 'mailOnCompletion'
}
$body = $body | ConvertTo-Json
#>

$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 -- is there a way to filter on this? Say if workspace name has something like "Global Performance" in part of the 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"

$MailFailureNotify = @{"notifyOption"="MailOnFailure"}
$restResponse = Invoke-RestMethod -Uri $uri -Method POST -Headers $authHeader -Body $MailFailureNotify
#Start-Sleep -s 30
}}

[lbendlin]

why are you refreshing these datasets in the first place?  Ideally they should only refresh when the source data has changed.  What SKU are you running on?

[jayjay1]

The datasets are using Azure SQL databases but due to the Dynamic Row Level Security I applied so users and teams can access their own data I had to switch from Direct Query to Import mode. Which is why I need the dataset refresh automated rather than manually setting up the schedule on each dataset as there are far too many to go through. 

[lbendlin]

Put the workspace and dataset IDs in a SharePoint list and create a flow in Power Automate to issue the refresh requests. Be mindful of your SKU's limit on concurrent renderers.

Since you are already using dynamic RLS can you not point all your workspaces to a shared dataset?

[jayjay1]

It's a good suggestion thanks but it would mean spending a lot more time to configure PowerApps when I have already spent a large amount of time trying to get the above code to work and it really only needs adding/adjusting one or two lines of code. I'm thinking I may need to post this on a Powershell forum as clearly not many Powershell experts are on here. 

I decided against a large master dataset as reports and refreshes can end up being inordinately slow and it can become a lot harder to keep track of what is being used by what or overcomplicated or why a particular count/set of counts or figures are showing what they are, etc you get the picture (I hope).