Real-time data warehouse loading methodology

ABSTRACT

A data warehouse provides information for analytical processing, decision making and data mining tools. As the concept of real-time enterprise evolves, the synchronism between transactional data and data warehouses, statically implemented, has been redefined. Traditional data warehouse systems have static structures of their schemas and relationships between data, and therefore are not able to support any dynamics in their structure and content. Their data is only periodically updated because they are not prepared for continuous data integration. For real-time enterprises with needs in decision support purposes, real-time data warehouses seem to be very promising. In this paper we present a methodology on how to adapt data warehouse schemas and user-end OLAP queries for efficiently supporting real-time data integration. To accomplish this, we use techniques such as table structure replication and query predicate restrictions for selecting data, to enable continuously loading data in the data warehouse with minimum impact in query execution time. We demonstrate the efficiency of the method by analyzing its impact in query performance using benchmark TPC-H executing query workloads while simultaneously performing continuous data integration at various insertion time rates.

REFERENCES

• 1: Daniel J. Abadi , Don Carney , Ugur Çetintemel , Mitch Cherniack , Christian Convey , Sangdon Lee , Michael Stonebraker , Nesime Tatbul , Stan Zdonik, Aurora: a new model and architecture for data stream management, The VLDB Journal — The International Journal on Very Large Data Bases, v.12 n.2, p.120-139, August 2003 [doi>10.1007/s00778-003-0095-z]
• 2: Shivnath Babu , Jennifer Widom, Continuous queries over data streams, ACM SIGMOD Record, v.30 n.3, September 2001 [doi>10.1145/603867.603884]
• 3: T. Binder, 2003. Gong User Manual, Tecco Software Entwicklung AG.
• 4: M. Bouzeghoub, F. Fabret, and M. Matulovic, 1999. "Modeling Data Warehouse Refreshment Process as a Workflow Application", Intern. Workshop on Design and Management of Data Warehouses (DMDW).
• 5: Robert M. Bruckner , Beate List , Josef Schiefer, Striving towards Near Real-Time Data Integration for Data Warehouses, Proceedings of the 4th International Conference on Data Warehousing and Knowledge Discovery, p.317-326, September 04-06, 2002
• 6: Robert M. Bruckner , A. Min Tjoa, Capturing Delays and Valid Times in Data Warehouses—Towards Timely Consistent Analyses, Journal of Intelligent Information Systems, v.19 n.2, p.169-190, September 2002 [doi>10.1023/A:1016555410197]
• 7: Surajit Chaudhuri , Umeshwar Dayal, An overview of data warehousing and OLAP technology, ACM SIGMOD Record, v.26 n.1, p.65-74, March 1997 [doi>10.1145/248603.248616]
• 8: W. H. Inmon , R. H. Terdeman , Joyce Montanari , Dan Meers, Data Warehousing for E-Business, John Wiley & Sons, Inc., New York, NY, 2001
• 9: Isabel Cristina Italiano , Joao Eduardo Ferreira, Synchronization Options for Data Warehouse Designs, Computer, v.39 n.3, p.53-57, March 2006 [doi>10.1109/MC.2006.104]
• 10: Alexandros Karakasidis , Panos Vassiliadis , Evaggelia Pitoura, ETL queues for active data warehousing, Proceedings of the 2nd international workshop on Information quality in information systems, June 17-17, 2005, Baltimore, Maryland [doi>10.1145/1077501.1077509]
• 11: 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
• 12: R. Kimball, and J. Caserta, 2004. The Data Warehouse ETL Toolkit, Wiley Computer Publishing.
• 13: Eva Kühn, The zero-delay data warehouse: mobilizing heterogeneous database, Proceedings of the 29th international conference on Very large data bases, p.1035-1040, September 09-12, 2003, Berlin, Germany
• 14: Wilburt Labio , Jun Yang , Yingwei Cui , Hector Garcia-Molina , Jennifer Widom, Performance Issues in Incremental Warehouse Maintenance, Proceedings of the 26th International Conference on Very Large Data Bases, p.461-472, September 10-14, 2000
• 15: D. Lomet, and J. Gehrke, 2003. Special Issue on Data Stream Processing, IEEE Data Eng. Bulletin, 26(1).
• 16: Oracle Corporation, 2005. www.oracle.com
• 17: T. B. Pedersen, 2004. "How is BI Used in Industry?", Int. Conf. on Data Warehousing and Knowledge Discovery (DAWAK).
• 18: John F. Roddick , Michael Schrefl, Towards an Accommodation of Delay in Temporal Active Databases, Proceedings of the Australasian Database Conference, p.115, January 31-February 03, 2000
• 19: 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]
• 20: Utkarsh Srivastava , Jennifer Widom, Flexible time management in data stream systems, Proceedings of the twenty-third ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems, June 14-16, 2004, Paris, France [doi>10.1145/1055558.1055596]
• 21: D. Theodoratus, and M. Bouzeghoub, 1999. "Data Currency Quality Factors in Data Warehouse Design", International Workshop on the Design and Management of Data Warehouses (DMDW).
• 22: TPC-H decision support benchmark, Transaction Processing Council, www.tpc.com.
• 23: 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]
• 24: C. White, 2002. "Intelligent Business Strategies: Real-Time Data Warehousing Heats Up", DM Preview, www.dmreview.com/article_sub_cfm?articleId=5570.
• 25: Jun Yang , Jennifer Widom, Temporal data warehousing, 2001
• 26: Jun Yang , Jennifer Widom, Incremental Computation and Maintenance of Temporal Aggregates, Proceedings of the 17th International Conference on Data Engineering, p.51-60, April 02-06, 2001
• 27: Jun Yang , Jennifer Widom, Temporal View Self-Maintenance, Proceedings of the 7th International Conference on Extending Database Technology: Advances in Database Technology, p.395-412, March 27-31, 2000
• 28: Thomas Zurek , Klaus Kreplin, SAP Business Information Warehouse - From Data Warehousing to an E-business Platform, Proceedings of the 17th International Conference on Data Engineering, p.388-390, April 02-06, 2001

INDEX TERMS

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

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.2 Physical Design

General Terms:
Design, Performance

Keywords:
continuous data integration for data warehousing, data warehouse refreshment loading process, real-time and active data warehousing