THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Power Query support for Analysis Services (MDX)

Today at TechEd Europe 2014 Miguel Llopis made the first public show of Power Query support for Analysis Services.

This is still not available, but it should be released soon (hopefully it will be our Christmas gift!).

Here is a list of features shown:

  • It should be able to query both Multidimensional and Tabular
  • Generates query in MDX (no DAX by now)
  • Load one table at a time (but a query can mix dimensions and measures)
  • Shows dimensions, measures, hierarchies and attributes in Navigator
  • Use the typical Power Query transformations working on a “table” result
  • You import one table at a time

I think the last point deserves an explanation. When you write a query in Power Query, the result is a single table. If I want to build a Power Pivot data model getting data from an existing cube in Analysis Services, but with a different granularity, I have to run one query for each dimension and one query for the fact table. Depending on the definition of the cube, this could be easier or harder, because original columns could have been hidden because measures are exposed instead. Moreover, the result of a measure that is not aggregated with a sum (imagine just an average) could be impossible to aggregate in Power Pivot in the right way.

Thus, if you want your user to take advantage of Power Query, make sure you expose in a model measures that can be aggregated to compute non-additive calculations (such as an average!)

Now I look forward for receiving this Christmas gift!

UPDATE: the November 2014 release of Power Query included Analysis Services support - read Power Query November Update blog post.

Published Tuesday, October 28, 2014 6:12 PM by Marco Russo (SQLBI)



Tarek Demiati said:

MDX - Act III - The Renaissance ? :-)

October 28, 2014 12:47 PM

Marco Russo (SQLBI) said:

At least some love! :)

October 28, 2014 1:08 PM

kasper said:

You're going to love it!

October 28, 2014 10:46 PM

Luis said:

that is great news.  When do we get an API to to Powerpivot/power view?

This is a good start , though.

October 28, 2014 11:44 PM

Marco Russo (SQLBI) said:

Luis, at the moment the Power Pivot / Power View addin are not integrated with Office object model - only tables, relationships and connections are exposed by now. Hopefully this will change in the future, but still no news about that.

October 29, 2014 2:16 AM

Matt said:

Power View on a Power BI site with a secure data gateway connection to on-premise ssas multi and tabular is what is most needed.

November 1, 2014 1:35 AM

dan hare said:

Great news, just in time for an in flight project I hope !

November 4, 2014 7:59 PM

Rayis Imayev said:

Were you able to get any idea on when MDX support in Power Query would become available ?

November 10, 2014 11:53 AM

Marco Russo (SQLBI) said:

No official release dates have been announced yet. But, as I said in the blog post... I hope in a Christmas gift!

November 10, 2014 12:00 PM

Rayis Imayev said:

Thank you Marco, it's one of those things that you'd like start using it now :-)

November 10, 2014 1:10 PM

GDub said:

<Sigh> a Christmas lump of coal from MS. Hope we'll see it soon.

January 20, 2015 10:55 AM

GDub said:

January 20, 2015 11:04 AM

Marco Russo (SQLBI) said:

You reminded me to update this post including a link to the announcement. Thanks!

January 20, 2015 11:31 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement