Extract-Transform-Load Processes Research Articles

Efficient incremental loading in ETL processing for real-time data integration

ETL (extract transform load) is the widely used standard process for creating and maintaining a data warehouse (DW). ETL is the most resource-, cost- and time-demanding process in DW implementation and maintenance. Nowadays, many graphical user interfaces (GUI)-based solutions are available to facilitate the ETL processes. In spite of the high popularity of GUI-based tool, there is still some downside of such approach. This paper focuses on alternative ETL developmental approach taken by hand coding. In some contexts like research and academic work, it is appropriate to go for custom-coded solution which can be cheaper, faster and maintainable compared to any GUI-based tools. Some well-known code-based open-source ETL tools developed by the academic world have been studied in this article. Their architecture and implementation details are addressed here. The aim of this paper is to present a comparative evaluation of these code-based ETL tools. Finally, an efficient ETL model is designed to meet the near real-time responsibility of the present days.

Privacy-enhancing ETL-processes for biomedical data

BackgroundModern data-driven approaches to medical research require patient-level information at comprehensive depth and breadth. To create the required big datasets, information from disparate sources can be integrated into clinical and translational warehouses. This is typically implemented with Extract, Transform, Load (ETL) processes, which access, harmonize and upload data into the analytics platform. ObjectivePrivacy-protection needs careful consideration when data is pooled or re-used for secondary purposes, and data anonymization is an important protection mechanism. However, common ETL environments do not support anonymization, and common anonymization tools cannot easily be integrated into ETL workflows. The objective of the work described in this article was to bridge this gap. MethodsOur main design goals were (1) to base the anonymization process on expert-level risk assessment methodologies, (2) to use transformation methods which preserve both the truthfulness of data and its schematic properties (e.g. data types), (3) to implement a method which is easy to understand and intuitive to configure, and (4) to provide high scalability. ResultsWe designed a novel and efficient anonymization process and implemented a plugin for the Pentaho Data Integration (PDI) platform, which enables integrating data anonymization and re-identification risk analyses directly into ETL workflows. By combining different instances into a single ETL process, data can be protected from multiple threats. The plugin supports very large datasets by leveraging the streaming-based processing model of the underlying platform. We present results of an extensive experimental evaluation and discuss successful applications. ConclusionsOur work shows that expert-level anonymization methodologies can be integrated into ETL workflows. Our implementation is available under a non-restrictive open source license and it overcomes several limitations of other data anonymization tools.

ETL Processes Security Modeling

The development of information systems (IS) in a secure environment or condition is a complex task that involves many additional basic security protocols, policies as well as industry standards on passwords, anti-virus programs, firewalls and data encryption. However, in traditional IS development lifecycles, security is either ignored or added as an afterthought, which does not assure the system complete security. So, it is necessary to give more importance to this issue and consider it as part of IS development process. In this context, the authors should guarantee the security of ETL (Extract, Transform, Load) processes, which are among the most critical and complex tasks during DW development project. In this study, security management is carried out for ETL processes by proposing a meta-model integrating the security concepts from the security requirements to the necessary preventive and / or corrective treatments. The proposed meta-model is validated with instantiation.

A New Approach for Conceptual Extraction-Transformation-Loading Process Modeling

Erroneous or incomplete data generated from various sources can have direct impact in business analysis. Extracted data from sources need to load into data warehouse after required transformation to reduce error and minimize data loss. This process is also known as Extraction-Transformation-Loading (ETL). High-level view of the system activities can be visualized by conceptual modeling of ETL process. It provides the advantage of pre-identification of system error, cost minimization, scope and risk assessment etc. A new modeling approach is proposed for conceptualization ETL process by using a standard Systems Modeling Language (SysML). For handling increasing complexity of any system model, it is preferable to go through verification and validation process in early stage of system development. In this article, the authors' previous work is extended by presenting a MBSE based approach to automate the SysML model's validation by using No Magic simulator. Here, the main objective is to overcome the gap between modeling and simulation and to examine the performance of the proposed SysML model. The usefulness of the authors' approach is exhibited by using a use case scenario.

An Integrated Approach for Massive Sequential Data Processing in Civil Infrastructure Operation and Maintenance

This paper presents an extract-transform-load (ETL) approach based on multilayer task execution for processing massive sequential data collected from infrastructure operation and maintenance. The proposed approach consists of ETL task partition, execution mode selection, and ETL modeling. The task partition focuses on dividing the ETL process into four tasks to be executed in accordance with different organizational forms of data. Sequenced or non-sequenced load mode is optional, which is independent of the data standardization. In addition, the ETL modeling phase implements conceptual, logical, and physical modeling for the multi-dimensional model. Our main objective is to integrate massive sequential data, enhancing decision-making performance for the intelligent management platform. Traffic data for two years were collected from various systems and acquisition tools of different providers to evaluate the data integration capability of the proposed approach. Furthermore, Kettle software was used to perform transformation and job modules for the multilayer tasks. In addition, a machine learning algorithm was used to generate traffic warning in the tunnels based on the integrated data. The proposed approach is promising for the management and analysis of massive sequential data generated in operation, the maintenance of transportation tunnels, and effective decision-making.

Frequent patterns in ETL workflows: An empirical approach

The complexity of Business Intelligence activities has driven the proposal of several approaches for the effective modeling of Extract-Transform-Load (ETL) processes, based on the conceptual abstraction of their operations. Apart from fostering automation and maintainability, such modeling also provides the building blocks to identify and represent frequently recurring patterns. Despite some existing work on classifying ETL components and functionality archetypes, the issue of systematically mining such patterns and their connection to quality attributes such as performance has not yet been addressed. In this work, we propose a methodology for the identification of ETL structural patterns. We logically model the ETL workflows using labeled graphs and employ graph algorithms to identify candidate patterns and to recognize them on different workflows. We showcase our approach through a use case that is applied on implemented ETL processes from the TPC-DI specification and we present mined ETL patterns. Decomposing ETL processes to identified patterns, our approach provides a stepping stone for the automatic translation of ETL logical models to their conceptual representation and to generate fine-grained cost models at the granularity level of patterns.

A comparative study of various ETL process and their testing techniques in data warehouse

Extraction–transformation–loading (ETL) tools are fraction of software which extract the data from various sources, and then it clean, customize, reformat, and integrate the data and insert into a data warehouse. ETL process in data warehousing responsible for pick data out from the operational systems and fixed it into the data warehouse. Construct the ETL process is one of the bulky tasks of construct a warehouse. In this paper we will try to explain the ETL process, ETL testing challenges and ETL testing techniques.

Distributed snapshot maintenance in wide-column NoSQL databases using partitioned incremental ETL pipelines

Wide-column NoSQL databases are an important class of NoSQL (Not only SQL) databases which scale horizontally and feature high access performance on sparse tables. With current trends towards big Data Warehouses (DWs), it is attractive to run existing business intelligence/data warehousing applications on higher volumes of data in wide-column NoSQL databases for low latency by mapping multidimensional models to wide-column NoSQL models or using additional SQL add-ons. For examples, applications like retail management can run over integrated data sets stored in big DWs or in the cloud to capture current item-selling trends. Many of these systems also employ Snapshot Isolation (SI) as a concurrency control mechanism to achieve high throughput for read-heavy workloads. SI works well in a DW environment, as analytical queries can now work on (consistent) snapshots and are not impacted by concurrent update jobs performed by online incremental Extract-Transform-Load (ETL) flows that refresh fact/dimension tables. However, the snapshot made available in the DW is often stale, since at the moment when an analytical query is issued, the source updates (e.g. in a remote retail store) may not have been extracted and processed by the ETL process in time due to high input data volume or slow processing speed. This staleness may cause incorrect results for time-critical decision support queries. To address this problem, snapshots which are supposed to be accessed by analytical queries need to be first maintained by corresponding ETL flows to reflect source updates based on given freshness needs. Snapshot maintenance in this work means maintaining the distributed data partitions that are required by a query. Since most NoSQL databases are not ACID compliant and do not provide full-fledged distributed transaction support, snapshot may be inconsistently derived when its data partitions are updated by different ETL maintenance jobs.This paper describes an extended version of HBelt system [1] which tightly integrates the wide-column NoSQL database HBase with a clustered & pipelined ETL engine. Our objective is to efficiently refresh HBase tables with remote source updates while a consistent snapshot is guaranteed across distributed partitions for each scan request in analytical queries. A consistency model is defined and implemented to address so-called distributed snapshot maintenance. To achieve this, ETL jobs and analytical queries are scheduled in a distributed processing environment. In addition, a partitioned, incremental ETL pipeline is introduced to increase the performance of ETL (update) jobs. We validate the efficiency gain in terms of data pipelining and data partitioning using the TPC-DS benchmark, which simulates a modern decision support system for a retail product supplier. Experimental results show that high query throughput can be achieved in HBelt when distributed, refreshed snapshots are demanded.

