Data integration flows for business intelligence

ABSTRACT

Business Intelligence (BI) refers to technologies, tools, and practices for collecting, integrating, analyzing, and presenting large volumes of information to enable better decision making. Today's BI architecture typically consists of a data warehouse (or one or more data marts), which consolidates data from several operational databases, and serves a variety of front-end querying, reporting, and analytic tools. The back-end of the architecture is a data integration pipeline for populating the data warehouse by extracting data from distributed and usually heterogeneous operational sources; cleansing, integrating and transforming the data; and loading it into the data warehouse. Since BI systems have been used primarily for off-line, strategic decision making, the traditional data integration pipeline is a oneway, batch process, usually implemented by extract-transform-load (ETL) tools. The design and implementation of the ETL pipeline is largely a labor-intensive activity, and typically consumes a large fraction of the effort in data warehousing projects. Increasingly, as enterprises become more automated, data-driven, and real-time, the BI architecture is evolving to support operational decision making. This imposes additional requirements and tradeoffs, resulting in even more complexity in the design of data integration flows. These include reducing the latency so that near real-time data can be delivered to the data warehouse, extracting information from a wider variety of data sources, extending the rigidly serial ETL pipeline to more general data flows, and considering alternative physical implementations. We describe the requirements for data integration flows in this next generation of operational BI system, the limitations of current technologies, the research challenges in meeting these requirements, and a framework for addressing these challenges. The goal is to facilitate the design and implementation of optimal flows to meet business requirements.

REFERENCES

