Edit : 16 Nov 2022, a new approach is to use DAX REST API, it does not require Premium license and works with any front end tool even on Linux, see example here ( source code in the Link)
https://powerbi.streamlit.app/
Streamlit is a new framework to build data web app using only python, you don’t need any knowledge of javascript/HTML.
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.
Project Controls blog 