Connecting to Powerbi Metatdata hosted in Azure Analysis Services through python

[Anonymous]

I am trying to query my powerbi metadata stored in Azure Analysis services through python.

I have used Nuget package and loaded the dll "Microsoft.AnalysisServices.AdomdClient" to my root folder (RHEL Linux), when I tried to execute the below program it throws an error (screenshot attached below)

from sys import path
path.append('/root/dll/Microsoft.AnalysisServices.AdomdClient')
from pyadomd import Pyadomd
conn='Provider=MSOLAP.7;Data Source=powerbi://api.powerbi.com/v1.0/myorg/;Initial Catalog=model;User Id=testuser;Password=;'
query = "SELECT DIMENSION_CAPTION AS [DIMENSION] FROM $system.MDSchema_Dimensions"

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

[nenkoru]

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

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 [0]

[0] https://gist.github.com/nenkoru/ab4544664755c89eb833456fe9557635

[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-end...

[5] https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/

[v-lionel-msft]

Hi @Anonymous ,

It may be API call error, you try to import urllib2 or requests package to call API.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.