Conceptual modeling for ETL processes

ABSTRACT

Extraction-Transformation-Loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleansing, customization and insertion into a data warehouse. In this paper, we focus on the problem of the definition of ETL activities and provide formal foundations for their conceptual representation. The proposed conceptual model is (a) customized for the tracing of inter-attribute relationships and the respective ETL activities in the early stages of a data warehouse project; (b) enriched with a 'palette' of a set of frequently used ETL activities, like the assignment of surrogate keys, the check for null values, etc; and (c) constructed in a customizable and extensible manner, so that the designer can enrich it with his own re-occurring patterns for ETL activities.

REFERENCES

• 1: Ardent Software. DataStage Suite. http://www.ardentsoftware.com/
• 2: M. Bouzeghoub, F. Fabret, M. Matulovic. Modeling Data Warehouse Refreshment Process as a Workflow Application. In Proc. DMDW'99 (Heidelberg, Germany, 1999).
• 3: V. Borkar, K. Deshmuk, S. Sarawagi. Automatically Extracting Structure from Free Text Addresses. Bulletin of the Technical Committee on Data Engineering, 23, 4, 2000.
• 4: Grady Booch , James Rumbaugh , Ivar Jacobson, The Unified Modeling Language user guide, Addison Wesley Longman Publishing Co., Inc., Redwood City, CA, 1999
• 5: Diego Calvanese , Giuseppe De Giacomo , Maurizio Lenzerini , Daniele Nardi , Riccardo Rosati, Information Integration: Conceptual Modeling and Reasoning Support, Proceedings of the 3rd IFCIS International Conference on Cooperative Information Systems, p.280-291, August 20-22, 1998
• 6: D. Calvanese, G. De Giacomo, M. Lenzerini, D. Nardi, R. Rosati. A principled approach to data integration and reconciliation in data warehousing. In Proc. DMDW'99, (Heidelberg, Germany, 1999).
• 7: DataMirror Corporation. Transformation Server. http://www.datamirror.com
• 8: M. Demarest. The politics of data warehousing. http://www.hevanet.com/demarest/marc/dwpol.html
• 9: Evolutionary Technologies Intl. ETI*EXTRACT. http://www.eti.com/
• 10: Helena Galhardas , Daniela Florescu , Dennis Shasha , Eric Simon, AJAX: an extensible data cleaning tool, Proceedings of the 2000 ACM SIGMOD international conference on Management of data, p.590, May 15-18, 2000, Dallas, Texas, United States
• 11: M. Golfarelli, D. Maio, S. Rizzi. The Dimensional Fact Model: a Conceptual Model for Data Warehouses. Invited Paper, International Journal of Cooperative Information Systems, 7, 2&3, 1998.
• 12: Matteo Golfarelli , Stefano Rizzi, A methodological framework for data warehouse design, Proceedings of the 1st ACM international workshop on Data warehousing and OLAP, p.3-9, November 02-07, 1998, Washington, D.C., United States [doi>10.1145/294260.294261]
• 13: B. Husemann, J. Lechtenborger, G. Vossen. Conceptual data warehouse modeling. In Proc. DMDW (Stockholm, Sweden, 2000), pp. 6.1--6.11.
• 14: B. Inmon. The Data Warehouse Budget. DM Review Magazine, January 1997. www.dmreview.com/master.cfm?NavID=55&EdID=1315
• 15: Manfred A. Jeusfeld , Christoph Quix , Matthias Jarke, Design and Analysis of Quality Information for Data Warehouses, Proceedings of the 17th International Conference on Conceptual Modeling, p.349-362, November 16-19, 1998
• 16: M. Jarke, M.A. Jeusfeld, C. Quix, P. Vassiliadis: Architecture and quality in data warehouses: An extended repository approach. Information Systems, 24, 3, 1999, pp. 229--253.
• 17: Matthias Jarke , Y. Vassiliou , P. Vassiliadis , M. Lenzerini, Fundamentals of Data Warehouses, Springer-Verlag New York, Inc., Secaucus, NJ, 1999
• 18: Ralph Kimball, A dimensional modeling manifesto, DBMS, v.10 n.9, p.58-70, Aug. 1997
• 19: Ralph Kimball , Laura Reeves , Warren Thornthwaite , Margy Ross , Warren Thornwaite, The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing and Deploying Data Warehouses with CD Rom, John Wiley & Sons, Inc., New York, NY, 1998
• 20: Wilburt Juan Labio , Janet L. Wiener , Hector Garcia-Molina , Vlad Gorelik, Efficient resumption of interrupted warehouse loads, Proceedings of the 2000 ACM SIGMOD international conference on Management of data, p.46-57, May 15-18, 2000, Dallas, Texas, United States
• 21: Microsoft Corp. MS Data Transformation Services. www.microsoft.com/sq
• 22: D.L. Moody, M.A.R. Kortink: From enterprise models to dimensional models: a methodology for data warehouse and data mart design. In Proc. DMDW (Stockholm, Sweden, June 2000).
• 23: A. Monge. Matching Algorithms Within a Duplicate Detection System. Bulletin of the Technical Committee on Data Engineering, 23, 4, 2000.
• 24: T. B. Nguyen , A. Min Tjoa , Roland Wagner, An Object Oriented Multidimensional Data Model for OLAP, Proceedings of the First International Conference on Web-Age Information Management, p.69-82, June 21-23, 2000
• 25: Oracle Corp. Oracle9i™ Warehouse Builder User's Guide, Release 9.0.2. November 2001.
• 26: E. Rahm, H. Do. Data Cleaning: Problems and Current Approaches. Bulletin of the Technical Committee on Data Engineering, 23, 4, 2000.
• 27: Vijayshankar Raman , Joseph M. Hellerstein, Potter's Wheel: An Interactive Data Cleaning System, Proceedings of the 27th International Conference on Very Large Data Bases, p.381-390, September 11-14, 2001
• 28: Carsten Sapia , Markus Blaschka , Gabriele Höfling , Barbara Dinter, Extending the E/R Model for the Multidimensional Paradigm, Proceedings of the Workshops on Data Warehousing and Data Mining: Advances in Database Technologies, p.105-116, November 19-20, 1998
• 29: C. Shilakes, J. Tylman. Enterprise Information Portals. Enterprise Software Team. http://www.sagemaker.com/company/downloads/eip/ indepth.pdf
• 30: Nectaria Tryfona , Frank Busborg , Jens G. Borch Christiansen, starER: a conceptual model for data warehouse design, Proceedings of the 2nd ACM international workshop on Data warehousing and OLAP, p.3-8, November 02-06, 1999, Kansas City, Missouri, United States [doi>10.1145/319757.319776]
• 31: Juan Trujillo , Manuel Palomar , Jaime Gómez, Applying Object-Oriented Conceptual Modeling Techniques to the Design of Multidimensional Databases and OLAP Applications, Proceedings of the First International Conference on Web-Age Information Management, p.83-94, June 21-23, 2000
• 32: A. Tsois. MAC: Conceptual data modeling for OLAP. In Proc. DMDW (Interlaken, Switzerland, 2001
• 33: P. Vassiliadis. Gulliver in the land of data warehousing: practical experiences and observations of a researcher. In Proc. DMDW (Stockholm, Sweden, 2000), pp. 12.1--12.16.
• 34: P. Vassiliadis, A. Simitsis, S. Skiadopoulos. Modeling ETL activities as graphs. In Proc. DMDW (Toronto, Canada, May 2002), pp. 52--61.
• 35: Panos Vassiliadis , Christoph Quix , Yannis Vassiliou , Matthias Jarke, Data warehouse process management, Information Systems, v.26 n.3, p.205-236, May 2001 [doi>10.1016/S0306-4379(01)00018-7]
• 36: Panos Vassiliadis , Zografoula Vagena , Spiros Skiadopoulos , Nikos Karayannidis , Timos Sellis, Arktos: towards the modeling, design, control and execution of ETL processes, Information Systems, v.26 n.8, p.537-561, December 2001 [doi>10.1016/S0306-4379(01)00039-4]

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.1 Logical Design
          Subjects: Data models

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.1 Logical Design
          Subjects: Schema and subschema

General Terms:
Design

Keywords:
ETL, conceptual modeling, data warehousing