ETL Process Research Articles

The Growing Trend of Cloud-Based Data Integration and Warehousing

This article examines the growing trend of cloud-based data integration and warehousing solutions in response to the exponential growth of data generation and the need for scalable, flexible analytics capabilities. It explores the key drivers behind cloud adoption, including data volume increases, demand for real-time insights, cost efficiencies, and advanced analytics requirements. The benefits of cloud solutions are discussed, such as scalability, cost-effectiveness, agility, and enhanced analytical capabilities. The article also addresses challenges like data security, vendor lock-in, and performance issues, providing best practices for successful implementation. Emerging trends in automated ETL processes, machine learning integration, and multi-cloud approaches are highlighted, showcasing the evolving landscape of cloud-based data management. Through analysis of market projections, case studies, and industry statistics, this comprehensive overview equips organizations with the knowledge to leverage cloud technologies to transform their data management capabilities and gain competitive advantages in the digital age.

Machine Learning Algorithms for Predictive Quality Assurance in Healthcare in DW ETL Processes

Machine Learning Algorithms for Predictive Quality Assurance in Healthcare in DW ETL Processes

Implementation of a common data model in Health Data Research Network Canada: Lessons learned

BackgroundThe Observational Medical Outcomes Partnership (OMOP) Common Data Model (CDM) is a standardized structure for observational data that enables collaborative health research. Health Data Research Network (HDRN Canada) is conducting a pilot study to implement this standard at partner data linkage centers. ObjectiveOur objective is to describe the process used to implement the OMOP CDM, methodological and technical challenges, and the approach to evaluate CDM implementation at multiple sites. ApproachCDM working group members include technical staff from data linkage centers in four provinces and a national data coordinating center, HDRN Canada operational and executive leads, and OMOP CDM consultants. The evaluation aims to collect quantitative and qualitative information on Extract, Transform, and Load (ETL) processes, performance of the CDM in a multi-site observational case study, and data quality. ResultsInitial steps have included collaborative learning about the CDM structure, importing open-source tools and licensed coding standards (i.e., Systematized Nomenclature of Medicine-Clinical Terms [SNOMED-CT], RxNorm) at data linkage centres, and exploring the technical requirements for developing ETL processes and hosting OMOP data. Creation of select OMOP tables has been accompanied by discussions about models for mapping clinical and prescription drug coding standards. A scoping review of evaluation strategies is underway. Conclusions and ImplicationsThe benefits of standardizing health data for multi-site research and supporting the production of findable, accessible, interoperable and reusable (FAIR) national data underscores the importance of HDRN Canada embarking on this OMOP CDM implementation project. The lessons learned will benefit other multi-site data standardization initiatives.

SparkDWM: a scalable design of a Data Washing Machine using Apache Spark.

Data volume has been one of the fast-growing assets of most real-world applications. This increases the rate of human errors such as duplication of records, misspellings, and erroneous transpositions, among other data quality issues. Entity Resolution is an ETL process that aims to resolve data inconsistencies by ensuring entities are referring to the same real-world objects. One of the main challenges of most traditional Entity Resolution systems is ensuring their scalability to meet the rising data needs. This research aims to refactor a working proof-of-concept entity resolution system called the Data Washing Machine to be highly scalable using Apache Spark distributed data processing framework. We solve the single-threaded design problem of the legacy Data Washing Machine by using PySpark's Resilient Distributed Dataset and improve the Data Washing Machine design to use intrinsic metadata information from references. We prove that our systems achieve the same results as the legacy Data Washing Machine using 18 synthetically generated datasets. We also test the scalability of our system using a variety of real-world benchmark ER datasets from a few thousand to millions. Our experimental results show that our proposed system performs better than a MapReduce-based Data Washing Machine. We also compared our system with Famer and concluded that our system can find more clusters when given optimal starting parameters for clustering.

Fostering Excellence through Mentorship: A Study of Professional Growth in Data Engineering

