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.
- 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.