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.

TSQL Tuesday #127 – Non SQL Tips and tricks

In case you haven't seen T-SQL Tuesday, this is a blog party created by Adam Machanic (b | @AdamMachanic) and now maintained by Steve Jones (b | @way0utwest). Each month someone hosts the party and comes up with a subject for all of us to blog about. Then anyone who is interested gets to blog on this same subject. The host also puts all the post for the month in a recap post.

This month our host is Kenneth Fisqher (the q is silent) (b | @sqlstudent144) and he’d like us to talk about non SQL tips and tricks.

Windows

Task View

I love using Task View, mostly during presentations, but also sometimes during my work when I'm developing.

You can start using Task View by clicking the button somewhere near the Start button. Depending on your settings, the search bar could also be there, or Task View might not be visible at all.

In the latter case, right-click anywhere on the task bar (not on an application) and check Show Task View button.

When you then press the button on the task bar (or WIN + TAB) the Task View opens, where you can add new or delete current Desktops. You can also drag applications from their current Desktop to another one.

As I mentioned I always use this when I'm presenting. I have my regular stuff from work still open in Destkop 1, everything I need for my demo's in Desktop 2 and my presentation in Desktop 3.

This way I can easily switch from my presentation (in presentation mode) to my demo's in Desktop 2 and back to my current slide in the presentation. All without the need to escape out of the presentation (or ALT+TAB and search for the right window) and without the need to start the presentation again after my demo. It's all just waiting there in presentation mode for me to come back! :-) 

If you are also into shortcuts, here are a few, of which the 3rd (switch the desktop) is the most important in my presentations:

• Task view: WIN + TAB – New Task view opens up and stays open.
• Create new virtual desktop: WIN + CTRL + D
• Switch virtual desktop: WIN + CTRL + LEFT or RIGHT
• Close current virtual desktop: WIN + CTRL + F4

ALT+TAB variant

Ok, I'll be honest, I use ALT+TAB very often. But it turns out there's also an alternative (and more) to that.

• WIN + [1-9]
• WIN + SHIFT + [1-9]

WIN + [1-9] switches between applications on the taskbar.

That can mean 2, or actually 3 things:

• if one or more instances of the application are already open, it will immediately switch to that (first instance of the) app
• if multiple instance are open, by holding WIN and pressing [1-9] again, it will cycle through the open instances (as long as you hold WIN). On release of WIN it will switch to that (then active) instance. Pressing SHIFT + [1-9] cycles through the open instances in reverse order.
• if no instance is open yet, it will open an instance of that app
• if that app is the current active instance, it will be minimized (unless it's a multi-instance app, then it will do nothing :-))

WIN + SHIFT + [1-9] opens a new instance, regardless of now many instances are already open.

Teams

In the current times I'm am using Teams more and more, so I thought I'd also add a few of my favorite shortcuts here:

• CTRL + Period (.): Bring up all the keyboard shortcuts
• CTRL + Equals sign (=): Zoom in (for example in a meeting where content is shared)
• CTRL + Minus sign (-): Zoom out
• CTRL + [1-6]: Open up the corresponding tab on the left side
• CTRL + SHIFT + M: Toggle mute in a meeting
• CTRL + SHIFT + O : Toggle video in a meeting
• Up arrow: not a shortcut per se, but very handy: if you just typed a message in a chat and press the up arrow afterwards you immediately go to edit mode of that message.

Have a look at all the keyboard shortcuts for Microsoft Teams here, for example also when you're in the Web app instead of the Desktop app.