This article examines the critical role of mentorship in developing data engineering skills and fostering professional growth within the field. Through a comprehensive analysis of mentorship programs in data engineering organizations, we investigate the impact on both technical proficiency and soft skill development. Our findings reveal that structured mentorship significantly enhances mentees' capabilities in areas such as programming, data modeling, and ETL processes, while also improving their communication and project management skills. The article highlights the mutual benefits for mentors and mentees, including accelerated learning curves, increased job satisfaction, and improved retention rates. We identify key elements of successful mentorship programs, including clear goal-setting, regular feedback mechanisms, and alignment with organizational objectives. The article also addresses common challenges in implementing mentorship initiatives and proposes best practices for overcoming these obstacles. Our results underscore the importance of mentorship as a strategic tool for talent development in the rapidly evolving field of data engineering, providing valuable insights for both practitioners and organizations seeking to cultivate expertise and drive innovation in this domain

Overview on Data Ingestion and Schema Matching

This overview traced the evolution of data management, transitioning from traditional ETL processes to addressing contemporary challenges in Big Data, with a particular emphasis on data ingestion and schema matching. It explored the classification of data ingestion into batch, real-time, and hybrid processing, underscoring the challenges associated with data quality and heterogeneity. Central to the discussion was the role of schema mapping in data alignment, proving indispensable for linking diverse data sources. Recent advancements, notably the adoption of machine learning techniques, were significantly reshaping the landscape. The paper also addressed current challenges, including the integration of new technologies and the necessity for effective schema matching solutions, highlighting the continuously evolving nature of schema matching in the context of Big Data

Mastering Data Product Development: Strategies, Architectures, and Best Practices

Abstract: The importance of data products for organizations cannot be overstated, as they enable valuable insights and informed decision-making. This article offers a thorough guide to constructing data products, with a specific emphasis on data warehouse architecture and data product management. Key concepts in data product management are explored, such as treating data as a product, understanding the data product lifecycle, and defining roles and responsibilities. The article explores the key elements of a data warehouse, including data integration and ETL processes, data modeling, and storage and retrieval techniques. This approach outlines a systematic process for developing data products, covering everything from identifying opportunities and defining requirements to design, implementation, testing, deployment, and maintenance. Emphasizing the significance of data governance, quality assurance, security, privacy, and regulatory compliance. Additionally, we delve into performance metrics, monitoring, and strategies for continuous improvement of data products. The article showcases case studies, such as Our World in Data [1], to demonstrate real-world applications and best practices. Finally, we will explore future trends, challenges, and opportunities in data p

Data Cleaning: Challenges and Existing Solutions

We classify data quality problems that are addressed by data cleaning and provide an overview of the main solution approaches. Data cleaning is especially required when integrating heterogeneous data sources and should be addressed together with schema-related data transformations. In data warehouses, data cleaning is a major part of the so-called ETL process. We also discuss current tool support for data cleaning.

76 Lessons learned during implementation of OMOP common data model across multiple health systems

OBJECTIVES/GOALS: Adoption of the Observational Medical Outcomes Partnership (OMOP) common data model promises to transform large-scale observational health research. However, there are diverse challenges for operationalizing OMOP in terms of interoperability and technical skills among coordinating centers throughout the US. METHODS/STUDY POPULATION: A team from the Critical Path Institute (C-Path) collaborated with the informatics team members at Johns Hopkins to provide technical support to participating sites as part of the Extract, Transform, and Load (ETL) process linking existing concepts to OMOP concepts. Health systems met regularly via teleconference to review challenges and progress in ETL process. Sites were responsible for performing the local ETL process with assistance and securely provisioning de-identified data as part of the CURE ID program. RESULTS/ANTICIPATED RESULTS: More than twenty health systems participated in the CURE ID effort.Laboratory measures, basic demographics, disease diagnoses and problem list were more easily mapped to OMOP concepts by CURE ID partner institutions. Outcomes, social determinants of health, medical devices, and specific treatments were less easily characterized as part of the project. Concepts within the medical record presented very different technical challenges in terms of representation. There is a lack of standardization in OMOP implementation even among centers using the same electronic medical health record. Readiness to adopt OMOP varied across the institutions who participated. Health systems achieved variable level of coverage using OMOP medical concepts as part of the initiative. DISCUSSION/SIGNIFICANCE: Adoption of OMOP involves local stakeholder knowledge and implementation. Variable complexity of health concepts contributed to variable coverage. Documentation and support require extensive time and effort. Open-source software can be technically challenging. Interoperability of secure data systems presents unique problems.

