Migrating a Well-Established Longitudinal Cohort Database From Oracle SQL to Research Electronic Data Entry (REDCap): Data Management Research and Design Study

Katharina Kusejko; Daniel Smith; Alexandra Scherrer; Paolo Paioni; Malte Kohns Vasconcelos; Karoline Aebi-Popp; Roger D Kouyos; Huldrych F Günthard; Christian R Kahlert; Swiss HIV Cohort Study and the Swiss Mother and Child HIV Cohort Study

doi:10.2196/44567

Migrating a Well-Established Longitudinal Cohort Database From Oracle SQL to Research Electronic Data Entry (REDCap): Data Management Research and Design Study

JMIR Form Res. 2023 May 31:7:e44567. doi: 10.2196/44567.

Authors

Affiliations

¹ Institute of Medical Virology, University of Zurich, Zurich, Switzerland.
² Division of Infectious Diseases and Hospital Epidemiology, University Hospital Zurich, Zurich, Switzerland.
³ Division of Infectious Diseases and Hospital Epidemiology, University Children's Hospital Zurich, Zurich, Switzerland.
⁴ Department for Infectious Diseases and Vaccinology, University of Basel Children's Hospital, Basel, Switzerland.
⁵ Department of Infectious Diseases, Inselspital Bern, University of Bern, Bern, Switzerland.
⁶ Department of Obstetrics and Gynecology, Lindenhofspital, Bern, Switzerland.
⁷ Department of Infectious Diseases and Hospital Epidemiology, Cantonal Hospital St Gallen, St Gallen, Switzerland.
⁸ Division of Infectious Diseases and Hospital Epidemiology, Children's Hospital of Eastern Switzerland, St Gallen, Switzerland.
⁹ See Acknowledgements, .

PMID: 37256686
PMCID: PMC10267784
DOI: 10.2196/44567

Abstract

Background: Providing user-friendly electronic data collection tools for large multicenter studies is key for obtaining high-quality research data. Research Electronic Data Capture (REDCap) is a software solution developed for setting up research databases with integrated graphical user interfaces for electronic data entry. The Swiss Mother and Child HIV Cohort Study (MoCHiV) is a longitudinal cohort study with around 2 million data entries dating back to the early 1980s. Until 2022, data collection in MoCHiV was paper-based.

Objective: The objective of this study was to provide a user-friendly graphical interface for electronic data entry for physicians and study nurses reporting MoCHiV data.

Methods: MoCHiV collects information on obstetric events among women living with HIV and children born to mothers living with HIV. Until 2022, MoCHiV data were stored in an Oracle SQL relational database. In this project, R and REDCap were used to develop an electronic data entry platform for MoCHiV with migration of already collected data.

Results: The key steps for providing an electronic data entry option for MoCHiV were (1) design, (2) data cleaning and formatting, (3) migration and compliance, and (4) add-on features. In the first step, the database structure was defined in REDCap, including the specification of primary and foreign keys, definition of study variables, and the hierarchy of questions (termed "branching logic"). In the second step, data stored in Oracle were cleaned and formatted to adhere to the defined database structure. Systematic data checks ensured compliance to all branching logic and levels of categorical variables. REDCap-specific variables and numbering of repeated events for enabling a relational data structure in REDCap were generated using R. In the third step, data were imported to REDCap and then systematically compared to the original data. In the last step, add-on features, such as data access groups, redirections, and summary reports, were integrated to facilitate data entry in the multicenter MoCHiV study.

Conclusions: By combining different software tools-Oracle SQL, R, and REDCap-and building a systematic pipeline for data cleaning, formatting, and comparing, we were able to migrate a multicenter longitudinal cohort study from Oracle SQL to REDCap. REDCap offers a flexible way for developing customized study designs, even in the case of longitudinal studies with different study arms (ie, obstetric events, women, and mother-child pairs). However, REDCap does not offer built-in tools for preprocessing large data sets before data import. Additional software is needed (eg, R) for data formatting and cleaning to achieve the predefined REDCap data structure.

Keywords: HIV; REDCap; cohort study; data collection; digital solution; eCRF; electronic case report forms; electronic data entry; software.

©Katharina Kusejko, Daniel Smith, Alexandra Scherrer, Paolo Paioni, Malte Kohns Vasconcelos, Karoline Aebi-Popp, Roger D Kouyos, Huldrych F Günthard, Christian R Kahlert, Swiss HIV Cohort Study and the Swiss Mother and Child HIV Cohort Study. Originally published in JMIR Formative Research (https://formative.jmir.org), 31.05.2023.