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?
Not to my knowledge, as the references to the objects involved always need to be within the SSAS-datasource.
But you can replace single parameters by references to values just like in cubefunctions: [“&…&”]
[Calendar].[Year].&[2015] will be translated to: [Calendar].[Year].&[“&YearNo&”]
with “YearNo” being the parameter passed from your Excel file. This way you can dynamically filter single fields or ordered ranges (date from – to).
But how to deal with tables that hold filter values that don’t fit into a “from-to”-pattern? …
Yep: Do the single parameter filter and transform it into a function that is called for every filter value in your filter-table. Performance will be fine for many cases but of course will decrease with the length of your filter table. But hey: This hasn’t been possible before at all!
OK, so now that we’ve managed the filtering: How about the nice formats and quick detailed reports?
Key is not to load your queries anywhere (no table nor data model), but simply stick to “only create Connection”. All options will be available for you: Flat table, simple & quick Pivot table or -chart as well as Power View. You only need to know where to find all these treasures:
Treasures revealed (all connections of your workbook will be shown in this section – also the ones you created yourself using Power Query), so just check what you want:
This is basically ultra-cool, as you can:
- Show your data in a flat table if you don’t need (sub-)totals. (Don’t be put off by the standard table-layout: You can choose your own layout & make this stick by choosing “Preserve column/sort/layout” and “Preserve cell formatting” in the “External Data Properties” in “Data Range Properties” within the “Table Tools”-“Design” tab).
- Show your data in a pivot table if you need totals and sub-totals as well. You can drag in as many fields into your row section without any major performance issues (as this is not an MDX/cube-pivot). And you can apply every imaginable number format for your fields in the row section as well.
- You could also choose some nice charts: Pivot Charts or even Power View reports if you wish.
NB1: If for any reason you prefer to use cube-functions in your reports, you simply load your Power Query to the data model and access the results via cube-functions from there.
NB2: Although your Power-Pivot data model will be empty, Power Update would update these reports as well, which is also pretty brilliant!
Have a look at the code in the FilterListSSASCustomMDX3.xlsx .
Enjoy and stay queryious 🙂
Edit 2016-Oct-19: Here you can find a very nice solution for dynamic member filters that folds.