Week 10: Data Warehousing & Star Schema Design¶

         OLTP Source Systems                    Data Warehouse
    ┌──────────────────────┐               ┌─────────────────────┐
    │  Hospital EHR        │──┐            │                     │
    └──────────────────────┘  │   ETL      │   Star Schema       │
    ┌──────────────────────┐  ├───────────►│                     │
    │  Billing System      │──┤  Extract   │  ┌──────────┐       │
    └──────────────────────┘  │  Transform │  │  FACT    │       │──► Reports
    ┌──────────────────────┐  │  Load      │  │  table   │       │──► Dashboards
    │  Scheduling App      │──┘            │  └────┬─────┘       │──► ML Models
    └──────────────────────┘               │  ┌────┼────┐        │
                                           │  DIM  DIM  DIM      │
                                           └─────────────────────┘

                        ┌─────────────────┐
                        │   dim_date      │
                        │─────────────────│
                        │ date_key     PK │
                        │ full_date       │
                        │ day_of_week     │
                        │ month, quarter  │
                        │ year            │
                        │ is_holiday      │
                        └────────┬────────┘
                                 │
┌─────────────────┐   ┌─────────┴──────────────┐   ┌─────────────────┐
│  dim_patient    │   │   fact_appointments    │   │ dim_department  │
│─────────────────│   │────────────────────────│   │─────────────────│
│ patient_key  PK ├───┤ appointment_key    PK  ├───┤ dept_key     PK │
│ name            │   │ patient_key        FK  │   │ dept_name       │
│ age_group       │   │ doctor_key         FK  │   │ building        │
│ gender          │   │ date_key           FK  │   │ floor           │
│ insurance_type  │   │ dept_key           FK  │   └─────────────────┘
│ zip_code        │   │                        │
└─────────────────┘   │ ── Measures ──         │   ┌─────────────────┐
                      │ visit_count            │   │  dim_doctor     │
                      │ total_cost             │   │─────────────────│
                      │ wait_time_min          │───┤ doctor_key   PK │
                      │ prescription_count     │   │ doctor_name     │
                      └────────────────────────┘   │ specialty       │
                                                   │ hire_year       │
                                                   └─────────────────┘

CREATE TABLE dim_date (
    date_key       NUMBER PRIMARY KEY,    -- YYYYMMDD (e.g., 20260318)
    full_date      DATE NOT NULL,
    day_of_week    VARCHAR2(10),           -- 'Monday', 'Tuesday', ...
    day_of_month   NUMBER,
    month_num      NUMBER,
    month_name     VARCHAR2(10),
    quarter        NUMBER,                 -- 1, 2, 3, 4
    year           NUMBER,
    is_weekend     CHAR(1),               -- 'Y' or 'N'
    is_holiday     CHAR(1)
);

Total rows = unique values per dimension₁ × dimension₂ × ... × fill rate

Example (Hospital):
  500 doctors × 10,000 patients × 365 days × 20% fill rate
  = 365,000,000 rows/year

Size in bytes = rows × columns × avg bytes/column
  = 365M × 8 columns × 15 bytes = ~44 GB/year

STAR (recommended):                SNOWFLAKE:
┌──────────────────┐               ┌──────────────────┐
│   dim_product    │               │   dim_product    │
│──────────────────│               │──────────────────│
│ product_key   PK │               │ product_key   PK │
│ product_name     │               │ product_name     │
│ brand_name       │ ←flattened    │ brand_key      FK│──→ dim_brand
│ category_name    │ ←flattened    │ category_key   FK│──→ dim_category
│ department_name  │ ←flattened    └──────────────────┘
└──────────────────┘                        ↓ More JOINs!

  EXTRACT                TRANSFORM                    LOAD
┌──────────────┐    ┌───────────────────┐     ┌────────────────┐
│ Read from    │    │ Clean & reshape   │     │ Insert into    │
│ OLTP tables  │───►│ • Deduplicate     │────►│ star schema    │
│ (SELECT)     │    │ • Standardize     │     │ • Dims FIRST   │
└──────────────┘    │ • Derive fields   │     │ • Facts SECOND │
                    │ • Handle NULLs    │     │ • Index        │
                    │ • Map surr. keys  │     └────────────────┘
                    └───────────────────┘

-- Load a dimension table
CREATE OR REPLACE PROCEDURE load_dim_doctor AS
BEGIN
    DELETE FROM dim_doctor;

    INSERT INTO dim_doctor (doctor_key, doctor_name, specialty, department, hire_year)
    SELECT d.doctor_id,
           d.name,
           s.spec_name,        -- JOIN to denormalize
           dept.dept_name,     -- JOIN to denormalize
           EXTRACT(YEAR FROM d.hire_date)
    FROM Doctors d
    JOIN Specialties s ON d.specialty_id = s.spec_id
    JOIN Departments dept ON d.department_id = dept.dept_id;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Loaded ' || SQL%ROWCOUNT || ' rows into dim_doctor');
END;
/

-- Load the fact table (AFTER dimensions)
CREATE OR REPLACE PROCEDURE load_fact_appointments AS
BEGIN
    DELETE FROM fact_appointments;

    INSERT INTO fact_appointments (
        appointment_key, patient_key, doctor_key, date_key, dept_key,
        visit_count, total_cost
    )
    SELECT a.appt_id,
           a.patient_id,
           a.doctor_id,
           TO_NUMBER(TO_CHAR(a.appt_date, 'YYYYMMDD')),  -- date surrogate key
           d.department_id,
           1,                    -- each row = 1 visit
           a.cost
    FROM Appointments a
    JOIN Doctors d ON a.doctor_id = d.doctor_id
    WHERE a.status = 'COMPLETED';

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Loaded ' || SQL%ROWCOUNT || ' facts');
END;
/

-- Master ETL: run everything in order
CREATE OR REPLACE PROCEDURE run_full_etl AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== Starting ETL ===');
    load_dim_date;           -- dimensions first!
    load_dim_patient;
    load_dim_doctor;
    load_dim_department;
    load_fact_appointments;  -- facts last (need dimension FKs)
    DBMS_OUTPUT.PUT_LINE('=== ETL Complete ===');
END;
/

SELECT NVL(d.dept_name, '** ALL **') AS department,
       NVL(doc.doctor_name, '** All Doctors **') AS doctor,
       COUNT(*) AS appointments,
       SUM(f.total_cost) AS revenue
FROM fact_appointments f
JOIN dim_department d ON f.dept_key = d.dept_key
JOIN dim_doctor doc ON f.doctor_key = doc.doctor_key
GROUP BY ROLLUP(d.dept_name, doc.doctor_name);

-- Top 3 doctors by revenue per department
SELECT * FROM (
    SELECT d.dept_name, doc.doctor_name, SUM(f.total_cost) AS revenue,
           RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(f.total_cost) DESC) AS rnk
    FROM fact_appointments f
    JOIN dim_department d ON f.dept_key = d.dept_key
    JOIN dim_doctor doc ON f.doctor_key = doc.doctor_key
    GROUP BY d.dept_name, doc.doctor_name
) WHERE rnk <= 3;