PowerPivot – Show Detail not allowed!

Last week, I at long last set aside some time to put PowerPivot through its paces, triggered by my purchasing of Excel 2010 (in itself a momentous occasion as without the attraction of PowerPivot I would have followed my, and most other Office users’, usual pattern of waiting 3-5 years or so before investigating the ‘latest’ edition).

The verdict?

In general, still impressed, the concept demo’d to me over a year ago has evolved into an impressive first version. Some things such as hierarchies did not make it (hierarchies are constructed from cross-joins of field /attribute sets as per a normal pivot table) so no hierarchy rules as would be the case with standard OLAP cubes; but perhaps for many end users the ‘traditional’ construct-on-the-fly hierarchies will be more approachable.

The DAX functionality is better than I expected; easier for non-techies than MDX but still powerful.

Importing data is hassle free and intuitive, the VertiPaq engine does a wonderful job of compressing the imported data and the resulting in-memory column-store is certainly very fast. I like the ‘linked-table’ option which allows for normal Excel tables (the one useful new feature that Excel 2007 introduced) to be added to the PowerPivot star-schema. Being able to import any datasource publishable in AtomPub format (such as Google Docs spreadsheets!) is also nice.

So it’s all good then? Yes, except for one really annoying missing; no drill-through (aka Show Detail, aka drill-thru) allowed.

What? surely some mistake!

Afraid not, I initially thought the measures I attempted to Show Detail on were too complex (as the error message “Show Details cannot be completed on a calculated cell” suggested). Then I assumed it had been cut to meet a delivery deadline and would appear in a subsequent version. But no, Donald Farmer confirmed that is was intentionally removed as the feedback from IT organisations was not to allow end-users the ability to drill-through to potentiality millions of rows when running under a SharePoint server. As for those of us running PowerPivot on the client, we already have all the data, so no need for show-detail!

Okay, I can understand IT’s reluctance to allow a multi-million row drill-through but surely that should be the decision of individual IT groups to allow or not, and if allowed, to provide the ability to limit the amount of data returned.

A million row result is of limited use, most Show Details are a few thousand at max, and typically sub-1000, so sensible limits can easily be enforced.

The client-side (the side that matters most to me) is a very different story. Here the excuse that the data is already there, is exactly that, an excuse. Using the same logic the drill-through on a normal pivot-table should be unnecessary. Yet, if you watch end-users construct pivots they use it constantly; not just to discover the detail behind a figure but as often as not as a way of validating that the model they’ve constructed is correct.

This spot-checking of figures is the main ‘test methodology’ used in the wild. Spreadsheet ‘developers’ do not construct sophisticated test harnesses and procedures. You might argue they should, but they don’t and likely never will. And as for the ‘multi-million row result’ problem, end-users are not idiots they’re just end-users, they’ll do it once, and learn to be more careful the next time (or they’ll use the limit-rows option).

This lack of drill-though will definitely mean I will continue to use normal pivot tables for situations that would otherwise be better solved using PowerPivot. As many such models will be based on relatively small datasets ( sub-100,000 ‘facts’) it might be suggested there’s no need for PowerPivot. But this is to miss the ‘intellectual’ power (as opposed to the massive data crunching power of VeriPaq) at the heart of PowerPivot; the star-schema.

Star-schema models (particularly when the speed of access worries are removed by an in-memory column-store) are superior in many respects to the fully denormalised flattened tables that we currently build to support pivot-tables and are also more flexible than the multi-dimensional cell approach of pure MOLAP cubes. Combining such data models with the user-friendliness of spreadsheets,alongside the added magic of a modelling language such as DAX (and some MDX where necessary) on a datasmith’s laptop is the true beauty of PowerPivot.

10 responses to “PowerPivot – Show Detail not allowed!”

Tom,
There are other limitations as well.
a) You cannot group a Date column in to months/years/quarters like you can in a normal pivot
b) you cannot use SQL queries if you choose Excel as a dataource inside a Power pivot

There are many minor irritations for those coming at this from a classic pivottable background as PowerPivot cubes are essentially based on OLAP pivots.

Missings such as the inability to “group” a date into months/quarters/years or not recognising standard order-by sequences such and “Jan,Feb,Mar …” are due to the different nature of OLAP Cubes as data sources.

Take the date problem for example; in an OLAP cube date hierarchies are usually provided via a “Time Dimension” i.e. a lookup table with an entry for each day of the cube’s timeline with additional attributes to recognise months, quarters, financial years, weeks etc. Although a PowerPivot “cube” can be built without reference to a Time Dimension, to get full advantage of the power of DAX time intelligence functions a “date lookup” table should ideally be added to all but the simplest cubes.

There are about a dozen annoying limitations to PowerPivot pivot tables that will drive regular pivot table users crazy.

There is an 8-mouse-click workaround to get month names to appear in the correct Jan, Feb, Mar sequence, but I have to tell you that those 8 clicks hack me off every time that I have to do them…especially since pivotcache pivot tables do it effortlessly!

On the flip side, there are a few things that you can do with a PowerPivot pivot table that you can not do with a pivotcache pivot table. For example, I sometimes find myself taking boring 1000-row data sets through PowerPivot just to enable the new Excel 2010 Named Sets feature. This feature finally allows asymmetric pivot tables.

Although the ability to handle very large datasets is the initial draw of PowerPivot I too find myself loading small datasets to take advantage of features such as Names Sets and the Time Intelligence DAX formulas etc.

But, on the other hand I also find myself continuing to do SQL “Select .. Group By”s to reduce large datasets in order to take advantage of “classic” pivots, in particular the Show-Detail capability.

One hugely annoying factor is that one cannot use a PostgreSQL database as the data source. Unless there has recently been an add-on released for this purpose I cannot find a way through this. Very annoying!!