Introduction to PowerPivot
Tuesday, August 9, 2011
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)
- 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
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.