The data warehouse technologies and architectures are changing at ever faster pace. Many organisations still have a large investment in a full SQLServer stack to load their data warehouse, but already switched to PowerBI as a reporting tool for much faster deliveries and ad hoc reporting needs.

Disconnected refresh schedules

However, when relying on the Import mode for PowerBI semantic models, the refreshes can only be triggered when the backend SQLServer data warehouse load has been completed. If you are using the full SQLServer stack, where your ETL flow is designed in SSIS, and SQLAgent is used to schedule those packages, then the support to incorporate PowerBI interaction is somewhat non-existing.

A simple (but risky) solution is to enable scheduled refresh at fixed times, when you anticipate the ETL process to have already completed.

That clearly has the disadvantage that you cannot be sure the ETL load completed (successfully) by the time the scheduled refresh starts. It also makes it more difficult to increase the number of times you can schedule the ETL in a day, due to the margins you’d have to incorporate.

Integrated refresh

As an alternative you would either have to modify the SSIS packages to include a REST API call to trigger a refresh, which is again not supported by standard components. 3rd party component suppliers for SSIS like TaskFactory might help out there, but is still not a pleasant experience.

If it has to be a basic REST API call, then this can also be launched from PowerShell, which luckily is supported by the SQLServer SQLAgent tool (granted, a command line tool would be even more hard-core, but I’ll leave that for the enthusiasts).

Adding a PowerShell step to existing SQLAgent jobs doesn’t interfere with existing build and deploy processes.

What is needed

To make this work we’ll need

  • a Service Principal in Azure
  • optionally (but preferably) a KeyVault
  • a PowerShell script

Service Principal

Unoriginally I’ll call this “PowerBI-ServicePrincipal”. You can find how to set this up in various other articles

Main important piece of information you need is the client ID from this principal, and you have to create a secret, of which you copy the value.

Note that the service principal will need to be added to the accounts in the PowerBI tenant that are allowed to use the PowerBI API (as documented in other articles as well).

KeyVault

You’ll probably already have a keyvault (e.g. my-vault ). Store the value of the (Service Principal) secret from the previous step in a new (KeyVault) secret, named e.g. “PowerBI-ServicePrincipal”

Make sure to setup RBAC security. Add a role assignment for the role KeyVault Secrets User, and include a “Managed Identity” for the azure virtual machine that is running SQLServer. This identity already exists in EntraID, so you just type the name of the VM and select it.

If you don’t want to use KeyVault just yet, that’s ok, but you’ll have to hardcode the secret in the PowerShell script.

PowerShell script

The below can be used as the script in the job step. 

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile
Import-Module Az.KeyVault

# variables to configure:
# PowerBI workspace
$workspaceId = "c8aaaac2-7bb2-4cc1-8dd5-4aeeeeee73" 
# PowerBI semantic model
$datasetId = "0fxxxxxa-9yye-4zz4-954f-dfzzzzzzb7" 
# Service Principal that will be used to call the API, which needs admin access to the workspace
$servicePrincipalId = "33kkkkke-0llf-4mmc-8nn9-c4ooooooe7" 
# PowerBI tenant = fixed
$tenantId = "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx"

# Connect to Azure resources
Connect-AzAccount -Identity # Managed identity, the virtual machine
# Get secret from KeyVault
$secret = Get-AzKeyVaultSecret -VaultName "my-vault" -Name "PowerBI-ServicePrincipal" -AsPlainText
# convert to secure string
$pwd = $secret | ConvertTo-SecureString -asPlainText -Force
# create credential
$credential = New-Object System.Management.Automation.PSCredential ($servicePrincipalId ,$pwd)

Connect-PowerBIServiceAccount -ServicePrincipal -Tenant $tenantId -Credential $credential
Invoke-PowerBIRestMethod -Url "groups/$workspaceId/datasets/$datasetId/refreshes" -Method Post
Disconnect-PowerBIServiceAccount

Conclusion

That is all, you can now run this step, which will call the PowerBI refreshes API and you’ll find in the semantic model settings, under refresh history an entry of a refresh “By API”.

This way, you chained the dataset refresh directly to the ETL process, avoiding any delay or unnecessary refreshes.