Can I run my own DAX or MDX queries against Power BI?

Question

I have a data model in Power BI desktop. I'd like to publish it to the server, but I'd also like to have an internal report run MDX (or DAX) queries against it. Is this possible? Can I just create a connection string and connect to Power BI like to a SSAS Cube? Maybe using the REST APIs?

Edit: Thanks for your answers. Kyle gave me the best answer to my question, so I accepted his, but all of you made me clear that I'd better just use SSAS. This is what I did, with some hassle of seeing up HTTP bridge, but it works like a charm now.

Answer 1

It actually is possible in a literal sense - every time you run PowerBI, it creates a behind-the-scenes instance of SSAS Tabular that you can connect to and run queries against. Obviously this isn't directly supported by Microsoft, but I leave these steps in case anyone else wants to know how:

1. Navigate to %user%/AppData/Local/Temp/Power BI Desktop
2. Open your PowerBI Desktop model
3. A new folder will appear in the temp folder, inside that is a folder called AnalysisServicesWorkspace1111111111 (numbers at end are random)
4. Inside that folder is a file, msmdsrv.port.txt, which contains the port number (portnum) on which the SSAS Tabular model is running
5. You can open SSMS and connect to Analysis Services server localhost:portnum
6. The specific database instance you can find either via SSMS or the name of the GUID folder in the workspace folder (it'll be something like "33df46dd-8c77-46eb-bf01-8d545f626723.0.db")
7. Or you can use this as the server / catalog in an SSAS connection string i.e.

---

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;
Initial Catalog=databasename;Data Source=localhost:portnum;
MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Also, for devs of note, inside that *.db folder is a SQLite database which contains all the PowerBI model metadata, you can modify it via code and have it persist as long as you do something trivial in the UI such as select add calculated column and then click away.

Answer 2

To my knowledge this is not possible. Whether there is a workaround or not, I don't know.

You're probably better served using SSAS and connecting to a model in that both from Power BI with the AS Connector and for whatever DAX queries you need to run against it.

Answer 3

By publish, if you mean to put it out on SharePoint, then, YES there is a way to access it.

PowerPivot for SharePoint actually consists of two components. First, there is the Service Application that runs in the SharePoint farm that is responsible for performing data refreshes, and usage analytics. The main part however is actually an instance of Analysis Services using the tabular engine. It’s properly referred to as Analysis Services SharePoint Mode, and as of SharePoint 2013/SQL Server 2012 SP1, it can be installed standalone. However, it is most commonly installed on SharePoint front end servers.

In the case above, the SharePoint front end server is named NautilusSP. You can also see that there is a model being hosted by the server already. The model is named by taking a workbook, and adding a GUID to it. This is done by Excel Services the first time that a model is interacted with. For example, if we add the file Health.xlsx, which contains an embedded PowerPivot model, and immediately refresh the object explorer in Management Studio, we will see that nothing has changed. However, if we then interact with the model at all, by clicking a slicer, or opening a pivot table category, we will see that the model has been automatically created for us.

Caveats:

These models are temporary. If they haven’t been used for a period of time, they get deleted. Also, if the source workbook is updated, a new model is automatically create upon first interaction. This can be seen if we edit, and save our Health.xlsx workbook, and then open it in the browser and interact with it.

The original model will be deleted in a garbage collection process. We therefore cannot reliably target these models, as any reference will become invalid relatively quickly.

The better and actually scalable option is to create a tabular model(we are talking SSAS here) and import this PowerPivot model into it.