Freigeben über

PowerPivot 101 – an Introduction

  • Artikel

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...

image

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

 image

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..

image

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..

image

https://www.powerpivot.com/

Comments

  • Anonymous
    December 14, 2009
    For additional, in-depth content on PowerPivot, includihng videos, tutorials, and case studies, check out http://powerpivotpro.com -Rob (member of the PowerPivot team at MS)

  • Anonymous
    April 21, 2011
    How did you create CPU/Memory guages?

  • Anonymous
    April 26, 2011
    Bharath the screen grabs in this post are form a pre-release veriosn of PowerPivot.  In the production dasshboard today you'll see a funky solverligth control that show powerpivot usage over time. But not the gauges.  If you want to create those you could put a separate report in sourced form the data on the dashboard, based on the connections used in the excel reports that are already there. Andrew