ETL Best Practices for Data Quality Checks in RIS Databases

Abstract

The topic of data integration from external data sources or independent IT-systems has received increasing attention recently in IT departments as well as at management level, in particular concerning data integration in federated database systems. An example of the latter are commercial research information systems (RIS), which regularly import, cleanse, transform and prepare the analysis research information of the institutions of a variety of databases. In addition, all these so-called steps must be provided in a secured quality. As several internal and external data sources are loaded for integration into the RIS, ensuring information quality is becoming increasingly challenging for the research institutions. Before the research information is transferred to a RIS, it must be checked and cleaned up. An important factor for successful or competent data integration is therefore always the data quality. The removal of data errors (such as duplicates and harmonization of the data structure, inconsistent data and outdated data, etc.) are essential tasks of data integration using extract, transform, and load (ETL) processes. Data is extracted from the source systems, transformed and loaded into the RIS. At this point conflicts between different data sources are controlled and solved, as well as data quality issues during data integration are eliminated. Against this background, our paper presents the process of data transformation in the context of RIS which gains an overview of the quality of research information in an institution’s internal and external data sources during its integration into RIS. In addition, the question of how to control and improve the quality issues during the integration process in RIS will be addressed.

A Systematic Literature Review for Implementing Data Ops in the Data Warehouse Lifecycle during the ETL Phase

“…The researchers presented new techniques of data cleansing which can be applied to research information. On the other hand, the research did not touch the Data Ops technique (Azeroual et al, 2019b).…”

Solving problems of research information heterogeneity during integration – using the European CERIF and German RCD standards as examples

“…To address these challenges during information integration, the solution is to implement the ETL process, information integration methods and techniques. The investigations clearly showed in the related paper [8] that during the transformation phase of the ETL process the processing of the internal and external data sources should take place. This enables the cleaning, transformation, harmonization and merging of the data which have already been consolidated in the RIMS in order to create new quality of information that may be of particular importance to an institution.…”

“…Since the RIMS obtains its information or data from several heterogeneous sources, the data must be converted into a uniform internal format. For this, the following transformations, which can be summarized under the term of "data migration", are required [7,8] [9], the following problem areas may also arise during the transformation phase. These were considered and investigated with examples in the context of RIMS, which can be found in the related paper [8], such as: These so-called errors or problem areas should be eliminated in order to achieve the highest possible data quality.…”

The Design and Implementation of Business Intelligence System for Lecturer Qualification Analysis based on ETL’s QOX Approach

“…The Quality Objective Matrix (QOX) is one of the quality measures of data and information that can be used to examine the performance of relevant data and information to ensure the effectiveness of ETL workflow [1,4,[7][8][24][25]. Previous studies identified four dimensions of QOX: accuracy, completenes, scalability, and efficiency [7][8][30][31][32][33] as presented in Table 1. The accuracy measures the correctness of data transformation in terms of right formats, data type, and data length [7].…”