Top

BMC Medical Informatics and Decision Making

Published in:

Open Access 01-12-2017 | Technical advance

Dynamic-ETL: a hybrid approach for health data extraction, transformation and loading

Authors: Toan C. Ong, Michael G. Kahn, Bethany M. Kwan, Traci Yamashita, Elias Brandt, Patrick Hosokawa, Chris Uhrich, Lisa M. Schilling

Published in: BMC Medical Informatics and Decision Making | Issue 1/2017

Abstract

Background

Electronic health records (EHRs) contain detailed clinical data stored in proprietary formats with non-standard codes and structures. Participating in multi-site clinical research networks requires EHR data to be restructured and transformed into a common format and standard terminologies, and optimally linked to other data sources. The expertise and scalable solutions needed to transform data to conform to network requirements are beyond the scope of many health care organizations and there is a need for practical tools that lower the barriers of data contribution to clinical research networks.

Methods

We designed and implemented a health data transformation and loading approach, which we refer to as Dynamic ETL (Extraction, Transformation and Loading) (D-ETL), that automates part of the process through use of scalable, reusable and customizable code, while retaining manual aspects of the process that requires knowledge of complex coding syntax. This approach provides the flexibility required for the ETL of heterogeneous data, variations in semantic expertise, and transparency of transformation logic that are essential to implement ETL conventions across clinical research sharing networks. Processing workflows are directed by the ETL specifications guideline, developed by ETL designers with extensive knowledge of the structure and semantics of health data (i.e., “health data domain experts”) and target common data model.

Results

D-ETL was implemented to perform ETL operations that load data from various sources with different database schema structures into the Observational Medical Outcome Partnership (OMOP) common data model. The results showed that ETL rule composition methods and the D-ETL engine offer a scalable solution for health data transformation via automatic query generation to harmonize source datasets.

Conclusions

D-ETL supports a flexible and transparent process to transform and load health data into a target data model. This approach offers a solution that lowers technical barriers that prevent data partners from participating in research data networks, and therefore, promotes the advancement of comparative effectiveness research using secondary electronic health data.

Available only for authorised users

https://www.talend.com/

http://www.pentaho.com/

http://www.hl7.org/

https://www.nlm.nih.gov/research/umls/Snomed/snomed_main.html

http://www.w3schools.com/sql/sql_insert.asp

https://github.com/OHDSI/SqlRender

Sox HC, Greenfield S. Comparative effectiveness research: a report from the institute of medicine. Ann Intern Med. 2009;151:203–5. doi:10.7326/0003-4819-151-3-200908040-00125.CrossRefPubMed

Danaei G, Rodríguez LAG, Cantero OF, et al. Observational data for comparative effectiveness research: an emulation of randomised trials of statins and primary prevention of coronary heart disease. Stat Methods Med Res. 2013;22:70–96. doi:10.1177/0962280211403603.CrossRefPubMed

Institute of Medicine (U.S.) Committee on Improving the Patient Record. In: Dick RS, Steen EB, editors. The computer-based patient record: an essential technology for health care, revised edition. Washington, D.C.: National Academy Press; 1997.

Grossmann C, Institute of Medicine (U.S.). Roundtable on Value & Science-Driven Health Care. Clinical data as the basic staple of health learning : creating and protecting a public good : workshop summary. Washington, D.C.: National Academies Press; 2010. http://www.ncbi.nlm.nih.gov/bookshelf/br.fcgi?book=nap12212 http://www.nap.edu/catalog.php?record_id=12212

Selby JV, Lipstein SH. PCORI at 3 years--progress, lessons, and plans. N Engl J Med. 2014;370:592–5. doi:10.1056/NEJMp1313061.CrossRefPubMed

Fleurence RL, Curtis LH, Califf RM, et al. Launching PCORnet, a national patient-centered clinical research network. J Am Med Inform Assoc JAMIA. 2014;21:578–82. doi:10.1136/amiajnl-2014-002747.CrossRefPubMed

Holve E, Segal C, Lopez MH, et al. The Electronic Data Methods (EDM) Forum for Comparative Effectiveness Research (CER). Med Care. 2012;50(Suppl):S7–10. doi:10.1097/MLR.0b013e318257a66b.CrossRefPubMed

