Running Your Own MDX And DAX Queries In Power BI Desktop

Reposted from Chris Webb's blog with the author's permission.

Every time there's a new release of Power Query or Power BI Desktop, I always check to see if there are any interesting new M functions that have been added (I used #shared to do this, as detailed here). For the RTM version of Power BI Desktop I spotted two new functions:

As well as ODBC connections, we can now use OLEDB and ADO.NET data sources - although they aren't shown in the UI yet. And you know what this means. with an OLEDB connection we can now run our own MDX and DAX queries against SSAS data sources! I assume this will be coming in Power Query in Excel soon too.

Here's an example query showing how to use OleDB.Query() to run an MDX query against the Adventure Works DW cube in SSAS Multidimesional:

 
 let     Source = OleDb.Query(               "Provider=MSOLAP.5;Data Source=localhost;                Initial Catalog=Adventure Works DW 2008",                "select {measures.[internet sales amount]} on 0,                 [date].[calendar].[calendar year].members on 1                 from [adventure works]"              ) in     Source 
 

As you can see, it's pretty straightforward: you just need to supply a connection string and a query. You will need to tell Power BI Desktop which credentials to use when running the query the first time you connect to SSAS, and that's probably going to be Windows:

You will also see a prompt the first time you run the query, asking for permission to run a Native Database Query:

This prompt will appear each time a different MDX query is run; you can turn off this prompt in the Options dialog on the Security tab by unchecking the Require user approval for new native database queries box:

Here's the output of the MDX query from the example code:

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .