How to rebind Power BI reports to semantic models?

Posted by:

Datasticot

|

On:

|

You often want to change the source of a Power BI report to another semantic model: a newer version of the semantic model was published or you just want to easily switch between the development and production semantic models for instance.

Usually to do so, you would just download the report as a pbix file and change the source semantic model from the Data source settings menu. But what if you can’t download the pbix file? The underlying semantic model could have been updated through the XMLA endpoint, the large semantic model format or incremental refresh could have been set up or the report could have been created or copied from an existing report directly online. What to do then?

What’s odd is that the graphical interface of the Power BI Service doesn’t help you there. That’s were Power BI REST APIs come to the rescue!

Power BI REST APIs

Application Programming Interfaces (or APIs in short) are a way for two systems to exchange information. When these APIs comply with a specific set of rules, they are called RESTful APIs. See What is a REST API? for additional details about APIs.

Power BI REST APIs let you manipulate Power BI objects (reports, semantic models, dashboards, …) programmatically. This can speed up pretty boring and time-consuming administrative tasks (like listing what reports exist on which workspaces or who has access to which reports and so on, you get the idea).

Today, we’ll see how to use these REST APIs to rebind a Power BI report to a new semantic model. For this without suprise, we’ll use the: Rebind Report API. To be able to use these Power BI REST APIs a bit of set up is needed. Let’s go through that set up step by step.

Configuration Steps

Here’s an overview of the steps that we’ll have to go through:

  1. Creating a Service Principal in Azure and putting it in a security group
  2. Granting Service Principals rights to use Power BI REST APIs and workspace access
  3. Getting a token for using Power BI REST APIs
  4. Using the rebind API to rebind the report to a new semantic model

Let’s get started!

1. Service Principals

Service Principals you said?

Ok I hear you. You’re like: “What the hell is a Service Principal and why do I even need one?”. I feel like the Microsoft documentation fails to explain in simple terms what a Service Principal is so here goes. A Service Principal is simply an identity that represents an application and that can be given some specific rights. An identity for a program in short.

Now why do we need one? Well to use Power BI REST APIs you need to be authenticated and authorized to do so. I think you’d be mad if I had the right to delete all the reports in your tenant using the APIs. To be authenticated and authorized, you need a token to give to the API. To get this token, you have two ways:

  • Using Scopes and user identities
  • Using Service Principals

I think you now which method we’re going to use (if you don’t, just reread everything from the beginning). But why are we chosing Service Principals over Scopes? The answer to that question is not so simple and depends on many factors. Here are the main things to have in mind.

  • Scopes let you limit quite strongly what actions you’re going to be able to perform using APIs: maybe only list reports and semantic models and that’s it. In addition, you’ll authenticate using a user identity, meaning the user using the API will only have access to objects in workspaces in which he himself has access to.
  • On the contrary, Service Principals cannot be limited in terms of what APIs they’ll be able to use (apart from Admin APIs which are handled separately). With them, a user can query objects that he could not access as long as the service principal can.

We’re choosing Service Principals here because they’re better for automation and we’ll pretend that in our use case, our user should be able to rebind reports that they don’t have access to (this is debatable, feel free to).

See the Microsoft documentation for additional info: https://learn.microsoft.com/en-us/rest/api/power-bi/.

Creating a Service Principal

Service Principals are created in Microsoft Entra ID (or Azure Active Directory for old people). You need pretty elevated rights for this, so ask your closest IT Admin friend for help if need be. Over in Entra, go to App registrations and click New registration. This creates a Service Principal.

Simply give your Service Principal a friendly name over in the next page. Nothing else needs to be configured.

The next thing you will need is to create a secret associated with that Service Principal (secrets act as passwords for Service Principals). Keep in mind that secrets only last for two years at maximum and will then to be regenerated when expired.

To create the secret, head over to the Certificates & secrets page and click New client secret. Provide a description and set the expiration date. Save the Value just after creating the secret, it’s gone forever after that!

For automation, I suggest keeping the secret value in an Azure Key Vault. For manual use, simply save the value in your favorite secret secure vault.

Save the Client ID and Tenant ID from the Overview page as well, we’ll need them afterwards. Similarily, for automation, consider using Azure Key Vault.

Note: make sure you save these three information: tenant ID, client ID and client secret in a secured manner. Anyone with these three information can potentially do anything on your Power BI tenant (granted that steps performed on step 2 have been done).

Last, we’ll put that Service Principal in an Microsoft Entra Security Group, so that we only grant access to Power BI REST APIs to that group instead of everyone in our company. Head over to the Groups page and click New group. Give the new group a name, an owner and put the created Service Principal in it as a member.

Ok, we’re finished with the Azure Portal. Let’s head over to Power BI now.

2. Granting Service Principals rights

There are two things our Service Principal will need to use Power BI REST APIs: the right to use the APIs (duh) and workspace access.

To give the right to use REST APIs, you’ll need admin rights on the Power BI tenant. Once you do (or once you’ve found a friend who does) head to the admin portal and look for the setting called: Service principals can use Fabric APIs (yeah, Power BI is now part of a broader Fabric ecosystem so the setting name changed). Make sure this setting is selected only for Specific security groups and add your recently created group.

Then grant at least Contributor access to the workspaces your Service Principal should be able to access. Grant the right to the group instead of the Service Principal Directly.

3. Getting the API token

Everything we did so far was only for one thing: generating a token to use against Power BI REST APIs with the correct rights. Let’s see how to generate the token.

To generate the token, we’ll need to make a first API call to the token API.

You have many ways to make APIs calls using libraries from your favorite development language (Powershell, Python, …), web clients or dedicated software. I’ll use Postman for this, it’s pretty easy to use and nice for debugging. Postman comes in both a web client and a software to install. I’m using the free web client for this demo.

If I had to industralize this process, I’d use Powershell or Python scripts.

Over in Postman, create a New collection and a New request. Configure the API call as follows:

  • Method: POST
  • URL: https://login.microsoftonline.com/{{tenantid}}/oauth2/token — replace tenantid by the value you saved earlier
  • Body:
    • grant_type: client_credentials
    • client_id: {{client_id}} — your Service Principal Client ID
    • client_secret: {{client_secret}} — your Service Principal secret value
    • resource: https://analysis.windows.net/powerbi/api

Send the request. This should give you an access token if you’ve configured everything properly. The access token will be usable for one hour. Save that access token.

4. Rebind report to new semantic model

Now that you have an access token configured with the proper rights, you can do a lot of things. Get the list of all reports in a workspace, get the list of users with specific access and so one. For now, we’ll simply rebind a report to use a new dataset.

Let’s say I’ve got a report called Report which is connected to a semantic model called Semantic model 2. I want to rebind Report to Semantic model 1, but I can’t download the Report as a pbix file.

Let’s create our second Postman request to rebind that report. Create a new request with the following information:

  • Method: POST
  • URL: https://api.powerbi.com/v1.0/myorg/groups/{{groupid}}/reports/{{reportid}}/Rebind — groupid is the workspace id the report is in and report id is the report id of the report. Both information be found in the url of the page when you open the report.
  • Authorization:
    • Auth Type: Bearer token
    • Token: access token value from the previous API call
  • Body: {“datasetId”: <datasetid>} — replace <datasetid> with the id of the new dataset to bind the report to, put the dataset id between double quotes

Once you’ve done that, your report should have been rebinded to Semantic model 1.

Conclusion

In this post, we’ve seen how to create and configure Service Principal authentication to use Power BI REST APIs. We then illustrated the use of rebinding Power BI reports to semantic models, a task that can only be done using APIs when the report cannot be downloaded.

Thanks for reading, stay tuned!

Posted by

Datasticot

in