By the continuing interest in my “PowerPivot – Show Detail not allowed!” post a lot of people are still obviously surprised when their attempts to “show details” on a PowerPivot cell fails. I still believe this is a major missing but I’ve learned to live with it and have a semi-acceptable replacement in Flattened PivotTables.
So, for example, you might have a Sales Fact on which you have some detail-level columns (such as Invoice No. or Customer Telephone) that you occasionally wish to drill-down to in order to further investigate pivot results. If you never wished to drill to that level of detail, there’s a case for not including them on your “fact” table, as by their nature they have a high “cardinality” and will not compress as well as low-cardinality columns (such as Invoice Type). But of course, columns such as Invoice No can also be used for measures (e.g. count(Invoice No)) thus “justifying” this memory overhead.
Back to the drill-down. The double-click “show detail”s we’ve come to expect doesn’t work, but it’s still possible to generate a table containing the detail-level attributes by using a new feature: Flattened PivotTables.
This is a new PivotTable design option found only on the PowerPivot menu. (It’s also possible to flatten PivotTables (both standard & PowerPivot based) using the ‘PivotTable Tools’ >’Report Layout’ menu but the new method is somewhat easier).
The trick is to select the most restrictive and low-cardinality columns first (after selecting at least one measure), setting the necessary filters (on the columns themselves, or by using “non-table” slicers) to achieve the view required and then, adding the detail-level columns to the table.
If you do this in reverse then the WorkBook is likely to choke on you if there are 10’s of thousands (or indeed millions) of the detail-level column’s distinct values.
Another method of avoiding this premature “explosion” of rows is to:
- Click the Field List button under PivotTable Option menu, this will display the “normal” field list alongside PowerPivot’s list.
- Select the Defer Layout Update option. Layout table, then click Update.
Not perfect I’ll admit, hopefully the next version of PowerPivot will at least allow WorkBook based cubes to show details (I can see the logic of disallowing or severely restricting this option for SharePoint hosted cubes) but until then this is as good as it gets.
Update: Seems the next version of PowerPivot, due this year, will remedy this !