Data Load Distribution by Semi Real Time Data Warehouse

Abstract

Many organizations use data warehouse for strategic decision making. Today's real‐ time business stresses the potential to process increasingly volumes of data at very high speed in order to stay competitive in market. Data Warehouse is populated by data extraction, transformation and loading from different data sources by software utilities called ETL (Extraction, Transformation & Loading). ETL is a time consuming process as it has to process large volume of data. ETL processes must have certain completion time window and ETL process must have to finish within this time window. In today's fast growing information based market, companies must be able to integrate huge volume of different OLTP source data for supporting tactical IT plan such as Business Intelligence or Corporate. At the same time, IT companies are under constant stress to get more work done with fewer resources. The best way to overcome these contradictory goals is to implement a cost‐ effective integration methodology that increases the efficiency of the IT business. The objective of this research work is to develop a technique to distribute the volume of data to be extracted, transformed and loaded in data warehouse by merging both conventional and real‐time techniques, so ETL process finishes its job within its time frame by utilizing ETL idle time. 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 extraction process based on oracle change data capture mechanism has been implemented over source OLTP system and java based utility has been developed to convert the captured source data to XML and CSV file. 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 that is used for analysis purpose. Then comprehensive testing and evaluation of the developed system has been carried out. Also present performance comparison of the proposed system with other research workers in the same area.