Extract-Transform-Load Processes Research Articles

Tracking provenance in clinical data warehouses for quality management

IntroductionData provenance, which documents the origin, history, and transformations of data, can enhance the reproducibility of processing workflows and help to address errors and quality issues. In this work, we focus on tracking and utilizing provenance information as part of quality management in Extract-Transform-Load (ETL) processes used to build clinical data warehouses. MethodsWe designed and implemented a framework that automatically tracks how data flows through an ETL process and detects errors and quality problems during processing. This information is then reported against an Application Programming Interface (API) that stores the issues along with contextual information on their location within the data being transformed and the overall workflow. We further designed a dashboard that supports health data engineers with inspecting the encountered issues and tracing them back to their root causes. ResultsThe framework was implemented in Java using the Spring Framework and integrated into ETL processes for Informatics for Integrating Biology and the Bedside (i2b2). The dashboard was realized using Grafana. We evaluated our approach on three different ETL processes for real-world datasets used to integrate them into our i2b2 clinical data warehouse. Using the provenance dashboard, we were able to identify frequent error patterns and link them to specific data points from the sources as well as ETL process steps. Provenance tracking increased the execution times of loading processes with an impact depending on the number of identified issues. ConclusionsProvenance tracking can be a valuable tool for implementing continuous quality management for ETL processes. Relevant information can be collected from existing ETL workloads using dedicated APIs and visualized through dashboards, which support the identification of frequent patterns of problems together with their root causes, providing valuable information for improvements.

MeDaX: A Knowledge Graph on FHIR.

In Germany, the standard format for exchange of clinical care data for research is HL7 FHIR. Graph databases (GDBs), well suited for integrating complex and heterogeneous data from diverse sources, are currently gaining traction in the medical field. They provide a versatile framework for data analysis which is generally challenging for raw FHIR-formatted data. For generation of a knowledge graph (KG) for clinical research data, we tested different extract-transform-load (ETL) approaches to convert FHIR into graph format. We designed a generalised ETL process and implemented a prototypic pipeline for automated KG creation and ontological structuring. The MeDaX-KG prototype is built from synthetic patient data and currently serves internal testing purposes. The presented approach is easy to customise to expand to other data types and formats.

How to customize common data models for rare diseases: an OMOP-based implementation and lessons learned

BackgroundGiven the geographical sparsity of Rare Diseases (RDs), assembling a cohort is often a challenging task. Common data models (CDM) can harmonize disparate sources of data that can be the basis of decision support systems and artificial intelligence-based studies, leading to new insights in the field. This work is sought to support the design of large-scale multi-center studies for rare diseases.MethodsIn an interdisciplinary group, we derived a list of elements of RDs in three medical domains (endocrinology, gastroenterology, and pneumonology) according to specialist knowledge and clinical guidelines in an iterative process. We then defined a RDs data structure that matched all our data elements and built Extract, Transform, Load (ETL) processes to transfer the structure to a joint CDM. To ensure interoperability of our developed CDM and its subsequent usage for further RDs domains, we ultimately mapped it to Observational Medical Outcomes Partnership (OMOP) CDM. We then included a fourth domain, hematology, as a proof-of-concept and mapped an acute myeloid leukemia (AML) dataset to the developed CDM.ResultsWe have developed an OMOP-based rare diseases common data model (RD-CDM) using data elements from the three domains (endocrinology, gastroenterology, and pneumonology) and tested the CDM using data from the hematology domain. The total study cohort included 61,697 patients. After aligning our modules with those of Medical Informatics Initiative (MII) Core Dataset (CDS) modules, we leveraged its ETL process. This facilitated the seamless transfer of demographic information, diagnoses, procedures, laboratory results, and medication modules from our RD-CDM to the OMOP. For the phenotypes and genotypes, we developed a second ETL process. We finally derived lessons learned for customizing our RD-CDM for different RDs.DiscussionThis work can serve as a blueprint for other domains as its modularized structure could be extended towards novel data types. An interdisciplinary group of stakeholders that are actively supporting the project's progress is necessary to reach a comprehensive CDM.ConclusionThe customized data structure related to our RD-CDM can be used to perform multi-center studies to test data-driven hypotheses on a larger scale and take advantage of the analytical tools offered by the OHDSI community.

An efficient hybrid optimization of ETL process in data warehouse of cloud architecture

In big data, analysis data is collected from different sources in various formats, transforming into the aspect of cleansing the data, customization, and loading it into a Data Warehouse. Extracting data in other formats and transforming it to the required format requires transformation algorithms. This transformation stage has redundancy issues and is stored across any location in the data warehouse, which increases computation costs. The main issues in big data ETL are handling high-dimensional data and maintaining similar data for effective data warehouse usage. Therefore, Extract, Transform, Load (ETL) plays a vital role in extracting meaningful information from the data warehouse and trying to retain the users. This paper proposes hybrid optimization of Swarm Intelligence with a tabu search algorithm for handling big data in a cloud-based architecture-based ETL process. This proposed work overcomes many issues related to complex data storage and retrieval in the data warehouse. Swarm Intelligence algorithms can overcome problems like high dimensional data, dynamical change of huge data and cost optimization in the transformation stage. In this work for the swarm intelligence algorithm, a Grey-Wolf Optimizer (GWO) is implemented to reduce the high dimensionality of data. Tabu Search (TS) is used for clustering the relevant data as a group. Clustering means the segregation of relevant data accurately from the data warehouse. The cluster size in the ETL process can be optimized by the proposed work of (GWO-TS). Therefore, the huge data in the warehouse can be processed within an expected latency.

A Data Warehouse Architecture Proposal and ETL Analysis, Case study of an Albanian Banking System

The increasing phenomenon of information overload is a direct result of the ongoing trend to reduce the cost of data distribution while the development of data processing platforms is not fast enough. Therefore, sending packets of data is not a big issue, but processing an increasing amount of data is a big challenge.
 Data Warehouse is a concept born in the late ‘80s and has been improved decade after decade with the growth of technology. Despite many application areas, the data warehouse concept is still new and unknown to Albanian businesses and companies with a large amount of data to store and analyze.
 This paper aims to provide an in-depth analysis of the concept of a data warehouse, the different architectures applied, and the Extract, Transform Load (ETL) process. The ETL process as a concept consists of extracting the data from a source, transforming them into different formats or dimensions, and then loading them somewhere to be checked for analysis or other purposes.
 In this paper, we can find different definitions of the data warehouse, where it can be implemented, the different architectures according to the need of the users, and how the ETL process works. The last chapter implements a data warehouse for the banking system seen from a Management Information Systems perspective followed by an application of a step-by-step ETL procedure.

Overcoming traditional ETL systems architectural problems using a service‐oriented approach

AbstractDeveloping analytical systems imposes several challenges related not only to the amount and heterogeneity of the involved data but also to the constant need to readapt and evolve to overcome new business challenges. Data are a determinant factor in the success of analytical and decision‐making applications, being its nature, availability, and quality, crucial aspects for planning and structuring populating analytical systems. Today's users are more demanding, requiring adaptable and flexible analytical applications, which impose serious challenges on extract‐transform‐load (ETL) systems design and development for ensuring flexible and robust data populating services, operating 24/7, and managing and processing large volumes of data. Thus, we should design and implement ETL processes using innovative and up‐to‐date approaches, having real application evidence. In this paper, we present a service‐oriented implementation for ETL design and development. We mapped and implemented some of the most conventional ETL processes in a service‐oriented architecture, to demonstrate the application and benefits that this kind of approach will provide to ETL systems project development.

Context-aware Big Data Quality Assessment: A Scoping Review

The term data quality refers to measuring the fitness of data regarding the intended usage. Poor data quality leads to inadequate, inconsistent, and erroneous decisions that could escalate the computational cost, cause a decline in profits, and cause customer churn. Thus, data quality is crucial for researchers and industry practitioners. Different factors drive the assessment of data quality. Data context is deemed one of the key factors due to the contextual diversity of real-world use cases of various entities such as people and organizations. Data used in a specific context (e.g., an organization policy) may need to be more efficacious for another context. Hence, implementing a data quality assessment solution in different contexts is challenging. Traditional technologies for data quality assessment reached the pinnacle of maturity. Existing solutions can solve most of the quality issues. The data context in these solutions is defined as validation rules applied within the ETL (extract, transform, load) process, i.e., the data warehousing process. In contrast to traditional data quality management, it is impossible to specify all the data semantics beforehand for big data. We need context-aware data quality rules to detect semantic errors in a massive amount of heterogeneous data generated at high speed. While many researchers tackle the quality issues of big data, they define the data context from a specific standpoint. Although data quality is a longstanding research issue in academia and industries, it remains an open issue, especially with the advent of big data, which has fostered the challenge of data quality assessment more than ever. This article provides a scoping review to study the existing context-aware data quality assessment solutions, starting with the existing big data quality solutions in general and then covering context-aware solutions. The strength and weaknesses of such solutions are outlined and discussed. The survey showed that none of the existing data quality assessment solutions could guarantee context awareness with the ability to handle big data. Notably, each solution dealt only with a partial view of the context. We compared the existing quality models and solutions to reach a comprehensive view covering the aspects of context awareness when assessing data quality. This led us to a set of recommendations framed in a methodological framework shaping the design and implementation of any context-aware data quality service for big data. Open challenges are then identified and discussed.

An Extract-Transform-Load Process Design for the Incremental Loading of German Real-World Data Based on FHIR and OMOP CDM: Algorithm Development and Validation.

In the Medical Informatics in Research and Care in University Medicine (MIRACUM) consortium, an IT-based clinical trial recruitment support system was developed based on the Observational Medical Outcomes Partnership (OMOP) Common Data Model (CDM). Currently, OMOP CDM is populated with German Fast Healthcare Interoperability Resources (FHIR) using an Extract-Transform-Load (ETL) process, which was designed as a bulk load. However, the computational effort that comes with an everyday full load is not efficient for daily recruitment. The aim of this study is to extend our existing ETL process with the option of incremental loading to efficiently support daily updated data. Based on our existing bulk ETL process, we performed an analysis to determine the requirements of incremental loading. Furthermore, a literature review was conducted to identify adaptable approaches. Based on this, we implemented three methods to integrate incremental loading into our ETL process. Lastly, a test suite was defined to evaluate the incremental loading for data correctness and performance compared to bulk loading. The resulting ETL process supports bulk and incremental loading. Performance tests show that the incremental load took 87.5% less execution time than the bulk load (2.12 min compared to 17.07 min) related to changes of 1 day, while no data differences occurred in OMOP CDM. Since incremental loading is more efficient than a daily bulk load and both loading options result in the same amount of data, we recommend using bulk load for an initial load and switching to incremental load for daily updates. The resulting incremental ETL logic can be applied internationally since it is not restricted to German FHIR profiles.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

Enhancing ETL Performance Using Delta Lake in Data Analytics Solutions

In the rapidly evolving field of data analytics, the performance of Extract, Transform, Load (ETL) processes is crucial for effective data management and insight generation. This study explores the integration of Delta Lake within ETL frameworks to enhance performance and reliability. Delta Lake, an open-source storage layer, facilitates ACID transactions, scalable metadata handling, and unifies batch and streaming data processing, addressing common challenges associated with traditional ETL processes. By leveraging Delta Lake’s capabilities, organizations can optimize data ingestion and transformation workflows, resulting in reduced latency and improved data quality. This research employs a comparative analysis of traditional ETL methods and those enhanced with Delta Lake, measuring key performance indicators such as processing speed, resource utilization, and error rates. Case studies illustrate the practical applications of Delta Lake in diverse industries, demonstrating its potential to streamline ETL operations while ensuring data consistency and reliability. Additionally, the study discusses the implications of adopting Delta Lake for businesses seeking to harness large-scale data for analytics and decision-making. Ultimately, this investigation highlights the transformative impact of Delta Lake on ETL performance, advocating for its adoption as a standard practice in data analytics solutions. By enhancing ETL processes, organizations can derive actionable insights more efficiently, driving innovation and competitive advantage in the data-driven landscape.

AN IMPACT ANALYSIS OF EXTRACT TRANSFORM LOAD PROCESS FOR MAINTAINING THE SYSTEM OF DATA WAREHOUSE

The Extract Transform Load (ETL) process involves extracting data from database sources, transforming them into a suitable form for research analysis, and then loading it into a data warehouse (DW) to support effective decision-support implementation. To maintain the target of the data warehouse, several issues are discussed in the DW life cycle, ETL processes, and impact analysis for maintaining the DW. This research focuses on the issue high frequency of data changes makes ETL processes difficult to propagate the data changes and to maintain the changes history in the DW life cycle. Therefore, the focus issues of this research are identifying factors for frequent data changes that occur in data sources and the DW structure that need to be modified by performing impact analysis. The general factors of data changes in DW were identified by questionnaire from 41 respondents, and the factor of impact analysis was evaluated using the statistic test method called Kruskal Wallis H Test to make a comparison between the impact analysis factor and the category of users. The aims of this research are to perform the impact analysis of the DW process in order to maintain the DW system and to achieve DW with the right requirements definition. In addition, this will help users working with the DW to understand the elements of impact analysis in DW, especially on how to ensure the DW process runs efficiently and successfully. Therefore, the database administrators, data analysts, and DW developers can utilize these research findings as a guideline to deal with the data changes in the DW process.

Big Data in Laboratory Medicine—FAIR Quality for AI?

Laboratory medicine is a digital science. Every large hospital produces a wealth of data each day—from simple numerical results from, e.g., sodium measurements to highly complex output of “-omics” analyses, as well as quality control results and metadata. Processing, connecting, storing, and ordering extensive parts of these individual data requires Big Data techniques. Whereas novel technologies such as artificial intelligence and machine learning have exciting application for the augmentation of laboratory medicine, the Big Data concept remains fundamental for any sophisticated data analysis in large databases. To make laboratory medicine data optimally usable for clinical and research purposes, they need to be FAIR: findable, accessible, interoperable, and reusable. This can be achieved, for example, by automated recording, connection of devices, efficient ETL (Extract, Transform, Load) processes, careful data governance, and modern data security solutions. Enriched with clinical data, laboratory medicine data allow a gain in pathophysiological insights, can improve patient care, or can be used to develop reference intervals for diagnostic purposes. Nevertheless, Big Data in laboratory medicine do not come without challenges: the growing number of analyses and data derived from them is a demanding task to be taken care of. Laboratory medicine experts are and will be needed to drive this development, take an active role in the ongoing digitalization, and provide guidance for their clinical colleagues engaging with the laboratory data in research.

An embedding driven approach to automatically detect identifiers and references in document stores

NoSQL stores have become ubiquitous since they offer a new cost-effective and schema-free system. Although NoSQL systems are widely accepted today, Business Intelligence & Analytics (BI&A) wields relational data sources. Exploiting schema-free data for analytical purposes is a challenge since it requires reviewing all the BI&A phases, particularly the Extract-Transform-Load (ETL) process, to fit big data sources as document stores. In the ETL process, the join of several collections, with a lack of explicitly known join fields is a significant dare. Detecting these fields manually is time and effort-consuming and infeasible in large-scale datasets. In this paper, we study the problem of discovering join fields automatically. We introduce an algorithm that aims to automatically detect both identifiers and references on several document stores. The modus operandi of our approach underscores three core stages: (i) global schema extraction; (ii) discovery of candidate identifiers; and (iii) identifying candidate pairs of identifier and reference fields. We use scoring features and pruning rules to discover true candidate identifiers from many initial ones efficiently. To find candidate pairs between several document stores, we put into practice node2vec as a graph embedding technique, which yields significant advantages while using syntactic and semantic similarity measures for pruning pointless candidates. Finally, we report our experimental findings that show encouraging results.

Utilization Extract, Transform, Load For Developing Data Warehouse In Education Using Pentaho Data Integration

