Columns

Taming spreadsheet jockeys

Despite the hype about metrics-driven organizations, no one has yet put forth a key performance indicator to evaluate the health of a data ware-housing environment, so here is one: The health of a data warehousing environment is inversely proportional to the number of spreadsheets used as data marts in the organization. Spreadsheets run amok in most organizations.

They proliferate like poisonous vines, slowly strangling companies 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 firms 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 to spin the data to reflect positively on their group, sometimes 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 to collect data from different sources at various 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, analysts duplicate effort and data. This hurts corporate productivity and increases costs. It also turns business analysts into pseudo-data warehousing managers because 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 eliminate 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 with 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 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 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 one 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 enterprise-wide 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% of the value of the spreadmarts, you won't stand a chance of stamping out spreadmarts. Users have to recognize that 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 to 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 e-mail. 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 that 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 the 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.

Following the above tactics 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 communication 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.

About the Author

Wayne W. Eckerson is director of education and research for The Data Warehousing Institute, where he oversees TDWI's educational curriculum, member publications, and various research and consulting services. He has published and spoken extensively on data warehousing and business intelligence subjects since 1994.