mark_ott
Databricks Employee
Databricks Employee

‎11-09-2025 06:34 AM

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
0 Kudos