The utilization of data warehouses in various fields is an absolute necessity. A data warehouse is a database that contains large amounts of data that aims to help organizations, fields, and institutions specifically for decision making. Data warehouses can produce important information in the future. Loading data from various sources and processed through an ETL (Extract, Transform, Load) process that displays data consistently is the basis for creating a data warehouse architecture. The development of a data warehouse in education will provide significant benefits for the progress of education. Integration of data and processing results stored in the data warehouse can be the basis for evaluating better planning. Development of data warehouse adopt the multidimensional modelling method which consists of four stages: select the business process, declare the grain, select dimensions, and identify facts. This stage produces a data warehouse architecture and influences and contributes to the advanced information technology in education.

Extract transform load process in banking reporting system

Banks must maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator. In Indonesia, they reported daily, monthly, or quarterly online through a paper template prepared by the Financial Services Authority (OJK). This reporting must accurate and on time or be subject to penalties. For banks that are still processing LCR semi-automatically, this reporting system is an obstacle that they continue to face and resolve. This article discusses the automation process method developed using the concept of Extract Transform Load (ETL), with a waterfall software development model, so that daily reports are generated automatically. This article proposed a methodology to anticipate problems in integrating banking with regulators by applying one of the Basel III frameworks, primarily based on Indonesia's case studies. The finding of the research is a method for the LCR report process through ETL. The proposed ETL method in this research had proven used success in processing LCR in the banking industry. This method is a solution and recommendation for banks in making reports based on Basel III to complete LCR reporting through the ETL method.•Maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator in Banking .•Automation process method developed using the concept of Extract Transform Load (ETL).•Recommendation for Banks to complete LCR reporting through the ETL method).

Personel İş Zekası Sistemi ve Veri Madenciliği ile Personel Memnuniyetinin Ölçülmesi

Veri madenciliği uygulamaları ve iş zekası sistemleri verileri anlamlandırarak bilgiye dönüştürülmesinde ve kuruluşlara karar verme süreçlerinde yardımcı olmak için kullanılır. Çalışmada bir devlet kurumunun personel verileri üzerinde iş zekası ve veri madenciliği süreçleri gerçekleştirilerek verilerin yönetimini sağlamak ve dinamik analizler ile stratejik öngörülerin yapılabilmesi amaçlanmıştır. İş zekası süreçlerinin uygulanmasında Microsoft iş zekası araçlarından SSIS (SQL Server Integration Service), SSAS (SQL Server Analysis Service) ve Power BI kullanılmıştır. Öncelikle veri yapısı modellendirilmiş olup ETL (Extract-Transform-Load) sürecinden geçirilerek anlamlı hale getirilen veri, veri ambarına SSIS programı ile aktarılmıştır. Daha sonra SSAS programı ile OLAP veri küpü oluşturulmuştur. Son olarak da OLAP küpü veri kaynağı olarak belirlenip zengin görsel araçlara sahip olan ve veri analizini çok daha etkili hale getiren Power BI iş zekası aracı kullanılarak çeşitli görsellerle veri analizi gerçekleştirilmiştir. Böylece, karar vericilerin veri analizini etkin bir şekilde yapabilecekleri bir karar destek sistemi geliştirilmiştir. Çalışmada kümeleme analizi yöntemlerinden biri olan K-Means algoritması ve birliktelik kurallarından kurallarından Apriori algoritması ile bir devlet kurumundaki İç Kontrol Sistemi anketi verileri kullanılarak personel memnuniyet analizi yapılmıştır. Veri madenciliği aracı olarak ise içerisinde bulundurduğu bir çok algoritma sayesinde verilerden bilgi çıkarımı yapılabilmesine olanak sağlayan ve açık kaynak kodlu bir araç olması sebebi ile WEKA kullanılmıştır. Kümeleme analizi sonucunda oluşan farklı personel grupları detaylı incelenerek bu gruplara nasıl yaklaşılması gerektiği ve nasıl hitap edilmesi gerektiği belirlenmiştir. Her grubun memnuniyet durumunun farklılaşan noktaları belirlenerek, stratejiler ve uygulama faaliyetleri bu grubun ihtiyacına göre planlanmıştır. Apriori algoritması, personelin İç Kontrol Sistemi anket sorularına vermiş olduğu cevaplar arasında anlamlı ilişkileri bulmak, bu ilişkilerden faydalanarak personelin çalıştığı birimden memnuniyetini karşılaştırarak anlamlı sonuçlar elde etmek ve çalışılan birimden memnun olmama nedenlerini keşfetmek için kullanılmıştır. Böylece İç Kontrol Sistemi anketi verilerinden anlamlı ve yararlı bilgiler elde etmek ve bu bilgiler ışığında personel karar destek planlama faaliyetlerinde destek olmak amaçlanmıştır.

