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.

Jaqitsch

our team used olap.xmla to connect to Mondrian, but we have not tried to connect to PBI yet, so I am out of my depth here too. Attempting to use common sense, since python is a 3rd party tool you'll need to supply login credentials. Have you tried something like: p.connect(location="powerbi://api.powerbi.com/v1.0/myorg/[My Workspace]", username="blah", password="blah") ? – Wharfage

Using DAX Studio and SSMS to connect, I get an OAuth redirect to log in. I hate having credentials in plaintext, but I suppose it's worth a try to see if it works. – Jaqitsch

I understand, same here. But I'd try it first just to make sure you can make a connection, and then see how to get the same OAuth redirect in python. – Wharfage

@RADO, I did find a solution that doesn't require plaintext! – Jaqitsch

Answer 1

After @Gigga pointed the 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 (note the pywin32 prerequisite).

Connecting is as simple as this:

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:

Jaqitsch

I receive this error while trying to install adodbapi: cannot import name 'build_py_2to3' from 'distutils.command.build_py' build_py_2to3 not found in distutils - it is required for Python 3.x – Sanguinolent

Answer 2

I'm not familiar with olap.xmla or using Python to connect to olap cubes, but I think the problem is with the driver (or connector ?) provided in olap.xmla.

In the announcement about XMLA endpoints page, it says that the connection only works with SSMS 18.0 RC1 or later, which is quite new. Same thing with DAX studio, the version where xmla connection is supported (Version 2.8.2, Feb 3 2019), is quite fresh.

The latest version of olap.xmla seems to be from august 2013, so it's possible that there's some Microsoft magic behind the PowerBI XMLA connection and that's why it doesn't work with older connectors.

Misjudge

That's a good point about the Python connector, but more generally, the big deal about the XMLA connection is that it doesn't need special Microsoft magic like Power BI and Excel already had. Notice how the announcement mentions "open-platform connectivity" and "third party data-visualization tools". It does look like it "requires the latest versions of MSOLAP". – Jaqitsch

Answer 3

They now have a REST endpoint via which you can execute DAX queries. This could be easier than trying to invoke the XMLA endpoint directly.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

Pegu

While true that it could be easier - the limitations currently in place are significant for anything other than small queries. See 'Limitations' section at learn.microsoft.com/en-us/rest/api/power-bi/datasets/… – Smoko