INSERT INTO cdm_source (
cdm_source_name,
cdm_source_abbreviation,
cdm_holder,
source_description,
cdm_etl_reference,
source_release_date,
cdm_release_date,
cdm_version,
cdm_version_concept_id,
vocabulary_version
) VALUES (
'Bệnh viện Bạch Mai - Hệ thống HIS',
'BACHMAI_HIS',
'Bệnh viện Bạch Mai',
'Dữ liệu EMR từ hệ thống HIS Bệnh viện Bạch Mai, '
|| 'bao gồm khám ngoại trú và nội trú từ 2020-2024. '
|| 'Chuyển đổi theo OMOP CDM 5.4 phục vụ nghiên cứu '
|| 'dịch tễ học lâm sàng.',
'https://github.com/bachmai-etl/omop-cdm',
'2024-06-30', -- Ngày xuất dữ liệu nguồn
'2024-09-15', -- Ngày hoàn tất ETL
'v5.4',
756265, -- CDM v5.4 concept_id
'v5.0 30-AUG-24' -- Vocabulary version từ Athena
);
1.3。為什麼 CDM_SOURCE 很重要？
可追溯性：知道資料從哪裡來，何時ETL
網路研究：比較站點之間的結果
再現性：再現研究結果
合規性：合規性審核
2. 元資料 — 附加資訊
2.1。表結構
專欄
類型
必填
說明
metadata_id
整數
✅ PK
唯一ID
metadata_concept_id
整數
✅
型元資料（FK → 概念）
metadata_type_concept_id
整數
✅
類型元資料
name
VARCHAR(250)
✅
按鍵名稱
value_as_string
VARCHAR(250)
文字值
value_as_concept_id
整數
概念價值
value_as_number
浮動
數值
metadata_date
日期
記錄日期
metadata_datetime
日期時間
記錄日期時間
2.2。使用範例
-- Ghi nhận thông tin ETL
INSERT INTO metadata VALUES (1, 0, 0, 'ETL_TOOL', 'WhiteRabbit + RabbitInAHat', NULL, NULL, '2024-09-15', NULL);
INSERT INTO metadata VALUES (2, 0, 0, 'ETL_VERSION', '1.2.0', NULL, NULL, '2024-09-15', NULL);
INSERT INTO metadata VALUES (3, 0, 0, 'SOURCE_PATIENT_COUNT', NULL, NULL, 125000, '2024-09-15', NULL);
INSERT INTO metadata VALUES (4, 0, 0, 'CDM_PATIENT_COUNT', NULL, NULL, 118500, '2024-09-15', NULL);
INSERT INTO metadata VALUES (5, 0, 0, 'MAPPING_COVERAGE_PCT', NULL, NULL, 94.8, '2024-09-15', NULL);
INSERT INTO metadata VALUES (6, 0, 0, 'COUNTRY', 'Vietnam', NULL, NULL, '2024-09-15', NULL);
METADATA 是一個靈活的鍵值表 — 用於儲存任何不適合 CDM_SOURCE 的資訊。
3. COHORT — 研究團隊
3.1。表結構
專欄
類型
必填
說明
cohort_definition_id
整數
✅
FK → 佇列定義
subject_id
整數
✅
實體 ID（通常 = person_id）
cohort_start_date
日期
✅
隊列進入日期
cohort_end_date
日期
✅
同類產品發布日期
3.2。 COHORT_DEFINITION（回憶第 16 課）
專欄
類型
說明
cohort_definition_id
整數PK
定義 ID
cohort_definition_name
VARCHAR(255)
群組名稱
cohort_definition_description
CLOB
描述
definition_type_concept_id
整數
型別
cohort_definition_syntax
CLOB
建立群組的邏輯
subject_concept_id
整數
對象
cohort_initiation_date
日期
建立日期
3.3。使用方法：建立第 2 型糖尿病隊列
-- Bước 1: Định nghĩa cohort
INSERT INTO cohort_definition (
cohort_definition_id,
cohort_definition_name,
cohort_definition_description,
definition_type_concept_id,
cohort_definition_syntax,
subject_concept_id,
cohort_initiation_date
) VALUES (
101,
'Tiểu đường Type 2 mới phát hiện 2023',
'BN có chẩn đoán T2DM lần đầu trong 2023, '
|| 'có ít nhất 365 ngày observation trước đó, '
|| 'không có T1DM.',
0,
'{
"PrimaryCriteria": {
"CriteriaList": [{
"ConditionOccurrence": {
"CodesetId": 201826
}
}],
"ObservationWindow": {"PriorDays": 365}
},
"ExclusionCriteria": [{
"ConditionOccurrence": {
"CodesetId": 201254
}
}]
}',
0,
'2024-09-15'
);
-- Bước 2: Populate cohort
INSERT INTO cohort (
cohort_definition_id,
subject_id,
cohort_start_date,
cohort_end_date
)
SELECT
101 AS cohort_definition_id,
co.person_id AS subject_id,
MIN(co.condition_start_date) AS cohort_start_date,
COALESCE(
(SELECT MAX(op.observation_period_end_date)
FROM observation_period op
WHERE op.person_id = co.person_id),
MIN(co.condition_start_date)
) AS cohort_end_date
FROM condition_occurrence co
JOIN concept_ancestor ca
ON co.condition_concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 201826 -- Type 2 DM
AND co.condition_start_date BETWEEN '2023-01-01' AND '2023-12-31'
-- Phải có 365 ngày observation trước
AND EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = co.person_id
AND op.observation_period_start_date
<= co.condition_start_date - INTERVAL '365 days'
)
-- Loại trừ T1DM
AND NOT EXISTS (
SELECT 1 FROM condition_occurrence co2
JOIN concept_ancestor ca2
ON co2.condition_concept_id = ca2.descendant_concept_id
WHERE ca2.ancestor_concept_id = 201254 -- Type 1 DM
AND co2.person_id = co.person_id
AND co2.condition_start_date <= co.condition_start_date
)
GROUP BY co.person_id;
3.4。隊列分析
-- Tổng quan cohort T2DM 2023
SELECT
cd.cohort_definition_name,
COUNT(DISTINCT c.subject_id) AS patient_count,
AVG(p.year_of_birth) AS avg_birth_year,
ROUND(
SUM(CASE WHEN p.gender_concept_id = 8507 THEN 1 ELSE 0 END)
* 100.0 / COUNT(*), 1
) AS male_pct
FROM cohort c
JOIN cohort_definition cd
ON c.cohort_definition_id = cd.cohort_definition_id
JOIN person p ON c.subject_id = p.person_id
WHERE c.cohort_definition_id = 101
GROUP BY cd.cohort_definition_name;
┌──────────────────────────────────────────┐
│ Data Quality Dashboard (DQD) │
│ │
│ Kiểm tra 3500+ rules: │
│ │
│ 1. Completeness — Đầy đủ │
│ Bao nhiêu % records có concept != 0? │
│ │
│ 2. Conformance — Tuân thủ │
│ Giá trị có hợp lệ? (date, range) │
│ │
│ 3. Plausibility — Hợp lý │
│ Trẻ 5 tuổi có chẩn đoán Alzheimer? │
│ │
│ Output: Bảng báo cáo PASS/FAIL │
│ cho từng rule │
└──────────────────────────────────────────┘
5.2。使用 SQL 快速檢查
-- Mapping completeness: % records có concept_id != 0
SELECT
'condition_occurrence' AS table_name,
COUNT(*) AS total,
SUM(CASE WHEN condition_concept_id = 0 THEN 1 ELSE 0 END) AS unmapped,
ROUND(
SUM(CASE WHEN condition_concept_id != 0 THEN 1 ELSE 0 END)
* 100.0 / COUNT(*), 1
) AS mapped_pct
FROM condition_occurrence
UNION ALL
SELECT 'drug_exposure', COUNT(*),
SUM(CASE WHEN drug_concept_id = 0 THEN 1 ELSE 0 END),
ROUND(SUM(CASE WHEN drug_concept_id != 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM drug_exposure
UNION ALL
SELECT 'procedure_occurrence', COUNT(*),
SUM(CASE WHEN procedure_concept_id = 0 THEN 1 ELSE 0 END),
ROUND(SUM(CASE WHEN procedure_concept_id != 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM procedure_occurrence
UNION ALL
SELECT 'measurement', COUNT(*),
SUM(CASE WHEN measurement_concept_id = 0 THEN 1 ELSE 0 END),
ROUND(SUM(CASE WHEN measurement_concept_id != 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM measurement;
-- Kiểm tra orphan records
-- (records không có observation_period tương ứng)
SELECT 'condition_occurrence' AS src, COUNT(*) AS orphan_count
FROM condition_occurrence co
WHERE NOT EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = co.person_id
AND co.condition_start_date BETWEEN
op.observation_period_start_date
AND op.observation_period_end_date
)
UNION ALL
SELECT 'drug_exposure', COUNT(*)
FROM drug_exposure de
WHERE NOT EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = de.person_id
AND de.drug_exposure_start_date BETWEEN
op.observation_period_start_date
AND op.observation_period_end_date
);
6. OHDSI 工俱生態系統
工具
角色
白兔
掃描來源資料
兔子戴著帽子
設計 ETL 映射
阿兔
原始碼圖→標準概念
雅典娜
下載/搜尋字彙
阿特拉斯
建立佇列、分析、描述
WebAPI
ATLAS 後端 API
阿喀琉斯
資料庫分析與 DQD
哈迪斯
用於研究的 R 包（PLE、PLP）
資料品質儀表板
檢查資料品質
7. 下一路線
Bạn đã hoàn thành ✅
──────────────────────────────────
OMOP CDM 5.4 — 37+ bảng, 7 nhóm
ETL concepts, Vocabulary system
VN-specific mapping patterns
Bước tiếp theo 📘
──────────────────────────────────
1. Thực hành ETL
→ Dùng WhiteRabbit + RabbitInAHat
→ Chuyển 1 bộ dữ liệu nhỏ sang OMOP
2. ATLAS & Cohort Building
→ Cài ATLAS + WebAPI
→ Tạo cohort definitions UI
3. Achilles + DQD
→ Chạy database profiling
→ Kiểm tra chất lượng dữ liệu
4. Nghiên cứu với HADES
→ Population Level Estimation
→ Patient Level Prediction
→ Characterization
5. Tham gia cộng đồng OHDSI
→ forums.ohdsi.org
→ OHDSI Symposium hàng năm
→ Study-a-thon
