Connect to Sql server analysis services(SSAS) server to run DAX query using python

rbee — Tue, 25 Feb 2025 15:43:51 GMT

Hi,

I have a powerbi server which I'm able to connect through SSMS. I tried using pyodbc to connect to same in databricks, but it is throwing below error.

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

As per what I've researched, powerbi metadata is SSAS and not odbc, so we cant connect through pyodbc. Let me know if my understanding is incorrect.
Please suggest alternative library or approach how to achieve this through python.

Re: Connect to Sql server analysis services(SSAS) server to run DAX query using python

mark_ott — Sun, 09 Nov 2025 14:34:22 GMT

Your understanding is correct: Power BI’s data model is stored in an Analysis Services (SSAS) engine, not a traditional SQL Server database. This means that while SSMS may connect to Power BI Premium datasets via XMLA endpoints, attempting to use pyodbc—which is intended for ODBC/SQL—will not work for SSAS connections and leads to the timeout error you encountered.

Correct Approach for Python Access

To interact with Power BI (or SSAS) datasets in Python, you should use a library that supports the XMLA protocol, such as pyadomd or msolap via ADOMD.NET. The most straightforward Python-native option is pyadomd:

pyadomd: A thin Python library to connect to SQL Server Analysis Services (including Power BI XMLA endpoints) and run MDX/DAX queries.

Example Usage with pyadomd

python

from pyadomd import Pyadomd

conn_str = "Data Source=powerbi://api.powerbi.com/v1.0/myorg/your_workspace;Initial Catalog=your_dataset"
with Pyadomd(conn_str) as conn:
    with conn.cursor().execute('EVALUATE ROW("Result", 1)') as cur:
        for row in cur:
            print(row)

Note:

Replace the Data Source and Initial Catalog values with your actual workspace and dataset names.
For Azure AD authentication, use azure-identity to get a token and append it to the connection.

Additional Notes

pyodbc cannot be used directly to connect to SSAS models or Power BI XMLA endpoints.
For automation in Databricks, ensure the library is installed on your cluster and configure authentication (via service principal or user credentials) as required by your Power BI workspace.

Alternatives

MSOLAP with adodbapi: You could use COM-based libraries like adodbapi on Windows, but this is less common and has more compatibility issues on Databricks.

Summary Table

Library	Use Case	Supported in Databricks	Notes
pyodbc	SQL Databases (not SSAS)	Yes	Not for Power BI/SSAS
pyadomd	SSAS/Power BI XMLA Endpoints	Yes	Recommended
adodbapi/MSOLAP	SSAS (Windows only)	Limited	Not Databricks-native