I recently attended a Pragmatic Works webinar entitled ‘Introduction to PowerPivot’ presented by Devin Knight.  The full webinar can be found at: http://pragmaticworks.com/Resources/webinars/Default.aspx

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.