We used ActiveBatch scheduler to trigger this powershell script.
SqlServer library latest version is not compatible use
Sqlserver = 21.1.18256
Microsoft.Identity.Client - 4.53.0
Microsoft.PowerBImgmt - 1.2.1111
PowerShell - 5.1
For Full dataset refresh add applyrefreshpolicy = 'false' instead of partitions in the xmla body.
Final Script:
param(
[Parameter(Mandatory = $true)]
[string]$WorkspaceName,
[Parameter(Mandatory = $true)]
[string]$GroupId,
[Parameter(Mandatory = $true)]
[string]$DatasetName
)
# Credentials
$AppId = # Service PRincipal ID
$TenantId = "TenantID"
$ClientSecret = # Secret from Service Principal
# Creating secure string & credential for Application ID and client secret
$PbiSecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText
$PbiCredential = New-Object Management.Automation.PSCredential($AppId, $PbiSecurePassword)
# Connect to the Power BI Service Account
Write-Output "Connecting to Power BI Service Account..."
Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $PbiCredential
Write-Output "Successfully connected to Power BI Service Account"
# Get the Dataset ID of the Target Dataset
Write-Output "Getting datasets for workspace: $GroupId"
$Datasets = Get-PowerBIDataset -WorkspaceId $GroupId
$DatasetId = ($Datasets | Where-Object { $_.Name -eq $DatasetName }).Id.Guid
Write-Output "Dataset ID for $DatasetName : $DatasetId"
# Set execution policy for the session
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
# Define the required SqlServer module version
$requiredVersion = "21.1.18256"
# Check if the required version is installed
if (Get-InstalledModule -Name SqlServer -RequiredVersion $requiredVersion -ErrorAction SilentlyContinue) {
Import-Module SqlServer -RequiredVersion $requiredVersion
Write-Host "Imported SqlServer module version $requiredVersion."
} else {
Write-Error "SqlServer module version $requiredVersion is not installed. Please install it before running this script."
exit 1
}
# Define JSON refresh command as a variable
$jsonRefresh = @"
{
"refresh": {
"type": "full",
"objects": [
{
"database": "Your Dataset",
"table": "Your Table",
"partition": "Your Partition"
}
]
}
}
"@
# Save JSON to a temporary file
$tempJsonPath = "$env:TEMP\refresh_command.json"
$xmlaEndpoint = "powerbi://api.powerbi.com/v1.0/myorg/$WorkspaceName"
# Run the command
try {
Write-Output "Triggering dataset refresh..."
Invoke-ASCmd -Server $xmlaEndpoint -InputFile $tempJsonPath -ServicePrincipal -ApplicationId $AppId -TenantId $TenantId -Credential $PbiCredential
Write-Output "Successfully triggered a Power BI Dataset Refresh."
}
catch {
Write-Error "Failed to trigger dataset refresh."
}
