Extract-Transform-Load Processes Research Articles

A proposed model for data warehouse ETL processes

Extraction–transformation–loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, its cleansing, customization, reformatting, integration, and insertion into a data warehouse. Building the ETL process is potentially one of the biggest tasks of building a warehouse; it is complex, time consuming, and consumes most of data warehouse project’s implementation efforts, costs, and resources. Building a data warehouse requires focusing closely on understanding three main areas: the source area, the destination area, and the mapping area (ETL processes). The source area has standard models such as entity relationship diagram, and the destination area has standard models such as star schema, but the mapping area has not a standard model till now. In spite of the importance of ETL processes, little research has been done in this area due to its complexity. There is a clear lack of a standard model that can be used to represent the ETL scenarios. In this paper we will try to navigate through the efforts done to conceptualize the ETL processes. Research in the field of modeling ETL processes can be categorized into three main approaches: Modeling based on mapping expressions and guidelines, modeling based on conceptual constructs, and modeling based on UML environment. These projects try to represent the main mapping activities at the conceptual level. Due to the variation and differences between the proposed solutions for the conceptual design of ETL processes and due to their limitations, this paper also will propose a model for conceptual design of ETL processes. The proposed model is built upon the enhancement of the models in the previous models to support some missing mapping features.

GOAL-ONTOLOGY ETL PROCESSES SPECIFICATION

The design-related problems for extract, transform, load (ETL) processes are far away from being resolved due to the ambiguity of user requirements and the complexity of operations. Current approaches are based on existing software requirement methods that have limitations on reconciliation of the requirement semantics toward generating the ETL processes specification. The solution is to apply the RAMEPs (Requirement Analysis Method for ETL Processes) that was developed to facilitate the design of the ETL processes in the perspectives of organization, decision-maker, and developer. The results are the ETL processes specification, which was validated on the correctness of the goal-ontology model and evaluated in the case study of Gas Malaysia Data Warehouse (DW) system. The case study illustrated how the goal-ontology approach was successfully implemented in designing and generating the ETL processes specification.

Representation of conceptual ETL designs in natural language using Semantic Web technology

Extract-Transform-Load (ETL) processes constitute the back stage of Data Warehouse architectures. Several studies characterize the ETL design as a time-consuming and error-prone procedure. A critical phase in the ETL lifecycle involves the early communications and design steps that aim at producing a conceptual ETL design. Various research approaches have dealt with the conceptual modeling of ETL processes, but all share two inconveniences: they require intensive human effort from the designers to create them, as well as technical knowledge from the business people to understand them. In this paper, we focus on the second aspect and provide a method for the representation of a conceptual ETL design as a narrative, which is the most natural means of communication and does not require particular technical skills or familiarity with any specific model. Specifically, this work builds upon previously proposed techniques that automate the conceptual design by leveraging Semantic Web technology. The key idea is to map the involved data stores, either source or target, to a domain ontology and then, to use a reasoner for producing the ETL design. We discuss how linguistic techniques can be used for the establishment of a common application vocabulary. We present a flexible and customizable template-based mechanism for the representation of the ETL design as a narrative. Finally, we discuss issues related to the production of meaningful reports and we provide implementation details.

A Survey of Extract–Transform–Load Technology

The software processes that facilitate the original loading and the periodic refreshment of the data warehouse contents are commonly known as Extraction-Transformation-Loading (ETL) processes. The intention of this survey is to present the research work in the field of ETL technology in a structured way. To this end, we organize the coverage of the field as follows: (a) first, we cover the conceptual and logical modeling of ETL processes, along with some design methods, (b) we visit each stage of the E-T-L triplet, and examine problems that fall within each of these stages, (c) we discuss problems that pertain to the entirety of an ETL process, and, (d) we review some research prototypes of academic origin.

Study and implementation of a new SQL-based ETL approach

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.

A method for the mapping of conceptual designs to logical blueprints for ETL processes

Extraction–Transformation–Loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleansing, customization and insertion into a data warehouse. In previous work, we presented a modeling framework for ETL processes comprised of a conceptual model that concretely deals with the early stages of a data warehouse project, and a logical model that deals with the definition of data-centric workflows. In this paper, we describe the mapping of the conceptual model to the logical model. First, we identify how conceptual entities are mapped to logical entities. Next, we determine the execution order in the logical workflow using information adapted from the conceptual model. Finally, we provide a method for the transition from the conceptual model to the logical model.

Physical Modeling of Data Warehouses Using UML Component and Deployment Diagrams

Several approaches have been proposed to model different aspects of a Data Warehouse (DW) during recent years, such as the modeling of a DW at the conceptual and logical level, the design of the ETL (Extraction, Transformation, Loading) processes, the derivation of the DW models from the enterprise data models, and customization of a DW schema. At the end of the design, a DW has to be deployed in a database environment, requiring many decisions of a physical nature. However, few efforts have been dedicated to the modeling of the physical design of a DW from the early stages of a DW project. In this article, we argue that some physical decisions can be taken from gathering main user requirements. In this article, we present physical modeling techniques for DWs using the component diagrams and deployment diagrams of the Unified Modeling Language (UML). Our approach allows the designer to anticipate important physical design decisions that may reduce the overall development time of a DW, such as replicating dimension tables, vertical and horizontal partitioning of a fact table, and the use of particular servers for certain ETL processes. Moreover, our approach allows the designer to cover all main design phases of DWs from the conceptual modeling phase to the final implementation. To illustrate our techniques, we show a case study that is implemented on top of a commercial DW management server.

State-space optimization of ETL workflows

Extraction-transformation-loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleansing, customization, and insertion into a data warehouse. In this paper, we derive into the logical optimization of ETL processes, modeling it as a state-space search problem. We consider each ETL workflow as a state and fabricate the state space through a set of correct state transitions. Moreover, we provide an exhaustive and two heuristic algorithms toward the minimization of the execution cost of an ETL workflow. The heuristic algorithm with greedy characteristics significantly outperforms the other two algorithms for a large set of experimental cases.

Arktos: towards the modeling, design, control and execution of ETL processes

Extraction-Transformation-loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleansing, customization and insertion into a data warehouse. Literature and personal experience have guided us to conclude that the problems concerning the ETL tools are primarily problems of complexity, usability and price. To deal with these problems we provide a uniform metamodel for ETL processes, covering the aspects of data warehouse architecture, activity modeling, contingency treatment and quality management. The ETL tool we have developed, namely Arktos, is capable of modeling and executing practical ETL scenarios by providing explicit primitives for the capturing of common tasks. Arktos provides three ways to describe an ETL scenario: a graphical point-and-click front end and two declarative languages: XADL (an XML variant), which is more verbose and easy to read and SADL (an SQL-like language) which has a quite compact syntax and is, thus, easier for authoring.