Python microsoft analysis services

python-ssas

I’ve been working for some time analyzing data using two unrelated tools – Python (primarily pandas), and DAX in Microsoft’s Tabular models. They are very different – Python is open source, Microsoft’s products are (obviously) not. It was frustrating to not be able to merge them. If I wanted to get data from a DAX data model into a Pandas dataframe, I would typically need to first export it to a file (like CSV) and then read it from there.

Also, I wanted a way to programatically «refresh» the data model (called «processing» it) from Python.

Solution:

Inspired by @akavalar’s great post, I discovered a nice workaround:

• DAX models (or any Analysis Services model) have several .Net APIs, see here for the Microsoft documentation
• Also, there is a fantastic Python library called Pythonnet that enables near seamless integration between Python and .Net. This is for the mainstream Python, called CPython, and not to be confused with the .Net implementation of Python which is called IronPython.

Using these ingredients, I created my ssas_api.py module with some utilities that I use frequently. Note that this just uses the parts of the APIs that I needed; there is a wealth more available, just dig through the documentation.

Note: I’ve only been using Azure Analysis Services, so the code is designed for that regarding the URL of the server and authentication string.

I haven’t found anything like this online, so feel free to use it.

Getting The Required .Net Libraries

ssas_api.py requires 2 specific DLLs to work:

These are usually already installed on most users’ computers if they are using any of the Microsoft tools that interact with DAX, such as Excel, Power BI Desktop, or SSMS. By default, they are installed in C:\Windows\Microsoft.NET\assembly\GAC_MSIL .

In cases when they aren’t installed, or if the user wants to install them manually, here is a quick and conveinent way to do so using PowerShell (requires Admin access)

# Register NuGet provider if not yet registered Install-PackageProvider -Name "Nuget" -Force Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet -Trusted -Force # Install the packages Install-Package Microsoft.AnalysisServices.retail.amd64 Install-Package Microsoft.AnalysisServices.AdomdClient.retail.amd64 

If installing via NuGet here is a Python snippet that will help with managing the path where it installs it to ( C:/Program Files/PackageManagement/NuGet/Packages/Microsoft.AnalysisServices ):

# dll paths setup, NuGet puts them here base = "C:/Program Files/PackageManagement/NuGet/Packages/Microsoft.AnalysisServices" _version = "19.4.0.2" # at time of this writing AMO_PATH = f".retail.amd64./lib/net45/Microsoft.AnalysisServices.Tabular.dll" ADOMD_PATH = f".AdomdClient.retail.amd64./lib/net45/Microsoft.AnalysisServices.AdomdClient.dll" 

Quickstart

In [1]: import ssas_api . . conn = ssas_api.set_conn_string( . server='', . db_name='', . username='', . password='' . ) In [2]: dax_string = ''' . //any valid DAX query . EVALUATE . CALCULATETABLE(MyTable) . ''' In [3]: df = ssas_api.get_DAX(connection_string=conn, dax_string=dax_string) 

Open Source Agenda is not affiliated with «Python Ssas» Project. README Source: yehoshuadimarsky/python-ssas

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

A proof of concept to integrate Python and Microsoft Analysis Services

License

yehoshuadimarsky/python-ssas

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