Rassen JA, Schneeweiss S. Using high-dimensional propensity scores to automate confounding control in a distributed medical product safety surveillance system. Pharmacoepidemiol Drug Saf. 2012;21(Suppl 1):41–9. doi:10.1002/pds.2328.CrossRefPubMed

Garbe E, Kloss S, Suling M, et al. High-dimensional versus conventional propensity scores in a comparative effectiveness study of coxibs and reduced upper gastrointestinal complications. Eur J Clin Pharmacol Published Online First: 5 July 2012. doi:10.1007/s00228-012-1334-2.

10.

Fleischer NL, Fernald LC, Hubbard AE. Estimating the potential impacts of intervention from observational data: methods for estimating causal attributable risk in a cross-sectional analysis of depressive symptoms in Latin America. J Epidemiol Community Health. 2010;64:16–21. doi:10.1136/jech.2008.085985.CrossRefPubMed

11.

Polsky D, Eremina D, Hess G, et al. The importance of clinical variables in comparative analyses using propensity-score matching: the case of ESA costs for the treatment of chemotherapy-induced anaemia. PharmacoEconomics. 2009;27:755–65. doi:10.2165/11313860-000000000-00000.CrossRefPubMed

12.

McCandless LC, Gustafson P, Austin PC. Bayesian propensity score analysis for observational data. Stat Med. 2009;28:94–112. 10.1002/sim.3460.CrossRefPubMed

13.

Jansen RG, Wiertz LF, Meyer ES, et al. Reliability analysis of observational data: problems, solutions, and software implementation. Behav Res Methods Instrum Comput. 2003;35:391–9.CrossRefPubMed

14.

PCORI Methodology Committee. PCORI Methodology Standards. 2012. http://www.pcori.org/assets/PCORI-Methodology-Standards.pdf

15.

Collins FS, Hudson KL, Briggs JP, et al. PCORnet: turning a dream into reality. J Am Med Inform Assoc JAMIA. 2014;21:576–7. doi:10.1136/amiajnl-2014-002864.CrossRefPubMed

16.

Forrest CB, Margolis PA, Bailey LC, et al. PEDSnet: a National Pediatric Learning Health System. J Am Med Inform Assoc JAMIA Published Online First: 12 May 2014. doi: 10.1136/amiajnl-2014-002743.

17.

Behrman RE, Benner JS, Brown JS, et al. Developing the sentinel system — A national resource for evidence development. N Engl J Med. 2011;364:498–9. doi:10.1056/NEJMp1014427.CrossRefPubMed

18.

Joe V Selby, Richard Platt, Rachael Fleurence, et al. PCORnet: achievements and plans on the road to transforming health research | PCORI. http://www.pcori.org/blog/pcornet-achievements-and-plans-road-transforming-health-research. Accessed 16 May 2016.

19.

Gini R, Schuemie M, Brown J, et al. Data extraction and management in networks of observational health care databases for scientific research: a comparison among EU-ADR, OMOP, mini-sentinel and MATRICE strategies. EGEMS. 2016;4:1189. doi:10.13063/2327-9214.1189.CrossRefPubMedPubMedCentral

20.

Kahn MG, Batson D, Schilling LM. Data model considerations for clinical effectiveness researchers. Med Care. 2012;50(Suppl):S60–7. doi:10.1097/MLR.0b013e318259bff4.CrossRefPubMed

21.

Schilling LM, Kwan B, Drolshagen C, et al. Scalable Architecture for Federated Translational Inquires Network (SAFTINet): technology infrastructure for a Distributed Data Network. EGEMs Gener. Evid. Methods Improve Patient Outcomes. 2013. in press.

22.

Ohno-Machado L, Agha Z, Bell DS, et al. pSCANNER: patient-centered Scalable National Network for Effectiveness Research. J Am Med Inform Assoc JAMIA. 2014;21:621–6. doi:10.1136/amiajnl-2014-002751.CrossRefPubMed

23.

Murphy SN, Weber G, Mendis M, et al. Serving the enterprise and beyond with informatics for integrating biology and the bedside (i2b2). J Am Med Inform Assoc JAMIA. 2010;17:124–30. doi:10.1136/jamia.2009.000893.CrossRefPubMed

