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.