A FEASIBILITY STUDY ON BIG DATA INTEGRATION AND ITS METHODOLOGIES FOR HADOOP TECHNIQUES USING MAP REDUCE MODEL

This paper aims at presenting the Big Data Integration and techniques available for Big Data Integration using Hadoop techniques. Data is collected and stored at unprecedented rates. Big data integration is the process of transferring data in source format into destination format. Many data warehouse and management are supported by integration techniques and transportation by using Extract Transform-Load process. Map Reducing method are used to extract and classify the data groups. Map takes a set of data and converts it into another set of data, where individual elements are broken down into tuples (key/value pairs). Secondly, reduce task, which takes the output from a map as an input and combines those data tuples into a smaller set of tuples. Big Data Integration as a process is highly combined and iterative to add new data sources. The challenge is not only to store and manage the vast volume of data, but also to analyze and extract meaningful value from it. Big data comes from relatively new types of data sources like social media, public filings, and content available in the public domain through agencies or subscriptions, documents and e-mails including both structured and unstructured texts format. Keywords—Map Reduce Techniques, Hadoop, Fault-Tolerance and High Availability

Automating ETL processes using the domain-specific modeling approach

The development of Extract–Transform–Load (ETL) processes is the most complex, time-consuming and expensive phase of data warehouse development. Yet, the dynamics of modern business systems demand a more agile and flexible approach to their development. As a result, current research in this area is focused on ETL process conceptualization and the automation of ETL process development. This paper proposes a novel solution for automating ETL processes using the domain-specific modeling (DSM) approach. The proposed solution is based on the formal specification of ETL processes and the implementation of such formal specifications. Thus, in accordance with the DSM approach, several new domain-specific languages (DSLs) are introduced, each defining concepts relevant for a specific aspect of an ETL process. The focus of this paper is the actual implementation of the formal specification of an ETL process. To this end, a specific ETL platform (ETL-PL) is introduced to technologically support both the modeling of ETL processes (i.e., the creation of models in accordance with the introduced DSLs) and the automated transformation of the created models into the executable code of a specific application framework (representing ETL-PL’s execution environment). It should be emphasized that ETL-PL actually presumes the dynamic execution of ETL models or, more precisely, the executable code is generated at runtime. Thus the execution environment consists of code generator components and the components implementing the application framework. ETL-PL has been implemented as an extension of the .NET platform.

Data generator for evaluating ETL process quality

Obtaining the right set of data for evaluating the fulfillment of different quality factors in the extract-transform-load (ETL) process design is rather challenging. First, the real data might be out of reach due to different privacy constraints, while manually providing a synthetic set of data is known as a labor-intensive task that needs to take various combinations of process parameters into account. More importantly, having a single dataset usually does not represent the evolution of data throughout the complete process lifespan, hence missing the plethora of possible test cases. To facilitate such demanding task, in this paper we propose an automatic data generator (i.e., Bijoux). Starting from a given ETL process model, Bijoux extracts the semantics of data transformations, analyzes the constraints they imply over input data, and automatically generates testing datasets. Bijoux is highly modular and configurable to enable end-users to generate datasets for a variety of interesting test scenarios (e.g., evaluating specific parts of an input ETL process design, with different input dataset sizes, different distributions of data, and different operation selectivities). We have developed a running prototype that implements the functionality of our data generation framework and here we report our experimental findings showing the effectiveness and scalability of our approach.

An Intelligent ETL Grid-Based Solution to Enable Spatial Data Warehouse Deployment in Cyber Physical System Context

Abstract Thanks to their spatially distributed sensors, Cyber-Physical System (CPS) applications are currently accumulating large amounts of heterogeneous data. When it comes to allowing several decision-makers to collaboratively plan their actions, these applications need appropriate tools for an efficient storage, analysis, and visualization of the available data. Spatial Data Warehouses (SDWs) have proven their efficiency in carrying out these operations. However, because of the increasing quantity of data, the Extract-Transform-Load (ETL) process (which is in charge of aggregating several data sources within a unified data storage repository) generally fails to update the SDW within predefined window times. In order to solve this problem, we propose in this paper to distribute the ETL tasks over a grid of computing resources. We also propose a multiagent-based approach that controls the ETL grid while allowing a convenient use of the shared resources. In addition to being the unique solution that uses grid computing for the ETL process of SDWs, our approach allows a joint use of archive and real-time data for personalized reporting and visualization of services envisioned to the decision-makers who are using the CPS application.

A Review of Contemporary Data Quality Issues in Data Warehouse ETL Environment

In today’s scenario, Extraction–transformation– loading (ETL) tools have become important pieces of software responsible for integrating heterogeneous information from several sources. The task of carrying out the ETL process is potentially a complex, hard and time consuming. Organisations now –a-days are concerned about vast qualities of data. The data quality is concerned with technical issues in data warehouse environment. Research in last few decades has laid more stress on data quality issues in a data warehouse ETL process. The data quality can be ensured cleaning the data prior to loading the data into a warehouse. Since the data is collected from various sources, it comes in various formats. The standardization of formats and cleaning such data becomes the need of clean data warehouse environment. Data quality attributes like accuracy, correctness, consistency, timeliness are required for a Knowledge discovery process. The present state -of –the- art purpose of the research work is to deal on data quality issues at all the aforementioned stages of data warehousing 1) Data sources, 2) Data integration 3) Data staging, 4) Data warehouse modelling and schematic design and to formulate descriptive classification of these causes. The discovered knowledge is used to repair the data deficiencies. This work proposes a framework for quality of extraction transformation and loading of data into a warehouse.

Empirical Models for the Performance of ETL Processes

software projects' outcomes will give us various aspects of quality parameters. In such cases, empirical studies with prototyping exercises are well suited to analyze/understand the system. ETL (Extraction- Transformation-Loading) is the software responsible for extracting data, cleaning, transforming and loading the data into a data warehouse. ETL is a large software system. The performance of the decision support system depends on the data warehouse that it uses. ETL tools play a major role in building the data warehouse; these tools need to have good performance in order to improve the performance of the whole system. An experimental study is conducted to analyze the performance of the ETL tool. Two ETL tools are considered; one with integrated security and another without integrated security. The time for data extraction in different environments is recorded. Further, regression analysis is done on the experimental data and observed the behavior of the tools and developed the empirical models. Both tools have shown the same behavior in performance for different extraction data sizes.

Managing Data Quality for a Drug Safety Surveillance System

The objective of this study is to present a data quality assurance program for disparate data sources loaded into a Common Data Model, highlight data quality issues identified and resolutions implemented. The Observational Medical Outcomes Partnership is conducting methodological research to develop a system to monitor drug safety. Standard processes and tools are needed to ensure continuous data quality across a network of disparate databases, and to ensure that procedures used to extract-transform-load (ETL) processes maintain data integrity. Currently, there is no consensus or standard approach to evaluate the quality of the source data, or ETL procedures. We propose a framework for a comprehensive process to ensure data quality throughout the steps used to process and analyze the data. The approach used to manage data anomalies includes: (1) characterization of data sources; (2) detection of data anomalies; (3) determining the cause of data anomalies; and (4) remediation. Data anomalies included incomplete raw dataset: no race or year of birth recorded. Implausible data: year of birth exceeding current year, observation period end date precedes start date, suspicious data frequencies and proportions outside normal range. Examples of errors found in the ETL process were zip codes incorrectly loaded, drug quantities rounded, drug exposure length incorrectly calculated, and condition length incorrectly programmed. Complete and reliable observational data are difficult to obtain, data quality assurance processes need to be continuous as data is regularly updated; consequently, processes to assess data quality should be ongoing and transparent.

Towards a Large Access-Oriented Digital Archive

The ultimate goal of preserving digital records is to make them accessible to the public and authorized parties for centuries. Such access should be done in a way that is independent of technical application softwares and platforms with which those records were created. According to the Open Archival Information System (OAIS) model, Access to records is one of the main components of an archival system. The Access component allows the Consumer to search for records in the Archival Storage, and obtain relevant result sets. Although Access is at the end of OAIS data flow, the quality of service that it can provide to the consumers depends on both ingest and preservation planning. Indeed, the ETL (Extract-Transform-Load) process at ingest time will determine the richness and comprehensiveness of the created metadata, and the level of granularity or coarseness of the discovery and access process. Moreover, the planning and execution of preservation methods should take into account not only the pure transformation of file formats, but the quality of access and discovery of digital records. This paper examines the challenges to prepare and provide search and access to a large digital archives throughout the different phases of the OAIS functional flow, namely, ingest, and preservation. From the architecture perspective, the guiding principle of a large Access-Oriented Digital Archive should be to realize an Open Platform that facilitates the flow of data from ingest to access. The framework used to architect this platform layer should be extensible so that future search and access techniques could be easily inserted without major redesign of the system.

