Taming Spreadsheet Jockeys

By Wayne Eckerson, Director of Education and Research

Spreadsheets as Data Marts

For all the hype about metrics-driven organizations, no one has yet put forth a key performance indicator to evaluate the health of a data warehousing environment. So, here is one:

The health of a data warehousing environment in inversely proportional to the number of spreadsheets used as data marts in the organization.

Spreadsheets run amuck in most organizations. They proliferate like poisonous vines, slowly strangling organizations by depriving them of a single consistent set of information and metrics to evaluate corporate performance and develop tactical and strategic plans.

Don't get me wrong. There is nothing inherently bad about spreadsheets. They provide valuable functionality when used for their intended purpose as personal productivity tools. Unfortunately, too many organizations allow business analysts and others to transform spreadsheets into data marts and corporate reporting engines.

Feuding "Spreadmarts"

Many of us have witnessed meetings that have degenerated into battles among business analysts who argue vociferously about the merits of the data in their spreadsheets. They spend more time arguing about "whose data is right" than helping their executives discuss strategy and make decisions. Many exploit spreadsheets to protect their organizational "turf" and spin the data to reflect positively on their group, some- times at the expense of others.

These spreadsheet feuds make it impossible for organizations to create and maintain a single version of the truth. Spreadsheets make it too easy for groups to define their own metrics and collect data from different sources at different levels of granularity at different times. Thus, each spreadsheet represents a unique view of the organization, an independent data mart--or "spreadmart" if you will--whose data cannot be reconciled with other spreadmarts.

Moreover, by collecting and massaging their own data in spreadsheets, business analysts duplicate effort and data. This practice hurts corporate productivity and increases costs. It also turns business analysts into pseudo-data warehousing managers, since they spend an inordinate amount of time extracting and transforming data instead of analyzing it.

Exasperated by spreadsheet feuds and their drain on corporate productivity and effectiveness, many executives finally recognize the need to invest in a data warehouse and create a single version of the truth. But deploying a data warehouse doesn't necessarily get rid of spreadmarts or the mentality that created them. Analytic habits die hard, especially when there are political or cultural reasons to resist change.

Consequently, many data warehousing managers find themselves in the untenable position of competing for time, money, and attention against the spreadmarts that their data warehousing initiative was designed to replace.

Weaning Spreadsheet Jockeys

So, what is a data warehousing manager to do? How can you change analytic habits, resolve informational turf wars, and eliminate spreadmarts that hold hostage the notion of creating a single version of the truth? To many, these may be insurmountable obstacles. Spreadmarts have caused the death of more than a few data warehousing initiatives. However, there are a number of things you can do to tame spreadsheet jockeys and their spreadmart creations.

1. Start at the top. Build your first application to support the organization's top executives. Once they start using the data you provide, everyone else down the hierarchy will be forced to follow suit.
2. Throw a pizza party. No joke. The only way you can get a single version of the truth is to get all the relevant business parties in a room long enough to hash out common definitions and rules for key business metrics. Buy plenty of pizza, lock the door, and post the CEO outside. Once you get enterprisewide buy-in to corporate metrics, the informational turf wars will dissipate.
3. Provide an incentive. Offer data, reports, and functionality that business users cannot get anywhere else. For example, the data warehouse (or dependent data marts) may provide cross-functional views of data, external data, or ways to annotate and distribute individual reports, among other things. Unless the data warehousing environment provides at least 150 percent of the value of the spreadmarts, you won't stand a chance of stamping out spreadmarts. Users have to recognize they are gaining more than they are giving up.
4. Make it easy to use and fast. Unless the new business analytic tools make it easy for business users to access, find, and use the data warehousing environment, you lose. Also, users should not have to wait more than five seconds to download or refresh a report or run a query.
5. Go with the flow. Some users will never give up their spreadsheets no matter how robust an analytic environment you provide. So, let them keep their spreadsheets, but configure them as front-ends to the data warehouse. This way, they can use their spreadsheets to access corporate-approved data, metrics, and reports. If they insist on creating new reports, provide an incentive for them to upload their reports to the data warehouse instead of distributing them via email. For example, deploy a portal that lets them publish, subscribe, and collaborate on personal or workgroup reports.
6. Provide high quality data. If your data doesn't match users' expectations, they won't use it. This means you have to provide accurate data, and you must go out of your way to explain where you got it, how it was calculated, and why it might differ from the reports they've been used to seeing. This meta data must be integrated into the fabric of the application. That is, it must be displayed alongside the report or data elements that users are viewing.
7. Sell, sell, sell. Once you've deployed a robust data warehousing environment, never stop communicating and demonstrating its benefits. Show what extra value it provides and how much time and money it saves individuals and the organization. Emphasize what they get, not what they give up.

Summary

Following the tactics described here will help you stamp out spreadmarts and deliver a single version of the truth. The key is to be patient. Analytic habits don't change overnight. It will also take time for groups to transition their turf wars to other battlefields. With a heavy dose of patience, strong communications skills, and a robust data warehousing environment, you should be able to tame the spreadsheet jockeys, or better yet, convert them into enthusiastic proselytizers of the new environment.

(Article appeared in "Case Studies & Solutions," July 8, 2004)