I wish to extract from PowerBI Datasets the metadata of all Measures, Relationships and Entities.
In VSCode I have a python script that connects to the PowerBI API using the Pyadomd module connecting via the XMLA endpoint. After much trial and error I've settled on this ADOMD approach as I've come to understand that Power BI's REST API for datasets doesn't directly provide an endpoint to list tables within a dataset, especially for Import datasets connected to an external source.
Below an example of my code which then works in VScode - essentially I can execute any DAX query I need.
import pandas as pd
# Include ADOMDClient in path before importing Pyadomd
import clr
dll_path = r"C:\Program Files\Microsoft.NET\ADOMD.NET\150\Microsoft.AnalysisServices.AdomdClient.dll"
clr.AddReference(dll_path)
from pyadomd import Pyadomd
DAX_query = """evaluate INFO.RELATIONSHIPS()"""
client_id = "<client_id>"
tenant_id = "<tenant_id>
client_secret = "<client_secret>"
workspace = "<workspace_name>"
catalog = "<catalog_name>"
connection_string = f"Data Source=powerbi://api.powerbi.com/v1.0/myorg/{workspace}; " \
f"Initial Catalog={catalog}; " \
f"User ID=app:{client_id}@{tenant_id}; " \
f"Password={client_secret}"
# Execute the query
with Pyadomd(connection_string) as conn:
with conn.cursor().execute(DAX_query) as result:
df_daxresult = pd.DataFrame(result.fetchall(), columns=[i[0] for i in result.description])
# Print Result
print(df_daxresult)
The issue now is getting this to work from within Databricks - which appears to be counter-intuitive given my use of the .Net runtime however there are online references to including ADOMD.dll in my DBFS FileStore and then Adding Reference to the path so that I can successfully "import Pyadomd".
I have however not managed to locate the ADOMD library for download and am wary if this is even the correct/best approach.
