In-Depth
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.
Use PowerPivot for Self-Service Reporting
PowerPivot can play a large role in getting IT out of the report writing business! With data available in traditional structures, it’s time to turn users loose with PowerPivot as a self-service reporting tool. PowerPivot is one of the easiest available reporting tools to use; once data is acquired by users, PowerPivot functions almost identically to familiar Excel Pivot Tables and charts. This allows the user to effectively create reports themselves, and if PowerPivot for SharePoint is implemented, publish those reports for other team members to work with.
By providing this level of self-service functionality to end users, Power Pivot frees technical resources’ time to focus on items that have larger enterprise-wide impact (such as making enterprise data consumable for self-service users).
PowerPivot is a power-user tool with a learning curve. Training will be a big part of the success of PowerPivot in any organization.
Use PowerPivot for Prototyping
PowerPivot is an excellent tool for prototyping to help gather and solidify requirements for traditional BI structures such as multidimensional cubes. In my experience, it is extremely rare to find users who can provide solid and complete requirements for a cube. Most simply can’t think through all the possibilities provided by the cube structure and express the types or extent of analysis they want to perform. Building and exploring prototypes with users has consistently proven to be the best method to extract this information, though it is a time-consuming process.
Because the entire premise underlying PowerPivot is to allow end users to build cubes on the fly without coding and without a development environment (other than Excel), it is a perfect fit for prototyping -- especially given the agile nature of tweaking and modifying a PowerPivot solution. Some prototypes may be deemed “good enough” for production or they can be used as a starting point for IT to build an enterprise-class BI solution that provides the data-quality checks, security, performance, and availability that enterprise solutions require.
Use PowerPivot for One-Off Integration of Non-Enterprise Data
Combining non-enterprise data with enterprise data may be the ultimate sweet spot for PowerPivot in the BI world. I have already discussed the importance of creating traditional BI structures and know that the majority of the data should come from those structures, but what happens when the CFO asks to compare corporate data against the latest industry information available on the Internet? Often the additional data is internally instead of externally sourced but hasn’t justified the time and effort to integrate into the formal BI structure. PowerPivot is the perfect solution for this recurring problem.
PowerPivot lets users easily integrate acquired data alongside sanctioned enterprise data. It supports multiple data connections in the same model and can import data from a variety of sources, including text files, SharePoint lists, Internet data feeds, Excel files, SQL Server databases, Analysis Services cubes, Reporting Services reports, and any other database that supports open connectivity standards such as OLE DB or ODBC.
The power user can satisfy that CFO's request by connecting to the corporate data warehouse for the enterprise data and using one of the other connection types to link in the Internet data. As long as there are common elements in both sources for creating the relationship, the data can be seamlessly integrated for reporting.
The Bottom Line: PowerPivot is for Personal and Team BI
Microsoft PowerPivot should be used for personal BI authoring and sharing with small teams through PowerPivot for SharePoint. It is a valuable new tool that allows analysts to “do their own thing” without heavy IT involvement. However, PowerPivot is not -- and should not be considered as -- a replacement for traditional enterprise-wide BI solutions.
Like me with my new running shoes, BI users and technical professionals should embrace their new tool, but they should also avoid the temptation to run everywhere with it! Instead, mix PowerPivot into the BI environment and strategy, leveraging the strengths provided by each available option. If the recommendations in this article are followed, PowerPivot and the rest of the Microsoft BI suite will provide all the options and functionality needed for many satisfying miles of reporting and analytics.
John Lauer is a senior manager at Hitachi Consulting where he leads the company’s Microsoft BI Solutions Practice in the U.S. He has more than 16 years of experience in IT and consulting, with 10 years specializing in business intelligence and performance management. You can contact the author at jlauer@hitachiconsulting.com