Business Intelligence in the Hands of the Business: Introducing PowerPivot

I have been exploring a tool, PowerPivot, in anticipation of using it as a business intelligence and performance management tool and I thought I would share my thoughts on it and how it can be used.

Microsoft recently released PowerPivot for Excel formerly code named Project Gemini. PowerPivot will be officially released as a part of Office 2010, but the beta versions are now accessible if you want to try it out ( What PowerPivot does is provide OLAP functionality within an Excel spreadsheet. This can either be used directly on the desktop or combined with SharePoint to publish data to the enterprise based on an Excel spreadsheet, rather than something created by the IT department.

For business intelligence professionals, the term OLAP is immediately recognizable while business users may find it a strange acronym. If you're not familiar with the term OLAP, you have undoubtedly seen or used its functionality. OLAP "Cubes" are what provide the ability to rapidly slice, dice, and drill through data from a data warehouse. In today's world, if you are a consumer of OLAP data from your data warehouse, then you are somewhat reliant on the IT department to provide the data in the cube and structure it in a way which you can then access it. If you need changes to the way the data is structured, or if you need to add more data, come up with calculated values, etc., you need to go to your IT department to make these changes. Given the amount of support from most IT departments, this can be a daunting task which includes a lot of resources, time, and getting on the priority schedule. PowerPivot goes a long way toward solving this problem by putting the ability in the hands of the end user to extend and restructure the cube instantly, as well as to publish these results to others.

To make this a little more real, let's examine a potential scenario. In this example you are a member of the budget office in a government agency and you are asked to prepare an expenditure report for the agency's director. In this report, the director wants the ability to drill through agency budget and actual figures by fund, division, work unit, and expenditure type for the fiscal year. Additionally, the director wants to be able to see the current and accumulated budget variance for each period. Finally, the director wants the information published to an internal website where executives and division directors can view and drill through the reports. Let's also assume that the agency has a finance data warehouse, but does not have a system which directly supports the report being asked for by the director.

In an organization without a data warehouse or other data store accessible by business users, one of two solutions would be available to get the director what she needs. The finance department could get existing budget and finance reports and then take values from these reports and put them in a series of spreadsheets stored on a network drive. Each month, the spreadsheets would need to be manually updated by budget staff. Alternatively, they could work with the IT department to get resources prioritized to design and implement the report. This could take a very long time depending on the IT department's current workload, and it would be a brittle solution because any changes down the road would require IT resources to fix the problem.

If the agency has a data warehouse already, the budget staff could create a spreadsheet which connects to the data warehouse and uses Excel pivot tables to get the data. The data could even be extended within the spreadsheet to calculate the variance amounts (assuming they are not tracked within the data warehouse already). This type of solution, which was provided by Microsoft in the Office 2007 release, goes a long way toward solving the problem. The most significant drawback, however, is that the variance amounts are only tracked within the individual spreadsheets rather than being available within the cube for further analysis. This means the variance figures need to be individually managed within the spreadsheet at each level in hierarchy of drill-down. PowerPivot solves this problem.

If we use PowerPivot in our scenario, the solution becomes more elegant. Instead of any involvement from IT, the budget analyst connects a PowerPivot workbook (similar to an Excel workbook) to a data source to get the expenditure data. Then, the analyst creates two new calculated fields (current and accumulated variance) within the PowerPivot workbook. Once created, the analyst attaches a pivot table from a standard Excel workbook to the PowerPivot table and can drill through the data including the variance amounts! Finally, the analyst publishes the spreadsheet through SharePoint and it is available to anyone in the organization with sufficient security rights. As the data is updated in future months, the reports are updated automatically.

Example of a PowerPivot table. As you can see, it's not that daunting if you have used Excel before:

PowerPivot Architecture (without SharePoint):

Leave a Comment

Leave a comment

Leave a Reply