24.

Brown JS, Lane K, Moore K, et al. Defining and evaluating possible database models to implement the FDA Sentinel initiative. 2009.https://www.brookings.edu/wp-content/uploads/2012/04/03_Brown.pdf. Accessed 15 Feb 2014.

25.

Patil PS. Improved extraction mechanism in ETL process for building of a data warehouse. INFLIBNET Published Online First: 22 February 2013. http://ir.inflibnet.ac.in:8080/jspui/handle/10603/7023. Accessed 13 Nov 2014.

26.

Devine E, Capurro D, van Eaton E, et al. Preparing electronic clinical data for quality improvement and comparative effectiveness research: the SCOAP CERTAIN automation and validation project. EGEMS. 2013;1:1025. doi:10.13063/2327-9214.1025.PubMedPubMedCentral

27.

Suresh S, Gautam JP, Pancha G, et al. Method and architecture for automated optimization of ETL throughput in data warehousing applications. 2001. http://google.com/patents/US6208990. Accessed 13 Nov 2014.

28.

Kushanoor A, Murali Krishna S, Vidya Sagar Reddy T. ETL process modeling in DWH using enhanced quality techniques. Int J Database Theory Appl. 2013;6:179–98.

29.

Peek N, Holmes JH, Sun J. Technical challenges for big data in biomedicine and health: data sources, infrastructure, and analytics. Yearb Med Inform. 2014;9:42–7. doi:10.15265/IY-2014-0018

30.

Sandhu E, Weinstein S, McKethan A, et al. Secondary uses of electronic health record data: Benefits and barriers. Jt Comm J Qual Patient Saf. 2012;38:34–40. 1CrossRefPubMed

31.

Schilling LM, Kwan BM, Drolshagen CT, et al. Scalable architecture for federated translational inquiries network (SAFTINet) technology infrastructure for a distributed data network. EGEMS. 2013;1:1027. doi:10.13063/2327-9214.1027.CrossRefPubMedPubMedCentral

32.

Observational Medical Outcomes Partnership. CDM and Standard Vocabularies (Version 4). 2012. http://omop.org/VocabV4.5. Accessed 1 Apr 2013.

33.

Schema Matching and Mapping. http://www.springer.com/computer/database+management+%26+information+retrieval/book/978-3-642-16517-7. Accessed 25 May 2014.

34.

Fagin R. Inverting Schema Mappings. In: Proceedings of the twenty-fifth ACM SIGMOD-SIGACT-SIGART symposium on principles of database systems. New York: ACM; 2006. p. 50–9. doi:10.1145/1142351.1142359.CrossRef

35.

Häyrinen K, Saranto K, Nykänen P. Definition, structure, content, use and impacts of electronic health records: a review of the research literature. Int J Med Inf. 2008;77:291–304. doi:10.1016/j.ijmedinf.2007.09.001.CrossRef

36.

Madhavan J, Jeffery SR, Cohen S, et al. Web-scale data integration: you can only afford to pay as you go. In: In Proc. of CIDR-07; 2007.

37.

Davidson SB, Overton C, Buneman P. Challenges in integrating biological data sources. J Comput Biol. 1995;2:557–72. doi:10.1089/cmb.1995.2.557.CrossRefPubMed

38.

Taniar D, Chen L, editors. Integrations of Data Warehousing, Data Mining and Database Technologies: Innovative Approaches. IGI Global 2011. http://www.igi-global.com/chapter/survey-extract-transform-load-technology/53076. Accessed 24 Sep 2014.

39.

Stang PE, Ryan PB, Racoosin JA, et al. Advancing the science for active surveillance: rationale and design for the Observational Medical Outcomes Partnership. Ann Intern Med. 2010;153:600–6. doi:10.1059/0003-4819-153-9-201011020-00010.CrossRefPubMed

40.

Rothstein MA. Currents in contemporary ethics. Research privacy under HIPAA and the common rule. J Law Med Ethics. 2005;33:154–9.CrossRefPubMed

41.

Nass SJ, Levit LA, Gostin LO, et al. Beyond the HIPAA privacy rule: enhancing privacy, improving health through research. Washington, D.C.: National Academies Press; 2009. https://www.nap.edu/read/12458/chapter/1 CrossRef

