How to perform E.T.L? 2017.03.08 아주대학교 의료정보학과 변정현 아주대학교 의료정보학과 변정현 안녕하세요? ETL 수행 방법에 대해 설명 해드릴 아주대학교 의료정보학과 변정현 입니다.

Extraction Transforming Load E.T.L

OMOP CDM 구축 과정 ETL process OMOP CDM v5 파악 및 환경설정 변환된 데이터 품질 관리 7 1 2 4 5 6 7 3 ETL process OMOP CDM v5 파악 및 환경설정 Vocabularies 다운로드 변환 가능한 데이터 파악 및 결정 데이터 품질 검토 ETL 문서 작성 CDM 변환 SQL Query 작성 및 수행 변환된 데이터 품질 관리

1) OMOP CDM v5 파악 및 환경설정 OMOP CDM 구조 파악

1) OMOP CDM v5 파악 및 환경설정 Local data expert CDM expert Data engineer 팀 구성 Members of the team CDM expert Local data expert Data engineer Person with medical knowledge

구분 요구사항 1 S/W MS-SQL 2 용어 SNOMED CT 3 권장 서버사양의 PC or 서버군 제품 2 SSD 256GB 이상 3 자체산정 HDD 4TB 이상 (데이터를 위한 충분한 용량 필요함), 연산속도를 위해서는 intel SSD 1.2TB PCIE 타입 2개 이상 추천 4 RAM 128GB 이상 5 DVD-RW 구분 요구사항 1 서버용 SQL Server 2012 Standard or Enterprise (Management Studio 포함) 2 JDK8 3 웹서비스용 Apache Tomcat 7 4 분석용 Windows 7 5 R (version 3.3 이상 권장) 6 Rstudio 7 Rtools (R version과 호환 여부 확인) 구분 요구사항 1 S/W MS-SQL 2 용어 SNOMED CT 3 매핑 용어 라이선스 (National medical terms for insurance to OMOP concept ID mapping table)

1) OMOP CDM v5 파악 및 환경설정 하드웨어 & 소프트웨어 스펙 Server Type: DELL POWEREDGE R730XD Processors: INTEL XEON E5-2667V4 3.2GHZ x 2 CPU Memory: 768GB, 24 x 32GB PC4-17000P DDR4-2133 REGISTERED ECC MEMORY Hard Drive:  18 X DELL 1.6TB MLC SAS III SSD 2.5 INCH ENTERPRISE CLASS 12GB/S SSD + 2 x DELL 300GB 10K SAS 2.5" HDD Raid Controller: PERC H730 1GB CACHE 12GB/S RAID CONTROLLER Management: IDRAC8 ENTERPRISE Networking: Dell Intel 2 x 10GBE & 2 x 1GBE Rack Network Daughter Card - 99GTM Power Supply: DUAL 1100W POWER SUPPLY OS: Windows server 2012 standard DB: MSSQL 2016 standard 아주대학교 의료정보학과 Server

2) Vocabularies 다운로드 OHDSI ATHENA에서 vocabularies 를 다운로드 후, DB에 적재 ③ ① ② ③ 작성 후 submit 버튼 클릭 하면 메일로 zip파일을 받을 수 있음 ④ 다운로드 받은 zip 파일 압출풀기 후 내용 검토 ⑤ MS SQL에 데이터 import

3) 변환 가능한 데이터 파악 및 결정 기관에서 CDM으로 변환 가능한 임상데이터파악을 위해 로컬 데이터 전문가들과 논의 후, 변환하고자 하는 데이터 결정 Table_Specification

4) 데이터 품질 검토 (1) White Rabbit ETL 수행 전 원본 데이터에 대한 특징 파악 원본 데이터의 각 테이블 및 컬럼의 속성 정보 분석 각 테이블의 데이터 분포 및 빈도수 분석 결과를 파일로 제공

4) 데이터 품질 검토 (2) 환자 정보 테이블 외래 내역 테이블1 테이블2 테이블3 테이블4 WhiteRabbit http://www.ohdsi.org/web/wiki/doku.php?id=documentation:software:whiterabbit

5) ETL 문서 작성 (1) Transformation을 위한 Rule, 컬럼 속성, 유의사항 등을 기록한 Extract, Transform and Load (ETL) specification 문서 작성 제일 중요한 과정임!!!

5) ETL 문서 작성 (1) Rabbit In A Hat 원본 데이터의 테이블과 CDM 테이블 간의 ETL 정의서 제작 지원 ETL 정의서 작성 전 UI를 통해 테이블 간 매핑 및 컬럼 간 매핑 정의 White Rabbit 수행 결과로 생성된 분석 정보를 활용

* Rabbit In a Hat

