how to call power bi rest apis from fabric data pipelines.
introduction.
Inspired by Gerhard Brueckl’s article on Querying Power BI REST API using Fabric Spark SQL, I thought I’d provide a walkthrough on how to call Power BI REST APIs from Fabric Data Pipelines. Especially the way of authenticating has changed since Data Pipelines and Power BI both live in Fabric. Before, we were able to leverage Azure Data Factory’s Managed Identity to access a Power BI Workspace, but now things work differently. With Fabric’s equivalent Data Pipelines, you specify the authentication method already in the connection itself.
Also, at the time of writing this post, Fabric is still in preview, meaning things can and will change. If the suggested approach does not work on your end or you have a better practice, please let us know in the comments below.
[Edit 2023-11-17 – literally a day after I released this blog post, Fabric was announced to become GA. They also pushed the Fabric API suite to Public Preview. If you are interested in learning how to call fabric rest apis from fabric data pipelines, just click here]
[Edit 2024-04-15 – last week a new Fabric pipeline activity was released into preview, which triggers a Power BI semantic model refresh. Its capabilities seem still be limited to for the refresh of the whole semantic model, but there might be other operations available from this activity in the future]
prerequisites.
1. A Fabric capacity and workspace
2. A Data pipeline
1. What’s the goal?
The goal is to be able to call Power BI REST APIs from a Fabric Data Pipeline. As seen in the example below, we called the Get Group API returning a workspace.
2. Set up a new connection
At first, we need to set up a new web V2 connection that will be used to call the API. For this, click on the settings icon in the top right and then Manage connections and gateways.
Next, click on + New
Now, select the Cloud option, provide a name and fill in the other fields according to the picture. When you are done, press the Create button.
Connection type: Web V2
Base Url: https://api.powerbi.com/v1.0/myorg
Token Audience Url: https://analysis.windows.net/powerbi/api
For the Authentication method, I selected OAuth 2.0. You need to provide your credentials by clicking on Edit credentials in order to be able to create the connection. Here you log in as a user, and then the data pipeline authenticates to Fabric on your behalf. In a real life example, I highly recommend to use a service principal instead of the OAuth 2.0 method. In the appendix, I listed the steps necessary for using a service principal for authentication. Also, check out this blog article, if you are in need for Resource / Audience Uris for other Azure data sources / destinations.
3. Add Web Activity to your Fabric Data pipeline
Now, add a web activity, pick the recently created connection, add “/groups” to the Relative URL and you are good to go. Of course, for other APIs you need to use another Relative URL and for more advanced APIs like POST or PUT, you also need to fill the body. By the way, for the error “The request entity’s media type ‘text/plain’ is not supported for this resource”, try adding a header with the name content-type and the Value application/json
4. Showtime
When running the pipeline with the above settings, the workspace is returned in the output.
end.
Ok, so what’s next? One of the standard use cases of data pipelines and Power BI REST APIs is the triggering of a dataset/semantic model refresh after a pipeline run. Admittedly, this has become obsolete with Fabric’s powerful Direct Lake feature. Or has it? Well, there are actually situations where you still wanna trigger a refresh manually. Also, you could use this REST API setup in order to pre-warm the cache, i.e. with the ExecuteQuery API call. If you have other ideas or have already implemented a solution where you used these APIs from a data pipeline, feel free to leave a comment. It’s always great to know what and how people build stuff!
appendix.
If you wanna use a service principal for authenticating to Fabric/Power BI, you first need to create a service principal in Azure. For this, go into the Azure Portal and create a new app registration. Note, there is no need to setup any scopes/API permissions.
Now, paste the Tenant ID, the Service principal ID and the Service principal key into the boxes:
The Tenant ID (or Directory ID) can be found in the Overview tab of your service principal. The Service principal ID equals the Application or Client ID:
The Service principal key is a secret you need to create under Certificates & secrets:
Also, you have to allow Service Principals to use REST API calls. You can find and enable this setting in the Admin portal in Fabric. Note, it is probably a good idea to not apply this configuration to the entire organization. It is better to use dedicated security groups for such matters.
And lastly, we need to add the service principal to our fabric workspace at least as a contributor:
Helpful info—keep up the good words.
Thanks Wadood! :)