Connect to Power BI XMLA endpoint with Python

Question

Since the announcement about XMLA endpoints, I've been trying to figure out how to connect to a URL of the form powerbi://api.powerbi.com/v1.0/myorg/[workspace name] as an SSAS OLAP cube via Python, but I haven't gotten anything to work.

I have a workspace in a premium capacity and I am able to connect to it using DAX Studio as well as SSMS as explained here, but I haven't figured out how to do it with Python. I've tried installing olap.xmla, but I get the following error when I try to use the Power BI URL as the location using either the powerbi or https as the prefix.

import olap.xmla.xmla as xmla

p = xmla.XMLAProvider()

c = p.connect(location="powerbi://api.powerbi.com/v1.0/myorg/[My Workspace]")

[...]

TransportError: Server returned HTTP status 404 (no content available)

I'm sure there are authentication issues involved, but I'm a bit out of my depth here. Do I need to set up an "app" in ActiveDirectory and use the API somehow? How is authentication handled for this kind of connection?

If anyone knows of any blog posts or other resources that demonstrate how to connect to a Power BI XMLA endpoint specifically using Python, that would be amazing. My searching has failed me, but surely I can't be the only one who is trying to do this. 

Answer 1

• As it is connector issue, I went looking for other Python modules that worked with MSOLAP to connect and found one that I got working!

• The module is adodbapi ( pywin32 is prerequisite).

• In this way we connect the string:

import adodbapi

# Connection string

conn = adodbapi.connect("Provider=MSOLAP.8; \

    Data Source='powerbi://api.powerbi.com/v1.0/myorg/My Workspace Name'; \

    Initial Catalog='My Data Model'")

# Example query

print('The tables in your database are:')

for name in conn.get_table_names():

    print(name)

• It authenticated using my Windows credentials by popping up a window like this:

Want to learn more about Power BI, Come & join Power BI tutorial. Also, enroll in Power BI Certification online to become proficient in this BI tool.