Installing TOM Library to connect Python to Power BI & SSAS

In this blog I want to show how you can load the Tabular DLL to connect Python with Power BI, SSAS, or PBI Service.

I am going to refer back this post in many blogs so that the setup stuff isn't repeated.

You don't need to follow the first steps if you have installed the SSAS Tabular on your machine during SQL Server installation, the TOM DLL is automatically installed for that.

We will take help of PowerShell to install this Library, so first thing to do is launch PowerShell as an Administrator and need to run the following code:

Set-ExecutionPolicy Unrestricted
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted

After this we need to run this code, that downloads the DLL from nuget market place

Install-Package Microsoft.AnalysisServices.retail.amd64 -Source "https://www.nuget.org/api/v2" -SkipDependencies

I added the -SkipDependencies part because otherwise the installation never works and PowerShell throws an error that a Dependency Loop was detected, not sure why this happens.

Once this is done, navigate to

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.retail.amd64.19.61.1.4\lib\net45

Copy the path to the Tabular DLL -

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.retail.amd64.19.61.1.4\lib\net45\Microsoft.AnalysisServices.Tabular.dll

And then you can use it in PowerShell using Add-Type command

Now, if you already have SSAS installed or you previously downloaded the Tabular Nuget inside a C# project then you don't need to copy the previous path, instead you can simply load it by just the name from C:\Windows\Microsoft.NET\assembly\GAC_MSIL

Now that we know that everything is working we can test it with Python.

To load the .net DLL in Python we need to use CLR from Pythonnet, and that means you need to uninstall any existing CLR.

Here is an image that shows how you work with TOM library in Python.

If you see the table Names then everything is working as expected.

For Power BI Desktop just changes the connection string to this, this connection string can be obtained using DAX Studio's bottom left pane.

For Power BI Service, you need to use XMLA endpoint so that means you are limited to only Premium Workspaces.