I normally don’t bother blogging when one of the Power Query monthly updates gets released – the posts on the Power BI blog have all the details and there’s nothing more to add. This month’s update, though, sees Power Query able to use SSAS Multidimensional and Tabular models as a data source (you can read the announcement here and watch the video here) and I thought that since these are my two favourite Microsoft products I should comment.
In no particular order, some observations/thoughts:
- Power Query is generating MDX queries in the background here. And yes, query folding is taking place here so where possible the filtering and sorting is being translated back to MDX so the work is taking place on the server. I’ve had a look at the MDX being generated and while it’s a bit strange in places it’s basically good and should perform well.
- In order to get Power Query to work I had to install the 2012 version of ADOMD, even though I already had the 2014 version installed. Hmmm.
- It also doesn’t display calculated measures that aren’t associated with a measure group, although this is going to be fixed in a later release. In fact I experienced several errors associated with calculated measures when I first installed the new release, but these went away after I cleared the Power Query cache and rebooted.
- This update does not support custom MDX queries, unlike the equivalent SQL Server data source; again this is on the roadmap though. This will be useful for two reasons:
- For complex queries, or queries that have to be written in a certain way to perform well, sometimes you have to have complete control of the query
- It would allow you to run DAX, DMX and SQL queries on SSAS, and also let you query SSAS DMVs. Custom DAX queries can perform a lot better than the equivalent MDX in some cases, and querying DMVs would allow you to build SSAS monitoring dashboards in Excel.
- The Excel Data Model (which lots of people call Power Pivot, but I’m going to stick to my guns on this one because the Excel Data Model is not the same thing as Power Pivot) is not supported as a data source yet either, even though it can be queried in MDX just like SSAS. This would also be useful to have although I can also see it’s harder to implement, given that the Excel Data Model is both a data source and a data destination.
- There are a whole bunch of new (well, not really new because they have been around since the support for SAP Business Objects came out this summer) M functions to generate those MDX queries. They probably deserve their own blog post at some point in the future.
- Apart from the obvious reason that it allows you to combine data from SSAS with other data sources and/or load it into the Excel Data Model, why is it useful to have Power Query to connect to SSAS? What does it give you over and above the existing PivotTable, Excel Cube Function and Query Table connectivity? Why do we even need to extract data from a cube in the first place?
- This may only be a minor reason, but a lot of people still do get caught by the problem of having multiple cubes where they should have one, and need to build reports that incorporate data from multiple cubes. Power Query gives them an easy way of doing this.
- I believe the step-based approach of Power Query makes it much easier for users to build complex queries than other SSAS front-ends, such as PivotTables. Being able to see the effect of filters and other transformations as they are applied, and being able to delete them and rearrange the order that they are applied in, is a huge benefit. Think of Power Query as being a custom MDX query builder where the output is a regular table in Excel rather than a PivotTable.
- This last point leads me on to a topic that I’ve been thinking about a lot recently, and which will be the subject of a whole series of blog posts soon, namely that it is wrong to think of Power Query as simply a self-service ETL tool. It is that, but I also think that has a lot of potential as a self-service reporting tool too. For both relational database and SSAS I think Power Query could be very useful as a means of creating SSRS-like detail-level reporting solutions inside Excel. Problems with the MDX generated by PivotTables mean that queries with large numbers of hierarchies crossjoined together perform badly; the MDX that Power Query generates does not suffer from this problem. Functions can be used to parameterise Power Query queries (in fact earlier this evening I learned something immensely cool about function parameters that I’ll blog about later this week and which will make functions even more useful!) and can therefore be used in almost exactly the same way as datasets in SSRS.
I made my first Power Query from my Tabular cube, and it works fine. I will use it to replace a PowerPivot query to get all of my company’s SKUs for scanning. Glad to see this future.
“The Excel Data Model is not supported as a data source yet either”. This would be an AMAZING feature. I would go pee pee in my shorts.
ok good for us thanks
Looking forward to the posts on how PQ can replace SSRS reports for an ERP system like Dynamics AX. I have also been pondering this. Wouldn’t you anticipate some design limitations, though?
Yes, there will be some for sure. However with all of the great functionality in Excel there will be plenty of advantages too. I’m not sure what the pros/cons will be exactly until I do the work though.
I recently undertook a project for a client after selling them on the benefits of Power Pivot and convincing their IT department to install both Power Pivot and Power Query. I needed to do some data cleaning before analysing with Power Pivot so Power Query was my first stop. Then when I go to the point where I had originally indended to send the data off to Power Pivot for the master analysis, I realised that I could do the same analysis with just two more lines of M in my Power Query script. I was able to unpivot from one field and re-piovot to another all in Power Query and then returning the final query to an Excel Table rather than a PivotTable gave me a lot more flexibility in generating the final report. I ended up using Power Query for the entire project and didn’t touch Power Pivot once.
Hi, I have a quick question about Power Query with SSAS Cube connector.
I tried to connect to my Cube but I can only connect the server I can not see my cubes ,measures nothing. Is there any restriction about the SSAS version cube should be hosted, I have my Cube on SQL 2008 R2
No restriction as far as I know, but I haven’t tested it with R2. Can you connect to the cube from a PivotTable in the same workbook?
Yes, before trying this new version of power query I was using powerpivot to connect ssas cube 2008 r2
I moved my cube to a SQL 2012 and I was able to connect the cube from power query now
It seems new connector only work with 2012 and 2014
Hi Chris,
how can i connect to my SSAS (Sql 2012) Multidimensional Cubes with Power Query, when i need to use user credentials (UserId & Password)?
I have actually only windows-credentials in my Power Query (Version: 2.17.3850.242), but this is unsatisfactory, to connect with my cubes.
Thank you.
Good question, this is a mistake on the part of the Power Query team. I’ll pass this on to them.
Hello again,
well why cant i see my ssas measures with power query (Version: 2.17.3850.242)?
I can select any from my dimensions and the hierachies in it, but my multidimensional cube measures are not there!
In your book was no reference to ssas, because it was not supported at that time :o(
Can you please help me now?
Sounds strange – are any of your measures hidden (ie have Visible=false)?
Which version is ssas cube you have
I am using ssas 2012 and I am able to pull measures to my power query
sure some of my measures are hidden, but not all of them. In PQ is not one measure to see. In ordinary excel connection to the same cube, i can use more than 20 measures.
Hmmm but all my visible measures are calculated in Mdx-Script. Problem for PQ?
After my test, I can say that power query can not display mdx-script calculated measures , but only the (base)measures created with the measure groups are visible. This is simply not enough. For example, we use this base-measures as a basis for business calculations, but not as an analysis indicators our customers.
I think there’s a problem with calculated measures in the current build if they aren’t in display folders. Can you try putting your measures into folders if they aren’t already?
All of our calculated measures are organized in Display folders. They are with power query not to see.
Can you try removing them from their folders?
I’ve just realised something – have you associated the calculated measures with a measure group, using the associated_measure_group property? If not, I think that’s the problem.
Hi Chris,
sorry for my delayed answer. I wish you a happy new year. And thank you for your great blog.
Removing my calculated measures from their Folders has not helped. Power Query stil can not see – best wait for the next version.
Hello, is anyone able to modify the MDX that PQ generates? I am trying to add where clause to limit data load into PQ from SSAS. Appreciate your help!
No, you can’t modify the MDX. You can get it to add a subselect (if not a where clause), although it’s not obvious. You need to add the hierarchy you want to filter to the query, filter it in the dropdown menu, then click the Collapse button to hide the hierarchy again. I’m going to blog about this soon.
Hi Chris,
Interesting problem: I could not figure out how to read data from SSAS (using PowerQuery) by actually using a DMV Query.
Use case: I am attempting to load a cube’s meta data with a DMV query, but I do not see practically how can PQ achieve this.
Got an idea ?
Thx
Miloud
You can’t do it directly unfortunately – PQ cannot use custom queries against SSAS. The only workaround I can think of would be to create a linked server in SQL Server and then query SSAS from PQ via that.
Hi Miloud,
some metadata of your cube can be accessed by Power Query directly using “Cube.DisplayFolder”:
Metadata= Cube.DisplayFolders(Cube),
Expand the data column and you will see all your measures, dimensions & attributes in a table
Hi Chris,
do you know a way to pass´parameters from the Excel file to the cube functions in Power Query? So that query folding can happen but with dynamic filters/fields passed in from the Excel sheet?
Thx, Imke
Hi Imke, no, I don’t – at least I haven’t tried to make it work. I’m on holiday right now but I’ll do some experiments when I get home next week.
Great – thx, this would be super!
We then could use this to replace cubefunctions that slow performance down when applied in thousands on a single sheet.