Data Quality and Data Integration: The Keys for Successful Data Warehousing

® Data Quality A Pitney Bowes Company White Paper Data Quality and Data Integration: The Keys for Successful Data Warehousing ® A Pitney Bowes Company Overview Many organizations have successfully implemented data resulted when analysts acquired data from multiple operational warehouses to analyze the data contained in their multiple or production systems. One of the most important functions of a operational systems to compare current and historical values. By data warehouse is to serve as a collection point for consolidating doing so, they can better, and more profitably, manage their and further distributing data extracts from an organization’s business, analyze past efforts, and plan for the future. When production systems. The data warehouse also must ensure that properly deployed, data warehouses benefit the organization by this data is uniform, accurate, and consistent and thereby serves significantly enhancing its decision-making capabilities, thus as a “single version of truth” for the enterprise. improving both its efficiency and effectiveness. However, this is much more complicated than it might first appear, However, the quality of the decisions that are facilitated by a especially since each production system was developed to satisfy a data warehouse is only as good as the quality of the data particular operational need. Consequently, each application system contained in the data warehouse - this data must be accurate, was designed with its own data standards and thus was poorly consistent, and complete. For example, in order to determine its integrated with other systems. This integration is particularly top ten customers, an organization must be able to aggregate challenging when dealing with legacy systems that were sales across all of its sales channels and business units and implemented before any real effort was made to establish recognize when the same customer is identified by multiple enterprise data standards or even common data definitions. names, addresses, or customer numbers. In other words, the data used to determine the top ten customers must be integrated and Even if we lived in a world with enough disk space and CPU of high quality. After all, if the data is incomplete or incorrect resources to allow time-stamped data values from each then so will be the results of any analysis performed upon it. transaction associated with every production system to be saved forever, year-end data purges never took place, and computers could quickly read and aggregate all this data for analysis, data Concepts Underlying a Data Warehouse warehouses would still be desirable. At a minimum, the data The data warehouse concept originated in an effort to solve data warehouse would be needed to integrate the data in each system synchronization problems and resolve data inconsistencies that and establish a common format. Moreover, not all of the data Web-based Retailer Challenge: Automate business processes associated with consumer purchases: Solution: The organization deployed a data warehouse to enable information to flow effectively through the company by automating the entire supply chain. From the receipt of a customer order via the Internet, through sending the order to the manufacturer and finally to organizing delivery logistics and invoicing – this retailer dramatically enhanced its end-to-end business processes. Benefit:The acquired functionality included the ability to: • Execute order placements • Monitor the books • Provide decision support • Analyze transactional data, product performance, sales and earnings statistics, and information on customer experiences. 2 Data Quality White Paper an organization requires for analysis purposes is stored in its of information are of paramount importance. Once this data is operational systems. Consequently, data warehouses are collected it can be made available both for direct analysis and for frequently augmented with data from third-party content distribution to other, smaller data warehouses. providers. This content might, for example, include customer demographics and lifestyle data, credit information, or geographic data used to determine distances from firehouses, Variations on a Theme: Data Warehouse, telephone company central offices, or even tax jurisdictions. Data Mart, Operational Data Store, EII: Data warehouses are also likely to contain derived data fields and summary values resulting from the consolidation of data The need to bring consistent data from disparate sources together contained in one or more operational systems. for analysis purposes is the basic premise behind any data warehouse implementation. Based on this need, various data Even when organizations developed data standards, it was unlikely warehouse architectures and implementation approaches have that they modified the existing operational systems to reflect these evolved from the basic concept as originally formulated by Bill standards; rather these standards were applied only when Inmon in his book “Building the Data Warehouse” (W.H. Inmon, developing and implementing new systems. Consequently, when 1992, John Wiley & Sons, Inc.). Inmon stated, “a data warehouse the data residing in these operational systems was needed to is a subject oriented, integrated, nonvolatile, time variant, and populate a data warehouse, it was often necessary to first transform nonvolatile collection of data in support of management’s the data from each source to be consistent with the enterprise data decisions.” standards prior to loading it into the data warehouse. The data warehouse was sometimes the first attempt and often the first place There are now a variety of approaches to data warehousing that the data actually conformed to corporate standards! including enterprise data warehouses, data marts, operational data stores, and enterprise information integration. However, Data integration and data quality are the two key components of most organizations deploy a hybrid combination with each a successful data warehouse as both completeness and accuracy Components Manufacturer Challenge: Provide access to information on production efficiency, sales activities and logistics and transform it into useful intelligence. Enable users to query data sources without having to rely on IT assistance. Solution: Implemented a Web-based data warehousing solution, providing the ability to: • Access and analyze data from anywhere via the Web. • Analyze group sales profits down to the customer or individual product level. Benefit: • Reliability of product shipments • Reduced manufacturing costs 3 ® A Pitney Bowes Company approach complementing the others. Although they may differ Users and applications can directly use the data warehouse to in content, scope, permanency or update cycle they all have two perform their analysis. Alternately, a subset of the data warehouse characteristics in common: the need to integrate data and the data, often relating to a specific line-of-business and/or a specific need for this data to be of high quality. functional area, can be exported to another, smaller data warehouse, commonly referred to as a data mart. Besides integrating and cleansing an organization’s data for better Data Warehouses analysis, one of the benefits of building a data warehouse is that From a conceptual perspective, data warehouses store snapshots the effort initially spent to populate it with complete and accurate and aggregations of data collected from a variety of source data content further benefits any data marts that are sourced from systems. Data warehouses encompass a variety of subject areas. the data warehouse. Each of these source systems could store the same data in different formats, with different editing rules, and different value lists. For Data Marts example, gender code could be represented in three separate systems as male/female, 0/1, and M/F respectively; dates might be A data mart, if populated from a data warehouse, contains a subset stored in a year/month/day, month/day/year, or day/month/year of the data from the data warehouse. If this is the case, then it is format.