Real Time Data Warehousing using Dynamic SQL
Sweety V. Batavia
Sweety Batavia, Department of Computer Engineering, Mumbai University, India.
Manuscript received on May 12, 2013. | Revised Manuscript received on June 13, 2013. | Manuscript published on June 30, 2013. | PP: 300-303 | Volume-2, Issue-5, June 2013. | Retrieval Number: E1814062513/2013©BEIESP
Open Access | Ethics and Policies | Cite
© The Authors. Blue Eyes Intelligence Engineering and Sciences Publication (BEIESP). This is an open access article under the CC BY-NC-ND license (http://creativecommons.org/licenses/by-nc-nd/4.0/)
Abstract: Data warehouse synchronization is a complex process in heterogeneous database environment. Maintaining the uniformity of data in real-time is a fundamental problem of data synchronization. In this paper we present a methodology to synchronize data warehouse in heterogeneous database environment in almost real-time using dynamic SQL approach. We capture all DML SQL query from source database and pass this to the processing module. Structure and DBMS systems at the source and target could be different; hence it is not possible to execute the source SQL directly into the target. A processing module runs continuously in the back end to read changes and prepare the dynamic SQL by referring the Reference Data. Reference Data acts as heart of the processing module that prepares the dynamic SQL. It specifies the mapping between source and target with all details. Another process executes the SQL query in the target system. If any error is encountered while executing the query in the target, it will be moved to the error processing module where it will be retried after some delay. Our preliminary experimental results evidence the effectiveness of the proposed method.
Keywords: Real-time Data warehouse synchronization, dynamic SQL, Heterogeneous database, SQL query capture.