From a lot of the coverage of PowerPivot you could easily be under the impression that the product is aimed only at those with a reasonable understanding of traditional BI/Data Warehousing/RDBMS techniques. A lot of the people writing about it are from “Big BI”/”Big IT” backgrounds and tend to view the world from that perspective. I too could be accused of this bias with my previous post detailing the benefits of first creating a star schema before loading data into PowerPivot. For those of you from a business or pure spreadsheet background don’t be put off by this coverage; PowerPivot is, like its PivotTable ancestor, a tool accessible to all.
Although a star schema design is necessary to get the most from PowerPivot, it can also add value to large datasets comprised of a single flat table or a table with one or two “vlookup” tables. In fact, the PowerPivot equivalent of ‘vlookup’ is much easier and far more powerful than the Excel original. Such lookups not only replace vlookup but also SQL joins. Unlike SQL joins, Powerpivot joins are case-insensitive (so “PowerPivot” = “Powerpivot”, for example) and trailing spaces are also ignored, so much more forgiving of “bad data”. The ever-tricky case of SQL outer-joins (allowing for blank lookup codes or the code not existing on the lookup table) is also taken care of via the “Unknown Member”.
The new DAX formula language might be off-putting to many (in particular DAX used in the creation of measures, its most powerful feature). But again, a huge amount can be achieved using the automatically generated measures (near replicas of those available in a normal pivot).
So if you have a data source that is too large to load into a spreadsheet, or perhaps up until now your data has had to be summarised or SQL joined by IT prior to loading, then PowerPivot is well worth investigating for its heavy-lifting capabilities alone.
Even without very large datasets to play with, PowerPivot can be useful as a micro-ETL (Extract, Transform & Load) tool. It’s very easy to load data from multiple databases and other sources and by learning a few simply DAX formulas it’s possible to transform the data to whatever formats required. The resulting table can then either be cut and pasted into an Excel sheet for pivoting/filtering or pivoted using PowerPivot. The main advantage of pivoting such data using a PivotTable rather than PowerPivot is the ability to show-detail (a big missing in the case of PowerPivot).
To further explore the power of this revolutionary tool, I’d recommend Bill Jelen’s (MrExcel) PowerPivot For The Data Analyst book. Aimed squarely at Excel power-users (don’t think it mentioned a star-schema once, and only dealt with SharePoint deployment in the last chapter) this is a much better choice that the IT focused (and MS Marketing driven) Professional Microsoft PowerPivot for Excel and SharePoint.
Another plus of Bill’s book, besides learning PowerPivot, is that you might learn something new about normal PTs, for example look out for the “un-crosstab a crosstab” trick using a consolidated range pivot. The Professional book also offers a behind the scenes look at the development of PowerPivot, including the gem that initially is was envisaged as an MS Access tool!
PowerPivot to the people, viva la Revolución!
UPDATE: Jan 2011
For a book that fits nicely between the two I’ve mentioned above check out Microsoft PowerPivot for Excel 2010: Give Your Data Meaning which as Marco Russso explains:
” (has) an extended coverage of DAX and we also try to explain how to model data for PowerPivot usage. I think it might be a good complement to other books you mentioned, especially for advanced Excel users that want to create complex models in PowerPivot.”