OLAP and OLTP Data Integration for Operational Level Decision Making

Abstract

OLAP and OLTP Data Integration for Operational Level Decision Making Data warehousing is being used by many organizations to make decisions at top level. These analysis‐based decisions are more helpful for top management for setting their market trends to compete their market rivals. However, some decisions have to be taken at operational level in an organization such that management should be able to compare current and targeted performance values so they can take proper steps according to the situation. For these decisions to be quicker and accurate, operational data is needed. Such data cannot be gathered from the current warehouse solutions alone as the ETL process of data warehouse is generally done periodically like once every 24 hours. This research work proposes an architecture that incorporates fresh data into DW without involving delayed ETL process. Architecture consists of three major components, Query Recognizer, Query Decomposer and Query Converter. Query Recognizer sends the query to Query Decomposer after analyzing whether query needs data from OLAP and OLTP. Query Decomposer then decomposes the query into two separate queries, one for OLAP and other for OLTP. The OLTP query is sent to Query Convertor which converts the OLTP query into OLAP query. Results are merged in Data Integrator and send back to user. To achieve this objective, an OLTP source system has been designed and implemented using oracle database 10g. Then, client side application has been build over source OLTP system using oracle 10g forms. The data warehouse has been designed and implemented using oracle warehouse builder 10g release 2 that is used to store the captured source OLTP data which is used for analysis purpose. Comprehensive testing and evaluation of the developed system has been carried out. Performance comparison of the proposed system has also been carried out with other researcher’s work in the same area. When compared with normal and active data warehouse ETL techniques, the proposed model provides better and more accurate results. The proposed model provides most recent data with 100% accuracy and there is almost 40% decrease in average query response time as compared to active data warehouse model.