Excel and PowerPivot – Missing the Point

Steve Gray posted a question about Excel 2010 and PowerPivot on the LinkedIn Microsoft Business Intelligence Group.  See the thread at http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=12822397&gid=59185&commentID=29629258&trk=view_disc

Steve wanted to know the business-level benefits of Excel and PowerPivot for the SME end-user.

Here’s my response with some editing.  My original comments can be found in the thread referenced above.

The bottom line is end-users are going to try to build it themselves.  I’ve seen some real monstrosities of Excel spreadsheets.  End-users, using tools they are familiar with and real work to be done, invest huge amounts of time and effort to get their spreadsheets just right.  By just right, I don’t mean pretty, I don’t mean well-formatted, I mean productive.

From a technical perspective, I don’t see Excel as an elegant repository for data warehousing or business intelligence.  But the real value of these solutions are in the effort of the end-users to craft a solution that meets their needs.  They have a sense of ownership.  They view their technically inelegant solutions like their children and love them unconditionally.  They interact with them daily and their current form is a result of weeks, months or even years of evolution.

There are many reasons to mature a user-created spreadsheet into a full-blown data warehouse, data mart and/or business intelligence facility.  Excel doesn’t scale, each spreadsheet becomes an island of information, data quality issues, data lineage issues; all of which can be solved with the application of data warehousing and business intelligence principles and infrastructure.

How do you leverage the strengths of the user-created solution?  Acknowledge them.

  • User created solutions solve a specific problem or problems.  This has been vetted by the user themselves.
  • The business knowledge encapsulated by the user-created solution is extraordinarily valuable and may not be documented in elsewhere.
  • The users are committed to the solution.  Software and hardware investments are wasted unless someone uses them and the business derives value from them.  Adoption is the route to reaching return on investment.

Businesses would benefit from periodically evaluating user-created solutions and have a process for on-boarding and maturing them.  The question is how to do this effectively, without alienating the end-user or end-users who created them.

Let’s face it:  for data warehousing and business intelligence professionals, creating a data warehouse or data mart, crafting ETL processes and building OLAP cubes are fairly easy when the sources and target schemas are well-defined, when data quality metrics are made clear and user-interface expectations are specified. 

Star or Snowflake schemas are fairly easy to figure out when you know what the dimensions and facts are going to be.  Your sources will be defined by the end-users themselves.  The end-user already knows how they massage the data when populating their spreadsheets.  The user interface is already defined in their spreadsheet.  The end users know where their data and information is going to wind up (they usually copy-and-paste it into another system or set of “reports”).

The hard parts of data warehousing and business intelligence are deriving the use-case scenarios and user expectations.  Missing the mark on these two points are where most DW/BI implementations fail.  They are also most the time-consuming and expensive portions of any DW/BI implementation.

But when your users have gift-wrapped their expectations and can communicate their use-case scenarios; the risk, the effort and the expense of a DW/BI implementation becomes much more reasonable.


  • Acknowledge those strengths publicly and loudly.
  • Associate the creators of those solutions with the business value they have delivered publicly and loudly. 
  • You want the end-user’s cooperation.  They are going to become your biggest, strongest and most important evangelists.  They are going to have a stronger relationship with your intended end-users than you will.  The end-user who created the spreadsheet will have more in common with the end-user who uses your solution than you do.  They view each other as brothers (and sisters) in arms.  They live in each others world.  You are the interloper.
  • Allow the end-user to retain their sense of ownership.  Allow them to champion the ultimate solution
  • Become a servant to the business.  DW/BI skills and solutions can be incredibly valuable to the business, but realize that unless you work for a DW/BI consultancy, you are a cost-center.  You can help the profit centers do their jobs.
  • Create a process that helps the user-created solutions “grow up”.  By “grow up” I mean: increase their utility, scale, interoperability and reliability.
  • Keep the user-interface as close to the original spreadsheet as is possible.  This keeps the end-user invested, gives them the opportunity to show off “their” spreadsheet and train other users in its use.  Future systems can leverage the information derived from the next-generation DW/BI system.

