{"id":93579,"date":"2022-05-02T17:00:18","date_gmt":"2022-05-02T17:00:18","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93579"},"modified":"2024-09-03T20:15:19","modified_gmt":"2024-09-03T20:15:19","slug":"template-for-automating-power-bi-refresh","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/template-for-automating-power-bi-refresh\/","title":{"rendered":"Template for automating Power BI Refresh"},"content":{"rendered":"

I wrote before about Automating table Refresh in Power BI<\/a>. On that article I explained how to schedule a refresh script using the Azure Automation Account<\/strong>.<\/p>\n

We can improve the automation process even more. If we create a parameterized Powershell<\/strong> script and publish it to github, the script becomes available in the Automation Account<\/strong> gallery. You can use it as many times as you wish, scheduling many different administrative tasks in Power BI<\/strong> using XMLA<\/strong> scripts.<\/p>\n

The script will be like this one:<\/p>\n

<\/p>\n

\n
<#<\/span>\r\n.SYNOPSIS<\/span>\r\nIndexes tables in a database if they have a high fragmentation<\/span>\r\n.DESCRIPTION<\/span>\r\nThis runbook indexes all of the tables in a given database if the fragmentation is<\/span>\r\nabove a certain percentage.<\/span>\r\nIt highlights how to break up calls into smaller chunks,<\/span>\r\nin this case each table in a database, and use checkpoints.<\/span>\r\nThis allows the runbook job to resume for the next chunk of work even if the<\/span>\r\nfairshare feature of Azure Automation puts the job back into the queue every 30 minutes<\/span>\r\n.PARAMETER<\/span> PowerBIEndpoint<\/span>\r\nPower BI XMLA endpoint address<\/span>\r\n.PARAMETER<\/span> ServicePrincipal<\/span>\r\nService principal to connect to the XMLA endpoint in the format Appid@TenantId<\/span>\r\n.PARAMETER<\/span> ServicePrincipalSecret<\/span>\r\nSecret value created for the service principal<\/span>\r\n.PARAMETER<\/span> Query<\/span>\r\nXMLA statement to be executed, either in XML or JSON.<\/span>\r\n.NOTES<\/span>\r\nAUTHOR: Dennes Torres<\/span>\r\nLASTEDIT: March 20, 2022<\/span>\r\n#><\/span>\r\nparam<\/span>(\r\n[parameter<\/span>(Mandatory<\/span>=$True<\/span>)]\r\n[string]<\/span> $PowerBIEndpoint<\/span>,\r\n\r\n[parameter<\/span>(Mandatory<\/span>=$True<\/span>)]\r\n[string]<\/span> $ServicePrincipal<\/span>,\r\n\r\n[parameter<\/span>(Mandatory<\/span>=$True<\/span>)]\r\n[string]<\/span> $ServicePrincipalSecret<\/span>,\r\n\r\n[parameter<\/span>(Mandatory<\/span>=$False<\/span>)]\r\n[string]<\/span> $Query<\/span>\r\n\r\n)\r\n\r\n$assemblyPath<\/span> = \"C:\\Modules\\User\\Microsoft.AnalysisServices.AdomdClient\\Microsoft.AnalysisServices.AdomdClient.dll\"<\/span>\r\ntry<\/span> {Add-Type<\/span> -Path $assemblyPath<\/span>}\r\ncatch<\/span> { $_<\/span>.Exception.LoaderExceptions }\r\n\r\n$Connection<\/span> = New-Object<\/span> Microsoft.AnalysisServices.AdomdClient.AdomdConnection\r\n$Connection<\/span>.ConnectionString = \"Datasource=\"<\/span>+ $PowerBIEndpoint<\/span> +\";User ID=\"<\/span>+ $ServicePrincipal\r\n<\/span>        +\";Password=\"<\/span>+ $ServicePrincipalSecret<\/span>\r\n$Command<\/span> = $Connection<\/span>.CreateCommand();<\/span>\r\n$Command<\/span>.CommandTimeout = 20000;<\/span>\r\n$Command<\/span>.CommandType = [System.Data.CommandType]<\/span>::<\/span>Text;<\/span>\r\n$Command<\/span>.CommandText = $Query<\/span>;<\/span>\r\n$Connection<\/span>.Open()\r\n\r\n$Command<\/span>.ExecuteNonQuery()\r\n\r\n$Connection<\/span>.Close()\r\n$Connection<\/span>.Dispose()\r\n<\/pre>\n<\/div>\n
 <\/p>\n
You can read about how to contribute to the Automation Gallery<\/a> here. I also wrote about this when I was explaining about a script to re-index SQL Server Tables (published to the automation gallery)<\/a>.<\/p>\n
Let’s follow some steps to schedule the script from the gallery in an automation account.<\/p>\n
1) In an automation account,\u00a0 click the Browse Gallery<\/em> button and search for Power BI.<\/strong>\u00a0You will find the script I published, as illustrated on the image below.<\/p>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
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.<\/p>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
3) Click on the Select<\/em> button. You will need to fill the details about the new runbook which will be created.<\/p>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
4) After clicking Import<\/em> button, you can execute the runbook clicking the Start<\/em> button and you will have the opportunity to fill the parameters for the powershell script, as illustrated on the screen below.<\/p>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
5) After that, you just need to follow the execution from the job screen.<\/p>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
Summary<\/h2>\n
Using the script provided in the automation gallery is even easier to automate a refresh or other administrative tasks in Power BI<\/strong> with XMLA<\/strong> scripts<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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….…<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159166],"tags":[145828,145827,101611,4635],"coauthors":[6810],"class_list":["post-93579","post","type-post","status-publish","format-standard","hentry","category-blogs","category-powerbi","tag-automation-account","tag-data-refresh","tag-power-bi","tag-powershell"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93579","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93579"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93579\/revisions"}],"predecessor-version":[{"id":94176,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93579\/revisions\/94176"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93579"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93579"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93579"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93579"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}