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

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

Data Integration:
Stopping Your Company’s DRIPs, Part 2

online columnist Greg Mancuso and Al Moreno     Column published in DMReview.com
May 13, 2004
  By Greg Mancuso and Al Moreno

This article is the second part in the series suggesting ways that an enterprise can work on solving their Data Rich Information Poor issues. Last month, the discussion focused around the gap assessment and how that can assist. This month, the focus will be on the systems audit. In review, the system audit is defined as: reviewing and "reanalyzing" existing systems to ensure they are still functioning as they were originally deployed and assessing the underlying data to ensure the IT and business users actually understand the meaning of the data maintained by the system.

When operational systems are originally implemented, they are geared to service and function to solve a specific set of business issues. Those issues - generally referred to as the "business requirements" - are carefully designed to solve a finite need within the enterprise at the time the requirements analysis is done. In the new world economy, enterprises can pretty much bank on change which means that the operational systems must periodically change. The issue is that the changes mandated by the business do not coincide with the programmatic changes made to the operational systems. As a result, the user community finds innovative and creative ways to get the job done by using the existing systems in ways IT would never have either imagined or intended, creating a significant gap.

Since most business users are not in tune with IT standards and best practices, when left to their own devices, users usually solve the gap by using data storage manipulations or methods that are partly automated and partly manual. Examples of this are users assigning specific meanings to a preface or suffix in a data element - using the first three digits of a part number for a specific family or the last two digits to indicate a bin number or using a data element that was originally implemented as one thing that is no longer used and now contains data that is not related to the element name in the database. In each case, while the users know the value and method used to decipher the data set, IT must somehow translate this logic when the data is required to create reports and to produce usable management information.

When an operational system is left to the devices of the end users to determine what information is stored and to assign meanings, the only way of discovering what is going on is to periodically do a complete system audit. The system audit will measure a variety of things, but the key components are: 1) a determination as to whether or not the systems are functioning as originally designed; 2) are there any data elements currently being stored in data set s that were not originally intended to store that information; and 3) do the systems require any updates and programming changes to ensure data quality and integrity.

Reviewing the Original Design and Intent: The Reality Check

The success or failure of an enterprise today is tied directly to how well the systems can maintain useful information. The system audit is a key tool in reviewing whether the operational systems are still in tune with the needs and functionality that the enterprise requires. The current business needs may no longer be adequately supported by an enterprise's old operational systems because today's business processes or business functionality is different than it was when the original system requirements where gathered. By doing a fairly detailed audit that matches the current business requirements with the original intent under which the operation system was built, it will become obvious very quickly whether the system is still doing an adequate job of serving the enterprise.

In addition, the system audit provides the enterprise with a mechanism to keep the operational systems up to date and provides a reality check of how well the existing systems are serving the enterprise. If there is an ever-widening gap between original intent and current needs, then the enterprise will have plenty of advanced warning that an impending update or rewrite is required. With the dependency on automation comes a much greater need to ensure consistency and usability. If users are having to do more and more extracts and manipulations within desktop tools such as Access or Excel, then this is a warning that the enterprise needs to really investigate the usefulness of the existing system. Considering the personnel costs versus the costs of "fixing" the gaps will also give the enterprise a good check of how well resources are spending their time.

Reviewing Data Quality and Integrity to the Original Design

Anyone who has had to deal with the integration nightmare of the user-defined data sets in the operational systems will agree that the periodic reality check is vital. There is nothing worse than trying to decipher and extract key informational pieces from a free-form text column. Users are apt to perform any number of things with columns that don't require field validations or are any type of text description. At a time when IT shops have had to significantly cut back personnel, the constant pressures to stay current with the daily functionality has limited the available programming maintenance and periodic updates that are so vital to try and maintain referential integrity in what a data set contains. Users, on the other hand, are fighting the pressure of trying to produce the management reports that are required to run the business. The natural opposition that occurs between getting program updates done to existing systems vs. the "I need it now" issue creates some interesting enterprise-wide data sets. What the system audit will reveal is what has occurred, when it occurred and what business rules need to be applied when the data is extracted for reporting purposes. Without understanding the when and the what, any use of the data sets for reporting becomes a real issue since IT rarely knows the data sets as well as the end users. IT treats the data sets as a conglomeration of columns in a table and when someone says I need to see part numbers by family and by bin number, IT will extract the requested data based on the defined data element names and/or descriptions. If the users have used family or bin columns for something other than what they were intended, the resulting reports will be grouped by whatever data is stored in those attributes. Unless everyone is aware of what the attributes really mean - the danger of errors in interpretation is clearly imminent.

Data Integrity: The Real Issue

Perhaps the single most important thing that the periodic system audit does is give the enterprise an opportunity to spot check whether the integrity of the data sets is still adequate for the intended uses of the information. Many operational systems become dated and while they store data adequately for their original limited uses - either to serve as historical holders of data or to produce limited reporting - they may not really support data exports that serve as imports into the newer business intelligence(BI)/business performance management (BPM) toolsets. Often times the exports require significant rewriting and cleansing to insure that the information flowing as input into these more robust BI/BPM applications is usable.

If the operational systems don't perform validations and/or cross checks to insure system-wide integrity, the audit becomes the sole mechanism to ensure that the data stored is meaningful and can be dependably used for any type of analysis. The results will help nip problems early and provide invaluable feedback on what system enhancements are required to improve usability.

The system audit can be a valuable tool for any enterprise. It is definitely one key component to ensure that the IT and business community of an enterprise is still in sync and working together to the benefit of everybody. There is little doubt that the recent economic situations have created the need for everyone in the enterprise to work hard to get their jobs done. Unfortunately, the day-to-day pressures oftentimes force enterprises to try and shortcut best practices, but the system audit is not one that should be allowed to go by the wayside. The benefits and need to monitor data integrity make it a vital part of any success IT strategy and the enterprise's reality check.


For more information on related topics visit the following related portals...
Data Integration.

Greg Mancuso and Al Moreno are principals with Sinecon, a business intelligence consultancy specializing in data integration and BI/DW solution architecture design. Together they have more than 29 years of data warehouse and business intelligence experience and have implemented many large-scale solutions in both the U.S. and European markets. They may be reached at gmancuso@sinecon-llc.com or amoreno@sinecon-llc.com.

Solutions Marketplace
Provided by IndustryBrains

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.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

dotDefender protects sites against Web attacks
30-day evaluation period for dotDefender, a high-end cost-effective security solution for web servers that protects against a broad range of attacks, is now available. dotdefender supports Apache, IIS and iPlanet Web servers and all Linux OS's.

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.