6) CDM 변환 SQL Query 작성 및 수행 Sprint 0 Sprint 1 Sprint 2 Sprint 3 Import Clinical data Location Care_site Provider Person Visit_occurrence Death Specimen Procedure Device_exposure Drug_exposure Condition_ occurrence Measurement Note Observation Payer_plan_period Cost Drug Era Dose Era Condition Era Observation Period Sprint 0 Sprint 1 Sprint 2 Sprint 3 Sprint 4

6) CDM 변환 SQL Query 작성 및 수행 작성된 ETL specification 문서에 맞춰 공통데이터모델(CDM) 데이터 변환 CODE 작성 (SQL Query) Era, period SQL query는 OHDSI에서 제공해줌

* 작성 및 이슈사항 해결 방법 작성된 문서 검토 및 문제점 발생 시 해결을 위해 2주에 한번씩 전체 회의를 진행함 임상전문가(MD)와 데이터 전문가(정보관리팀)와 상의 OHDSI Forum에 질의  테이블 별로 이슈리스트를 작성하였고 결정사항이나 해결방안에 대해 정리함

작성된 문서 검토 및 문제점 발생 시 해결을 위해 2주에 한번씩 전체 회의 진행

임상전문가(MD)와 데이터 전문가(정보관리팀)와 상의 예시 ) Drug_exposure  삭제해야 할 약처방 데이터 상의 외래 DCYN = ‘Y’ 처방발생구분 not in ( ’N’, ‘P’) ‘P’  수술실, 검사실 추가처방, 건진, 직업환경의학실에서 처방된 추가처방임 / N : 원 처방 ‘R’ : 반납처방/ ‘T’ : 수탁처방/ ’D’,’B’ : 불출 전,후 DC처방 약구분= ’4’ 인 것 제외 (임상시험약 제외) PRN처방 = ‘Y’ 인 것 제외 PREORDYN = ‘Y’ 인 것 제외 항암제를 미리 만들기 위해서 선처방한 것임, 이중처방이 되기 때문에 제외시켜야 함

OHDSI Forum에 질의 예시1) Measurement  숫자가 아닌 값 SELECT 결과값, COUNT(결과값) AS CNT FROM 통합검사결과테이블 WHERE ISNUMERIC(결과값) = 0 AND 결과값 IS NOT NULL GROUP BY 결과값 ORDER BY CNT DESC 검색단어 총 건수 측정값 종류 %소견% 581,306 6 %<% 771,840 259 %이상% 56,775 440 %이하% 352,103 153 %>% 73,106 188 BELOW% 38 5 Elevated% 62 48 Equivocal% 3,844 46 N% 18,032,125 1,649 N(% 43,765 988 Neg% 16,858,592 529 검색단어 총 건수 측정값 종류 POSI% 120,042 790 P% 1,382,426 79,251 P(% 261,115 70,590 P (% 220 %1:% 66,632 468 %1 :% 140 24 %:1 8,528 947 Pos(+) 7,525 1 Pos(++) 2,368 Pos(+++) 1,479 Pos(++++) 1,057

OHDSI Forum에 질의 예시) Measurement  숫자가 아닌 값 SLTRSTMT RSLTNUM %소견% 581,306 6 %<% 771,840 259 %이상% 56,775 440 %이하% 352,103 153 %>% 73,106 188 BELOW% 38 5 Elevated% 62 48 Equivocal% 3,844 46 N% 18,032,125 1,649 N (% - N(% 43,765 988 Neg% 16,858,592 529 SLTRSTMT RSLTNUM POSI% 120,042 790 P% 1,382,426 79,251 P(% 261,115 70,590 P (% 220 %1:% 66,632 468 %1 :% 140 24 %:1 8,528 947 Pos(+) 7,525 1 Pos(++) 2,368 Pos(+++) 1,479 Pos(++++) 1,057

Measurement 1:N concept id For antibody titers do the following: measurement_concept_id : the LOINC concept for the antibody you are looking at operator_concept_id: stays empty (which is the equivalent of = ) value_as_number: 0.163934426 (which is 1 divided by 6.1) value_as_concept_id: stays empty. This is only for things like "positive", "negative", etc. unit_concept_id: 8525 (stands for {titer}, which is really a unitless unit)

7) 변환된 데이터 품질 관리 ETL 전후 비교 Post ETL I Post ETL II Achilles Heel 원본 데이터로부터 변환된 데이터의 양에 대한 평가 Post ETL I 변환된 데이터 값의 범위, 평균 등을 통해 이상값 검토 Post ETL II OMOP CDM 변환기준 기반 데이터 적합성 정성적 평가 Achilles Heel 데이터 간의 논리적 오류 검토 결과를 바탕으로 오류수정 Frequent concepts 특정 개념에 대한 빈도를 타기관 또는 기존지식과 비교