PowerPivot 101 – an Introduction - Insufficient data from Andrew Fryer - Site Home

If you showed a business analyst or finance guy how to manipulate a whole year’s worth of data in Excel they’ll probably buy you lunch. PowerPivot for Excel does just that, but before you cash in, you need to be aware of how it works its magic and understand why it compliments rather than replaces your existing investment in business intelligence.

First of all having this power in Excel can be seen as a two edged sword..

On the one hand it is very fast and easy too use, even on a standard laptop (with 2Gb+ RAM). Users just see a pivot table although there are also some slick add-ins to this functionality in Excel 2010.
But could lead the user to create their own BI on a desktop, and this type of spreadsheet has been the exact problem that business intelligence is designed to solve not make worse!

However PowerPivot functionality is also built into SharePoint 2010, enabling the designer of the PowerPivot to share his work with his colleagues, by posting it to a special PowerPivot gallery...

What’s clever about this is that they can quickly slice and dice the PowerPivot but in a browser…

PowerPivot has other really useful features..

The underlying data can be refreshed from the original sources to keep them up to date on a user defined schedule.
It integrates well into Office so users don’t need to learn that many new skills. It also integrates really well with Reporting Services so you can still provide report users with a view of PowerPivot data.
There are monitoring tools in SharePoint which show the resources the PowerPivots are using..

a management screen showing PowerPivot Report Usage

However it is not the total cure for all BI ..

It doesn’t scale that well nor is it intended to. It’s designed for tactical ad hoc BI in a team or small department. So PowerPivots can be considered as sand boxes (and are referred to as such under the covers in SharePoint) which are designed to be quickly thrown together to meet a particular need.
Most Business Intelligence burn a lot of time addressing data quality issues and PowerPivot only consumes data so you’ll still need to address this.

I see PowerPivot as a way of letting the business concentrate on the analysis and presentation of data, in order to meet an immediate need. This leaves the technical team to concentrate on providing good clean data, and to incorporate some of this tactical work in PowerPivot into new & existing enterprise/strategic BI projects as appropriate.

To learn more about PowerPivot, simply go to it’s own special site..