Study and Implementation of a New SQL-Based ETL Approach

作     者：BAO Yubin SONG Jie LENG Fangling WANG Daling YU Ge BAO Yubin,SONG Jie,LENG Fangling,WANG Daling,YU Ge College of Information Science and Engineering,Northeast-ern University,Shenyang 110004,Liaoning,China

作者机构：College of Information Science and Engineering Northeastern University Shenyang 110004 Liaoning China 

出 版 物：《Wuhan University Journal of Natural Sciences》 (武汉大学学报（自然科学英文版）)

年 卷 期：2007年第12卷第5期

页      面：804-808页

学科分类：12[管理学] 1201[管理学-管理科学与工程(可授管理学、工学学位)] 

基　　金：Supported by the National Natural Science Foundation of China (60673139  60573090) 

主　　题：data warehouse ETL E-LT SQL 

摘      要：This paper analyzes the main characteristics, benefits, and disadvantages of existing traditional ETL （extraction, transformation, loading） methods, and summaries some factors affecting the performance of ETL tools. Then, a new ETL approach, E-LT （extraction, loading and transformation）, is proposed. The E-LT approach applies database mapping technique to realize that loading stage and transformation stage in the ETL process are performed at the same time after the extraction stage. Thus, it can use SQL commands to complete loading and transformation processing, and eliminates the staging area before loading in traditional ETL process. The framework of an ETL engine based on E-LT method is presented. The ETL process including initial loading and incremental refreshment is discussed in detail, and the SQL-based algorithm for initial loading is presented. The performance of E-LT method on loading throughout outperforms some commercial ETL approaches by experimental proof and theoretical analysis. At last, a real case in marine data warehousing of the E-LT method is discussed for illustrating the validity of the proposed method.