Extract-Transform-Load Workflows Research Articles

ETL Logs Under a Pattern-Oriented Approach

Considering extract-transform-load (ETL) as a complex and evolutionary process, development teams must conscientiously and rigorously create log strategies for retrieving the most value of the information that can be gathered from the events that occur through the ETL workflow. Efficient logging strategies must be structured so that metrics, logs, and alerts can, beyond their troubleshooting capabilities, provide insights about the system. This paper presents a configurable and flexible ETL component for creating logging mechanisms in ETL workflows. A pattern-oriented approach is followed as a way to abstract ETL activities and enable its mapping to physical primitives that can be interpreted by ETL commercial tools.

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.

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.

Research and realization of improved extract–transform–load scheduler in China Southern Power Grid

Applications of big data techniques in power system will make contributions to the sustainable development and robust establishment of China Southern Power Grid; thus, it is necessary that a new framework of China Southern Power Grid big data platform is constructed. Apart from key technologies, like data analysis, data process, and data visualization, the integration and fusion problem in the data warehouse plays an important role in the data analysis and mining with high quality. In order to minimize the operation time and memory consumption, various scheduling strategies of extract–transform–load workflows are proposed, including round-robin algorithm, minimum-cost algorithm, minimum-memory algorithm, and mixture of the minimum-cost and minimum-memory algorithm. In combination with above algorithms, a workflow is divided into many subflows by effective algorithms, like shortest-subflow-first and priority-backfilling algorithms, which can further improve the parallel computation ability. Then, the minimum-cost and minimum-memory with shortest-subflow-first algorithm, the minimum-cost and minimum-memory with priority-backfilling algorithm, and the minimum-cost and minimum-memory with shortest-subflow-first and priority-backfilling algorithm are established, which are designed to schedule subflows. Finally, aiming at characteristics of China Southern Power Grid big data, different performance indexes are cited to evaluate above algorithms, and the experiment results show that the minimum-cost and minimum-memory with shortest-subflow-first and priority-backfilling algorithm is superior to the hybrid prioritization algorithm based on the rank level of each task (hybrid), online workflow management, minimum-cost and minimum-memory with shortest-subflow-first, and the minimum-cost and minimum-memory with priority-backfilling algorithm, and the system robust performance is also significantly met and improved.

Scheduling strategies for efficient ETL execution

Extract-transform-load (ETL) workflows model the population of enterprise data warehouses with information gathered from a large variety of heterogeneous data sources. ETL workflows are complex design structures that run under strict performance requirements and their optimization is crucial for satisfying business objectives. In this paper, we deal with the problem of scheduling the execution of ETL activities (a.k.a. transformations, tasks, operations), with the goal of minimizing ETL execution time and allocated memory. We investigate the effects of four scheduling policies on different flow structures and configurations and experimentally show that the use of different scheduling policies may improve ETL performance in terms of memory consumption and execution time. First, we examine a simple, fair scheduling policy. Then, we study the pros and cons of two other policies: the first opts for emptying the largest input queue of the flow and the second for activating the operation (a.k.a. activity) with the maximum tuple consumption rate. Finally, we examine a fourth policy that combines the advantages of the latter two in synergy with flow parallelization.

Metrics for the Prediction of Evolution Impact in ETL Ecosystems: A Case Study

The Extract-Transform-Load (ETL) flows are essential for the success of a data warehouse and the business intelligence and decision support mechanisms that are attached to it. During both the ETL design phase and the entire ETL lifecycle, the ETL architect needs to design and improve an ETL design in a way that satisfies both performance and correctness guarantees and often, she has to choose among various alternative designs. In this paper, we focus on ways to predict the maintenance effort of ETL workflows and we explore techniques for assessing the quality of ETL designs under the prism of evolution. We focus on a set of graph-theoretic metrics for the prediction of evolution impact and we investigate their fit into real-world ETL scenarios. We present our experimental findings and describe the lessons we learned working on real-world cases.