I wrote before about Automating table Refresh in Power BI. On that article I explained how to schedule a refresh script using the Azure Automation Account.
We can improve the automation process even more. If we create a parameterized Powershell script and publish it to github, the script becomes available in the Automation Account gallery. You can use it as many times as you wish, scheduling many different administrative tasks in Power BI using XMLA scripts.
The script will be like this one:
<# .SYNOPSIS Indexes tables in a database if they have a high fragmentation .DESCRIPTION This runbook indexes all of the tables in a given database if the fragmentation is above a certain percentage. It highlights how to break up calls into smaller chunks, in this case each table in a database, and use checkpoints. This allows the runbook job to resume for the next chunk of work even if the fairshare feature of Azure Automation puts the job back into the queue every 30 minutes .PARAMETER PowerBIEndpoint Power BI XMLA endpoint address .PARAMETER ServicePrincipal Service principal to connect to the XMLA endpoint in the format Appid@TenantId .PARAMETER ServicePrincipalSecret Secret value created for the service principal .PARAMETER Query XMLA statement to be executed, either in XML or JSON. .NOTES AUTHOR: Dennes Torres LASTEDIT: March 20, 2022 #> param( [parameter(Mandatory=$True)] [string] $PowerBIEndpoint, [parameter(Mandatory=$True)] [string] $ServicePrincipal, [parameter(Mandatory=$True)] [string] $ServicePrincipalSecret, [parameter(Mandatory=$False)] [string] $Query ) $assemblyPath = "C:\Modules\User\Microsoft.AnalysisServices.AdomdClient\Microsoft.AnalysisServices.AdomdClient.dll" try {Add-Type -Path $assemblyPath} catch { $_.Exception.LoaderExceptions } $Connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $Connection.ConnectionString = "Datasource="+ $PowerBIEndpoint +";User ID="+ $ServicePrincipal +";Password="+ $ServicePrincipalSecret $Command = $Connection.CreateCommand(); $Command.CommandTimeout = 20000; $Command.CommandType = [System.Data.CommandType]::Text; $Command.CommandText = $Query; $Connection.Open() $Command.ExecuteNonQuery() $Connection.Close() $Connection.Dispose()
You can read about how to contribute to the Automation Gallery here. I also wrote about this when I was explaining about a script to re-index SQL Server Tables (published to the automation gallery).
Let’s follow some steps to schedule the script from the gallery in an automation account.
1) In an automation account, click the Browse Gallery button and search for Power BI. You will find the script I published, as illustrated on the image below.
2) Click on the script and you will be able to see the entire code. You can decide if you will import it to your automation account as a runbook.
3) Click on the Select button. You will need to fill the details about the new runbook which will be created.
4) After clicking Import button, you can execute the runbook clicking the Start button and you will have the opportunity to fill the parameters for the powershell script, as illustrated on the screen below.
5) After that, you just need to follow the execution from the job screen.
Summary
Using the script provided in the automation gallery is even easier to automate a refresh or other administrative tasks in Power BI with XMLA scripts
Load comments