PowerPivot (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions. |
Microsoft PowerPivot is the latest, and one of the most exciting, members of the Microsoft BI stack. If used properly, it can empower end-users to define their own analytics in common interfaces such as Microsoft Excel.
PowerPivot provides numerous capabilities and features:
Extends Excel and SharePoint to create a self-service BI system.
Creates applications inside Excel 2010.
A server-side component that enhances SharePoint 2010 with the capability to share those applications across the organization.
Allows applications to be updated with the latest data, and monitors how people are using them.
Best Practices
The following resources provide reference material and additional information.
PowerPivot Technical Diagram: PowerPivot Client/Server Architecture 2
Microsoft SQL Server 2008 R2 PowerPivot Planning and Deployment 3
PowerPivot Technical Diagram: PowerPivot Security Architecture 4
Case Studies and References
Examples of successful architectures are described in the following case studies and white papers.
Mediterranean Shipping Company: Shipping Company Makes Critical Data Available Faster with New BI Tools 11: Mediterranean Shipping Company (MSC) is the second-largest container ship line in the world based on volume. MSC was especially excited about the extensive support for self-service BI in SQL Server 2008 R2, particularly with Microsoft SQL Server PowerPivot for Microsoft Excel. By using this free, downloadable BI technology, MSC users will be able to work with massive volumes of data in their Microsoft Excel 2010.
BNZ: New Zealand Bank Turns to New BI Tools for Delivering Vital Branch Performance Data Processes 12: PowerPivot for Excel makes it possible for users to perform BI tasks on hundreds of millions of rows of data using Microsoft Excel 2010, which is part of Microsoft Office 2010 Professional. BNZ plans to deploy Excel 2010 in the Finance Department in late 2010.
CareGroup Healthcare System: Healthcare Group to Enhance Information Access with Powerful Business Intelligence Tools 13: In late 2009, CareGroup began a pilot of the PowerPivot technologies involving 10 employees. The SQL Server 2008 R2 solution is expected to become available to a broad base of employees by the second half of 2010, and full deployment is scheduled to begin in late 2010.
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
PowerPivot empowers end users to build their own analytical apps, but this power must be deployed with caution as end-users may not be familiar with raw data characteristics (e.g. averaging an average).
Understand the sizes of data being analyzed. Average compression ratios are 15:1, but ratios as high as 1000:1 are not uncommon. A 2GB dataset will take 4GB of memory because of required memory operations, but remember that 2GB represents a lot of data in PowerPivot.
Requires "power" end users who are comfortable with Excel and multi-dimensional data structures.
Since it is an end-user tool, data governance and control requirements may have to be redefined.
Excel governance and compliance rules might need to be put into place to manage the PowerPivot spreadsheets.
Each table must have a single column that uniquely identifies each row in that table. Therefore, the schema used for analysis must be known to meet these criteria.
When using SSAS as a source, realize that PowerPivot still makes a copy of the data and builds its own in-memory cube.
Create a SharePoint strategy for PowerPivot document management and workflow.
Consider ramifications of Office 2010 32-bit versus 64-bit.
Appendix
Following are the full URLs for the hyperlinked text.
1 PowerPivot Overview msdn.microsoft.com/en-us/library/ee210692.aspx
2 PowerPivot Technical Diagram: PowerPivot Client/Server Architecture http://sqlcat.com/whitepapers/archive/2010/04/23/powerpivot-technical-diagram-powerpivot-client-server-architecture.aspx
3 Microsoft SQL Server 2008 R2 PowerPivot Planning and Deployment http://sqlcat.com/whitepapers/archive/2010/04/14/microsoft-sql-server-2008-r2-powerpivot-planning-and-deployment.aspx
4 PowerPivot Technical Diagram: PowerPivot Security Architecture http://sqlcat.com/whitepapers/archive/2010/08/17/powerpivot-technical-diagram-powerpivot-security-architecture.aspx
5 Server Installation http://powerpivotgeek.com/server-installation
6 PowerPivot part 1 https://technet.microsoft.com/en-us/edge/video/ff711395
7 PowerPivot for SharePoint – Existing Farm Installation http://sqlcat.com/whitepapers/archive/2010/09/07/powerpivot-for-sharepoint-existing-farm-installation.aspx
8 Powerpivot-info.com http://www.powerpivot-info.com/
9 The Great PowerPivot FAQ http://powerpivotfaq.com/Lists/TGPPF/AllItems.aspx
10 Microsoft PowerPivot for Excel and SharePoint http://www.amazon.com/Professional-Microsoft-PowerPivot-SharePoint-Programmer/dp/0470587377/ref=sr_1_3?ie=UTF8&qid=1294947867&sr=8-3
11 Mediterranean Shipping Company: Shipping Company Makes Critical Data Available Faster with New BI Tools https://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000006944
12 BNZ: New Zealand Bank Turns to New BI Tools for Delivering Vital Branch Performance Data Processes https://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000007015
13 CareGroup Healthcare System: Healthcare Group to Enhance Information Access with Powerful Business Intelligence Tools https://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000007023