Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events
Archived Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

BI Briefs:
Shedding Light on Data Shadow Systems

online columnist Rick Sherman     Column published in DMReview.com
April 29, 2004
  By Rick Sherman

Is your company losing money because of data shadow systems? If so, you're not alone. They are everywhere - dozens (maybe hundreds) of Microsoft Access databases, Microsoft Excel spreadsheets, SAS databases and localized systems used by business groups.

How can you know for sure? Just ask any business user how she performed her analysis for management. It may take a little prodding, but soon enough you'll discover the presence of Microsoft Excel spreadsheets and Access databases. Maybe even SAS or FOCUS. Sure, the initial data may have come from a data warehouse, data mart or ERP system, but some data, such as demographics, has been added to initial data imported from the IT systems to help users analyze the data in the context of their business unit.

Why do business users build data shadow systems? Because they think there is no other way to get the data they need to do their jobs. They build their analysis around spreadsheets because they know how to use them, it is "free," they can exchange information with everyone and, most importantly, it gets them the results they need.

A lot of the data in shadow systems originates in the corporate data warehouse or departmental data marts. Often this data comes from extract feeds, reports or queries that the business user performs using a business intelligence tool. External data is added to create the dimensions and aggregations that the business needs to analyze the data in their business context.

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.


For more information on related topics visit the following related portals...
Business Intelligence and Data Management.

Rick Sherman has more than 18 years of business intelligence and data warehousing experience, having worked on more than 50 implementations as an independent consultant and as a director/practice leader at a big five firm. He founded Athena IT Solutions, a Boston-based business intelligence consulting firm and is a published author and industry speaker. He can be reached at rsherman@athena-solutions.com or (617) 835-0546.

Solutions Marketplace
Provided by IndustryBrains

Autotask: The IT Business Solution
Run your tech support, IT projects and more with our web-based business management. Optimizes resources and tracks billable project and service work. Get a demo via the web, then try it free with sample data. Click here for your FREE WHITE PAPER!

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Data Quality Tools, Affordable and Accurate
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

File Replication and Web Publishing - RepliWeb
Cross-platform peer-to-peer file replication, content synchronization and one-to-many file distribution solutions enabling content delivery. Replace site server publishing.

Rosette Linguistics Platform
Basis Technology utilizes powerful techniques to provide software solutions for extracting meaningful intelligence from unstructured text in Asian, European and Middle Eastern languages.

Click here to advertise in this space

E-mail This Column E-Mail This Column
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.