Dashboard Design for New Employee Candidate Program at XYZ Bank Using Tabelau

Bank XYZ is one of the government-owned companies engaged in banking and is one of the largest banks in Indonesia. Bank XYZ has a division called XYZ Corpu which aims to support the company's business operations by developing employee competencies. One of the training programs conducted at XYZ Corpu is the ODP (Officer Development Program). ODP participants are prospective XYZ Bank employees who enter through several selection stages and then carry out training for 12 months which applies a knockout system if the participants are not competent. ODP participants will undergo 12 months of education and training with several stages of the journey. To make it easier to get important information such as the distribution of participants based on the location of the placement division, placement city, distribution of journey end dates, and journey stages. So this research designs a dashboard to make it easier for management to get important information for decision making. This research uses Pentaho tools to perform ETL processes and Tableau tools to create dashboards. Based on the design made, the dashboard user/manager can anticipate or detect early delays in the process of making rundowns for the next journey stage through this dashboard. Based on the OLAP design of this research, the ODP distribution fact table and dimension table consisting of location, journey, city, start date, end date, batch, gender, university and major are formed. Based on the design made, dashboard users/managers can anticipate or detect early delays in the process of making rundowns for the next journey stage through this dashboard. With this dashboard, it can find out the most universities and study programs for more precise decision making. The dashboard created in this study has a data filtering feature that is useful for searching for certain data more quickly.

Abstract 3547: The ISB Cancer Gateway in the Cloud (ISB-CGC): Access, explore and analyze large-scale cancer data through the Google Cloud

Abstract Rapid growth of cancer data in recent decades has made data discovery and wrangling difficult for the average cancer research lab. Our mission at the ISB Cancer Gateway in the Cloud (ISB-CGC), part of the NCI’s Cancer Research Data Commons ecosystem, is to democratize access to large cancer datasets. Funded by the NCI, we have performed ETL processes on data from GDC and PDC projects such as TCGA, TARGET, and CPTAC. We generated hundreds of BigQuery tables containing data such as mutations, gene expression, and protein abundance, which enable data analysis in the cloud via SQL. BigQuery analyses are inexpensive and rapid even when scaled to petabyte sized inputs, for example we ran 6.6 billion correlations in 2.5 hours with a total cost of about one dollar. These data can also be accessed affordably from Google Cloud VMs where researchers can develop analysis pipelines in Python, R, and workflow languages such as CWL. We present two recent collaborations: In one BigQuery was used to develop machine learning algorithms that calculated genetic risk scores from TCGA glioblastoma and ovarian cancer copy number variation. In another example researchers combined SQL queries of our BQ tables with data from the ISPY2 Trial initiative and generated an R shiny app that can dynamically create data visualizations for genes of interest in different TCGA cohorts. Citation Format: Fabian Seidl, Lauren Hagen, Jacob Wilson, Boris Aguilar, Deena Bleich, Lauren Wolfe, Poojitha Gundluru, Prema Venkatesan, Mi Tian, Suzanne Paquette, Elaine Lee, Danna Huffman, David Pot, William Longabaugh. The ISB Cancer Gateway in the Cloud (ISB-CGC): Access, explore and analyze large-scale cancer data through the Google Cloud [abstract]. In: Proceedings of the American Association for Cancer Research Annual Meeting 2024; Part 1 (Regular Abstracts); 2024 Apr 5-10; San Diego, CA. Philadelphia (PA): AACR; Cancer Res 2024;84(6_Suppl):Abstract nr 3547.

