SQL Analysis Services Connection DAX (SSRS) using python

Introduction:

Python is a versatile programming language that offers a wide range of capabilities for data analysis and manipulation. One of the areas where Python excels is in connecting to various data sources and retrieving information. In this article, we will explore how to establish a connection to Analysis Services using Data Analysis Expressions (DAX) in SQL Server Reporting Services (SSRS) using Python.

Overview of Analysis Services and SSRS: Analysis Services is a component of Microsoft SQL Server that provides online analytical processing (OLAP) and data mining functionalities. It allows users to design and create multidimensional structures that contain data for analysis and reporting purposes. SQL Server Reporting Services (SSRS) is a reporting platform that enables the creation, deployment, and management of reports.

Python Libraries for Analysis Services Connection:

To connect to Analysis Services and execute DAX queries from Python, we will be utilizing the following libraries:

• adodbapi: A Python DB API 2.0 compliant interface to Microsoft ADO (ActiveX Data Objects). It provides a convenient way to connect to SQL Server Analysis Services.
• pandas: A popular data manipulation library that provides data structures and functions for efficiently handling structured data. It will help us organize and analyze the results obtained from the DAX queries.
• pyodbc: A Python library that provides an interface to ODBC (Open Database Connectivity) databases. It allows us to connect to the SQL Server database used by Analysis Services.

Installing Required Libraries:

To install the necessary libraries, open your command prompt or terminal and execute the following commands:

pip install adodbapi
pip install pandas
pip install pyodbc

apart from above libraries you need to install the operation system level following Microsoft Analysis Service libraries:

• Download MSOLAP (amd64) or Download MSOLAP (x86)
• Download AMO
• Download ADOMD

Note: these three libraries are very important to download and install on your windows machine or Linux accordingly. You can download libraries for other supported operating systems from Microsoft.

Once you are done with downloading and installing, cross verify the installation by following below steps.

• Locate Microsoft.AnalysisServices.AdomdClient.dll in the C drive or on the following possible locations
• C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.AdomdClient.dll
• Program Files\\Microsoft.NET\\ADOMD.NET\\150

Once "Microsoft.AnalysisServices.AdomdClient.dll" is located means your required libraries for operating system are download and available. 

Establishing a Connection to Analysis Services:

Before connecting to Analysis Services, ensure that the necessary drivers are installed on your system. Typically, you would need the SQL Server Native Client or the ODBC Driver for SQL Server.

To connect to Analysis Services using Python, use the following code snippet as a starting point:

import adodbapi
conn_str = "Provider=MSOLAP;Data Source=servername;Initial Catalog=databasename;Integrated Security=SSPI"
conn = adodbapi.connect(conn_str)
cursor = conn.cursor()

In the conn_str variable, replace servername with the name of the server hosting Analysis Services, and databasename with the name of the specific database or cube you want to connect to. The Integrated Security=SSPI parameter allows Windows Authentication.

Executing DAX Queries:

Once the connection is established, we can execute DAX queries to retrieve data from Analysis Services. Here's an example of executing a simple DAX query and fetching the results into a Pandas DataFrame:

import pandas as pd
query = "EVALUATE Sales"
cursor.execute(query)
df = pd.DataFrame(cursor.fetchall(), columns=[desc[0] for desc in cursor.description])

In this example, the DAX query "EVALUATE Sales" retrieves data from the Sales table or measure within the connected Analysis Services database. The fetchall() method retrieves all the rows returned by the query, and the cursor.description provides column names. The Pandas DataFrame is then created using the retrieved data.

Performing Analysis on Data:

Once the data is available in the Pandas DataFrame, you can leverage the powerful data manipulation capabilities of Pandas to perform analysis and generate insights. For instance, you can filter, group, aggregate, or visualize the data using various functions and libraries available in Python.

# Example: Display top 10 sales records

top_10_sales = df.sort_values('Sales', ascending=False).head(10)
print(top_10_sales)

Closing the Connection:

After executing the desired DAX queries and performing the necessary analysis, it is important to close the connection to Analysis Services to release resources. Use the following code snippet to close the connection:

cursor.close()
conn.close()

If above code doesn't works then you can use the following alternate code as well. 

# Import packages
import pandas as pd
from sys import path
path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\150')
from pyadomd import Pyadomd

# Build connection string
conn_str = 'Provider=MSOLAP; Data Source=<servername>; Catalog=<database_name>;'

# Enter DAX or MDX query
dax_query = """Your DAX or MDX query here"""

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

# Rename Columns
df.rename(columns={'OriginalColumnName1':'NewColumnName1',
                   'OriginalColumnName2':'NewColumnName2',
                   'OriginalColumnName3':'NewColumnName3'},
          inplace = True)

df.head()

Following path contains the directory path of the Microsoft.AnalysisServices.AdomdClient.dll

path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\150')

Conclusion:

In this article, we explored how to establish a connection to Analysis Services using Python and execute DAX queries to retrieve data. We leveraged libraries such as adodbapi and pandas to connect to the database and organize the data into a Pandas DataFrame. With the data in hand, we demonstrated how Python's data manipulation capabilities can be utilized to analyze and extract insights from the retrieved data. By combining the power of Python with Analysis Services, developers and data analysts can unlock new possibilities in data analysis and reporting.