#MSIgnite 2020 - Power BI Recap

While there were several issues with the first sessions having no video, there was still a lot of excitement around all the announcements being made at Ignite! And luckily we can re-watch those sessions!

As I watched the sessions I made some notes, I cleaned them up a bit and thought I'd share them for anyone that likes to read up.

It's in no way a full update on Power BI related news, but highlights I found interesting. If you think I missed something that should be here, please let me know.

It's full of links to read more, also sometimes [Sessions] are linked where you can have a look yourself!

Enjoy! :-)

Icon or logo

So, what do you think about the new Logo? Or is it an Icon, or a …? :-)

Power BI Desktop September updates

Smart Narratives (September Desktop release)

Search capability for workspaces when publishing a report from Power BI Desktop

A small step for the Power BI team, a giant leap for the #pbifamily! :-)

Visual data preparation (coming soon) in Power Query: I'm really looking forward to this one!

Sessions

Demo by Kim Manis in [Session]Deliver insights where decisions are made with Power BI

• Power BI Service (with the whole left-side menu) rendered inside Teams
• Create quick reports by copy-pasting data

Power BI Premium Gen2

[Session] Advancing Power BI Premium for the enterprise analytics market and beyond

Wow, am I excited about this one! Of course we use Premium, so that's obvious, but also the announcement of Premium Per User is very exciting!

• Power BI Premium Gen 2
• Faster processing of refreshes
• No concurrency limitation
• No cumulative memory limitation, so each refresh can take up 25GB of RAM (for a P1)!
• Premium Per User, available at no cost during public preview, no price announced yet
• Autoscale (optional feature)
• Add (paid) vCores for at least 24h
• Billed through Azure subscription
• Set limits to control cost
• Cost-management on a workspace level
• Utilization breakdown

Have a look at the Power BI blog where Chris Finlan answers the most common questions around Power BI Premium Per User lincensing!

Deployment pipelines

This feature goes GA: read the GA blog, with an important excerpt:

• Using deployment pipelines, you can make updates to a model with incremental refresh configured, and deploy it to production, while retaining both data and partitions!
  *Only Datasets with the ‘Enhanced metadata format’ switch turned on

So, what else is coming? For example, Paginated reports and Dataflows will be supported with deployment pipelines in a later stadium. 

For all updates, I suggest you check the Power BI roadmap wave 2 release notes!

Free Certification exam

And of course, if you are attending Ignite, and complete at least one collection in the Microsoft Ignite Cloud Skills Challenge, you are eligible for a FREE certification exam! Have a look here for the details: Cloud Skills Challenge

Other interesting sessions still to watch:

And if you want to have a complete overview of what has happened at Ignite, not only Power BI, you should read the Ignite Book of News!

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.