Tag Archives: Drill-down

PowerPivot Show Details

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 !

Update: An so it has …

Advertisements

New ETL platform for PALO OLAP

Jedox have announced that they intend to ship a Palo centric ETL open source server product early next year. This is excellent news and is on top of the new rules engine that was added to Palo this summer. Open source MOLAP has suddenly taken off the training wheels and is getting ready to mess with the big kids. The two things I really like about the new proposed Palo-ETL server are; it’s open-source and it’s designed to enable drill-down from the analysis cube back to the data source.

Drill back is the 2nd most common reason for continuing IT staff involvement in the day-to-day running of BI projects (the 1st is of course the ETL process). The “Where did that figure come from?” question is one of the reasons that the Excel pivot table function is so popular, double-click on a data cell – the rows used to generate that figure are displayed on a new sheet; simple but powerful.

As to what platform the new server is to be built on, I’m guessing Talend or Kettle. Now it is possible that Jedox have rolled their own product from scratch but with two superb open-source products already out there it would seem pointless. Talend actively seeks out technology licensing agreements with other companies and has just opened an office in Germany (Jedox is based in Freiburg) so it would be the most likely contender. But, Pentaho’s Kettle may also be in the running as there’s already prior-art and this Pentaho forum thread. Also, the one major missing from the comprehensive Pentaho stable is lack of an in-memory OLAP tool (their current OLAP offering is the ROLAP Mondrian project).