Redgate logo
  • Products
    • All products
    • Automate database deployments
      • Redgate Deploy
    • Monitor performance and availability
      • SQL Monitor
    • Standardize team-based development
      • SQL Toolbelt
      • SQL Prompt
      • SQL Search
      • Flyway
      • SQL Source Control
      • Source Control for Oracle
      • SQL Compare
      • Schema Compare for Oracle
      • SQL Data Compare
      • Data Compare for Oracle
    • Protect and preserve data
      • SQL Provision
      • Data Masker
      • SQL Clone
      • SQL Backup
      • SQL Data Catalog
    • .NET
      • .NET Developer Bundle
      • .NET Reflector
      • ANTS Performance Profiler
      • ANTS Memory Profiler
      • SmartAssembly
    • All products
      • .NET Developer Bundle
      • .NET Reflector
      • ANTS Memory Profiler
      • ANTS Performance Profiler
      • Data Compare for Oracle
      • Data Masker
      • Flyway
      • Redgate Deploy
      • Schema Compare for Oracle
      • SmartAssembly
      • Source Control for Oracle
      • SQL Backup
      • SQL Clone
      • SQL Compare
      • SQL Data Catalog
      • SQL Data Compare
      • SQL Data Generator
      • SQL Dependency Tracker
      • SQL Doc
      • SQL Index Manager
      • SQL Monitor
      • SQL Multi Script
      • SQL Prompt
      • SQL Provision
      • SQL Scripts Manager
      • SQL Search
      • SQL Source Control
      • SQL Test
      • SQL Toolbelt
  • Solutions
    • Overview
    • By Need
      • Standardize
      • Automate
      • Monitor
      • Protect
    • By Role
      • Development
      • Operations
      • IT Management
      • Enterprise leadership
    • By Industry
      • Tech
      • Finance
      • U.S. Federal
      • Australian Government
      • Healthcare
      • Manager Service Providers
    • Resources
    • Maturity assessment
  • Our Company
    • Overview
    • Careers
    • Contact us
    • Redgate blog
    • Press kit
  • Support
  • Learning & community
    • Learning & community | Redgate Hub
    • Product articles
    • University
    • Events
    • Forums
    • Community
    • Simple Talk
  • Search
  • My account
  • Shopping cart
  • Buy now
    • See all products
    • Request a quote
    • Contact sales
    • Your account
  • Products
    • All products
    • Automate database deployments
      • Redgate Deploy
    • Monitor performance and availability
      • SQL Monitor
    • Standardize team-based development
      • SQL Toolbelt
      • SQL Prompt
      • SQL Search
      • Flyway
      • SQL Source Control
      • Source Control for Oracle
      • SQL Compare
      • Schema Compare for Oracle
      • SQL Data Compare
      • Data Compare for Oracle
    • Protect and preserve data
      • SQL Provision
      • Data Masker
      • SQL Clone
      • SQL Backup
      • SQL Data Catalog
    • .NET
      • .NET Developer Bundle
      • .NET Reflector
      • ANTS Performance Profiler
      • ANTS Memory Profiler
      • SmartAssembly
    • All products
      • .NET Developer Bundle
      • .NET Reflector
      • ANTS Memory Profiler
      • ANTS Performance Profiler
      • Data Compare for Oracle
      • Data Masker
      • Flyway
      • Redgate Deploy
      • Schema Compare for Oracle
      • SmartAssembly
      • Source Control for Oracle
      • SQL Backup
      • SQL Clone
      • SQL Compare
      • SQL Data Catalog
      • SQL Data Compare
      • SQL Data Generator
      • SQL Dependency Tracker
      • SQL Doc
      • SQL Index Manager
      • SQL Monitor
      • SQL Multi Script
      • SQL Prompt
      • SQL Provision
      • SQL Scripts Manager
      • SQL Search
      • SQL Source Control
      • SQL Test
      • SQL Toolbelt
  • Solutions
    • Overview
    • By Need
      • Standardize
      • Automate
      • Monitor
      • Protect
    • By Role
      • Development
      • Operations
      • IT Management
      • Enterprise leadership
    • By Industry
      • Tech
      • Finance
      • U.S. Federal
      • Australian Government
      • Healthcare
      • Manager Service Providers
    • Resources
    • Maturity assessment
  • Our Company
    • Overview
    • Careers
    • Contact us
    • Redgate blog
    • Press kit
  • Support
  • Learning & community
    • Learning & community | Redgate Hub
    • Product articles
    • University
    • Events
    • Forums
    • Community
    • Simple Talk

