Tabular model & Python

Sometimes you need to retrieve data from the SSAS service but you don’t want to use an application like Excel or Power BI. You can do it by Python.

We will connect to a local SSAS instance.

Using this code…

"""TABULAR MODEL Connection script"""
from sys import path

path.append("\\Program Files\\Microsoft.NET\\ADOMD.NET\\150")
from pyadomd import Pyadomd

CONNECTION_STRING = (
    r"Provider=MSOLAP;Data Source=.\SQLSERVER2019;Catalog=StackOverflow_Tabular;"
)
QUERY_STRING = """EVALUATE
    VAR MaxValue = MAX(Badges_201907[Id]) - 1784000
    VAR MinValue = MIN(Badges_201907[Id])  + 200000
RETURN 
    CALCULATETABLE(
        NATURALINNERJOIN(
            Users_201907, 
            Badges_201907
          ), 
          Badges_201907[Id] >  MinValue && 
          Badges_201907[Id] < MaxValue
        )
 """
with Pyadomd(CONNECTION_STRING) as conn:
    with conn.cursor().execute(QUERY_STRING) as cur:
        Dataset = cur.fetchall()
column_name = [i.name for i in cur.description]

print(column_name[9], column_name[10], column_name[11])
for index, row in enumerate(Dataset):
    if index < 10:
        print(row[9], row[10], row[11])

…we got the following result.

You can also connect to the Power BI Desktop tabular model instance.

You need to find a port and a database name. Then change them in the CONNECTION_STRING variable.

CONNECTION_STRING = r"Provider=MSOLAP;Data Source=localhost:60184;Catalog=67a2ca6f-74ea-414a-9857–1766e347f616;"

The easiest way to find a port number is to connect to the local model through DAX Studio.

It is visible on the bottom bar.