Shedding Light on Data Shadow Systems

The Birth of a Data Shadow System

Generally, data shadow systems are not built to an overall design or architecture. In fact, they often evolve by accident, one analysis at a time.

Picture this: a businessperson performing an analysis cannot get all the information he needs from the existing IT systems. To solve the problem, he pulls data from a data warehouse or data mart into Microsoft Access, manipulates the data a few times with SQL queries/modules/tables (maybe a half a dozen times or more), pulls in some other data the business unit uses, puts the results into a spreadsheet and crunches some numbers to achieve his final analysis. Because he got what he needed, he does it again the next time. Then another person does it. A third person becomes the Microsoft Access "guru" and starts doing it for others. Before long you have hundreds of Microsoft Access databases and Excel spreadsheets that are used to gather and transform data. More and more of the business users' time is spent in the care of feeding of this data shadow system.

This leaves the IT manager scratching his head, wondering why the users are creating shadow systems when they already have great DW and BI tools. Often, the IT group pushes back on the business users and tells them how poorly their system is built. The situation gets to be adversarial as the business users tell IT they wouldn't have created the shadow system if the DW and BI solution did what is was supposed to do!

The Pain Grows

The IT group wants to build a corporate data warehouse or data mart using standard ETL and BI tools. That would have been fine in 1999, but now the business group can't afford a big-bang/big-bucks project. In addition, the business users don't have the time to implement a solution that will take 9 to 12 months just for the first iteration.

Just because the business group is pushing back on IT's proposed solution doesn't mean they're happy with their current situation. They want better quality, more complete data. They want to focus on their "real" jobs and spend less time on the care and feeding of the data shadow system. The business group is frustrated that the IT group doesn't understand their business or how they use the data in their department. They're tired of being told that they're violating corporate IT standards.

Meanwhile, the IT group's tight budget can't accommodate the proliferation of departmental shadow systems, even if everyone feels they need to be replaced.

Should the business continue to rely on the data shadow system? What happens when the shadow system gets bigger? Regardless of what anyone says, it will grow organically as the business users continually respond to new business pressures to analyze, understand and manage their company's business.

Moving Beyond Data Shadow Systems

You can't just take the data shadow system out behind the woodshed and shoot it. First, the business users and IT need to have a civilized dialog. Not lectures, but discussions.

Second, the two parties need to compromise. The business is using the data shadow system to do its analysis today and will continue to use it and expand it if nothing better comes along. A big-bang/big-bucks/big-direction project is probably not going to offer the business value or ROI regardless of how great the architecture is or what standards are followed. I have seen too many IT groups propose an all-or-nothing solution - either the business users do it the "right" way or it won't get done. Then the project doesn't get approved, the users go back to the expanding data shadow system, and the IT group feels it stopped the business users from implementing an "incorrect" solution. The reality is that without an alternative, the business group will continue to use a shadow system that neither group wants. That scenario, repeated time and time again, costs companies millions of dollars and undermines business productivity.

The business group and IT need to explore three scenarios:

1. The IT group architects and develops the "right" solution.
2. The business group continues to use and expand the data shadow system.
3. Together, they agree on a more cost-effective and timely solution.

In option #3 the groups examine a scaled-down version of the data shadow system replacement. This is the time to think outside of the box. Build a business unit database, i.e., data mart, to replace all the Microsoft Access databases. Include both the data initially extracted from the data warehouse and the additional lookup tables and external data that business users incorporated in the existing data shadow system.

Use an extract, transform and loading (ETL) tool to systematically pull data into the business unit's data mart and distribute the data to tools that the business users are using today, such as SAS. Often, the company's standard ETL tool may cost too much (in terms of license or resource costs) or would be overkill for this application. In this scenario, it may be better to examine the use of an ETL tool that more closely matches the project's needs. Many IT people balk at this suggestion, but they need to ask if it is better for departments to continue to use Microsoft Access as the default ETL or use a real ETL tool (even if it is not the corporate IT standard.). Compromise may be better for the business and produce more business value. This is often the single breaking point for many projects, i.e., the all-or-nothing scenario. If the project is too costly, it ends up being a lose/lose situation.

Microsoft Excel is most likely the final BI tool used in the analysis food chain. Another BI tool may be included in the environment to pull data from the data mart, but that tool needs to get the data into the spreadsheet for the business users to complete their analysis.

Keep the project team small and make the deliverables quick. The business users need to drive the project. A working proof of concept is often a great way to present the initial deliverables.

Get Enlightened

Replacing data shadow systems lets business users increase their productivity by focusing on their work, rather than the mechanics of how it gets done. Once you stop viewing it as yet another IT project, it is really just a simple, smart business decision.

(Don't miss the next issue of Business Intelligence Briefs! See www.athena-solutions.com to subscribe.)

© 2004 Athena IT Solutions