42.

Ness RB. Influence of the HIPAA privacy rule on health research. JAMA. 2007;298:2164–70. doi:10.1001/jama.298.18.2164.CrossRefPubMed

43.

Herdman R, Moses HL, United States, et al. Effect of the HIPAA privacy rule on health research: Proceedings of a workshop presented to the National Cancer Policy Forum. Washington D.C.: National Academies Press; 2006.

44.

Selker HP, Pienta KJ. The importance of proposed changes in the ‘Common Rule’ for clinical and translational researchers. Clin Transl Sci. 2011;4:312–3. doi:10.1111/j.1752-8062.2011.00352.x.CrossRefPubMedPubMedCentral

45.

Wyatt L, Caufield B, Pol D. Principles for an ETL Benchmark. In: Nambiar R, Poess M, eds. Performance Evaluation and Benchmarking. Springer Berlin Heidelberg 2009. 183–98.https://link.springer.com/book/10.1007%2F978-3-642-10424-4. Accessed 1 Dec 2014.

46.

Sills MR, Kwan BM, Yawn BP, et al. Medical home characteristics and asthma control: a prospective, observational cohort study protocol. EGEMS Wash DC. 2013;1:1032. doi:10.13063/2327-9214.1032.PubMedPubMedCentral

47.

Kwan BM, Sills MR, Graham D, et al. Stakeholder Engagement in a Patient-Reported Outcomes (PRO) Measure Implementation: A Report from the SAFTINet Practice-based Research Network (PBRN). J Am Board Fam Med JABFM. 2016;29:102–15. doi:10.3122/jabfm.2016.01.150141.

48.

Kahn MG, Raebel MA, Glanz JM, et al. A pragmatic framework for single-site and multisite data quality assessment in electronic health record-based clinical research. Med Care. 2012;50(Suppl):S21–9. doi:10.1097/MLR.0b013e318257dd67.CrossRefPubMed

49.

Legris P, Ingham J, Collerette P. Why do people use information technology? A critical review of the technology acceptance model. Inf Manag. 2003;40:191–204. doi:10.1016/S0378-7206(01)00143-4.CrossRef

50.

Fagin R, Haas LM, Hernández M, et al. Clio: Schema Mapping Creation and Data Exchange. In: Borgida AT, Chaudhri VK, Giorgini P, et al., eds. Conceptual Modeling: Foundations and Applications. Springer Berlin Heidelberg 2009. 198–236.http://link.springer.com/chapter/10.1007/978-3-642-02463-4_12. Accessed 11 Jun 2014.

Title: Dynamic-ETL: a hybrid approach for health data extraction, transformation and loading
Authors: Toan C. Ong
Michael G. Kahn
Bethany M. Kwan
Traci Yamashita
Elias Brandt
Patrick Hosokawa
Chris Uhrich
Lisa M. Schilling
Publication date: 01-12-2017
Publisher: BioMed Central
Published in: BMC Medical Informatics and Decision Making / Issue 1/2017
Electronic ISSN: 1472-6947
DOI: https://doi.org/10.1186/s12911-017-0532-3

At a glance: The ONWARDS insulin icodec trials

Springer Medicine

Dynamic-ETL: a hybrid approach for health data extraction, transformation and loading

Abstract

Background

Methods

Results

Conclusions

At a glance: The ONWARDS insulin icodec trials

Springer Medicine

Abstract

Background

Methods

Results

Conclusions

Please log in to get access to this content

Other articles of this Issue 1/2017

Erratum to: development and testing of a mobile application to support diabetes self-management for people with newly diagnosed type 2 diabetes: a design thinking case study

Unsatisfactory colposcopy: clinical decision-making in conditions of uncertainty

Development and testing of a mobile application to support diabetes self-management for people with newly diagnosed type 2 diabetes: a design thinking case study

Erratum to: Implementing 360° Quantified Self for childhood obesity: feasibility study and experiences from a weight loss camp in Qatar

Mobile Technology for Community Health in Ghana: what happens when technical functionality threatens the effectiveness of digital health programs?

Usability of data integration and visualization software for multidisciplinary pediatric intensive care: a human factors approach to assessing technology