Forum Discussion

mnd's avatar
mnd
Regular Visitor
1 year ago

Execute queries using Power BI API - authentication issue

Hi All,

 

I am trying to execute a query in a notebook in Fabric using the Power BI rest.

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

 

I have successfully retrieved an access token and I am able call operations such as get dataset but when I try to perform an execute query operation I get an 401 unauthorized error. Both my service principal and the API has acces to the dataset and "Semantic Model Execute Queries REST API" is enanbled in the tenant settings. Are there any other permissions I should be aware of? 

 

Get datasets works perfecty:

header = {'Authorization': f'Bearer {access_token}'}

partitions_url = 'https://api.powerbi.com/v1.0/myorg/groups/' + WorkspaceID + '/datasets/' + DatasetID
response_tables = requests.get(url=partitions_url, headers=header)

 

Execute queries returns unauthorized access:

header = {'Authorization': f'Bearer {access_token}'}
partitions_url = 'https://api.powerbi.com/v1.0/myorg/groups/' + WorkspaceID + '/datasets/' + DatasetID + '/executeQueries'
query_tables =
{
"queries": [
{"query": "EVALUATE INFO.TABLES()"}
]
}
response_tables = requests.post(url=partitions_url, headers=header, json=query_tables)

 

Need Help

5 Replies

  • mohitkumawat's avatar
    mohitkumawat
    Impactful Individual
    1 year ago

    Hi, 

    please check your App has added in worskpace as admin and provide deligated permission to the App  of Dataset.ReadWrite.All or Dataset.Read.All.

     

    The user must have dataset read and build permissions. For more information, see Manage dataset access permissions.

  • ibarrau's avatar
    ibarrau
    Super User
    1 year ago

    Hi. As mohitkumawat says, check the permissions at the registered app and make sure the service principal has build permissions.
    In addition to his comment, check the limitations. https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group#limitations

    One that can make a problem here is using service principal to execute a query to a semantic model that has RLS. That can't be done for example. Try running simple evaluates to more than one workspace just in case the one you are querying has an issue.

    I hope that helps,

  • mnd's avatar
    mnd
    Regular Visitor
    1 year ago

    Hi mohitkumawat and ibarrau 

     

    Thanks for your inputs. The service principal has build permissions and the model does not have RLS. 

    I have tried to create a new plain semantic model in the same workspace with no data sources. I am able to execute queries using the api for this dataset, but as soon as I added a data source (dimension tables from another workspace) I cannot execute queries anymore. Could it be due to missing permissions in the workspace with my data source?

    • ibarrau's avatar
      ibarrau
      Super User
      1 year ago

      The words "Added a data source (dimension tables from another workspace) sounds like a live connection or composite with direct query. I don't think that's supported. If you can execute queries with one dataset and you can't with another. Then it's permissions or limitation with the semantic model. Usually this works against import models, so data sources shouldn't be a problem. The permissions would be "build for a semantic model".

      I hope that make sense.

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Remember that for composite models the user needs to have "registered/installed" each of the participating apps.