• 1: M. Anthimopoulos, B. Gatos, I. Pratikakis. Multiresolution text detection in video frames. In VISAPP (2), pp. 161--166, 2007.
• 2: Michael W. Berry , Malu Castellanos, Survey of Text Mining II: Clustering, Classification, and Retrieval, 2007
• 3: Malu Castellanos , Alkis Simitsis , Kevin Wilkinson , Umeshwar Dayal, Automating the loading of business process data warehouses, 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.1516431]
• 4: Surajit Chaudhuri , Umeshwar Dayal , Venkatesh Ganti, Database Technology for Decision Support Systems, Computer, v.34 n.12, p.48-55, December 2001 [doi>10.1109/2.970575]
• 5: Qiming Chen , Meichun Hsu, Data-Continuous SQL Process Model, Proceedings of the OTM 2008 Confederated International Conferences, CoopIS, DOA, GADA, IS, and ODBASE 2008. Part I on On the Move to Meaningful Internet Systems:, November 09-14, 2008, Monterrey, Mexico [doi>10.1007/978-3-540-88871-0_14]
• 6: S. Chen, L. Bao, P. Chen. OptBPEL: A Tool for Performance Optimization of BPEL Process. In Software Composition, pp. 141--148, 2008.
• 7: L. Chung, B. A. Nixon, E. Yu, J. Mylopoulos. Non-Functional Requirements in Software Engineering. Kluwer Academic Publishing, 1999.
• 8: 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]
• 9: Jeffrey Dean , Sanjay Ghemawat, MapReduce: simplified data processing on large clusters, Proceedings of the 6th conference on Symposium on Opearting Systems Design & Implementation, p.10-10, December 06-08, 2004, San Francisco, CA
• 10: Ahmed Elmagarmid , Marek Rusinkiewicz , Amit Sheth, Management of heterogeneous and autonomous database systems, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1998
• 11: Dayne Freitag , Andrew McCallum, Information Extraction with HMM Structures Learned by Stochastic Optimization, Proceedings of the Seventeenth National Conference on Artificial Intelligence and Twelfth Conference on Innovative Applications of Artificial Intelligence, p.584-589, July 30-August 03, 2000
• 12: P. Gillin. BI @ the Speed of Business. Computer World Technology Briefings. December 2007. Available at: http://resources.computerworld.com/sas_imw/registration.php?item=12&tab=1.
• 13: Laura M. Haas , Mauricio A. Hernández , Howard Ho , Lucian Popa , Mary Roth, Clio grows up: from research prototype to industrial tool, Proceedings of the 2005 ACM SIGMOD international conference on Management of data, June 14-16, 2005, Baltimore, Maryland [doi>10.1145/1066157.1066252]
• 14: Alon Halevy , Anand Rajaraman , Joann Ordille, Data integration: the teenage years, Proceedings of the 32nd international conference on Very large data bases, September 12-15, 2006, Seoul, Korea
• 15: Richard Hull, Artifact-Centric Business Process Models: Brief Survey of Research Results and Challenges, Proceedings of the OTM 2008 Confederated International Conferences, CoopIS, DOA, GADA, IS, and ODBASE 2008. Part II on On the Move to Meaningful Internet Systems, November 09-17, 2008, Monterrey, Mexico [doi>10.1007/978-3-540-88873-4_17]
• 16: Informatica. Pushdown Optimization. Available at: http://www.informatica.com/INFA_Resources/ds_pushdown_optimization_6675.pdf
• 17: Informatica. How to Achieve Flexible, Cost-effective Scalability and Performance through Pushdown Processing. White paper, November 2007.
• 18: William H. Inmon, Building the Data Warehouse, John Wiley & Sons, Inc., New York, NY, 1992
• 19: William Inmon , Anthony Nesavich, Tapping into unstructured data: integrating unstructured data and textual analytics into business intelligence, Prentice Hall Press, Upper Saddle River, NJ, 2007
• 20: William H. Inmon , Derek Strauss , Genia Neushloss, DW 2.0: The Architecture for the Next Generation of Data Warehousing, Morgan Kaufmann Publishers Inc., San Francisco, CA, 2008
• 21: H. A. Kuno, K. Yuasa, K. Govindarajan, K. Smathers, B. Burg, P. Carau, K. Wilkinson. Governing the Contract Lifecycle: A Framework for Sequential Configuration of Loosely-Coupled Systems. In DNIS, pp. 264--279, 2005.
• 22: S. Luján-Mora, P. Vassiliadis, J. Trujillo. Data Mapping Diagrams for Data Warehouse Design with UML. In ER, pp. 191--204, 2004.
• 23: Elzbieta Malinowski , Esteban Zimnyi, Advanced Data Warehouse Design: From Conventional to Spatial and Temporal Applications (Data-Centric Systems and Applications), Springer Publishing Company, Incorporated, 2008
• 24: C. Thomsen, T. B. Pedersen, W. Lehner. RiTE: Providing On-Demand Data for Right-Time Data Warehousing. In ICDE, pp. 456--465, 2008.
• 25: 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.
• 26: 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
• 27: T. K. Sellis, A. Simitsis. ETL Workflows: From Formal Specification to Optimization. In ADBIS, pp. 1--11, 2007.
• 28: 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]
• 29: 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]
• 30: Dimitrios Skoutas , Alkis Simitsis, Designing ETL processes using semantic web technologies, Proceedings of the 9th ACM international workshop on Data warehousing and OLAP, November 10-10, 2006, Arlington, Virginia, USA [doi>10.1145/1183512.1183526]
• 31: Stephen Soderland, Learning Information Extraction Rules for Semi-Structured and Free Text, Machine Learning, v.34 n.1-3, p.233-272, Feb. 1999 [doi>10.1023/A:1007562322031]
• 32: 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]
• 33: P. Vassiliadis, A. Simitsis. Near Real Time ETL. In Springer Annals of Information Systems, Vol. 3, pp. 19--29, 2008.
• 34: 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]
• 35: P. Vassiliadis, A. Simitsis, M. Terrovitis, S. Skiadopoulos. Blueprints and Measures for ETL Workflows. In ER, pp. 385--400, 2005.
• 36: C. White. The Next Generation of Business Intelligence: Operational BI. DM Review Magazine, May 2005
• 37: K. Wilkinson, H. A. Kuno, K. Govindarajan, K. Yuasa, K. Smathers, J. Nanda, U. Dayal. Enabling Outsourced Service Providers to Think Globally While Acting Locally. In EDBT, pp. 1106--1109, 2006.
• 38: WS-BPEL Version 2.0, Oasis. Available at: http://docs.oasis-open.org/wsbpel/2.0/wsbpel-v2.0.pdf

INDEX TERMS

Keywords:
ETL, business intelligence, data integration, data warehousing