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:
[sourcecode language=”text”]
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
[/sourcecode]
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:
Is this possible in PowerQuery from Excel as well? I don’t see the Ole source there. I am trying to find a way to do MDX in powerquery
No, it’s not in Power Query in Excel yet. I assume it will come soon though.
This is it!
Checked.
Thank you for the information.
Hmm the next question is, can i load power bi results in excel – maybe with excel power query?
No you can’t, at least not yet.
Now it is possible to connect to power bi from Excel.
Using the Power Bi Publisher for Excel add-in you can generate a connection string.
Some modifications are required:
1) REMOVE all the DOUBLE QUOTES from the connection string that Excel generates for you
2) only the following properties have to be retained to make this work:
the “…” need to be taken from the Excel generated connection string
Provider=MSOLAP.8;
Initial Catalog=…;
Data Source=pbiazure://;
Location=…;
Identity Provider=…
To write the dax query downloading DAX studio is a good idea
yeah:
Support for Custom MDX/DAX queries when importing data from SSAS
http://blogs.msdn.com/b/powerbi/archive/2015/08/20/announcing-the-power-bi-desktop-august-update.aspx#mdx-dax
Thanks for the help in turning off the Native Query warning.
Chris, is it possible to create a DAX calculation within PowerBI against a data source connection to an MDX/SSAS Cube? Seems writing DAX in PBI only works against a Direct Query or other kinds of data sources but not against an MDX cube.
You should be able to run DAX and MDX queries against an SSAS Multidimensional cube if you have SSAS 2012 SP1 CU3 or higher.
MDX is not working properly in PowerQuery. It ignores or missinterprets “union” statements – instead of union – it makes another column.
Can you post an example query?
I don’t have Adventure works, so I’m posting example from our cube – you should replace server, cube, etc..:
let
Source = OleDb.Query(
“Provider=MSOLAP.5;Data Source=YourSSASserver;Initial Catalog=CubeName”,
”
select
{
[Measures].[SomeMeasure]
} on columns,
{
{except([Date].[week_id].children,[Date].[week_id].&) } *
union(
[Org].[OrgStruct].[Region].members,
[Org].[OrgStruct].[Branch].members
)
} on rows
from [CubeName]”
)
in
Source
This isn’t a Power Query problem exactly, it’s due to Power Query requesting a flattened rowset instead of a cellset. SSRS does the same.
I once tried to extract data from a published power bi model using a DAX query in Excel, but i power query was unable to retain the login credentials. has this been fixed recently?
my workaround was to create a simple pivot table with a measure i could use to drill through to some details. This action would create a new connection to the datamodel with a DAX query in there to retrieve the first 1000 rows. This query could be replaced by the data you wanted.
Still had to write a macro to refresh the connections, because these tables do not refresh when you click Data > Refresh All … Microsoft says it was purposfully designed this way.
So doing this directly in PQ is much better… it worked, but the credentials expired soon and so it didnt work as a future proof solution … only for quick ad hoc queries