The Critical Role of Information Controls
Christopher Reed, Yaping Wang, CISA, PMP, and Angsuman Dutta
Would one buy a house when the stability of the foundation is uncertain? Would one make a payment if the accuracy of the bill is in question? If the answer is no, then why would any organization settle for making business decisions based on inaccurate and inconsistent data warehouse information? A number of studies1, 2, 3 show that much of the data warehouse information available to business users is not accurate, complete or timely. Despite significant investment in data warehouse technologies and efforts to ensure quality, the trustworthiness of data warehouse information at best remains questionable.4, 5 Current approaches to restore trust in data warehouse information are often heroic efforts of the individuals responsible for the data warehouse and include:
These approaches provide short-term respites but are not sustainable in the long run. The increased labor cost for manual processes and the high processing cost for reruns when errors are identified late in the process increase ongoing operational costs. The cumbersome and costly processes for supporting audit needs also create organizational stress. Frequently, a large number of data warehouse projects are abandoned because of the high costs of efforts to ensure information quality.6
While standardized tools, such as those for extraction, transformation and loading (ETL) and data quality processes, solve part of the problem, there is an urgent need for adopting a systematic approach for establishing trust in data warehouse information. The proposed approach outlines a framework for ensuring the integrity of data warehouse information by using end-to-end information controls.
While several factors can be attributed to the information quality issues, the following are the major causes of information errors within data warehouses:
The current focus in most data warehouse initiatives is to use ETL tools to standardize the data transfer process and to use data quality solutions to detect and correct incomplete and inconsistent data. While these efforts result in significant improvements, data warehouse teams rely on a number of manual/semiautomated processes to balance and reconcile the data warehouse information with the source system information. Some of the techniques currently used by various organizations are:
While these methods are somewhat effective in detecting the errors, they rely heavily on the ETL process, which is often the source of the error. More important, these approaches are not effective when the transactions from source systems are either split into several transactions or combined into a single transaction. Such scenarios require advanced logic for balancing the information between the source system and data warehouse. In addition, the inability to reconcile detaillevel information using scripts or ETL processes does not allow users to pinpoint the exact issue, resulting in significant manual research and resolution efforts. In addition to the high operational costs related to research and reruns to ensure quality, the current approach impacts the morale of the data warehouse team and the confidence of the business users.
The problem of data quality exacerbates when the data warehouse information is used for storing and reporting financial information. In this scenario, internal audit requests evidence of controls’ operation and documentation related to error resolutions when controls detect errors. Such requests are often met by querying a myriad number of log files, e-mail chains and data warehouse tables. This increases the workload of the data warehouse resources and increases the rift between audit and data warehouse teams.
Current approaches are not scalable and sustainable. There is an urgent need to use automated information controls for verifying, balancing, reconciling and tracking the data warehouse information. Ideally, information controls should be independent of the underlying application and should have the ability to store an audit trail of the information transfer process and its validation results.
Successful and cost-effective data warehouse quality initiatives in Fortune 500 organizations are founded on three critical pillars, as shown in figure 1.
Information controls not only balance and reconcile the data before and after the load, but also can be expanded outside the scope of the data warehouse to ensure that the data warehouse information is aligned with other critical applications such as the general ledger (GL). For example, although the same journal systems may feed both the data warehouse and the GL, manual adjustments in the GL system may cause an out-of-sync condition that could be detected early if an automated information control is in place. In addition, automated information controls store the audit trail information about the control actions and the resolutions in case of exceptions. Figure 2 compares ETL and DQ tools with IC solutions from various aspects.
The proposed framework recommends a minimum of six information controls to achieve the objectives of the data warehouse quality initiatives. The locations of the information controls are depicted in figure 3. The six controls are:
With the accelerating changes in the source systems to support business needs, increasing reliance on data warehouse information for critical business operation and decisions, and an expanding (and ever-changing) array of regulations and compliance requirements, the use of automated information controls is no longer an option; it is the only way to ensure information accuracy within the data warehouse and across the enterprise. Successful organizations expand the scope of information controls beyond the scope of the data warehouse by developing a companywide program for ensuring the enterprise information quality. With an appropriate selection of tools and frameworks for information controls, organizations can achieve the elusive goal of having higherquality enterprise information assets.
1 English, Larry; Improving Data Warehouse and Business Information Quality, Wiley and Sons, USA, 20002 Eckerson, Wayne W.; Data Quality and the Bottom Line, TDWI research series, USA, 20013 Friedman, Ted; Data Quality “Firewall” Enhances Value of the Data Warehouse, Gartner Report, USA, 20044 Violino, Bob; “Do You Trust Your Information?,” The Information Agenda, 23 October 20085 Computer Sciences Corp., Technology Issues for Financial Executives, USA, 20076 Gupta, Sanjeev; “Why Do Data Warehouse Projects Fail?,” Information Management, 16 July 2009
Christopher Reedis solution consultant at Infogix Inc., leading solution consulting efforts. He works with Fortune500 companies to assist in the creation of information control solutions throughout the enterprise. In addition to his work at Infogix, Reed was an architectural consultant at Unisys, where he consulted with customers on deploying mission critical applications.
Yaping Wang, CISA , PMPis product consultant at Infogix, where she leads client service projects that provide assessment, advisory, implementation and other services in automated information control domains.
Angsuman Duttais unit leader of the customer acquisition support team at Infogix. Since 2001, he has assisted numerous industry-leading enterprises in their implementation of automated information controls.
Enjoying this article? To read the most current ISACA® Journal articles, become a member or subscribe to the Journal.
The ISACA Journal is published by ISACA. Membership in the association, a voluntary organization serving IT governance professionals, entitles one to receive an annual subscription to the ISACA Journal.
Opinions expressed in the ISACA Journal represent the views of the authors and advertisers. They may differ from policies and official statements of ISACA and/or the IT Governance Institute® and their committees, and from opinions endorsed by authors’ employers, or the editors of this Journal. ISACA Journal does not attest to the originality of authors’ content.
© 2010 ISACA. All rights reserved.
Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, MA 01970, to photocopy articles owned by ISACA, for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.