What is PowerPivot 2010 for Excel?  An Overview

Having just announced our new two-day PowerPivot course, I thought i'd give a brief overview of what it actually is.  This isn't intended to be a tutorial - more an overview for managers wondering whether they should be learning PowerPivot.

In the Beginning there were Pivot Tables

Most readers of this blog will know that you can take a table of data:

Here we have a table of dinosaur records

From this, you can create a pivot table:

Here the selected cell shows that there are 9 carnivorous dinosaurs which have lizard hip type.

You can twiddle the dimensions of a pivot table like a Rubik's cube, and even double-click on any cell to drill-down and show its constituent records (so double-clicking on the selected cell above would show the 9 dinosaurs which eat meat and are lizardy).

Limitations of Pivot Tables

Pivot tables have become victims of their own success: they've become so popular that people have started to complain about their limitiations.  In particular:

• They can only link to a single data source;
• They are slow;
• They are limited within Excel by the number of rows in a worksheet; and
• There's only so many statistics you can display in a pivot table.

For some time now, those crunching huge datasets for a living have had to resort to other software applications, such as SSAS (shown below).

SQL Server Analysis Services (SSAS)

Analysis Services allows you to create cubes of data based on multiple data sources.  A cube is like a pivot table, in the sense that they can have multiple dimensions (in this respect cube is a poor name, but perhaps Microsoft thought that n-dimensional space might put people off ...).

A cube also allows you to create:

• hierarchies (you can specify, for example, how stores fit into regions which fit into areas);
• measures (you can create new calculations within a cube); and
• MDX expressions (you can create a calculation, say, comparing year-to-date sales with the previous year's equivalent figure).

However, SSAS suffered from two main disadvantages: it wasn't sexy to use, and it wasn't availabe from within Excel.

PowerPivot for Excel - Getting Started

If you have Excel 2010, you can now download a free add-in from Microsoft (I love the tag line: Don't just crunch numbers - crush them).  Here's what then shows up in Excel:

The PowerPivot tab in Excel 2010 is just like any other tab on the ribbon

From this you can launch PowerPivot for Excel, which shows up as a separate application:

The first thing you'll probably want to do is to link to some data

Connecting to Data Sources in PowerPivot

The following dialog box shows some of the data formats you can use (the list goes on for some way!):

Some of the many data formats supported by PowerPivot

Here, for example, we've linked to our Excel worksheet of dinosaurs again:

The result looks just like a table in Excel - which I'm sure is deliberate

Pivot Tables and Measures

One of the main things that you'll then want to do is to create a pivot table, but there are more options than there are in Excel:

The menu for inserting a pivot table includes a variety of templates.

When you've added your pivot table, you can create new calculations called measures - here's an example to calculate a dinosaur's weight-to-height ratio:

An example of a calculation - you have the usual range of Excel functions available too.

Conclusion

We suspect that PowerPivot will take off in a big way in the many organisations which analyse large volumes of data.  We also suspect that Analysis Services will quietly fall into disuse (if indeed it ever rose out of it), as throughout the history of software people have always preferred simple, easy to install software over complicated server-based applications.