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

Case Studies and References

Examples of successful architectures are described in the following case studies and white papers.

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.


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 http://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 http://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 http://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 http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000007023