Introduction to PowerPivot

Tuesday, August 9, 2011
by jsalvo

I recently attended a Pragmatic Works webinar entitled ‘Introduction to PowerPivot’ presented by Devin Knight.  The full webinar can be found at:

My notes from the webinar are included below:

Benefits of PowerPivot

  • Self-Service BI
  • Cube usability without a data warehouse
  • Mashing of loosely related data (from disparate sources)

BI Categories

  • Personal BI – PowerPivot falls in this category.  Uses local resources.  All data is included in Excel file, data is compressed but files can grow large quickly.
  • Team BI – PowerPivot + SharePoint (Enterprise Edition).  PowerPivot deployed to SharePoint for collaboration.
  • Organized BI – SSAS Cubes (and Excel Pivot Tables)

Requirements of PowerPivot Usage (without SharePoint)

  • Excel 2010 (Preferably 64 bit)
  • PowerPivot free plugin

Benefits of PowerPivot + SharePoint

  • Accessibility – Uses SharePoint security
  • Scalability – Analysis server runs side-by-side with SharePoint
  • Collaboration

Installation and Configuration for SharePoint Integration

  • Need SharePoint Enterprise Edition (formerly MOSS), Office 2010, SQL Server 2008 R2.
  • SQL Server PowerPivot for SharePoint
  • Domain Account
  • Requires an extra instance of SSAS, produces an SSAS database when PowerPivot data is filtered/updated.

Features / Functionality (CTP3)

  • Uses DAX language (blend of MDX and Excel formulas)
  • Linked tables – import data from spreadsheet into PowerPivot
  • Connects to multitude of sources (anything accessible via ODBC)
  • Once data is imported, it is static and does not automatically refresh. Need to manually refresh by clicking ‘Refresh’ button. In SharePoint it is possible to schedule automated data refresh.
  • Can create new columns from within PowerPivot. Calculations are created using the DAX language.
  • *New* KPI functionality.
  • *New* ‘Active’ flag in ‘Create Relationship’ wizard to enhance PowerPivot for role-playing dimensions.
  • *New* Diagram view provides visualization of tables and relationships, drag-n-drop functionality.
  • *New* Functionality to create user defined hierarchies, includes drag-n-drop interface to create the hierarchy.
  • *New* Measure formatting capabilities (e.g. currency)

Cube vs. PowerPivot

Cubes have performance tuning capabilities to handle 100s-1000s of users and queries. PowerPivot is better for smaller scale roll-outs. PowerPivot may be used to prove-out if a cube is needed.


comments powered by Disqus