QoX-driven ETL design: reducing the cost of ETL consulting engagements

ABSTRACT

As business intelligence becomes increasingly essential for organizations and as it evolves from strategic to operational, the complexity of Extract-Transform-Load (ETL) processes grows. In consequence, ETL engagements have become very time consuming, labor intensive, and costly. At the same time, additional requirements besides functionality and performance need to be considered in the design of ETL processes. In particular, the design quality needs to be determined by an intricate combination of different metrics like reliability, maintenance, scalability, and others. Unfortunately, there are no methodologies, modeling languages or tools to support ETL design in a systematic, formal way for achieving these quality requirements. The current practice handles them with ad-hoc approaches only based on designers' experience. This results in either poor designs that do not meet the quality objectives or costly engagements that require several iterations to meet them. A fundamental shift that uses automation in the ETL design task is the only way to reduce the cost of these engagements while obtaining optimal designs. Towards this goal, we present a novel approach to ETL design that incorporates a suite of quality metrics, termed QoX, at all stages of the design process. We discuss the challenges and tradeoffs among QoX metrics and illustrate their impact on alternative designs.

REFERENCES

• 1: L. Chung, B.A. Nixon, E. Yu, J. Mylopoulos. Non-Functional Requirements in Software Engineering. Kluwer Academic Publishing, 1999.
• 2: Nilesh N. Dalvi , Sumit K. Sanghai , Prasan Roy , S. Sudarshan, Pipelining in multi-query optimization, Proceedings of the twentieth ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems, p.59-70, May 2001, Santa Barbara, California, United States [doi>10.1145/375551.375561]
• 3: Umeshwar Dayal , Malu Castellanos , Alkis Simitsis , Kevin Wilkinson, Data integration flows for business intelligence, Proceedings of the 12th International Conference on Extending Database Technology: Advances in Database Technology, March 24-26, 2009, Saint Petersburg, Russia [doi>10.1145/1516360.1516362]
• 4: Lukasz Golab , Theodore Johnson , Vladislav Shkapenyuk, Scheduling Updates in a Real-Time Stream Warehouse, Proceedings of the 2009 IEEE International Conference on Data Engineering, p.1207-1210, March 29-April 02, 2009 [doi>10.1109/ICDE.2009.202]
• 5: Informatica. How to Achieve Flexible, Cost-effective Scalability and Performance through Pushdown Processing. White paper, November 2007.
• 6: William H. Inmon, Building the Data Warehouse, John Wiley & Sons, Inc., New York, NY, 1992
• 7: 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
• 8: G. Papastefanatos, P. Vassiliadis, A. Simitsis, Y. Vassiliou. Policy-Regulated Management of ETL Evolution. In Springer JoDS, Vol. XIII, pp. 146--176, 2009.
• 9: N. Polyzotis, S. Skiadopoulos, P. Vassiliadis, A. Simitsis, N.-E. Frantzell. Supporting Streaming Updates in an Active Data Warehouse. In ICDE, pp. 476--485, 2007.
• 10: Prasan Roy , S. Seshadri , S. Sudarshan , Siddhesh Bhobe, Efficient and extensible algorithms for multi query optimization, Proceedings of the 2000 ACM SIGMOD international conference on Management of data, p.249-260, May 15-18, 2000, Dallas, Texas, United States
• 11: Timos K. Sellis, Multiple-query optimization, ACM Transactions on Database Systems (TODS), v.13 n.1, p.23-52, March 1988 [doi>10.1145/42201.42203]
• 12: 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]
• 13: Christian Thomsen , Torben Bach Pedersen , Wolfgang Lehner, RiTE: Providing On-Demand Data for Right-Time Data Warehousing, Proceedings of the 2008 IEEE 24th International Conference on Data Engineering, p.456-465, April 07-12, 2008 [doi>10.1109/ICDE.2008.4497454]
• 14: Vasiliki Tziovara , Panos Vassiliadis , Alkis Simitsis, Deciding the physical implementation of ETL workflows, Proceedings of the ACM tenth international workshop on Data warehousing and OLAP, November 09-09, 2007, Lisbon, Portugal [doi>10.1145/1317331.1317341]
• 15: P. Vassiliadis, A. Simitsis. Near Real Time ETL. In Springer Annals of Information Systems, Vol. 3, pp. 19--29, 2008.
• 16: P. Vassiliadis, A. Simitsis, M. Terrovitis, S. Skiadopoulos. Blueprints and Measures for ETL Workflows. In ER, pp. 385--400, 2005.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.7 Database Administration
          Subjects: Data warehouse and repository

General Terms:
Design, Experimentation, Management, Performance, Reliability

Keywords:
data warehouses, etl, metrics, modeling, qox, quality