Towards generating ETL processes for incremental loading

ABSTRACT

Extract, Transform, and Load (ETL) processes physically integrate data from multiple, heterogeneous sources in a central repository referred to as data warehouse. Physically integrated data gets stale when source data is changed, hence periodic refreshes are required. For efficiency reasons data warehouses are typically refreshed incrementally, i.e. changes are captured at the sources and propagated to the data warehouse on a regular basis. Dedicated ETL processes referred to as incremental load processes are employed to extract changes from the sources, propagate the changes, and refresh the data warehouse incrementally. Changes required in the data warehouse are inferred from changes captured at the sources during change propagation. The creation of incremental load processes is a complex task reserved to trained ETL programmers. In this paper we review existing Change Data Capture (CDC) techniques and discuss limitations of different approaches. We further review existing techniques for refreshing data warehouses. We then present an approach for generating incremental load processes from abstract schema mappings.

REFERENCES

• 1: IBM DB2 Data Warehouse Enterprise Edition. www.ibm.com/software/data/db2/dwe/.
• 2: IBM WebSphere DataStage. http://www-306.ibm.com/software/data/integration/datastage/.
• 3: Informatica PowerCenter. http://www.informatica.com/products_services/powercenter/.
• 4: Oracle Database Change Data Capture. http://www.oracle.com/database.
• 5: Oracle Warehouse Builder. http://www.oracle.com/technology/products/warehouse/index.html.
• 6: WebSphere Replication Server (SQL replication ). http://www-306.ibm.com/software/data/integration/replication_server/.
• 7: D. Agrawal , A. El Abbadi , A. Singh , T. Yurek, Efficient view maintenance at data warehouses, Proceedings of the 1997 ACM SIGMOD international conference on Management of data, p.417-427, May 11-15, 1997, Tucson, Arizona, United States
• 8: José A. Blakeley , Neil Coburn , Per-:1Vke Larson, Updating derived relations: detecting irrelevant and autonomously computable updates, ACM Transactions on Database Systems (TODS), v.14 n.3, p.369-400, Sept. 1989 [doi>10.1145/68012.68015]
• 9: M. Bokun and C. Taglienti. Incremental Data Warehouse Updates. DM Review Magazine, May 1998.
• 10: E. F. Codd, A relational model of data for large shared data banks, Communications of the ACM, v.13 n.6, p.377-387, June 1970 [doi>10.1145/362384.362685]
• 11: S. Dessloch, M. A. Hernández, R. Wisnesky, A. Radwan, and J. Zhou. Orchid: Integrating Schema Mapping and ETL. In ICDE, pages 1307--1316, 2008.
• 12: Timothy Griffin , Leonid Libkin, Incremental maintenance of views with duplicates, Proceedings of the 1995 ACM SIGMOD international conference on Management of data, p.328-339, May 22-25, 1995, San Jose, California, United States
• 13: Ashish Gupta , H. V. Jagadish , Inderpal Singh Mumick, Data Integration using Self-Maintainable Views, Proceedings of the 5th International Conference on Extending Database Technology: Advances in Database Technology, p.140-144, March 25-29, 1996
• 14: Himanshu Gupta , Inderpal Singh Mumick, Incremental maintenance of aggregate and outerjoin expressions, Information Systems, v.31 n.6, p.435-464, September 2006 [doi>10.1016/j.is.2004.11.011]
• 15: Ralph Kimball , Joe Caserta, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin, John Wiley & Sons, 2004
• 16: Ralph Kimball , Margy Ross, The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, John Wiley & Sons, Inc., New York, NY, 2002
• 17: Wilburt J Labio , Hector Garcia-Molina, Comparing Very Large Database Snapshots, Stanford University, Stanford, CA, 1995
• 18: Wilburt Labio , Hector Garcia-Molina, Efficient Snapshot Differential Algorithms for Data Warehousing, Proceedings of the 22th International Conference on Very Large Data Bases, p.63-74, September 03-06, 1996
• 19: U. Leser and F. Naumann. Informationsintegration. dpunkt.verlag, 2007.
• 20: Mukesh K. Mohania , Shin'ichi Konomi , Yahiko Kambayashi, Incremental Maintenance of Materialized Views, Proceedings of the 8th International Conference on Database and Expert Systems Applications, p.551-560, September 01-05, 1997
• 21: Themistoklis Palpanas , Richard Sidle , Roberta Cochrane , Hamid Pirahesh, Incremental maintenance for non-distributive aggregate functions, Proceedings of the 28th international conference on Very Large Data Bases, p.802-813, August 20-23, 2002, Hong Kong, China
• 22: X. Qian , Gio Wiederhold, Incremental Recomputation of Active Relational Expressions, IEEE Transactions on Knowledge and Data Engineering, v.3 n.3, p.337-341, September 1991 [doi>10.1109/69.91063]
• 23: D. Quass. Maintenance Expressions for Views with Aggregation. In VIEWS, pages 110--118, 1996.
• 24: A. Simitsis. Modeling and managing ETL processes. In VLDB PhD Workshop, 2003.
• 25: Alkis Simitsis, Mapping conceptual to logical models for ETL processes, Proceedings of the 8th ACM international workshop on Data warehousing and OLAP, November 04-05, 2005, Bremen, Germany [doi>10.1145/1097002.1097014]
• 26: Alkis Simitsis , Panos Vassiliadis , Timos Sellis, Optimizing ETL Processes in Data Warehouses, Proceedings of the 21st International Conference on Data Engineering, p.564-575, April 05-08, 2005 [doi>10.1109/ICDE.2005.103]
• 27: A. Simitsis, P. Vassiliadis, M. Terrovitis, and S. Skiadopoulos. Graph-Based Modeling of ETL Activities with Multi-level Transformations and Updates. In DaWaK, pages 43--52, 2005.
• 28: Toni Urpí , Antoni Olivé, A Method for Change Computation in Deductive Databases, Proceedings of the 18th International Conference on Very Large Data Bases, p.225-237, August 23-27, 1992
• 29: Panos Vassiliadis , Alkis Simitsis , Spiros Skiadopoulos, Conceptual modeling for ETL processes, Proceedings of the 5th ACM international workshop on Data Warehousing and OLAP, p.14-21, November 08-08, 2002, McLean, Virginia, USA [doi>10.1145/583890.583893]

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.1 MODELS AND PRINCIPLES

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.1 Logical Design

General Terms:
Design

Keywords:
data integration, data warehousing, extract, information integration, load (ETL), transform