Available options for connecting to Power BI Semantic Model for writing formula results to SQL Table

[Gabry]

Hello everyone,

I'm exploring ways to connect Power BI's Semantic Model to a SQL table to write formula results directly. Could anyone shed some light on how to achieve this?

What are the available options?
I saw xmla endpoint with premium license.

Anyother options? Like with datafactory or powerautomate or other option?

Thanks!

[ibarrau]

Hi. The only alternative from that I can think about is using the PowerBi Rest API. You can run dax query to semantic models. Using this request: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group

I hope that helps,

[Gabry]

I tried but do you need premiu capacity? I keep getting an authorization error but if I try the refreshdataset api everything works fine.

This is the error i'm getting:

Response status code: 401 Response content: {'error': {'code': 'PowerBINotAuthorizedException', 'pbi.error': {'code': 'PowerBINotAuthorizedException', 'parameters': {}, 'details': [], 'exceptionCulprit': 1}}}

I've already enabled from the admin portal to use api rest 

[ibarrau]

Hi. You don't need premium. I have just tried at a shared capacity workspace and worked.

Make sure you have all the permissions configured properly

The tenant setting Dataset Execute Queries REST API, found under Integration settings, must be enabled.

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

Regards,

[Gabry]

This is driving me crazy. How is it possible that when I call the API to refresh the dataset, it works perfectly fine, but when I try to read from the API, it returns a PowerBINotAuthorizedException?

[Gabry]

I enabled rest api from admin portal
I've tried many things I also made another app with no permissions because I read that need to be done like this.

What access method did you use? I'm using service principal
I keep getting the same error but if I try to just refresh the dataset it works.

this is the python code:

def leggi_dati(dataset_id, headers, workspace_id😞

    relative_url = f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/executeQueries"

    body = {

        "queries": [

            {

                "query": "EVALUATE VALUES(INDICI)"

            }

        ],

        "serializerSettings": {

            "includeNulls": True

        },

        "impersonatedUserName": "-------------"

    }

    response = requests.post(relative_url, headers=headers, data=json.dumps(body))

    if response.status_code == 200:

        print(f"Data read successfully from dataset {dataset_id}.")

        print("Response content:", response.json())

    else:

        print(f"Failed to read data from dataset {dataset_id}.")

        print("Response status code:", response.status_code)

        print("Response content:", response.json())

        print("Status code:", response.status_code)

        print("Headers:", response.headers)

        print("Content (text):", response.text)

        print("Content (binary):", response.content)

        print("Final URL:", response.url)

[ibarrau]

This request it's a little big more complex. I'm using SimplePBI library (python lib) to test it.

If you are using service principal make sure the admin tenant setting Allow service principals to use Power BI APIs under Developer settings is enabled. However, regardless of the admin tenant setting, Service Principals aren't supported for datasets with RLS per RLS limitations or datasets with SSO enabled.

from simplepbi import token
from simplepbi import datasets

TENANT_ID = "XXX-XXX-XXX-XXX"
power_bi_client_id = "XXX-XXX-XXX-XXX"
power_bi_secret = "*************"
workspace_id = "XXX-XXX-XXX-XXX"
dataset_id = "XXX-XXX-XXX-XXX"

t = token.Token(TENANT_ID, power_bi_client_id, None, None, power_bi_secret, use_service_principal=True)
d = datasets.Datasets(t.token)

response = d.execute_queries_in_group(workspace_id, dataset_id, query="EVALUATE Orders", return_pandas=True)

I hope that make sense

[Gabry]

I attempted utilizing SimplePBI with a different workspace, and it functioned properly! When employed in a standard workspace, everything operates as expected. However, when attempting to utilize it in a workspace that has been assigned an embedded capacity, it fails to function, despite all settings being identical. The client ID, access to the workspace, and tenant settings are all consistent. Are you aware of any distinctions between a typical Power BI Pro workspace and one with an assigned embedded capacity? Both workspaces exist within the same tenant, and the configurations pertaining to apps, client secrets, service principals, etc., are identical.

[ibarrau]

Hi. I'm happy to hear that you were able to make it work in a workspace. Now you have the configuration. I haven't read anything about an issue with embedded. I also never tried. Maybe you could take a look at the limitations of the request just in case the dataset you want to query is the one with the issue and it's not a workspace situation.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group#limitations

In addition, if the workspace is embedded, then you can connect with third party tools like DAX Studio to the XMLA endpoint to query the data with DAX. If I recall correctly, there is even a python library to query XMLA.

I hope that helps,

[Gabry]

Tried using this library. This is the error i get:

ERROR 403 Client Error: Forbidden for url: https://api.powerbi.com/v1.0/myorg/groups/--------/datasets/---------/executeQueries