Excel-reports on SSAS cubes (multidimensional and tabular) can have some flaws that now can be overcome by using Power Query for sourcing your cubedata:
- filter your cube by complete Excel-tables without loading them to the model/cube
- Apply nice number and date formats to non-measure number and date fields in your row- or column section
- create fast detailed reports (multiple attributes in your row sections, overcome the slow MDX that the pivots on cubes produce)
As with the recent Power Query update (26) you can now create your own MDX and DAX-statements for retrieving data from a cube, it is also possible to pass individual parameters from your Excel-sheet to the queries. This is a prerequisite for dynamically reducing the number of returned fields to the query, thereby allowing a decent performance of these reports.
So how about filtering the query by a table that sits in your local Excel file? Can we do an inner-join just like on the SQL-server-source? Read more
Power Query provides excellent functions to filter your SQL-Server queries by complete Excel-tables instead of just single parameters (like in all other Excel-based approaches). But you need to take care that the filtering is done at the server – before the data is loaded to Excel. This has a huge impact on performance that you don’t want to miss and is called “query folding”. Koen Veerbeck’s article provides a very good overview, links to other usefull resources on this topic as well as a list of limitations for query folding to happen.
Recently I ran into a problem where folding seemed to stopp randomly: Used a table to filter and with some entry sets it folded and some other entries it didn’t. I received very good help from Microsofts Dev-&Support Team on this issue which I thought was worth sharing: They pointed out a bug in the current heuristic that is used to balance load times. This bug means that queries will stop folding now if the number of rows in the Excel-filtertable exceeds 200 rows:
So if the heuristic would work as planned, query folding would take place, if:
Today I read Andrew Todd’s brilliant guest post on Powerpivotpro.com about some really cool tricks on cubefunctions. The second one shows a very elegant solution to what I’ve formerly done with my “hidden-Pivot”-technique: Avoid the cubeset-functions by referencing a hidden pivot table instead. So if the set of “cubeslices” you want to use in your reports is static, this is the most elegant way to go in my eyes.
But if it is dynamic, like in a Top-10 ranking or similar, this technique still has it’s reason of being: Say you want to show a section in your dashboards that shows top-x elements, a calculation of their share on the total, then an aggregation on all others and a sum on all like this: