Phone (01457) 858877 or email
PowerPivot for Excel allows you to crunch very large amounts of data quickly, using pivot tables and slicers and other familiar Excel concepts. It also includes the DAX language, allowing you to create complex calculations.
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.
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).
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:
For some time now, those crunching huge datasets for a living have had to resort to other software applications, such as SSAS (shown below).
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:
However, SSAS suffered from two main disadvantages: it wasn't sexy to use, and it wasn't availabe from within Excel.
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
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
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.
This calculation uses DAX (Data Analysis Expressions), which is like the MDX language built into Analysis Services. It allows you to create more complicated calculations than are allowed in a pivot table in Excel.
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.
Comments on this blog
This blog currently has no comments.