Spreadsheets run amok 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, 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 collect data from different sources at different levels of granularity at different times. Thus, each spreadsheet represents a unique view of the organization.

Data warehousing experts continuously caution against creating analytical silos –­ or independent data marts –­ that aren't integrated with a data warehouse in a top-down architecture or other data marts in a bottom-up, dimensional bus architecture. However, most overlook the culprits in plain sight –­ the dozens or hundreds of independent data marts masquerading as spreadsheets. We call these types of nefarious analytical silos "spreadmarts."

In addition to undermining a single version of truth, spreadmarts waste a lot of time and money. By collecting and massaging their own data in spreadsheets, business analysts duplicate effort and data, hurting corporate productivity and increasing costs. Spreadmarts also turn business analysts into pseudo data warehousing managers by forcing them to spend an inordinate amount of time extracting and transforming data from the spreadmarts instead of analyzing it.

Exasperated by spreadsheets feuds and their drain on corporate productivity and effectiveness, many executives finally recognize the need to invest in a data warehousing infrastructure and create a single version of the truth.

However, 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 against the spreadmarts that their data warehousing initiative is designed to replace.

Weaning Spreadsheet Jockeys

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.

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.

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 enterprise-wide buy-in to corporate metrics, the informational turf wars will dissipate.

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-subject views of data, external data or ways to collaborate with others about the reports (e.g., publishing the report to a shared portal, annotation, built-in threaded discussions, and so on). Unless the data warehousing environment provides at least 150 percent of the value of the spreadmarts, you won't stand a chance of stamping them out. Users have to recognize they are gaining more than they are giving up.

Make it easy to use and fast. You need to make sure that your spreadmart replacement has no serious blemishes. That means the new business intelligence environment needs to be extremely easy to use and must return results quickly. A general rule of thumb is that users should not have to wait more than five seconds to download or refresh a report or run a query. Skeptical spreadmart users will grasp for any reason, no matter how small, to avoid using your solution. Don't give them any ammunition!

Go with the flow. Some users will never give up their spreadsheets, regardless of how robust an analytic environment you provide. 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.

Many vendors now offer "managed spreadsheet" environments. This means that the spreadsheet is the front end to an analytic server, replacing the tool's user interface. For example, Business Objects offers BusinessQuery for Excel and Actuate offers e.Spreadsheet Option. Just make sure these front ends actually access a corporate data warehouse rather than an independent data mart, which is just a bigger version of a spreadmart.

Also, planning, forecasting and budgeting applications, such as those from Cognos, Hyperion and Comshare, are designed to bring order to spreadsheet chaos. It's great to see these vendors integrate these semi-operational applications with traditional business intelligence toolsets. These integrated business performance management (BPM) environments give spreadsheet users no excuse to remain lone rangers.

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

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.

Source of the Problem?

These remedies suggest that sometimes we, as data warehousing managers, contribute to the proliferation of spreadmarts. If our data warehouses are not providing enough value or incentive to lure folks away from their spreadsheets, we are missing the mark. In fact, a key performance indicator (KPI) to assess the health of your data warehousing environment might be: The health of a data warehousing environment is inversely proportional to the number of spreadmarts in the organization. That is, the more spreadmarts that exist in your organization, the more likely it is that your data warehousing environment is not meeting end-user needs. The fewer the spreadmarts, the more likely it is that your analytic environment is providing significant value to business users. Just as a thermometer can tell us when we have a fever, the existence of spreadmarts tells us we have to work harder to make our data warehousing environment more attractive to business users.

Although the data warehouse was funded to eliminate dueling spreadsheets and the informational chaos and feuding they cause, it is often culturally and politically challenging to convert business users to a centralized information utility. Following the tactics outlined in this article 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.