topic Re: Troubles with Dataset ExecuteQueries In Group PowerBI API in Developer

Troubles with Dataset ExecuteQueries In Group PowerBI API

sjt2090 — Mon, 08 Jul 2024 12:16:06 GMT

Hello,

I am having trouble executing the "ExecuteQueries In Group" PowerBI API for Datasets.

Here's what I did:

Microsoft Entra ID:

1. Create an App Registration with the name "PowerBI-SP".

2. Created a Client Secret with extended validity. Using the secret value as the client secret.

2. Added the following Delegated API Permissions with no admin consent required: "Dataset.Read.All Dataset.ReadWrite.All"

3. Created a MS Entra Security Group with the name "Group For PowerBI SPs".

4. Added App Registration "PowerBI-SP" as the member of the MS Entra Security Group "Group For PowerBI SPs".

PowerBI Admin Portal:

1. Under "Integration Settings", enabled the "Dataset Execute Queries REST API" option.

2. Applies to "the entire organization". No need to whitelist my MS Entra Security Group.

3. Under "Developer Settings", enabled the "Allow Service Principals to use the PowerBI/Fabric APIs" option.

4. Applies to "Specific Security Groups" with my MS Entra Security Group "Group For PowerBI SPs" whitelisted.

Fabric/PowerBI Service:

1. Added my MS Entra Security Group "Group For PowerBI SPs" as the "Member" of the workspace that I am trying to read the data from.

2. Gave the MS Entra Security Group "Group for PowerBI SPs" the access of "read, build" permissions on the dataset/semantic-model that I am trying to query the data from, using DAX.

Generate Access Token:

1. Token Endpoint V2: https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token

2. Content-Type: x-www-form-urlencoded

3. Request Body: client_id={client_id}&client_secret={client_secret}&grant_type="client_credentials"&scope="https://analysis.windows.net/powerbi/api/.default"

ExecuteQueries in Group API: Datasets - Execute Queries In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

1. Endpoint: https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/executeQueries

2. Authorization: "Bearer {access_token}"

3. Request Body: A valid Json with a valid DAX.

Example: "{
"queries": [
{
"query": "EVALUATE VALUES(MyTable)"
}
],
"serializerSettings": {
"includeNulls": true
}}"

Response:

ERROR: 401 Unauthorized

{

"error": {

"code": "PowerBINotAuthorizedException",

"pbi.error": {

"code": "PowerBINotAuthorizedException",

"parameters": {},

"details": [],

"exceptionCulprit": 1

}

Observations:

1. ExecuteQueries In Group API works with my own Access Token. I am just a member in the Workspace. I have same permissions as the Service Principal.

2. All other Dataset related APIs work with the Service Principal access token, that have read or read-write access on the dataset. Datasets - REST API (Power BI Power BI REST APIs) | Microsoft Learn

2. DAX is valid. I have verified everything with my own access token. It returns the data well within the limitations of the API.

3. I tried generating the access token using both v1 and v2 token endpoints with valid resource and scope values. I keep getting the same error "PowerBINotAuthorizedException". resource_v1=https://analysis.windows.net/powerbi/api, scope_v1="Dataset.Read.All Dataset.ReadWrite.All"

4. Service Principal Manifest has listed entries of all alloted API permissions, stated as above.

5. All Workspace and Dataset permissions have been verified and reverified but to no avail.

6. Added my MS Entra Security Group as the Admin of the workspace as well with all permissions on the dataset to no avail.

7. All ids used in this process are correct, be it dataset id, group id, client id, etc.. I have verified 100 times.

8. Both RLS and SSO are disabled on the dataset.

9. Also tried giving "SemanticModel.Read.All", "Semantic.Model.ReadWrite.All" and just about any other permission that does not require admin consent to no avail.

I followed all instructions presented in the MS Docs and some, still getting the same error.

What am I missing?

Any help would be appreciated.

Thanks

Re: Troubles with Dataset ExecuteQueries In Group PowerBI API

lbendlin — Mon, 08 Jul 2024 22:30:46 GMT

Take your access token and run it through jwt.io - most likely your scope request Dataset.Read.All didn't make it through.

Re: Troubles with Dataset ExecuteQueries In Group PowerBI API

sjt90 — Tue, 09 Jul 2024 04:34:05 GMT

@lbendlin That's right. There wasn't an "scp" claim in the token.

1. But it must be noted that the same token is able to query the data using DAX from a "My Workspace" using "ExecuteQueries (Not Group) API" where the SP has "read, build" permissions on the dataset.

2. I also uploaded another semantic model, a very basic one to my dataset under the same premium workspace from where I have not been successful in querying the data, as highlighted in the question. I decided to remove "Member" permission entirely from the Workspace for my MS Entra ID Security Group "Group for PowerBI SPs" and gave direct access to my MS Entra ID Security Group on the new semantic model or the dataset with "read, build" permissions. And it worked!! with "ExecuteQueries (Not Group) API" even without "scp" claim in the token.

Now I wonder if the semantic model which I am not able to query, is the problem here... Need to investigate further.

In the meantime, I would really appreciate it if you could please elaborate on any remedies regarding the "scp" claim that could be of help here. Thanks!

Re: Troubles with Dataset ExecuteQueries In Group PowerBI API

sjt90 — Tue, 09 Jul 2024 05:31:13 GMT

@lbendlin I think I found the issue!

Most of our Semantic Models has a live "Analysis Services" connection to a common Semantic Model in the same premium workspace. As per the limitations of the ExecuteQueries API, both Group and Non Group,
"Datasets that are hosted in Azure Analysis Services or that have a live connection to an on-premises Azure Analysis Services model aren't supported".

Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Datasets - Execute Queries In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Neither our dataset has a live connection to the on-prem azure analysis services model, nor any of our datasets hosted in Azure Analysis Services.

But as we all know, the underlying engine of PowerBI service workloads uses Analysis Services which is not so different from Azure Analysis Services, if I remember correctly. And maybe that's why I get "PowerBINotAuthorized" exception only for those datasets that have "Analysis Services" live connection to another dataset in the same workload.

Thoughts?

Re: Troubles with Dataset ExecuteQueries In Group PowerBI API

lbendlin — Tue, 09 Jul 2024 10:34:41 GMT

Did you give build access through the app or through the workspace? App set to auto install?

Re: Troubles with Dataset ExecuteQueries In Group PowerBI API

sjt90 — Tue, 09 Jul 2024 11:35:38 GMT

Issue confirmed! Analysis Services connections are causing the API to fail. These connections are basically created to connect to other datasets in the same premium workspace.

Regards