# MySQL DW / DM ERD `hospital_dw` MySQL 스키마는 PostgreSQL `TOBE` 데이터를 받아 적재해서 분석(DW/DM) 용도로 쓰는 모델입니다. - 스테이징: `stg_tobe_*` (PostgreSQL TOBE 데이터를 먼저 적재) - DW: `dim_*`, `fact_*` (대체키(Surrogate Key) 기반 스타 스키마) - DM: `dm_*` (요약/집계 테이블, FK 미설정) 실행 순서는 `scripts/mysql/10_staging.sql` -> `scripts/mysql/11_etl_dim.sql` -> `scripts/mysql/12_etl_fact.sql` -> `scripts/mysql/13_etl_dm.sql` 입니다. ## DW (Dim/Fact) ERD ```mermaid erDiagram dim_date["dim_date
(날짜 차원)"] { int date_key PK date full_date } dim_org["dim_org
(기관 차원)"] { int org_sk PK string org_cd string org_nm } dim_dept["dim_dept
(부서 차원)"] { int dept_sk PK int org_sk FK string dept_cd string dept_nm } dim_emp["dim_emp
(직원 차원)"] { int emp_sk PK int org_sk FK int dept_sk FK string emp_no string emp_nm } dim_patient["dim_patient
(환자 차원)"] { int patient_sk PK int org_sk FK string pt_no string pt_nm } dim_payer["dim_payer
(보험자 차원)"] { int payer_sk PK string pyr_cd string pyr_nm } dim_room["dim_room
(병실 차원)"] { int room_sk PK int dept_sk FK string room_cd string room_nm } dim_diagnosis["dim_diagnosis
(진단 차원)"] { int diagnosis_sk PK string dx_cd string dx_nm } dim_medication["dim_medication
(약품 차원)"] { int medication_sk PK string med_cd string med_nm } dim_procedure["dim_procedure
(처치 차원)"] { int procedure_sk PK string proc_cd string proc_nm } dim_lab_test["dim_lab_test
(검사 항목 차원)"] { int lab_test_sk PK string tst_cd string tst_nm } dim_imaging_exam["dim_imaging_exam
(영상 검사 차원)"] { int imaging_exam_sk PK string exam_cd string exam_nm } fact_encounter["fact_encounter
(내원 팩트)"] { int encounter_fact_id PK string enc_no int org_sk FK int dept_sk FK int patient_sk FK int attending_emp_sk FK int arrive_date_key FK int close_date_key FK } fact_billing["fact_billing
(원무 수익 팩트)"] { int billing_fact_id PK string bill_no int org_sk FK int dept_sk FK int patient_sk FK int payer_sk FK int issue_date_key FK int close_date_key FK } fact_medication_order["fact_medication_order
(투약 오더 팩트)"] { int medication_fact_id PK string ord_no int org_sk FK int dept_sk FK int patient_sk FK int ordering_emp_sk FK int medication_sk FK int order_date_key FK int stop_date_key FK } fact_procedure_order["fact_procedure_order
(처치 오더 팩트)"] { int procedure_fact_id PK string ord_no int org_sk FK int dept_sk FK int patient_sk FK int ordering_emp_sk FK int procedure_sk FK int request_date_key FK int perform_date_key FK } fact_lab_result["fact_lab_result
(검사 결과 팩트)"] { int lab_result_fact_id PK string lab_ord_no int anl_seq int org_sk FK int dept_sk FK int patient_sk FK int ordering_emp_sk FK int lab_test_sk FK int order_date_key FK int result_date_key FK } fact_imaging_exam["fact_imaging_exam
(영상 검사 팩트)"] { int imaging_fact_id PK string img_ord_no int org_sk FK int dept_sk FK int patient_sk FK int ordering_emp_sk FK int imaging_exam_sk FK int schedule_date_key FK int start_date_key FK int end_date_key FK int report_date_key FK } fact_dispense["fact_dispense
(조제 팩트)"] { int dispense_fact_id PK string dsps_no string rx_ord_no int org_sk FK int dept_sk FK int patient_sk FK int medication_sk FK int pharmacist_emp_sk FK int dispense_date_key FK } dim_org ||--o{ dim_dept : has dim_org ||--o{ dim_emp : has dim_dept ||--o{ dim_emp : belongs dim_org ||--o{ dim_patient : has dim_dept ||--o{ dim_room : has dim_org ||--o{ fact_encounter : occurs dim_dept ||--o{ fact_encounter : occurs dim_patient ||--o{ fact_encounter : visits dim_emp ||--o{ fact_encounter : attends dim_date ||--o{ fact_encounter : dates dim_org ||--o{ fact_billing : billed dim_dept ||--o{ fact_billing : billed dim_patient ||--o{ fact_billing : billed dim_payer ||--o{ fact_billing : covers dim_date ||--o{ fact_billing : dates dim_org ||--o{ fact_medication_order : orders dim_dept ||--o{ fact_medication_order : orders dim_patient ||--o{ fact_medication_order : orders dim_emp ||--o{ fact_medication_order : orders dim_medication ||--o{ fact_medication_order : ordered dim_date ||--o{ fact_medication_order : dates dim_org ||--o{ fact_procedure_order : orders dim_dept ||--o{ fact_procedure_order : orders dim_patient ||--o{ fact_procedure_order : orders dim_emp ||--o{ fact_procedure_order : orders dim_procedure ||--o{ fact_procedure_order : ordered dim_date ||--o{ fact_procedure_order : dates dim_org ||--o{ fact_lab_result : results dim_dept ||--o{ fact_lab_result : results dim_patient ||--o{ fact_lab_result : results dim_emp ||--o{ fact_lab_result : orders dim_lab_test ||--o{ fact_lab_result : tests dim_date ||--o{ fact_lab_result : dates dim_org ||--o{ fact_imaging_exam : exams dim_dept ||--o{ fact_imaging_exam : exams dim_patient ||--o{ fact_imaging_exam : exams dim_emp ||--o{ fact_imaging_exam : orders dim_imaging_exam ||--o{ fact_imaging_exam : ordered dim_date ||--o{ fact_imaging_exam : dates dim_org ||--o{ fact_dispense : dispenses dim_dept ||--o{ fact_dispense : dispenses dim_patient ||--o{ fact_dispense : dispenses dim_emp ||--o{ fact_dispense : dispenses dim_medication ||--o{ fact_dispense : meds dim_date ||--o{ fact_dispense : dates ``` ## DM (집계) 테이블 DM은 조회 편의를 위한 집계 결과 테이블이며, FK 제약을 두지 않고 `scripts/mysql/13_etl_dm.sql`로 적재합니다. ## DM Lineage (논리 연결) DM은 `scripts/mysql/13_etl_dm.sql`에서 Dim/Fact(그리고 일부 스테이징)를 조인/집계해 만들며, 아래는 그 “논리적” 연결(라인리지)과 DM 테이블 구조를 함께 표시한 것입니다. ```mermaid flowchart TB subgraph SOURCE["DW / STAGE 원천"] fact_billing["
fact_billing
PK billing_fact_id
bill_no
enc_no
issue_date_key
org_sk
dept_sk
total_charge_amt
insurance_amt
patient_pay_amt
receipt_amt
claim_amt
"] fact_encounter["
fact_encounter
PK encounter_fact_id
enc_no
enc_tp_cd
arrive_date_key
close_date_key
org_sk
dept_sk
patient_sk
"] fact_medication_order["
fact_medication_order
PK medication_fact_id
ord_no
order_date_key
org_sk
dept_sk
medication_sk
qty
"] fact_procedure_order["
fact_procedure_order
PK procedure_fact_id
ord_no
request_date_key
org_sk
dept_sk
"] fact_lab_result["
fact_lab_result
PK lab_result_fact_id
lab_ord_no
anl_seq
lab_test_sk
abnormal_yn
"] fact_dispense["
fact_dispense
PK dispense_fact_id
dsps_no
rx_ord_no
medication_sk
dsps_qty
"] stg_tobe_enc["
stg_tobe_enc
PK enc_no
org_cd
dept_cd
"] stg_tobe_lab_ord["
stg_tobe_lab_ord
PK lab_ord_no
enc_no
ord_dtm
"] stg_tobe_lab_rslt["
stg_tobe_lab_rslt
PK lab_ord_no
PK anl_seq
rslt_dtm
"] dim_date["
dim_date
PK date_key
full_date
"] dim_org["
dim_org
PK org_sk
org_cd
"] dim_dept["
dim_dept
PK dept_sk
dept_cd
dept_nm
"] dim_medication["
dim_medication
PK medication_sk
med_cd
med_nm
"] dim_lab_test["
dim_lab_test
PK lab_test_sk
tst_cd
tst_nm
tst_grp_cd
rslt_unit_cd
"] end subgraph DM["Data Mart 집계 결과"] dm_daily_revenue_by_dept["
dm_daily_revenue_by_dept
PK revenue_dt
PK org_cd
PK dept_cd
dept_nm
outpatient_cnt
inpatient_cnt
total_charge_amt
insurance_amt
patient_pay_amt
receipt_amt
claim_amt
"] dm_monthly_clinical_kpi["
dm_monthly_clinical_kpi
PK yyyymm
PK org_cd
PK dept_cd
dept_nm
encounter_cnt
patient_cnt
medication_order_cnt
procedure_order_cnt
avg_order_per_encounter
"] dm_daily_lab_tat["
dm_daily_lab_tat
PK tat_dt
PK org_cd
PK dept_cd
PK test_group_cd
order_cnt
result_cnt
abnormal_cnt
avg_tat_minutes
"] dm_monthly_top_medication["
dm_monthly_top_medication
PK yyyymm
PK org_cd
PK dept_cd
PK med_cd
med_nm
order_cnt
order_qty
dispense_qty
rank_no
"] dm_monthly_patient_flow["
dm_monthly_patient_flow
PK yyyymm
PK org_cd
PK dept_cd
new_patient_cnt
revisit_patient_cnt
admission_cnt
discharge_cnt
emergency_cnt
"] end classDef dmTable fill:transparent,stroke:transparent,color:#111827 classDef sourceTable fill:transparent,stroke:transparent,color:#111827 class fact_billing,fact_encounter,fact_medication_order,fact_procedure_order,fact_lab_result,fact_dispense,stg_tobe_enc,stg_tobe_lab_ord,stg_tobe_lab_rslt,dim_date,dim_org,dim_dept,dim_medication,dim_lab_test sourceTable class dm_daily_revenue_by_dept,dm_monthly_clinical_kpi,dm_daily_lab_tat,dm_monthly_top_medication,dm_monthly_patient_flow dmTable fact_billing -->|금액 SUM| dm_daily_revenue_by_dept fact_encounter -.->|enc_no 기준 외래/입원 건수| dm_daily_revenue_by_dept dim_date -->|issue_date_key -> revenue_dt| dm_daily_revenue_by_dept dim_org -->|org_sk -> org_cd| dm_daily_revenue_by_dept dim_dept -->|dept_sk -> dept_cd/dept_nm| dm_daily_revenue_by_dept fact_encounter -->|내원/환자 COUNT| dm_monthly_clinical_kpi fact_medication_order -->|투약 오더 COUNT| dm_monthly_clinical_kpi fact_procedure_order -->|처치 오더 COUNT| dm_monthly_clinical_kpi dim_date -->|arrive/order/request date -> yyyymm| dm_monthly_clinical_kpi dim_org -->|org_sk -> org_cd| dm_monthly_clinical_kpi dim_dept -->|dept_sk -> dept_cd/dept_nm| dm_monthly_clinical_kpi stg_tobe_lab_ord -->|ord_dtm -> tat_dt, order_cnt| dm_daily_lab_tat stg_tobe_lab_rslt -->|rslt_dtm -> TAT, result_cnt| dm_daily_lab_tat fact_lab_result -->|abnormal_yn, lab_test_sk| dm_daily_lab_tat stg_tobe_enc -->|enc_no -> org_cd/dept_cd| dm_daily_lab_tat dim_lab_test -->|tst_grp_cd -> test_group_cd| dm_daily_lab_tat dim_org -->|org_cd 매핑| dm_daily_lab_tat dim_dept -->|dept_cd 매핑| dm_daily_lab_tat fact_medication_order -->|약품별 주문/수량 집계| dm_monthly_top_medication fact_dispense -.->|rx_ord_no 기준 조제 수량| dm_monthly_top_medication dim_medication -->|med_cd, med_nm| dm_monthly_top_medication dim_date -->|order_date_key -> yyyymm| dm_monthly_top_medication dim_org -->|org_sk -> org_cd| dm_monthly_top_medication dim_dept -->|dept_sk -> dept_cd| dm_monthly_top_medication fact_encounter -->|신환/재진/입원/퇴원/응급 집계| dm_monthly_patient_flow dim_date -->|arrive_date_key, close_date_key -> yyyymm| dm_monthly_patient_flow dim_org -->|org_sk -> org_cd| dm_monthly_patient_flow dim_dept -->|dept_sk -> dept_cd| dm_monthly_patient_flow ``` - `dm_daily_revenue_by_dept`: 일자/부서 단위 매출 - `dm_monthly_clinical_kpi`: 월별 진료 KPI - `dm_daily_lab_tat`: 일자별 검사 TAT - `dm_monthly_top_medication`: 월별 상위 약품 - `dm_monthly_patient_flow`: 월별 환자 흐름(신환/재진/입퇴원/응급)