Proposed Techniques to Optimize the DW and ETL Query for Enhancing data warehouse efficiency

Abstract

To improve time-bound decision capability of any business corporates, they have to perform data-analysis based on historical and current data. For handling this data, data warehouse (DWH) plays a crucial role. Further-more as time passes, the data also grows exponentially, because in data warehouse historical data also needs to be maintained. If we further drill now-a-days Extract Transform and Load (ETL) tools are being used to perform extract, transform and loading of data. These ETL tools are based on any specific language like in case of Informatica ETL tool, which is based on Structured Query Language (SQL) language. So, it's imperative to enhance the efficiency of these languages to enhance the capacity of ETL tools and this will significantly impact the performance of data warehouse ETL work, and by this data can also be provided in faster manner for analysis and reporting purposes. Additionally, data quality is also an important factor, as data-analysis will be performed on the same data supplied by data warehouse. So, it's highly concerned to improve the data quality by applying data-quality checks before supplying for data-analysis purpose. So, we have worked in this direction and gone through various research papers which are mainly focused on query optimization and data quality improvements. After going through all research papers, we have not only summarized the performance tuning tips given in these papers but also worked and experimented various things, and then we have proposed some unique techniques for query optimization and improved data quality techniques. After which we have incorporated it in data warehouse to enhance overall performance of data warehouse. These proposed techniques can be applied in any place wherever queries are being used to extract/transform and/or load the data to enhance the performance.