Refresh a Power BI Dataset with PowerShell and a Service Principal

After Installing the PowerShell Power BI Management cmdlets a few months back, I finally managed to get a dataset refresh working, initiated from PowerShell with a Service Principal. Yeej :-)

Congrats on getting this done! Would you write a blog about this adventure? 🙂

— Chuy Varela (@chuy_varela_) June 12, 2020

But as you can imagine, it didn't go without a fight (or 2, or maybe more), so I thought it would probably be good to share my experiences so you don't fall in the same traps as I did.

Use case

At Van Lanschot Kempen, we are doing daily refreshes of the corporate DWH, connected datamarts and extraction marts after that, all on-premises. Then the Power BI data models are refreshed on a time trigger (say 10 AM) to refresh the data from the datamart to Power BI. We would like that dataset refresh to be event-based. So whenever the datamart is refreshed, immediately refresh the corresponding Power BI dataset.

Setup

To get this working I've used the greatest part of this post How To Trigger A Power BI Dataset Refresh Using An Azure Data Factory Web Activity by Dave Ruijter (blog | @DaveRuijter). Although that's aimed at Azure Data Factory, most of the (setup) steps still apply to my scenario. But as you will see, others don't :-)

Permissions

At my company, I am the Power BI Service Administrator (and Power Platform Admin), so I have sufficient rights in the Power BI Admin Portal, but not in Office 365. So I needed the help of other colleagues who do have these rights.

Make sure that you have the right people aligned, or at least know where to go in case you need anything, before you start.

Steps taken

I'll outline the steps I took here to have a complete list of things I did to accomplish my setup. Again, for detailed explanations per step please visit Dave's blog post:

1. Create a Service Principal and also create a secret with that
2. Create an Azure Active Directory (AAD) group and add the Service Principal from Step 1
3. Enable the tenant setting for Service Principals and add the group from Step 2
   
4. Create a (V2!) Power BI workspace (or use an existing one)
5. Add the Service Principal as an Admin to the workspace
6. Use PowerShell to refresh a dataset

I think Step 6 actually deserves a chapter of its own, while it looks so easy afterwards, I think I struggled with this for more than 20 hours over a few weeks time.

Especially because Dave's setup uses ADF, the authentication to the REST API threw me off. In the end, I "just" had to pass the secret to a PSCredential and use Connect-PowerBIServiceAccount with the Service Principal.

Then the next step is getting an Access token, similar to the setup of using the Power BI REST API as Admin, but now using that header in the Invoke-RestMethod call, as shown below.

You can also get the code from my Github page.

As evidence, I also queried the REST API to show the refresh history :)

Next steps

At this time I do have a working solution, executing the script manually, with my secret in plain text. So there are still a few things to have a look at, which I haven't had the time for yet unfortunately:

• Add the PowerShell script to a SQL Agent Job step, which is the specific use case for me
• Make the solution secure, so don't save sensitive data inside the script or on a server
• One option is to use parameters in SSIS as Brett Powell explains in his post
• I'd like to investigate if I can use the Azure Key Vault from a SQL Agent Job and store the secret there, I'll probably have to authenticate to the Key Vault with the Agent user running the job
• Incorporate the success of the refresh itself into the solution as a step, so I can take action if it fails

Let me know if you have any comments, or know how to improve certain steps of this process :)

Resources

• PowerShell script on my GitHub page
• Dave Ruijter's post: How To Trigger A Power BI Dataset Refresh Using An Azure Data Factory Web Activity
• Brett Powel's post: Refresh Power BI Datasets With PowerShell
  
• Power BI PowerShell commandlets: MicrosoftPowerBIMgmt.Profile
• Power BI REST APIs documentation
  
• Azure Key Vault REST API reference
  

Using the Power BI REST API as Admin

Last month when I was trying to follow the excellent video on Building a Power BI Admin View by Parker Stevens ( b | @PowerBIElite | YouTube).

However, I ran into a few things that I'd like to dedicate this quick post on.

 

Connecting to the API

The first thing was I ran into the following error:

Turns out it had to do with me connecting to the REST API in an earlier session, but with Windows authentication. 

So after clearing the permissions I could login fine after selecting Anonymous:

 

Exceeded Amount of Allowed Requests

Probably because I was not as fast as Parker creating the report, I ran into the following problem :-)

 

Power BI Desktop wil NOT return an error message, instead it will just keep spinning.. The Try It button on the REST API Documentation site will however return a 429 Response code, saying you have exceeded the amount of allowed requests, and I have to wait (approximately 😀) 1256 seconds.

Refreshable Access Token

In Part 2: Obtaining a Refreshable Access Token Parker explains how to get past the point of entering your Bearer token everytime it expires :-)

 

Limit The Number of API Calls

Another important point was made by Marc Lelijveld ( b | @MarcLelijveld), he suggests to use the $expand parameter of the GetGroupsAsAdmin function. That way you don't have to query the API every time per dataset/dataflow/etc.

 

You can check a few examples of the use of $expand at the documentation of the Admin- Groups GetGroupsAsAdmin page.

 

Recap

I hope reading this will help you in better setting up management of your Power BI environment.

Installing the PowerShell Power BI Management cmdlets

So I thought this would be a pretty straightforward task. But because I wrote this short post you probably figured out I ran into a couple of issues trying to install the Power BI Management commands for PowerShell.
I should also mention that I think I can say I'm pretty proficient at using the Power Platform products, but I'm still a beginner in the PowerShell world :-)

Prerequisites

For starters, keep in mind the supported environments and PowerShell versions, luckily I met those:

• Windows PowerShell v3.0 and up with .NET 4.7.1 or above
• PowerShell Core (v6) and up on any OS platform supported by PowerShell Core

Older versions of MicrosoftPowerBIMgmt

The first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. :-)

You can update to the latest version of the Power BI Management module with the following command:

Update-Module -Name MicrosoftPowerBIMgmt

If you want to uninstall all the Power BI PowerShell cmdlets, run the following in an elevated PowerShell session:

Get-Module MicrosoftPowerBIMgmt* -ListAvailable | Uninstall-Module -Force

Untrusted Repository

The second thing that hit me was this message that kept coming back about an untrusted repository PSGallery.

After some searching I found this blog that has a nice solution: adding the PSGallery to the trusted repositories will get rid of the necessary annoying pop-up:
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted

Install-Module

I thought I could finally get around to installing the desired module, but unfortunately I ran into another error:

I also tried installing only the Profile cmdlet, but that didn't help and turned up the same warning error.

Going to the link provided for the Source Location actually downloaded the NuGet-package, so the URL seemed valid, just not in PowerShell.

Another Google search got me the solution. Apparently switching to TLS 1.2 solves the problem, so I did:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12


Now I can finally get around to searching for one of my workspaces with PowerShell :-)

@NickyvV