| COLUMN |
Getting control of runaway Microsoft Excel spreadsheets |
 |
|
|
It's amazing that so many organizations run large chunks of their businesses on a $200 piece of software invented more than 25 years ago. But nothing quite offers the same level of functionality and personal control as Microsoft Excel spreadsheets. Individuals can use Excel to create simple charts and reports, query remote databases, generate complex models and planning scenarios, and create sophisticated budgets, forecasts and operational reports. Its low cost, ease of use and power have made it a ubiquitous fixture in offices around the world.
Although Excel makes a great personal productivity tool -- which is its purpose -- it makes a lousy information system. Just because users can create a management report, an executive dashboard or budgeting program in Excel doesn't mean that they should. Microsoft Excel spreadsheets give individuals too much leeway to create their own view of the business and spin the numbers in a way that best suits their interests. Users also spend too much time collecting, integrating, aggregating and distributing data -- which is the job of the IT department -- rather than doing the jobs they were hired to do.
Too many organizations now are paying the price for runaway spreadsheets, which I call spreadmarts, because they look like spreadsheets but function like independent data marts. Organizations must gain control of spreadmarts if they are going to use information to operate more efficiently and compete more effectively. Without centrally defined metrics and a single version of corporate information, organizations can't compete effectively.
Spreadmarts proliferate like weeds -- organizations have dozens, if not hundreds or thousands, of these pernicious analytic structures. Research from The Data Warehousing Institute shows that organizations on average have 28.5 spreadmarts. But in reality, most organizations have no idea how many spreadmarts they have, and many have given up trying to control their proliferation.
Remedies
Although spreadmarts are difficult to eradicate, there are remedies for curing this "disease" before it poisons the entire organization. Here are five strategies -- the five "C's" -- for eradicating spreadmarts:
1. Coercion: Have the CEO mandate the proper use of spreadsheets and desktop databases. By itself, this strategy rarely works because it is difficult to enforce. In fact, coercion usually makes the problem worse. Users go underground, managing their divisions and departments with clandestine spreadmarts that run parallel to "official" systems.
2. Conversion: This strategy involves selling the benefits of the organization's standard business intelligence (BI) environment. The key is to make sure the BI environment provides at least 150% of the value of spreadmarts, which is sometimes difficult. The BI tool and the data warehouse should:
- Save time collecting and formatting data.
- Provide deeper insights since it provides more data
- Provide additional functionality, such as the ability to schedule, share, or annotate reports; collaborate with colleagues; and easily publish reports to a portal.
- Provide support via the BI team.
3. Co-existence: This strategy turns Excel into a full-fledged client to a BI server. Rather than force users to switch tools, let them use Excel to access data and reports on the BI server. This gives them all the spreadsheet features and lets the organization manage critical data and reports in a standard way. This is perhaps the best option when used in conjunction with No. 2 above.
4. Co-option: This strategy automates spreadmarts by running them on a central server maintained and managed by IT. IT doesn't change the data access methods, processes or rules set up by spreadmart users. It just maintains them on their behalf, freeing users to spend more time analyzing data and less time collecting and massaging it. Gradually, over time, the IT department can transfer the spreadmarts to a more standard environment. Several BI vendors now offer co-option tools.
5. Cower: The last strategy is a variation of the first one. Sarbanes-Oxley regulations in the United States provide the IT department with a huge stick to enforce data management standards across the organization. Since most top executives would prefer to stay out of jail, it often doesn't take much to convince them to support an enterprise architecture that standardizes the use of tools and data to deliver key financial reports, among other things.
The five remedies above can help you get control over spreadmarts. But the key is to be patient. Analytic habits don't change overnight. With a heavy dose of patience, strong communications skills and a robust BI environment, you should make steady headway in controlling the proliferation of spreadmarts.
Wayne Eckerson is director of research at The Data Warehousing Institute, a Seattle-based provider of in-depth, high-quality training and education in the data warehousing and business intelligence fields.
|
|
|
|
