PowerPivot’s Role in Enterprise BI
How PowerPivot can be incorporated into your enterprise BI projects.
by John Lauer
Last week I pulled a new pair of running shoes out of the box and laced them up. As much as I love running and a pristine new pair of shoes, I have no intention of giving up my bicycle or car and running everywhere in my new shoes! All these methods of locomotion have a proper place in my life.
This summer, business intelligence (BI) users and technologists everywhere are eyeing a shiny new pair of “shoes” called PowerPivot. Some users are imagining a world free of IT reliance where they can run anywhere and do anything all by themselves. Others are a bit afraid of the new shoes and want to keep using the tried and true bicycle and car for everything. Many are just confused about exactly what, when and how they are supposed to use these new shoes.
This article is to help those grappling with this dilemma understand when they should run, when they should pedal, and when they should fire up the car and drive.
What is PowerPivot?
PowerPivot consists of a pair of new products from Microsoft; one is for Excel, the other is for SharePoint.
PowerPivot for Excel empowers information workers to build BI solutions on their own — a cube, reports, and dashboards — without any code. This powerful add-in to Excel 2010 makes it possible to work with large volumes of data ( 100 million rows), combine and refresh data from multiple sources, develop complex measures (year over year, ratios, etc.) using DAX, and design dashboards and pivot table reports. Everything is compressed and stored right in the Excel file and the PowerPivot add-in runs a dynamic Analysis Services cube locally.
PowerPivot for SharePoint allows users to publish, share and schedule automatic refresh of the solutions they’ve built. There, users can view and filter the reports right in the browser. This extends PowerPivot beyond personal BI into the realm of team BI.
Not a Replacement
Now that we know what PowerPivot is, let’s examine scenarios for appropriate use of PowerPivot.
The first and most important point is that PowerPivot should not replace traditional BI structures in the BI environment. The data warehouse, data mart, and OLAP structures already in place are still needed. Traditional fixed-format reporting (such as monthly financial packets) should still be created. You will continue to develop traditional OLAP cubes for data that is commonly accessed by groups of users.
BI professionals should continue to acquire, transform, cleanse, summarize, and otherwise prepare enterprise data for consumption by users. If the governance, control, and value-added analysis these processes and structures impose on the data were omitted, users would likely become confused with complicated and unclean data and could quickly undo many years of trust built into enterprise data.