OVERVIEW

  • Compliant Database DevOps

BY NEED

  • Standardize team-based development
  • Automate database deployments
  • Monitor performance and availability
  • Protect and preserve data

BY ROLE

  • Development
  • Operations
  • IT Management
  • Enterprise leadership

BY INDUSTRY

  • Tech
  • Finance
  • U.S. Federal
  • Australian Government
  • Healthcare
  • Managed Service Providers

Redgate Hub

  • Product articles
  • University
  • Events
  • Forums
  • Community
  • Simple Talk
  • Home
  • Cloud
  • DevOps
  • Sysadmin
  • Development
  • Databases
  • Opinion
  • Books
  • Blogs
  • Log in
  • Sign up
Dennes Torres
Dennes Torres

02 May 2022

  • 1
  • 860 views

  • 0
    • Printer friendly version
  • Home
  • Blogs
  • Template for automating Power BI Refresh
Dennes Torres
Dennes Torres

02 May 2022

860 views

1
0

Template for automating Power BI Refresh

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

 

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Subscribe to our fortnightly newsletter

  • 1
  • 860 views

    • Printer friendly version

Rate this article

[Total: 1    Average: 5/5]

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Subscribe to our fortnightly newsletter

Dennes Torres

Dennes Torres

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com

Follow Dennes on

View all articles by Dennes Torres

Load comments

Related articles

Dennes Torres
Dennes Torres
25 April 2022
Dennes Torres
Dennes Torres
25 April 2022

Power BI Last Refresh Time Visualization Tool

0
2
  • Blogs
On the article Automating table refresh in Power BI I explained many methods to automate refresh of individual objects, which could be tables or partitions. This creates the need of good ways to visualize the last refresh date and time for each partition and table. The portal shows the refresh date/time for the entire dataset, … Read more
0
2
  • Blogs
Dennes Torres
Dennes Torres
07 March 2022
Dennes Torres
Dennes Torres
07 March 2022

Power BI: ETL or not ETL, that’s the question

0
23
  • Blogs
ETL stands for Extract, Transform, and Load. Dennes Torres explains what an ETL tool is and isn't.… Read more
0
23
  • Blogs
Dennes Torres
Dennes Torres
04 April 2022
Dennes Torres
Dennes Torres
04 April 2022

Updating Powershell version in Azure CloudShell

0
2
  • Blogs
Azure Cloudshell uses PowerShell 7.2.1. However, Powershell 7.2.2 is already available and we have also a PowerShell version 7.3.0 in preview. Wouldn’t it be nice if you could update the powershell version in cloudshell, instead of waiting for Microsoft? This would allow you to run scripts using the new features of the updated versions. Before … Read more
0
2
  • Blogs

Tags

Automation Account, Data Refresh, Power BI, Powershell

Simple Talk

  • FAQ
  • Sitemap
  • Write for Redgate
  • Contact Us

The Redgate Update Get the latest news and training with the monthly Redgate Update Sign up

Products

  • Automate
  • Monitor
  • Standardize
  • Protect & preserve

Support

  • Forums
  • Contact product support
  • Find my serial numbers
  • Download older versions

Solutions

  • Overview
  • Maturity Assessment

Our Company

  • Careers
  • Contact us
  • Redgate Blog
  • Our values

Community & Learning

  • Product Learning
  • University
  • Events & Friends
  • Simple Talk
  • Books
  • Forums

Partners

  • SQL Server Central
  • Resellers
  • Consulting partners

Privacy & compliance

  • Privacy and cookies
  • License agreement
  • Accessibility
  • Report security issue
  • Modern slavery
  • Gender pay gap report
  • CCPA - Do not sell my data

Follow us

Redgate on Twitter Redgate on LinkedIn Redgate on YouTube
  • Copyright 1999 - 2022 Red Gate Software Ltd