In data warehousing, Extract-Transform-Load (ETL) extracts the data from data sources into a central data warehouse regularly for the support of business decision-makings. The data from transaction processing systems are featured with the high frequent changes of insertion, update, and deletion. It is challenging for ETL to propagate the changes to the data warehouse, and maintain the change history. Moreover, ETL jobs typically run in a sequential order when processing the data with dependencies, which is not optimal, e.g., when processing early-arriving data. In this paper, we propose a two-level data staging ETL for handling transaction data. The proposed method detects the changes of the data from transactional processing systems, identifies the corresponding operation codes for the changes, and uses two staging databases to facilitate the data processing in an ETL process. The proposed ETL provides the “onestop" method for fast-changing, slowly-changing and early-arriving data processing.
Optimizing ETL is a time-consuming process, but it is essential to ensure ETL jobs to complete within specific time frames. For ETL optimization, Simitsis propose a theoretical framework @cite_27 @cite_6 @cite_25 , which formalizes ETL state spaces into a directed acyclic graph (DAG), then searches the best execution plan with regards to the time of the state spaces. Tziovara propose the approach of optimizing ETL based on an input logical ETL template @cite_8 . In @cite_12 , Li and Zhan analyze the task dependencies in an ETL workflow, and optimize ETL workflow by applying the parallelization technique to the tasks without dependency. Behrend and J " o rg use rule-based approach to optimize ETL flows @cite_20 , where the rules are generated based on the algebraic equivalences. Our approach is an ETL method, but can also be used to optimize the ETL in solving the early-arriving data problem, in which no dependencies need to be considered.
@cite_8 @cite_6 @cite_27 @cite_20 @cite_25 @cite_12
2020829916 2149877754 2096347930 2003374655 2158643472 2184279421
In this paper, we deal with the problem of determining the best possible physical implementation of an ETL workflow, given its logical-level description and an appropriate cost model as inputs. We formulate the problem as a state-space problem and provide a suitable solution for this task. We further extend this technique by intentionally introducing sorter activities in the workflow in order to search for alternative physical implementations with lower cost. We experimentally assess our method based on a principled organization of test suites. 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. Usually, these processes must be completed in a certain time window; thus, it is necessary to optimize their execution time. In this paper, we delve 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 algorithms towards the minimization of the execution cost of an ETL workflow. 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. ETL jobs are used to integrate data from distributed and heterogeneous sources into a data warehouse. A well-known challenge in this context is the development of incremental ETL jobs for efficiently maintaining warehouse data in the presence of source data updates. In this paper, we present a new transformation-based approach to automatically derive incremental ETL jobs. To this end, we consider a simplification of the underlying update propagation process based on the computation of so-called safe updates instead of true ones. Additionally, we identify the limitations of already proposed incremental solutions, which are cured by employing Magic Sets leading to dramatic performance gains. As business intelligence becomes increasingly essential for organizations and as it evolves from strategic to operational, the complexity of Extract-Transform-Load (ETL) processes grows. In consequence, ETL engagements have become very time consuming, labor intensive, and costly. At the same time, additional requirements besides functionality and performance need to be considered in the design of ETL processes. In particular, the design quality needs to be determined by an intricate combination of different metrics like reliability, maintenance, scalability, and others. Unfortunately, there are no methodologies, modeling languages or tools to support ETL design in a systematic, formal way for achieving these quality requirements. The current practice handles them with ad-hoc approaches only based on designers' experience. This results in either poor designs that do not meet the quality objectives or costly engagements that require several iterations to meet them. A fundamental shift that uses automation in the ETL design task is the only way to reduce the cost of these engagements while obtaining optimal designs. Towards this goal, we present a novel approach to ETL design that incorporates a suite of quality metrics, termed QoX, at all stages of the design process. We discuss the challenges and tradeoffs among QoX metrics and illustrate their impact on alternative designs. Approaches to shorten workflow execution time have been discussed in many area of computer engineering such as parallel and distributed systems, a computer circuit, and PERT chart for project management. To optimize workflow model structure of workflow, an approach with corresponding algorithms is proposed to cut timed critical path of workflow schema, which has the longest average execution time path from the start activity to the end activity. Through systematically analyzing the dependency relationships between tasks at build-time, traditional optimization method of critical path is improved through adding selective and parallel control structures into workflow schemas. Data dependency rules are converted to control dependency rules according to semantic rules mined. Further more, consistency between tasks is guaranteed. Finally, to explain validity of the algorithm proposed, an experiment is provided to compare optimized model with original using critical path identification algorithm. (Nature and Science. 2005;3(2):65-74).