How to connect to Power BI XMLA Endpoint using InteractiveBrowserCredential in python?

Question

I am trying a approach to use the token generated using InteractiveBrowserCredential to connect to Power BI XMLA Endpoint. I was able to connect to the model using pyadomd locally but the same won't work with the Power BI Service models

import pandas as pd
from azure.identity import InteractiveBrowserCredential
from sys import path
path.append(r'C:\Program Files\Microsoft.NET\ADOMD.NET\150')
from pyadomd import Pyadomd
import requests

# Azure AD credentials


# Authenticate and get access token
def get_access_token():
    credentials = InteractiveBrowserCredential()
    scopes =  "https://analysis.windows.net/powerbi/api/.default"  # Scope for Power BI XMLA
    token = credentials.get_token(scopes)
    return token.token

# Get the access token
access_token = get_access_token()

# Connection string with access token
conn_str = f"Data Source=powerbi://api.powerbi.com/v1.0/myorg/MY-%20TM;Initial Catalog=My Dataset;User ID=;Password={access_token};"

# Enter DAX or MDX query
dax_query = """EVALUATE INFO.VARIATIONS()"""

# Output results as pandas dataframe
with Pyadomd(conn_str) as conn:
    with conn.cursor().execute(dax_query) as cur:
        df = pd.DataFrame(
            cur.fetchall(),
            columns=[i.name for i in cur.description]
        )
        print(df)type here

your service principal or interactive token access is explicitly enabled for XMLA read/write connections in the dataset settings? — Mihir Saxena
– Mihir Saxena, Commented May 8 at 15:56
No I haven't created any service principals. But I was able to sign in to azure using pyadomd in local desktop. — Jai R
– Jai R, Commented May 8 at 20:03
Try by adding Integrated Security=ClaimsToken; to your connection string. And the token must be passed via User ID=; Password=<OAuth Access Token>. — Balaji
– Balaji, Commented May 9 at 6:50
Hi Balaji, tried the below connection string f"Data Source=powerbi://api.powerbi.com/v1.0/myorg/FinanceOnly;Initial Catalog=Financial Analysis;User ID=;Password={access_token};Integrated Security=ClaimsToken;" Got the same error "The remote server returned an error: (401) Unauthorized." — Jai R
– Jai R, Commented May 10 at 7:13
As it shows 401 error, that XMLA read/write access is not enabled for your user or token in the dataset settings on the Power BI Service side. Enable XMLA Read/Write in Power BI Dataset Settings and ensure the Dataset Is in a Premium Workspace Only Power BI Premium or Power BI Embedded (A-SKU / PPU) workspaces support XMLA endpoint connections. — Balaji
– Balaji, Commented May 12 at 4:15

Balaji · Accepted Answer · 2025-05-13 12:03:45Z

The remote server returned an error: (401) Unauthorized.

It shows error: (401) Unauthorized is because if Power BI workspace or dataset does not allow XMLA read/write access for your identity even if you're the owner. Check the workspace whether it is Premium or not. Only Premium-capacity workspaces support XMLA endpoint read/write. So, enable XMLA Read/Write Access. If this is disabled, no token will be allowed. Also, check with the connection string, it is missing the value for User ID. Check with all the above mentioned points and try with the below code.

def get_access_token():
    credential = InteractiveBrowserCredential()
    scope = ["https://analysis.windows.net/powerbi/api/.default"]
    token = credential.get_token(*scope)
    return token.token

workspace_name = "FinanceOnly"  
dataset_name = "Financial Analysis"  
access_token = get_access_token()

conn_str = (
    f"Data Source=powerbi://api.powerbi.com/v1.0/myorg/{workspace_name};"
    f"Initial Catalog=<dataset_name>;"
    f"User ID=app;"
    f"Password=<access_token>;"
    f"Integrated Security=ClaimsToken;"
)

dax_query = "EVALUATE ROW(\"Status\", \"Connection successful!\")"

try:
    with Pyadomd(conn_str) as conn:
        with conn.cursor().execute(dax_query) as cur:
            df = pd.DataFrame(
                cur.fetchall(),
                columns=[col.name for col in cur.description]
            )
            print("Query Result:")
            print(df)
except Exception as e:
    print("Error occurred:")
    print(e)

Output:

Query Result:
              Status
0  Connection successful!

Collectives™ on Stack Overflow

How to connect to Power BI XMLA Endpoint using InteractiveBrowserCredential in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related