Do Not:

  • Denigrate the technical choices that the end-users made.  They are generally business people, not technologists.  Excel formulas and VBA scripts are not technically elegant, even when well implemented.  But even with those blunt tools, the users have created something with utility and its own beauty.
  • Do not question the business processes modeled within the spreadsheet, seek to understand them and show your appreciation for them.
  • Do not co-opt the project.  DW/BI efforts are a ton of work and you (and your team) are going to work hard and work long hours.  There is a very reasonable urge to show how important your team’s contribution is to the final effort.  But this is going to alienate the end-user who created the solution and you will lose your best evangelist.
  1. #1 by Jaimie on January 17, 2011 - 10:18 pm

    First, Excel, PowerPivot, etc. are not for data warehousing and are not claimed to be whatsoever. I have had this discussion before with people. I am sure Excel and Access are used for DW’s, but none of us can stop people from using products for their unintended uses for any product, IT or otherwise (ie: the backend of a screwdriver to hammer in nails).
    Second, process and governance are required to be successful with any self-service BI tool or paradigm and you mention what happens when these are absent. Excel Services and PowerPivot can make it very easy for users and companies to forget about process and governance, but again no one can stop people from hurting themselves.
    The purpose of these tools is to help customers reduce the time-to-benefit for their BI efforts and maximize ROI. Using a self-service development model leveraging very familiar tools is more efficient than just about any other product scenarios on the market. The biggest barriers I always see are customers not owning the correct level of SharePoint license or the correct version of Excel, which are cheaper than purchasing any other tools.
    Typically, I have seen these “monstrosities” when customers do not have process, governance, or the right software licenses. On the larger scale the “monstrosities” grow as work-arounds for when the items I mention are lacking and in the end become a lot more problematic and expensive than “doing things right”. Once again, can’t stop people from hurting themselves and/or being short-sighted.

  2. #2 by Marc Jellinek on January 17, 2011 - 10:52 pm


    I agree with all of your points. But I also see the things you say as causing a divide between end-users who need to get work done and IT departments who want “to do things right”.

    As long as there is a divide, IT will be seen as an interloper. They will be seen as telling the end-users (or the business) that they are begin short-sighted or hurting themselves.

    My post is about taking the misbegotten bastard step-children of BI and their parents and bringing them into the light… without making them feel like misbegotten bastard step-children.

    I see the user-developed tools as being a phenomenal prototyping situation. Like Fred Brook’s “The Mythical Man-Month” says in Chapter 4, “Throw the First One Away”. This is where needs, requirements and desires are identified by the people closest to the problems. Don’t expect the solution they come up with to be technically elegant.

    I guess the core point of my post is that once you acknowledge the value of the end-user’s contribution, it actually makes it much easier, much more effective and much lower risk when it comes time to grow their proto-solution into an enterprise-class facility. By bringing the end-users into the process in a positive way, you can almost guarantee that you will have an end-user evangelist/trainer and accerate adoption of the final (enterprise-class) facility.

    The only reason I haven’t seen as many “monstrosities” on the other side of the coin (ie: a beautifully implemented system of ETL->ODS->ETL->DW->DM1, DM2… DMx->OLAP1, OLAP2…. OLAPx) are these systems are expensive and time consuming to build. But I have seen them where they miss the mark, regardless of the elegance of the implementation.

    The primary benefits of Excel are the ability of end-users to create useful solutions without a huge investment. Anyone who can click a mouse can create an Excel spreadsheet. Solutions can (and will) grow organically. One way of thinking about this is: “You can shoot yourself in the foot faster and cheaper”. That means there are going to be more of them.

    At the end of the day, unless the facility is providing business advantage and is adopted by the end-users, it’s a failure.

  3. #3 by Kevin Cox on January 18, 2011 - 1:32 pm

    Nice rebuttal to the original article and good followup discussion. I actually had to go join the LinkedIn group to see the original article.
    Kevin Cox, SQLCAT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: