Chapter 11. PowerPivot and Excel

"Easy" is a relative term.

Depending on how you count, Excel is arguably the most popular reporting and analysis tool on the planet today. This is not to say it is the best tool, but its broad availability, powerful expression language, programming capabilities, fine grained formatting functions, and data accessibility make it the starting point for most business analysts.

Microsoft's PowerPivot add-in for Excel 2010 takes Excel reporting and analytics to a whole new level. PowerPivot for Excel is an in-memory database add-in that allows Excel users to work with millions of rows of data at memory speeds. This evokes a high level of enthusiasm among its supporters. One of Microsoft's white papers on PowerPivot includes the line: "The ultimate goal of PowerPivot for Excel is to make data analysis really easy." It goes on to describe PowerPivot as "...a new product that provides self-service BI (Business Intelligence) functionality for users of Microsoft Office." It sounds like all you really need to do is hand out Office 2010 licenses and set free all that self-service BI. Once you've heard these kinds of statements enough times, you might start to question your efforts to create a full-scale DW/BI system. In fact, you're probably wondering why you read this far in the first place.

Of course, reality is seldom as rosy as marketing would have you believe. PowerPivot is the equivalent of pivot tables on steroids. You can load and effectively work with much ...

Get The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.