Connecting to a PowerBI using Python is well documented , see those excellent tutorials here by David Eldersveld
using this code, I managed to build a small app that using an existing XMLA end point, first it will extract the existing models and then you can run arbitrary DAX queries.
please note as of August 2020, XMLA end point is a PowerBI premium only feature
the main connection string and how to export to a df was copied from this Answer in Stackoverflow
import adodbapi as ado import numpy as np import pandas as pd import streamlit as st def get_df(data): ar = np.array(data.ado_results) # turn ado results into a numpy array df = pd.DataFrame(ar).transpose() # create a dataframe from the array df.columns = data.columnNames.keys() # set column names return df source=st.sidebar.text_input('Write your XMLA endpoint') if source: with ado.connect("Provider=MSOLAP.8; Data Source="+source) as con: with con.cursor() as cur: cur.execute('select * from $SYSTEM.DBSCHEMA_CATALOGS') data = cur.fetchall() catalogue = get_df(data) catalogue_Select= st.sidebar.selectbox('Select Models', catalogue['catalog_name']) dax=st.text_area('Write your DAX Query:') if dax: with ado.connect("Provider=MSOLAP.8; Data Source="+source+" ;Initial catalog="+catalogue_Select) as con: with con.cursor() as cur: cur.execute(dax) data = cur.fetchall() df = get_df(data) st.write (df)
and here is the result
Unfortunately adodbapi required Windows , which make deploying the app a bit harder, yo can try Azure Web app which has a windows runtime, I wish it was as easy as Heroku !!!
The good new Microsoft added recently the support for .Net Core, so hopefully I will Update the blog with a cross platform solution
to run the app on your laptop, just type
streamlit run app.py
it is a proof of concept but I see a lot of use cases, an obvious one is to build web app for visualization not supported by PowerBI like massive dataset maps, or 3 D viz.