• Good working knowledge of:
  • Microsoft SQL Server Analysis Services (Tabular models)
  • Python (specifically pandas)
  • General Microsoft .Net familiarity

  I’ve been working for some time analyzing data using two unrelated tools – Python (primarily pandas), and DAX in Microsoft’s Tabular models. They are very different – Python is open source, Microsoft’s products are (obviously) not. It was frustrating to not be able to merge them. If I wanted to get data from a DAX data model into a Pandas dataframe, I would typically need to first export it to a file (like CSV) and then read it from there.

  Also, I wanted a way to programatically «refresh» the data model (called «processing» it) from Python.

  Inspired by @akavalar’s great post, I discovered a nice workaround:

  • DAX models (or any Analysis Services model) have several .Net APIs, see here for the Microsoft documentation
  • Also, there is a fantastic Python library called Pythonnet that enables near seamless integration between Python and .Net. This is for the mainstream Python, called CPython, and not to be confused with the .Net implementation of Python which is called IronPython.

  Using these ingredients, I created my ssas_api.py module with some utilities that I use frequently. Note that this just uses the parts of the APIs that I needed; there is a wealth more available, just dig through the documentation.

  Note: I’ve only been using Azure Analysis Services, so the code is designed for that regarding the URL of the server and authentication string.

  I haven’t found anything like this online, so feel free to use it.

  Getting The Required .Net Libraries

  ssas_api.py requires 2 specific DLLs to work:

  These are usually already installed on most users’ computers if they are using any of the Microsoft tools that interact with DAX, such as Excel, Power BI Desktop, or SSMS. By default, they are installed in C:\Windows\Microsoft.NET\assembly\GAC_MSIL .

  In cases when they aren’t installed, or if the user wants to install them manually, here is a quick and conveinent way to do so using PowerShell (requires Admin access)

  # Register NuGet provider if not yet registered Install-PackageProvider -Name "Nuget" -Force Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet -Trusted -Force # Install the packages Install-Package Microsoft.AnalysisServices.retail.amd64 Install-Package Microsoft.AnalysisServices.AdomdClient.retail.amd64

  If installing via NuGet here is a Python snippet that will help with managing the path where it installs it to ( C:/Program Files/PackageManagement/NuGet/Packages/Microsoft.AnalysisServices ):

  # dll paths setup, NuGet puts them here base = "C:/Program Files/PackageManagement/NuGet/Packages/Microsoft.AnalysisServices" _version = "19.4.0.2" # at time of this writing AMO_PATH = f"base>.retail.amd64._version>/lib/net45/Microsoft.AnalysisServices.Tabular.dll" ADOMD_PATH = f"base>.AdomdClient.retail.amd64._version>/lib/net45/Microsoft.AnalysisServices.AdomdClient.dll"

  In [1]: import ssas_api . . conn = ssas_api.set_conn_string( . server='', . db_name='', . username='', . password='' . ) In [2]: dax_string = ''' . //any valid DAX query . EVALUATE . CALCULATETABLE(MyTable) . ''' In [3]: df = ssas_api.get_DAX(connection_string=conn, dax_string=dax_string)

  About

  A proof of concept to integrate Python and Microsoft Analysis Services

  Источник

  Using Python to Execute DAX Queries in Power BI & SSAS Tabular — Part 1

  In this blog let’s take a look at how you can use Python to connect to either Power BI or SQL Server Analysis Services to run DAX Queries.

  First you need a Python IDE so that you can run the code, and then we need to import Pandas and path from sys and first let’s check what all is included in path.

  

  Now we need to import Pyadomd, but if you try to do that it gives you an error that «Make sure that the dll is added, to the path, before you import Pyadomd.»

  

  So first we need to add a certain dll to the path and that is ‘\\Program Files\\Microsoft.NET\\ADOMD.NET\\150’

  I have used «=» to separate the printed code.

  

  And now you can see that, that dll has been added to the path:

  

  If during installation of pyadomd you face any issues then just run

  pip install —pre pythonnet

  now we need to create 2 variables that will hold the name of the data model in Power BI/SSAS and the port number/server name on which SSAS is running.

  To identify the model name and port number for Power BI we can use DAX Studio, and we need to run a DMV to get the database ID.

  

  This is how the code looks like so far:

  

  Now we need to write the connection string, ther are 2 ways

  either you use this one: f ‘Provider=MSOLAP;Data Source= ;Catalog= ;’

  Or you rely on automatically generated one, which you can get from Excel.

  To get the complete connection string from Excel you need to know the port number shown above and follow the steps shown below.

  

  Go to Queries & Connection and copy the connection string and modify the Source and Initial Catalog arguments in Python code.

  

  After this we need to write our DAX Query that will be executed against the data model. For the first example we can use basic query such as EVALUATE Products.

  

  Next we need to open the connection to the Power BI model:

  

  next we are going to fetch the records and load it into a data frame

  

  

  We can run more complex quries as well that gets the running total:

  

  And now we can rename the columns and load the dataframe to Excel and if you get an error just make sure that openpyxl is installed.

  

  Succesfully exported the data:

  

  Connecting to SSAS Tabular

  Connecting to SSAS Tabular is even easier as you only need to specify the name of the data model and the server name:

  

  

  Now we can declare measures on the fly and run more complex queries:

  

  Once you paste the above DAX Query into the dax_query variable you will get your desired result:

  

  import pandas as pd from sys import path path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\150') from pyadomd import Pyadomd model_name = 'Contoso 2022' port_number = r'summer\antriksh' connection_string = f'Provider=MSOLAP;Data Source=;Catalog=;' dax_query = """ DEFINE MEASURE Sales[Total Sales] = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) MEASURE Sales[Running Total] = CALCULATE ( [Total Sales], DATESYTD ( Dates[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( Dates[Date], "Sales", [Total Sales], "Running Total", [Running Total] ) """ con = Pyadomd(connection_string) con.open() # Open the connection result = con.cursor().execute(dax_query) df = pd.DataFrame(result.fetchone()) df.rename(columns = 0: 'Dates', 1: 'Sales Amount', 2:'Running Total'>, inplace = True) df.to_excel(r"C:\Users\Antriksh\OneDrive\Desktop\Python SSAS.xlsx", index = False) print(df) con.close() # Proactively close it as well

  One way of renaming the columns is to get the column names from the recordset itself and for that you can use this code:

  con = Pyadomd(connection_string) con.open() # Open the connection result = con.cursor().execute(dax_query) col_names = [i.name for i in result.description] df = pd.DataFrame(result.fetchone(), columns=col_names)

  Источник