Connect to PowerBI OLAP from Python cross-platform using dotnet

pbi.py

"""
My journey started after reading this topic. And I started to gather all the breadcrumbs and clues I have found around the Internet.

First of all. There is a difference between Mono and dotnet(aka .net core), as pointed by Darren Gosbell on a microsoft learn page [4].
As everyone stumbling upon this issue I tried using Mono and specific to Windows version of Adomd whic is located in this link [5]. It didn't work as there were API used specific to Windows. It was failing.
Then I learned that there is actualy a 'native' dotnet app that could run those DLLs.
I started by loading that native to Windows AdomdClient DLL version using Mono - Fail.
Then using dotnet - Fail.
And right after that I understood that a .net core DLL has to be used. So I found one on Nuget [3].
And boom, right after that the original problem has gone, but I had a malformed connection string, which I believe was leading to lib to try to run a interactive login(just imo) and then failing into an issue where it said that a function or a dependency is only supported on Windows platform or something like that.
So make sure you add a valid url to the powerbi(in my case), username and password.

I am using Python3.9 with pyadomd==0.1.1 and
```bash
dotnet --version
7.0.102
```
installed using brew
So, you download .net core version of AdomdClient DLL, Microsoft.Identity.Client, Microsoft.IdentityModel.Abstractions as listed below.
Put them in the same folder and add the absolute path to that folder into sys.path so that it could retrieve it.
And there you are. Link to github gist which works for me

[1] https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64
lib/netcoreapp3.0/Microsoft.AnalysisServices.AdomdClient.dll


[2] https://www.nuget.org/packages/Microsoft.Identity.Client/
lib/netcoreapp2.1/Microsoft.Identity.Client.dll

[3] https://www.nuget.org/packages/Microsoft.IdentityModel.Abstractions
lib/netstandard2.0/Microsoft.IdentityModel.Abstractions.dll


[4] https://learn.microsoft.com/en-us/answers/questions/233745/can-adomd-net-core-connect-to-an-ssas-endpoint-fro?page=2#answers
[5] https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/
"""

# adds a path to DLLs into sys.path
from sys import path
path.append("/powerbi-adomd/netcore.adomd/lib/netcoreapp3.0")

# forces pythonnet to use dotnet
# also could be forced by setting env variable(checkout pythonnet repository)
from pythonnet import load
load("coreclr")
import clr


from pyadomd import Pyadomd

conn= (
        'Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/<WORKSPACE_IF_NEEDED>;Initial Catalog=<DATASETNAME>;'
        'User ID=<USERNAME(EMAIL)>;Password=<PASSWORD>;Persist Security Info=True;'
        'Impersonation Level=Impersonate;'
        )
# this particular example loads a list of RLS roles needed for the DATASETNAME
# I suggest to tinker with this in a Windows, where it's at least stable
# and making sure that no interactive login is required and making sure that
# the request works fine, then you could try to port it to be usable on linux
query = "Select [Name] from $SYSTEM.TMSCHEMA_ROLES"

#connection = Pyadomd(conn)
with Pyadomd(conn) as conn:
    with conn.cursor().execute(query) as cur:
        print(cur.fetchall())

Hi there,

Hope you are ok. Just trying to follow your guide but when executing the code i am getting the error

python3.10/site-packages/pyadomd/pyadomd.py", line 32, in
from Microsoft.AnalysisServices.AdomdClient import AdomdConnection, AdomdCommand # type: ignore
ModuleNotFoundError: No module named 'Microsoft.AnalysisServices'

Do you have an idea of what can be missing?

Info:
Python: 3.10
dotnet: 7.0.117

Thanks.

Hi there,

Hope you are ok. Just trying to follow your guide but when executing the code i am getting the error

python3.10/site-packages/pyadomd/pyadomd.py", line 32, in
from Microsoft.AnalysisServices.AdomdClient import AdomdConnection, AdomdCommand # type: ignore
ModuleNotFoundError: No module named 'Microsoft.AnalysisServices'

Do you have an idea of what can be missing?

Info: Python: 3.10 dotnet: 7.0.117

Thanks.

Hey there!

Make sure that you have all the DLLs in the PATH I mentioned in the original post.
Below is the screenshot of all the DLLs needed and used;

Life saver.

Thank you.

I was missing the Runtime.Core and Runtime.Windows DLL's.

Life saver.

Thank you.

I was missing the Runtime.Core and Runtime.Windows DLL's.

You are welcome!
🤝