A domain-specific language for managing ETL processes.

Maintenance of Data Warehouse (DW) systems is a critical task because any downtime or data loss can have significant consequences on business applications. Existing DW maintenance solutions mostly rely on concrete technologies and tools that are dependent on: the platform on which the DW system was created; the specific data extraction, transformation, and loading (ETL) tool; and the database language the DW uses. Different languages for different versions of DW systems make organizing DW processes difficult, as minimal changes in the structure require major changes in the application code for managing ETL processes. This article proposes a domain-specific language (DSL) for ETL process management that mitigates these problems by centralizing all program logic, making it independent from a particular platform. This approach would simplify DW system maintenance. The platform-independent language proposed in this article also provides an easier way to create a unified environment to control DW processes, regardless of the language, environment, or ETL tool the DW uses.

Structured Queries to AQL: Querying OpenEHR Data Leveraging a FHIR-Based Infrastructure for Federated Feasibility Queries.

In digital healthcare, data heterogeneity is a reoccurring issue caused by proprietary source systems. It is often overcome by utilizing ETL processes resulting in data warehouses, which ensure common data models for interoperability. Unfortunately, the achieved interoperability is usually limited to an institutional level. The broad solution space to achieve interoperability with different health data standards is part of the problem, resulting in different standards used at various institutions. For cross-institutional use cases like federated feasibility queries, the issue of heterogeneity is reintroduced. This work showcases how the existing German infrastructure for federated feasibility queries based on Hl7 FHIR can be extended to support openEHR without further data transformation. By utilizing an intermediate query format that can be transferred to FHIR Search, CQL, and AQL.

An Overview of ETL Techniques, Tools, Processes and Evaluations in Data Warehousing

An Overview of ETL Techniques, Tools, Processes and Evaluations in Data Warehousing

The OREGANO knowledge graph for computational drug repurposing

Drug repositioning is a faster and more affordable solution than traditional drug discovery approaches. From this perspective, computational drug repositioning using knowledge graphs is a very promising direction. Knowledge graphs constructed from drug data and information can be used to generate hypotheses (molecule/drug - target links) through link prediction using machine learning algorithms. However, it remains rare to have a holistically constructed knowledge graph using the broadest possible features and drug characteristics, which is freely available to the community. The OREGANO knowledge graph aims at filling this gap. The purpose of this paper is to present the OREGANO knowledge graph, which includes natural compounds related data. The graph was developed from scratch by retrieving data directly from the knowledge sources to be integrated. We therefore designed the expected graph model and proposed a method for merging nodes between the different knowledge sources, and finally, the data were cleaned. The knowledge graph, as well as the source codes for the ETL process, are openly available on the GitHub of the OREGANO project (https://gitub.u-bordeaux.fr/erias/oregano).

Pervasive Real-Time Analytical Framework—A Case Study on Car Parking Monitoring

Due to the amount of data emerging, it is necessary to use an online analytical processing (OLAP) framework capable of responding to the needs of industries. Processes such as drill-down, roll-up, three-dimensional analysis, and data filtering are fundamental for the perception of information. This article demonstrates the OLAP framework developed as a valuable and effective solution in decision making. To develop an OLAP framework, it was necessary to create the extract, transform and load the (ETL) process, build a data warehouse, and develop the OLAP via cube.js. Finally, it was essential to design a solution that adds more value to the organizations and presents several characteristics to support the entire data analysis process. A backend API (application programming interface) to route the data via MySQL was required, as well as a frontend and a data visualization layer. The OLAP framework was developed for the ioCity project. However, its great advantage is its versatility, which allows any industry to use it in its system. One ETL process, one data warehouse, one OLAP model, six indicators, and one OLAP framework were developed (with one frontend and one API backend). In conclusion, this article demonstrates the importance of a modular, adaptable, and scalable tool in the data analysis process and in supporting decision making.

Analyze the effectiveness of ETL processes implemented using SQL and Apache HiveQL languages

In the era of digitization, where data is collected in ever-increasing quantities, efficient processing is required. The article analyzes the performance of SQL and HiveQL, for scenarios of varying complexity, focusing on the execution time of individual queries. The tools used in the study are also discussed. The results of the study for each language are summarized and compared, highlighting their strengths and weaknesses, as well as identifying their possible areas of application.

Design and Implementation of an ETL-Process to Transfer Wound-Related Data into a Standardized Common Data Model.

For observational studies, which are relevant especially for chronic conditions like chronic wounds, the Observational Medical Outcomes Partnership (OMOP) Common Data Model (CDM) offers a standardized database schema. In this study an ETL process for the transition of wound related data was developed. After understanding the data in general and mapping the relevant codes to concepts available in OMOP, the ETL process was implemented. In a first step, a generic algorithm to convert data to a csv format was implemented in Java. The resulting csv file was then processed within KNIME to be loaded into an OMOP CDM conformant database. During the whole ETL process, HL7 FHIR CodeSystem and ConceptMap resources were used for coding and mapping. First clinical test cases to retrieve data were successfully processed as an example to demonstrate the feasibility and usefulness. They concerned wound size at the first visit and the main issues of patients in the wound quality of life questionnaire (n = 24). In general, the ETL process worked well, yet some challenges arose, like post coordinated SNOMED codes or conditions, which might occur more than once.

Pengembangan Sistem Business Intelligence Dalam Monitoring Performa Perusahaan Multi Company

Multi-company companies have challenges in managing subsidiary data into performance information of all subsidiaries in one window. This is due to the variation of data dimensions according to the business processes of each subsidiary. CV. XYZ is a holding company engaged in the food & beverage business. CV. XYZ currently manages three companies consisting of restaurants, catering, and tent and decoration rental. The problem faced by the company owner is the limited access to company performance in one information window. Each company has working papers in Excel format to record expenditure and income transactions. This article proposes the development of a website-based business intelligence system to overcome the problems of CV. XYZ. The purpose of developing a business intelligence system in this article is to provide access to the performance of each subsidiary in one website media. The business intelligence system is developed through the stages of data collection and analysis, data warehouse design, ETL process, and data visualization with Microsoft Power BI. The data warehouse design uses Kimball's nine-step method which produces a data warehouse with a star scheme. The developed Business Intelligence system was tested using the UAT method. The UAT test results show that the system development is following the company's needs as indicated by the UAT score of 92%

Electrochemical CO2 Reduction to Ethylene Using Coupled Tandem Electrolyzers

The thermal catalytic “Ethylene to Liquids” (ETL) Process is an emerging alternative to the two classical “Fischer-Tropsch” and “Methanol-to-Liquids” process routes toward production of synthetic/e- hydrocarbon fuels. The ETL process, however, relies on a non-fossil ethylene supply. The electrochemical reduction of CO2 to ethylene can serve as an emerging new ethylene supply route. In this talk, I will highlight some of our recent advances in the design and characterization of efficient CO2 electrolyzers for the electroreduction of CO2 to ethylene in tandem cell designs. The cell tandem consists of a “CO2 to CO” electrolyzer (cell 1) incorporating NiNC single site, coupled to “CO2/CO mixed feed to ethylene” electrolyzer (cell 2). Carbonate transport processes in cell 1 are diagnosed using the carbon cross over coefficient (CCC). Mechanistic aspects of mixed feed reduction are discussed. Performance and its limitations of tandem operation will be discussed.