Last active
March 31, 2026 01:10
-
-
Save nenkoru/ab4544664755c89eb833456fe9557635 to your computer and use it in GitHub Desktop.
Connect to PowerBI OLAP from Python cross-platform using dotnet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| """ | |
| 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()) |
Here's some all-in-one python to get this working again for 2026 - though, I think GraphQL is a better option than XMLA at this point.
import os
import requests
import zipfile
import io
PACKAGE_NAME = "Microsoft.AnalysisServices.AdomdClient"
VERSION = "19.113.2"
DLL_NAME = "Microsoft.AnalysisServices.AdomdClient.dll"
def download_and_extract_latest():
script_dir = os.getcwd()
print(f"📦 Downloading {PACKAGE_NAME} v{VERSION}...")
url = f"https://www.nuget.org/api/v2/package/{PACKAGE_NAME}/{VERSION}"
r = requests.get(url, allow_redirects=True)
if r.status_code == 200:
with zipfile.ZipFile(io.BytesIO(r.content)) as z:
dll_files = [n for n in z.namelist() if n.endswith(".dll") and "lib/" in n]
for member in dll_files:
filename = os.path.basename(member)
target_path = os.path.join(script_dir, filename)
with z.open(member) as source, open(target_path, "wb") as target:
target.write(source.read())
print(f" ✅ Extracted: {filename}")
print(f"\n🚀 Done! {VERSION} files are now in: {script_dir}")
else:
print(f"❌ Failed to download. Status code: {r.status_code}")
if __name__ == "__main__":
download_and_extract_latest()
import os
import requests
import zipfile
import io
LIBRARIES = {
"MSAL": {
"package": "Microsoft.Identity.Client",
"version": "4.65.0", # Exact version from your error
"dll": "Microsoft.Identity.Client.dll"
},
"Identity_Abstractions": {
"package": "Microsoft.IdentityModel.Abstractions",
"version": "8.0.0", # Foundation for modern MSAL
"dll": "Microsoft.IdentityModel.Abstractions.dll"
}
}
def download_dependencies():
script_dir = os.getcwd()
for key, info in LIBRARIES.items():
print(f"📦 Downloading {info['package']} v{info['version']}...")
url = f"https://www.nuget.org/api/v2/package/{info['package']}/{info['version']}"
r = requests.get(url, allow_redirects=True)
if r.status_code == 200:
with zipfile.ZipFile(io.BytesIO(r.content)) as z:
# We prioritize netstandard2.0 or net6.0 for Linux/CoreCLR compatibility
dll_files = [n for n in z.namelist() if n.endswith(info['dll'])]
# Filter for the best cross-platform version
target_member = next((f for f in dll_files if "netstandard2.0" in f),
next((f for f in dll_files if "net6.0" in f), dll_files[0]))
target_path = os.path.join(script_dir, info['dll'])
with z.open(target_member) as source, open(target_path, "wb") as target:
target.write(source.read())
print(f" ✅ Extracted: {info['dll']}")
else:
print(f"❌ Failed to download {info['package']}. Status: {r.status_code}")
if __name__ == "__main__":
download_dependencies()
print("\n🚀 All identity foundations are now in your directory.")
from sys import path
from pythonnet import load
load("coreclr")
import clr
from pyadomd import Pyadomd
conn = (
f"Provider=MSOLAP;"
f"Data Source=powerbi://api.powerbi.com/v1.0/myorg/{WORKSPACE_ID};"
f"Initial Catalog={MODEL_NAME};"
f"User ID=app:{CLIENT_ID}@{TENANT_ID};"
f"Password={CLIENT_SECRET};"
)
query = """
Select [Name] from $SYSTEM.TMSCHEMA_ROLES
"""
with Pyadomd(conn) as conn:
with conn.cursor().execute(query) as cur:
print(cur.fetchall())
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi there! Thank you very much for taking the time to write this guide, it's really tough to find anything even semi-modern on this topic.
I was excited to see someone had got this working this year. Unfortunately I'm running into a really strange error.
I'm getting this error anytime AdomdConnection's
Open()method is called. I've tried usingPyadomdand I've tried using coreclr directly to add the AdomdClient assembly and create a connection directly.I've tried with dotnet 9.0.101 and I've tried with dotnet core 3.1.426 and get the same error on both.
I'm using packages:
microsoft.identity.client.4.66.2microsoft.identitymodel.abstractions.8.3.0microsoft.analysisservices.adomdclient.netcore.retail.amd64.19.84.1I'm using python
3.12.6and I havepythonnet==3.0.4andpyadomd==0.1.1I've slammed my head against google for most of the day today so just posting this here in the ditch hope that someone much smarter has a clue what is going on. :)