Harnessing the Power of PowerPivot in the Enterprise
How to build a PowerPivot Competency Center
- By Patrick Bolin
- July 7, 2010
by Patrick Bolin
For many years, corporations have wrestled with the challenge of data management. Some of the brightest minds in business and technology have dedicated their careers to creating “a single version of the truth” or “a 360-degree view of the business.” There are a plethora of acronyms to describe this challenge -- BI, EDW, MDM, EIM, ECM, BPM, BSO, OLAP, ETL, ELT, etc. Although we are far from winning the war on data, the fortunate of the Fortune 1000 have at least recognized that information management is an important, strategic, and valuable investment. Today, a new and fearsome challenge arises -- Microsoft PowerPivot, a feature found in Microsoft Excel 2010.
Over the past year, I have watched rooms full of BI professionals drool over the promise that this tool brings. PowerPivot may be the spark that starts an analytical wildfire never before seen in corporate America. On the other hand, it could also be the catalyst that ushers in an unprecedented period of confusion and chaos, potentially unwinding the good that has come from our years of investment in the discipline of information management.
The issue here is not the tool itself. From a technical perspective, PowerPivot provides an incredible opportunity for organizations to implement true self-service business intelligence, harness the power of Microsoft Office and encourage incredible collaboration around analysis and decision making. However, as we have learned, technology is not the only consideration.
The real question facing CIOs is, “When I look back on my decisions about PowerPivot, how can I guarantee that I was viewed as a pioneering thought leader?” The answer, though far from simple, is to take a disciplined approach to establishing a PowerPivot Competency Center. We will examine the steps to this approach in this article.
Understand its Purpose and Intent
The first question any organization implementing PowerPivot must ask is, “What is its purpose and intent?” This should be tied directly to the company’s information strategy and culture. Where does PowerPivot fit in the plan for information consumption and dissemination? What kind of information culture do I have or want?
For an innovative culture, PowerPivot may be an excellent tool for information workers to explore, hypothesize, examine, and use for collaboration. Conversely, a culture that needs controls, structure, and procedure may find PowerPivot more useful when confined to a small group of users who then publish structured information to the masses. The key to success is developing guiding principles that create shared understanding and build momentum toward a stated, meaningful purpose.
Foster Learning and Development
Understanding purpose and intent is only part of the equation. As with any successful journey, the step before the first step is preparation.
One of the promises of PowerPivot is technical simplicity. On the surface, this may seem true, but the reality is the exact opposite. How many IT organizations have spent significant amounts of money to take over MS Access applications that were written by business users? Most companies have, at some point, spent a fortune transitioning, changing platforms of, or supporting mission-critical applications written in MS Access by users who are, shall we say, less than savvy technically.
PowerPivot could easily create a far greater drain on an unsuspecting IT organization. Not only can users create desktop applications (similar to Access), but they will also have the ability to publish their creations to the masses using shared infrastructure, a frightening prospect for many CIOs.
The PowerPivot Competency Center should first focus on preparing and educating. Education starts with creating a vision and helping users take the first step. In this case, it is toward building personal applications. New users will need to understand basic BI concepts such as data merging, dimensional analysis, creating basic calculations, filtering, and other important building blocks. They will also need to have an understanding of how to validate and test their logic and reconcile results to source data. Intermediate users will need to understand more advanced topics such as master data management, data quality, advanced metric calculation, visual design, and advanced testing practices.
These users are preparing to move from pure analysts to publishers. Publishers have to understand the importance of establishing trust in their data, and they also have to know how to help users consume it in a meaningful way.
Advanced users have an even greater burden. They have to understand their role as “trusted advisor” to the organization. They have to understand the relationship between the information they publish and the behavior it will generate. Poorly planned publication can lead to chaotic, disruptive behavior, poor decisions, and costly organizational distraction.
Successful adopters of PowerPivot will be led by a Competency Center that focuses on creating an educated, thoughtful community that applies appropriate discipline to the creation of personal and shared PowerPivot applications.
Implement Patiently
One of the greatest temptations any organization faces is giving in to the torrent of pressure that comes with the introduction of ground-breaking technology. Cautious adopters mistakenly avoid it completely because they fear losing control. The real answer lies in the middle. Appropriate adoption of new, meaningful technology such as PowerPivot can bring huge, immediate returns, build incredible momentum, and propel the perceived value of IT into the stratosphere.
The PowerPivot Competency Center must understand the organizational balance between risk and return and shepherd the adoption process forward at a brisk -- but manageable -- pace. Understanding the PowerPivot application lifecycle, including when to use and not use the technology, is critical.
There are three critical junctures in this lifecycle.
- The first is determining whether the application is PowerPivot appropriate. PowerPivot is a great tool, but it should not be the answer to every BI question.
- The second is understanding when a personal application is ready to be published into a corporate SharePoint environment. This opens up a myriad of questions about information security, data quality, data accuracy, metric certification, and environmental performance. The Competency Center must provide appropriate safeguards so published applications contain reliable, responsive, appropriately shared content without becoming mired in process and bureaucracy.
- The third critical branch is when PowerPivot is no longer the appropriate solution. There is no cut-and-dried answer, but it is foolish to think that PowerPivot applications will initially be robust enough to replace traditional delivery mechanisms such as Performance Point, Analysis Services, or Reporting Services, much less a well-architected data warehouse or data mart. This line will morph over time as features and functions grow and merge, but the wise organization will have mechanisms to identify and convey candidate applications into the IT budgeting and prioritization process.
Enjoy Success
The old adage fail to plan, plan to fail has never been more true. Creating a PowerPivot Competency Center doesn’t require a large investment, but it is certainly a smart one. Successful adoption of PowerPivot will be a journey of many steps, similar to any successful trip into uncharted territory: