ETL Process Research Articles

Data Warehousing Process Modeling from Classical Approaches to New Trends: Main Features and Comparisons

The extract, transform, and load (ETL) process is at the core of data warehousing architectures. As such, the success of data warehouse (DW) projects is essentially based on the proper modeling of the ETL process. As there is no standard model for the representation and design of this process, several researchers have made efforts to propose modeling methods based on different formalisms, such as unified modeling language (UML), ontology, model-driven architecture (MDA), model-driven development (MDD), and graphical flow, which includes business process model notation (BPMN), colored Petri nets (CPN), Yet Another Workflow Language (YAWL), CommonCube, entity modeling diagram (EMD), and so on. With the emergence of Big Data, despite the multitude of relevant approaches proposed for modeling the ETL process in classical environments, part of the community has been motivated to provide new data warehousing methods that support Big Data specifications. In this paper, we present a summary of relevant works related to the modeling of data warehousing approaches, from classical ETL processes to ELT design approaches. A systematic literature review is conducted and a detailed set of comparison criteria are defined in order to allow the reader to better understand the evolution of these processes. Our study paints a complete picture of ETL modeling approaches, from their advent to the era of Big Data, while comparing their main characteristics. This study allows for the identification of the main challenges and issues related to the design of Big Data warehousing systems, mainly involving the lack of a generic design model for data collection, storage, processing, querying, and analysis.

Big Data ETL Process and Its Impact on Text Mining Analysis for Employees’ Reviews

Big data analysis is challenging in the current context for enterprises that would like to apply these capabilities in the human resource sector. This paper will show how an organization can take advantage of the current or former employees’ reviews that are provided on a constant basis on different sites, so that the management can adjust or change business decisions based on employees’ wishes, dissatisfaction or needs. Considering the previously mentioned challenge on big data analysis, this research will first provide the best practice for the collection and transformation of the data proposed for analysis. The second part of this paper presents the extraction of two datasets containing employee reviews using data scraping techniques, the analysis of data by using text mining techniques to retrieve business insights and the comparison of the results for these algorithms. Experimental results with Naïve Bayes, Logistic Regression, K-Nearest Neighbor and Support Vector Machine for employee sentiment prediction showed much better performances for Logistic Regression. Three out of the four analyzed algorithms performed better for the second, triple-size dataset. The final aim of the paper is to provide an end-to-end solution with high performance and reduced costs.

Study of ETL Process and Its Testing Techniques

Abstract: ETL stands for Extract Transform and Load. ETL is a process in Data Warehouse. In this process ETL tools are used to extracts the data from various data source systems from where data can be retrieved, transforms it in the staging area, and at the end it loads the data into the Data Warehouse system. This process picks data from operational system and fixes it into the data warehouse. Construction of an ETL process is one of the bulky task of data warehouse. In this paper we will try to cover ETL process, Different ETL testing techniques and their challenges. Keywords: Extract, Transform, Load, ETL testing

How to Optimize Connection Between PACS and Clinical Data Warehouse: A Web Service Approach Based on Full Metadata Integration.

Clinical image data analysis is an active area of research. Integrating such data in a Clinical Data Warehouse (CDW) implies to unlock the PACS and RIS and to address interoperability and semantics issues. Based on specific functional and technical requirements, our goal was to propose a web service (I4DW) that allows users to query and access pixel data from a CDW by fully integrating and indexing imaging metadata. Here, we present the technical implementation of this workflow as well as the evaluation we carried out using a prostate cancer cohort use case. The query mechanism relies on a Dicom metadata hierarchy dynamically generated during the ETL Process. We evaluated the Dicom data transfer performance of I4DW, and found mean retrieval times of 5.94 seconds and 0.9 seconds to retrieve a complete DICOM series from the PACS and all metadata of a series. We could retrieve all patients and imaging tests of the prostate cancer cohort with a precision of 0.95 and a recall of 1. By leveraging the CMOVE method, our approach based on the Dicom protocol is scalable and domain-neutral. Future improvement will focus on performance optimization and de identification.

Opportunities of Digital Infrastructures for Disease Management-Exemplified on COVID-19-Related Change in Diagnosis Counts for Diabetes-Related Eye Diseases.

Background: Retrospective research on real-world data provides the ability to gain evidence on specific topics especially when running across different sites in research networks. Those research networks have become increasingly relevant in recent years; not least due to the special situation caused by the COVID-19 pandemic. An important requirement for those networks is the data harmonization by ensuring the semantic interoperability. Aims: In this paper we demonstrate (1) how to facilitate digital infrastructures to run a retrospective study in a research network spread across university and non-university hospital sites; and (2) to answer a medical question on COVID-19 related change in diagnostic counts for diabetes-related eye diseases. Materials and methods: The study is retrospective and non-interventional and runs on medical case data documented in routine care at the participating sites. The technical infrastructure consists of the OMOP CDM and other OHDSI tools that is provided in a transferable format. An ETL process to transfer and harmonize the data to the OMOP CDM has been utilized. Cohort definitions for each year in observation have been created centrally and applied locally against medical case data of all participating sites and analyzed with descriptive statistics. Results: The analyses showed an expectable drop of the total number of diagnoses and the diagnoses for diabetes in general; whereas the number of diagnoses for diabetes-related eye diseases surprisingly decreased stronger compared to non-eye diseases. Differences in relative changes of diagnoses counts between sites show an urgent need to process multi-centric studies rather than single-site studies to reduce bias in the data. Conclusions: This study has demonstrated the ability to utilize an existing portable and standardized infrastructure and ETL process from a university hospital setting and transfer it to non-university sites. From a medical perspective further activity is needed to evaluate data quality of the utilized real-world data documented in routine care and to investigate its eligibility of this data for research.

Dynamic multi-variant relational scheme-based intelligent ETL framework for healthcare management.

The growth of information technology has opened the gate for the organizations to maintain their data in various forms and at various volumes. This increases the volume and dimension of data being maintained. However, they store their data in their data servers or in cloud environment. Such data have been used to generate various intelligence to support various problems. To support such analysis process, different data have been used and the big data comes to play in this part. Optimizing techniques to help improve the process of ETL could greatly help in real-time analysis of data. ETL optimization could be achieved through several factors simplest being increasing the frequency of the process. Other ways to achieve optimization are through the use of various architectures, programming models, intelligence in transformation and security. To improve the performance of ETL, an efficient dynamic multi-variant relational intelligent ETL framework has been presented in this article. The distributed approach maintains various ontology’s and data dictionaries which have been dynamically updated by different threads of ETL process. Initially, the process is start by applying the extraction process which extracts the data from different sources and finds set of dimensions and their characteristics. Such data extracted have been verified over the data dictionary. Further, the relational score has been measured for each data source with the existing one. Similarly, the method computes the value of multi-variant relational similarity (MVRS) for the data obtained from a single source. This will be performed by different threads of ETL process. According to the value of MVRS, the method performs map reduce and merging of data. According to the value of MVRS the method selects the data node and merges the data to store in the data warehouse. The threads of ETL are capable of reading the changes in data dictionaries and ontology’s to iterate the process of transformation and loading. The method improves the performance of ETL with least time complexity and higher performance.

OVERVIEW OF OPTIMIZATION METHODS FOR PRODUCTIVITY OF THE ETL PROCESS

One of the important aspects in management and acceleration of processes, operations in databases and data warehouses is ETL processes, the process of extracting, transforming and loading data. These processes without optimizing, a realization data warehouse project is costly, complex, and time-consuming. This paper provides an overview and research of methods for optimizing the performance of ETL processes; that the most important indicator of ETL system's operation is the time and speed of data processing is shown. The issues of the generalized structure of ETL process flows are considered, the architecture of ETL process optimization is proposed, and the main methods of parallel data processing in ETL systems are presented, those methods can improve its performance. The most relevant today of the problem is performance of ETL processes for data warehouses is considered in detail.

Automated data extraction into acataract surgery registry : Automatic investigation of results in the registry on ophthalmological analysis (ROPHA)

Medical data should be evaluated with respect to quality management, for scientific and research purposes on the basis of registries. These data are stored in the EMR in an unstructured way and are not suitable for automatic evaluation. The EMR tomedo (Zollsoft/Jena) and the PACS of FORUM (Zeiss/Oberkochen) manage personal, medical and image data for cataract surgery. New developed input masks gather data from diagnostic tools direct in astructured way. These data are automatically extracted by an ETL process and are stored in our data warehouse. Via Excel specific data can be selected and displayed in the dashboard. All medical data are structured and stored electronically. Few data are entered manually and most of the data are entered by pull-down menus or taken over directly from diagnostic equipment. A total of 2816 cataract surgeries of 1844patients were selected for afirst analysis. Results of IOL type distribution in relation to the patients and to the surgeons are displayed as well as prediction error of refraction for the 4different groups of IOLs. Structured data input into the EMR (electronic medical record) makes data for automatic extraction available thus reducing input errors or confounding data. The result is ahigh quality of data sets. Input of epidemiological and general medical data also helps to perform health services research. These registry data represent better information for real live data than data from clinical trials. Bureaucratic hurdles in Germany prevent the use of registry data.

Developing an ETL tool for converting the PCORnet CDM into the OMOP CDM to facilitate the COVID-19 data integration

ObjectiveThe large-scale collection of observational data and digital technologies could help curb the COVID-19 pandemic. However, the coexistence of multiple Common Data Models (CDMs) and the lack of data extract, transform, and load (ETL) tool between different CDMs causes potential interoperability issue between different data systems. The objective of this study is to design, develop, and evaluate an ETL tool that transforms the PCORnet CDM format data into the OMOP CDM. MethodsWe developed an open-source ETL tool to facilitate the data conversion from the PCORnet CDM and the OMOP CDM. The ETL tool was evaluated using a dataset with 1000 patients randomly selected from the PCORnet CDM at Mayo Clinic. Information loss, data mapping accuracy, and gap analysis approaches were conducted to assess the performance of the ETL tool. We designed an experiment to conduct a real-world COVID-19 surveillance task to assess the feasibility of the ETL tool. We also assessed the capacity of the ETL tool for the COVID-19 data surveillance using data collection criteria of the MN EHR Consortium COVID-19 project. ResultsAfter the ETL process, all the records of 1000 patients from 18 PCORnet CDM tables were successfully transformed into 12 OMOP CDM tables. The information loss for all the concept mapping was less than 0.61%. The string mapping process for the unit concepts lost 2.84% records. Almost all the fields in the manual mapping process achieved 0% information loss, except the specialty concept mapping. Moreover, the mapping accuracy for all the fields were 100%. The COVID-19 surveillance task collected almost the same set of cases (99.3% overlaps) from the original PCORnet CDM and target OMOP CDM separately. Finally, all the data elements for MN EHR Consortium COVID-19 project could be captured from both the PCORnet CDM and the OMOP CDM. ConclusionWe demonstrated that our ETL tool could satisfy the data conversion requirements between the PCORnet CDM and the OMOP CDM. The outcome of the work would facilitate the data retrieval, communication, sharing, and analysis between different institutions for not only COVID-19 related project, but also other real-world evidence-based observational studies.

Towards Harmonized Data Quality in the Medical Informatics Initiative - Current State and Future Directions.

Health data from hospital information systems are valuable sources for medical research but have known issues in terms of data quality. In a nationwide data integration project in Germany, health care data from all participating university hospitals are being pooled and refined in local centers. As there is currently no overarching agreement on how to deal with errors and implausibilities, meetings were held to discuss the current status and the need to develop consensual measures at the organizational and technical levels. This paper analyzes the discovered similarities and differences. The result shows that although data quality checks are carried out at all sites, there is a lack of both centrally coordinated data quality indicators and a formalization of plausibility rules as well as a repository for automatic querying of the rules, for example in ETL processes.

Predictive models assessment based on CRISP-DM methodology for students performance in Colombia - Saber 11 Test

The purpose of this paper is to evaluate several machine learning models under the CRISP-DM methodology in order to determine, through its metrics, the best model for predicting the performance of high school students in the Colombian Caribbean region in the Saber 11º test, while proposing a new methodology for evaluating the results of the test by regions in order to take into account the socioeconomic particularities of each one of them. The CRISP-DM methodology is taken as a basis due to its maturity, this methodology allows the extraction of business and data knowledge, offers a guide for data preparation, modeling and validation of the models; it is expected that the proposed methodology will be implemented by the Colombian Institute for the Promotion of Higher Education (ICFES), departmental education secretariats and educational institutions. A variety of techniques and tools were used to develop ETL processes to obtain a data set with the most relevant attributes, in order to evaluate four machine learning models developed with the J48 (C4.5), LMT, PART and Multilayer Perceptron algorithms; obtaining that the best data set and the best learning model is obtained using the InfoGain attribute selection method and the LMT decision tree algorithm, respectively. Therefore, this project will facilitate the actors of the National Education System to make decisions for the benefit of students and the quality of education in the country, especially in the Caribbean region.

Managing vulnerabilities during the development of a secure ETL processes

Managing vulnerabilities during the development of a secure ETL processes

Managing vulnerabilities during the development of a secure ETL processes

Managing vulnerabilities during the development of a secure ETL processes

Analisis Dynamic ETL Incremental Load untuk Data Integration Datawarehouse

Data integration is a combination of techniques and businesses that are used to collect data from different sources into useful and valuable information ETL process that includes extracting data from various data sources, transforming data to form and calculate data and load data on target storage, to support data warehouse need. Based on organizations and industries that have implemented data warehouse, the problem that generally arises regarding data load is the difficulty in integrating different data sources, how to form data from various data formats into uniform data, how to integrate data delta between data sources and target storage in an incremental load process so that this data synchronization process can be carried out continuously and relatively faster. ETL process requires a platform that can facilitate data integration needs, in order to run this process. SSIS (SQL Server Integration Service) is a Data Integration platform to build an enterprise-level data integration and solutions for data transformation. Integration Service can extract and change data (transform) from various sources such as XML data files, flat files, APIs, and relational data sources, and then load into one or several destination data. According to the problem related to data load, we will examine how the solution model uses SSIS for the ETL process. This paper proposed an ETL Architecture model by completing the ETL process for full & incremental load extraction and the original data layer.

OVERVIEW OF OPTIMIZATION METHODS FOR PRODUCTIVITY OF THE ETL PROCES

One of the important aspects in management and acceleration of processes, operations in databases and data warehouses is ETL processes, the process of extracting, transforming and loading data. These processes without optimizing, a realization data warehouse project is costly, complex, and time-consuming. This paper provides an overview and research of methods for optimizing the performance of ETL processes; that the most important indicator of ETL system's operation is the time and speed of data processing is shown. The issues of the generalized structure of ETL process flows are considered, the architecture of ETL process optimization is proposed, and the main methods of parallel data processing in ETL systems are presented, those methods can improve its performance. The most relevant today of the problem is performance of ETL processes for data warehouses is considered in detail.

Dashboard Business Intelligence Vusialisasi Data Akreditasi Sekolah Pada SMP Negeri 1 Sembawa

This study aims to create a business intelligence dashboard to support school accreditation at SMP Negeri 1 Sembawa in the form of data visualization. In this study, several business intelligence dashboards were produced with the aim of providing convenience for schools in supporting the school accreditation process. Business intelligence is a tool to manage data and perform data analysis that can support school accreditation which will be collected into a data mart and the analysis process is carried out in the form of a cube. For the ETL process using the help of SQL Server Management Studio and SQL Server Development software and as a design for the dashboard creation using the Power BI software. This research has several dashboards including student achievement dashboard based on achievement level, student achievement dashboard based on year of achievement, and student achievement based on graduation which can be seen by year and gender of students.

Making EHRs Reusable: A Common Framework of Data Operations.

Reuse of EHRs requires data extraction and transformation processes are based on homogeneous and formalized operations in order to make them understandable, reproducible and auditable. This work aims to define a common framework of data operations for obtaining EHR-derived datasets for secondary use. Thus, 21 operations were identified from different data-driven projects of a 1,300-beds tertiary Hospital. Then, ISO 13606 standard was used to formalize them. This work is the starting point to homogenize ETL processes for the reuse of EHRs, applicable to any condition and organization. In future studies, defined data operations will be implemented and validated in projects of different purposes.

Implementasi Dashboard Untuk Visualisasi Data Penerimaan Mahasiswa Baru Studi Kasus : Universitas Kristen Duta Wacana

New Student Admissions activities at an University needs to be monitored to determine the performance of Admission and Promotion. Admission data can be processed and analyzed to determine the expo destination schools, promotion locations, and appropriate potential areas. In addition, the results of Admission data processing can be used to determine the fluctuation of enthusiasts in the Majors from previous periods or the comparison of the number of enthusiasts between Majors. For that reason we need a dashboard as a tool to help user for analyze, monitor and be taken into consideration in making strategic decisions related to Admission activities. The implemented dashboard consists of a collection of information that is visualized in graphs and numbers. The users of the dashboard are employees of the Admissions and Promotion Unit, Head of Majors for each department, and Administrators. There are 6 dimensions displayed in the dashboard, namely time, region, test route, path transfer, prospective students, and school origin. The data of new student admissions is processed and stored into the data warehouse through the ETL process. Furthermore, the data warehouse is used as data storage for the implementation of a web-based dashboard application using the PHP programming language. The implemented dashboard consists of Admission information that is visualized into charts and numbers. the graphs that used in PMB data visualization include bar charts, doughnut charts, pie charts, column charts, line charts, and map charts.

Building a federated research infrastructure for a policy-rapid response

BackgroundMobilizing real world data from multiple data hubs in multiple countries to carry out policy-oriented research, requires orchestrating the governance of workflows, being compliant with legal and ethical requirements, and semantic harmonization and technological interoperability. In the context of PHIRI, policy-oriented research has to provide insight to policy-makers to cope with pandemics.MethodsPHIRI builds this concept on the deployment of four use cases throughout a federated research infrastructure. A central hub orchestrates all the elements encompassing the development of such an infrastructure; so, a common governance model, common methodology pursuing semantic interoperability, and the development and deployment of technological solution containing ETL processes, data quality assessment solutions and data analyses, all packaged to be rolled out in the different data hubs composing the federation.ResultsA prototype orchestrating those workflows is being followed in four use cases: indirect effects of the pandemic on vulnerable populations, delayed breast cancer treatments due to the pandemic, perinatal health affections along the COVID19 crisis, effects of the pandemic on mental health care. A prototype of the technological platform supporting interoperable federated analyses has been prepared.ConclusionsIt is pertinent, feasible and reliable using a federated research infrastructure leveraging real world data from many data hubs in many countries to answer research queries on the COVID19 pandemic.

MDA Approach for Designing and Developing Data Warehouses: A Systematic Review & Proposal

A data warehouse (DW) is a vast repository of data that facilitates decision-making for businesses and companies. This concept dates back to the 1980s and it has been widely accepted. One of the key points for the success of the process of data warehousing lies in the definition of the warehouse model depending on data sources and analysis needs. Once the data warehouse is designed, the content and structure of the data sources, as well as the requirements analysis are required to evolve, therefore, an evolution of the model must take place (diagram and data). In this context, several approaches have been developed to design and implement data warehouses. Nevertheless, there is no standard process that deals with designing all of the data warehouse layers, also, there is no software that encompasses this type of problem. In general, the majority of these approaches focus on a particular aspect of data warehouse such as data storage, ETL process, OLAP, reporting, etc, and does not cover its entire lifecycle. A Model-Driven Architecture (MDA) is a standard approach, its aims to support all phases of software manufacturing by promoting the use of models and the transformations between them. Moreover, this approach aims to automate the process of software engineering, thereby decreasing the cost of software development and enhancing its productivity. In this study, we present a systematic review of various works on the data warehouse design methods. We compare and discuss these works according to the criteria that seem relevant for this issue. We present a new design approach for multidimensional schemas construction from relational models using MDA techniques, we also develop the resulting research perspectives.