Extending and automating ETL processes in DWH

Abstract

Data mining and Data Warehouse (DWH) effectiveness depends on integrating a number of data sources. Extract Transform and Load (ETL) is a fundamental process for data integration, improving data quality, timelines and efficacy of data. Its implementation is known to be quite code intensive and contrived. Various data integration tools and process languages exist that aim at making ETL more manageable. Nonetheless, to implement an ETL process using these tools is still complex, intensive and comparatively fine grained. This dissertation involves an investigation of data and processes commonly required in an ETL process in order to automate these at a higher level. To achieve this, Business Process Model and Notation (BPMN) is used to design an ETL workflow in conjunction with a specification file. This file describes additional details about the ETL processes depicted in a BPMN model. Moreover, this work extends the automated processes to abstract the complexity and intensity of the ETL. In order to extend the ETL processes, data definition files are supplied to an automated ETL workflow, where these files are used to define the source and destination DWH structure. The work presented proves to be effective as queries were run to evaluate the ETL processes implemented, where the results obtained illustrate that the processes function as expected. Consequently, the source data is extracted, transformed and loaded into the DWH as specified.