MapReduce-based dimensional ETL made easy

This paper demonstrates ETLMR , a novel dimensional Extract--Transform--Load (ETL) programming framework that uses Map-Reduce to achieve scalability. ETLMR has built-in native support of data warehouse (DW) specific constructs such as star schemas, snowflake schemas, and slowly changing dimensions (SCDs). This makes it possible to build MapReduce-based dimensional ETL flows very easily. The ETL process can be configured with only few lines of code. We will demonstrate the concrete steps in using ETLMR to load data into a (partly snowflaked) DW schema. This includes configuration of data sources and targets, dimension processing schemes, fact processing, and deployment. In addition, we also present the scalability on large data sets.

Configuring and executing etl tasks on grid environments - requirements and specificities

Data Warehouses store integrated and consistent data in a subject-oriented data repository dedicated especially to support business intelligence processes. Nevertheless, in order to maintain a data warehouse up-to-date, data intensive tasks retrieve regularly specialized information from specific preselected information sources, transforming and conforming it accordingly to some specific business requirements provided by decision-makers. Such tasks, commonly named as Extract-Transform-Load (ETL) processes, have a limited time frame window to be executed over an ever increasing amount of data with extremely complex operations. The common approach to deal with the need of more computational power is the acquisition of new and more powerful hardware. This expensive approach disregards the unused computational resources available in desktop computers already present at most enterprises’ computational environments. This paper intends to define a different approach to deal with ETL processes, taking advantage of parallel processing over a GRID environment using XML data as an effective support to data storage and communication, demonstrating that GRID environments could be a real alternative for the implementation of low cost data warehouses.

An Integrated Conceptual Model for Temporal Data Warehouse Security

In the past few years, several conceptual approaches have been proposed for the specification of the main multidimensional (MD) properties of the data warehouse (DW) repository. However, most of them deal with isolated aspects of the DW and do not provide designers with an integrated and standard method for designing the whole DW life cycle (ETL processes, data sources, DW repository and so on).Some approaches are depending on specific platform or neglecting important issues in DW design life cycle. Extraction-transformation-loading (ETL) processes play an important role in data warehouse architecture because they are responsible of integrating data from heterogeneous data sources into the DW repository. This paper proposes a conceptual model to refresh data warehouse by (insert, update, delete) data using ETL processes and considering DW security requirements. Firstly, the proposed ETL model is based on Unified Modeling Language (UML), which allows us to accomplish the conceptual modeling of ETL processes .secondly; this part focuses on how to integrate the proposed ETL model with the DW model. The proposed DW model depends on the Model Driven Architecture (MDA). MDA is a standard framework for software development that addresses the complete life cycle of designing, deploying, integrating, and managing applications by using models in software development. This paper proposes an integrated conceptual model for addressing temporal data warehouse security requirements (CMTDWS).

AN APPROACH FOR DESIGNING, MODELING AND REALIZING ETL PROCESSES BASED ON UNIFIED VIEWS MODEL

Extraction-Transformation-Loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleaning, customization and insertion into Data Warehouses (DWs). Complexity, usability and maintainability are the primary problems concerning ETL processes. To deal with these problems, in this paper we provide a dynamic approach for designing, modeling and realizing ETL processes. We propose a new architecture based on Unified Views Model (UVM) for ETL processes, in which Unified view layer is added between source data level and DWs level. The unified views model serves as the means to conform the structure and semantics of the source data to the ones of the data warehouses, and help designers understand and analyze the meaning, relationships and lineage of information. In order to guarantee the transparency access and the usability, two mapping methods are adopted between Unified view level and source data level as well as between DWs level and Unified view level. Based on this architecture, the method of constructing UVM and ETL operations among three levels is given. Then, we describe how to build the conceptual modeling for ETL processes based on UVM by using an extension of the Unified Modeling Language (UML). Finally, we present an ETL tool based on UVM (UVETL) with the goal of facilitating the design, modeling and realization of ETL processes, and give a case study to exemplify the benefits of our proposal.