PENERAPAN TEKNOLOGI DATAWAREHOUSE NOSQL DAN BUSINESS INTELLIGENCE UNTUK ANALISA TRANSAKSI PENJUALAN

Business process are increasingly developing, causing data generated will be even greater. The application of datawarehouse technology and MongoDB as one of the NoSQL technologies can solve large historical data processing problems. In addition, business intelligence technology that is able to visualize data in into graphics and tables will make it easier for stakeholders to analyze sales transactions. The process for transforming transaction data into a datawarehouse is the Extract Transform Load (ETL) process. The data generated from the ETL process is quality data and is really needed for analysis. Black box testing results and data validity indicate the system can be accepted and can produce the right information for decision support.

From Big Data to business analytics: The case study of churn prediction

The success of companies hugely depends on how well they can analyze the available data and extract meaningful knowledge. The Extract-Transform-Load (ETL) process is instrumental in accomplishing these goals, but requires significant effort, especially for Big Data. Previous works have failed to formalize, integrate, and evaluate the ETL process for Big Data problems in a scalable and cost-effective way. In this paper, we propose a cloud-based ETL framework for data fusion and aggregation from a variety of sources. Next, we define three scenarios regarding data aggregation during ETL: (i) ETL with no aggregation; (ii) aggregation based on predefined columns or time intervals; and (iii) aggregation within single user sessions spanning over arbitrary time intervals. The third scenario is very valuable in the context of feature engineering, making it possible to define features as “the time since the last occurrence of event X”. The scalability was evaluated on Amazon AWS Hadoop clusters by processing user logs collected with Kinesis streams with datasets ranging from 30 GB to 2.6 TB. The business value of the architecture was demonstrated with applications in churn prediction, service-outage prediction, fraud detection, and more generally — decision support and recommendation systems. In the churn prediction case, we showed that over 98% of churners could be detected, while identifying the individual reason. This allowed support and sales teams to perform targeted retention measures.

ARRANGEMENT AND MODULATION OF ETL PROCESS IN THE STORAGE


 
 
 
 Data warehouse (DW) is the basis of systems for operational data analysis (OLAP-Online Analytical Processing). Data extracted from different sources transforms and load in DW. Proper organization of this process, which is called ETL (Extract, Transform, Load) has important significance in creation of DW and analytical data processing. Forms of organization, methods of realization and modeling of ETL processes are considered in this paper.
 
 
 

Extract transform load (ETL) process in distributed database academic data warehouse

While a data warehouse is designed to support the decision-making function, the most time-consuming partis the Extract Transform Load (ETL) process. Case in Academic Data Warehouse, when data source came from thefaculty’s distributed database, although having a typical database but become not easier to integrate. This paperpresents how to an ETL process in distributed database academic data warehouse. Following Data Flow Threadprocess in the data staging area, a deep analysis performed for identifying all tables in each data sources, includingcontent profiling. Then the cleaning, confirming, and data delivery steps pour the different data source into the datawarehouse (DW). Since DW development using bottom-up Kimball’s multidimensional approach, we found the threetypes of extraction activities from data source table: merge, merge-union, and union. Result for cleaning andconforming step set by creating conform dimension on data source analysis, refinement, and hierarchy structure. Thefinal of the ETL step is loading it into integrating dimension and fact tables by a generation of a surrogate key. Thoseprocesses are running gradually from each distributed database data sources until it incorporated. This technicalactivity in distributed database ETL process generally can be adopted widely in other industries which designer musthave advance knowledge to structure and content of data source.