Query a dataset with Power Bi REST APIs using a Service Principal

Question

Our goal is to query a dataset that is published to PowerBI via the REST APIs ( https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries ). I'm not talking about the metadata of the dataset, I mean the row-level data contained within the tables in the dataset.

We are going to write a service (probably on prem) that will need to query this data, format it, and push it to another system. From what we understood, we could use a service principal as the identity to query the PowerBI API and retrieve the data.

The very important factor in this, is the service principal should not have access to the row level data of any other dataset. If we have to separate the datasets in a different workspace, that is workable, but not preferred.

Answer 1

Service Principal can be used to access that PBI API. It will have access to the data only if it has authorization on that workspace. So you need to separate workspace in order to manage the access of the dataset.

Sample in postman

From my experience, PowerBI execute DAX query can be quite slow. So do keep that in mind if your integration will require a quick response of PBI API.

Answer 2

Also just going through this and found that your service principle not only needs to be granted permission to access the workspace in PowerBI (Manage Access) but it must also be set to 'Admin'.