Connect and process Azure analysis services

[omfspartan]

How do I connect to Azure analysis services from databricks? I need to process the tabular model from databricks. I tried to use adodbapi. while connecting it is failing with error message "windows com error dispatch adodb.connection". please help

[SparkJun]

To connect to Azure Analysis Services from Databricks, you can try the SQL Server Analysis Services (SSAS) connector. Please note that adodbapi is a Python library used for connecting to databases using the ADO (ActiveX Data Objects) technology, which is a part of the Windows COM (Component Object Model) technology. It's not directly compatible with Databricks as Databricks runs on Linux-based Spark clusters and doesn't support Windows COM/ADO technologies. So, can you try to connect to Azure Analysis Services using the Pyodbc library:

%python
import pyodbc

driver = "{ODBC Driver 17 for SQL Server}"
server = "your_server.database.windows.net"
database = "your_database"
username = "your_username"
password = "your_password"
analysis_services_database = "your_analysis_services_database"

connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};"

conn = pyodbc.connect(connection_string, autocommit=True)
cursor = conn.cursor()

query = f"SELECT * FROM {analysis_services_database}..."
cursor.execute(query)

Please enter your actual MSFT Azure Analysis Services server, database, username, and password. Please note that you need to install the Pyodbc library and ODBC Driver for SQL Server in your Databricks cluster.

[omfspartan · Accepted solution]

Thank you. I ended up using Azure Analysis services rest api for now because my use case is to process the model and tables from databricks

[omfspartan]

I got another use case now "to run dax against Azure Analysis Services model" from AWS databricks. I tried above suggestion from "Jun Yang" and it is erroring out after 30 seconds with the exception that "Login timeout is expired"

[rbee]

I am working on the same use case. Can you please share if you were able to achieve this and how? Using pyodbc, I have tried but I'm facing same error, it seems like i cannot use pyodbc, only option is to use pyadomd, but that is only supported on windows