BI 3 — Mô hình hoá chiều: Fact, Dimension, Star & SCD

Vì sao bài này là xương sống của BI

Nếu bạn chỉ học một thứ về kho dữ liệu phân tích, hãy học mô hình hoá chiều (dimensional modeling). Đây là phương pháp do Ralph Kimball đề xướng, và nó là cách tổ chức dữ liệu phổ biến nhất cho báo cáo, dashboard và phân tích trong ngành ngân hàng, bán lẻ, viễn thông — gần như mọi nơi.

Bài này đi từ con số 0: tại sao mô hình quan hệ chuẩn hoá (3NF) — thứ rất tốt cho hệ thống giao dịch — lại không tối ưu cho phân tích; rồi xây dần lên fact table, dimension table, star schema, các loại measure, và đỉnh điểm là Slowly Changing Dimension (SCD) — chỗ mà rất nhiều người làm sai.

Mọi ví dụ sẽ xoay quanh một bài toán ngân hàng quen thuộc: phân tích giao dịch tài chính.

Ôn lại: 3NF tốt cho cái gì, dở cho cái gì

Hệ thống vận hành (OLTP) — ví dụ core banking — được thiết kế theo chuẩn hoá bậc 3 (Third Normal Form, 3NF). Nguyên tắc của 3NF là loại bỏ dữ liệu trùng lặp: mỗi sự thật chỉ được lưu ở đúng một chỗ. Tên chi nhánh nằm ở bảng chi_nhanh, tài khoản chỉ giữ chi_nhanh_id. Nhờ vậy khi đổi tên chi nhánh, ta sửa đúng một dòng.

3NF tối ưu cho ghi: thêm/sửa/xoá nhanh, nhất quán, ít rủi ro mâu thuẫn dữ liệu. Đó là lý do mọi hệ thống giao dịch đều dùng nó.

Nhưng phân tích (OLAP) lại có nhu cầu đọc hoàn toàn khác:

• Một câu hỏi phân tích như "tổng giá trị giao dịch theo loại sản phẩm, theo tỉnh, theo quý" sẽ phải JOIN qua 6–10 bảng trong mô hình 3NF.
• Người dùng nghiệp vụ (không phải kỹ sư) không hiểu nổi sơ đồ 3NF với hàng trăm bảng chằng chịt.
• Mỗi JOIN là một phép tốn kém; trên hàng tỷ dòng giao dịch, truy vấn 8-bảng-JOIN có thể chạy hàng phút.

Mô hình chiều giải bài toán này bằng cách đánh đổi: chấp nhận lặp dữ liệu (denormalize) để giảm số JOIN, tăng tốc đọc, và dễ hiểu với con người. Trong kho phân tích, dữ liệu thường chỉ nạp theo lô (batch) và hiếm khi update tại chỗ, nên nhược điểm của việc lặp dữ liệu gần như không đáng kể.

Quy tắc ghi nhớ: OLTP → 3NF (tối ưu ghi). OLAP → dimensional (tối ưu đọc).

Hai loại bảng: Fact và Dimension

Mô hình chiều chia mọi thứ trên đời thành đúng hai loại bảng.

Fact table — nơi chứa "số đo"

Fact table chứa các đo lường (measures) của một quá trình nghiệp vụ, cộng với các khoá ngoại (foreign keys) trỏ tới các dimension. Đặc điểm:

• Rất dài (nhiều dòng — hàng triệu, hàng tỷ) nhưng hẹp (ít cột).
• Cột chủ yếu là số đo định lượng: số tiền, số lượng, phí, số dư.
• Cột còn lại là các FK dạng số nguyên nhỏ trỏ tới dimension.
• Hầu như không chứa thuộc tính mô tả dạng text (text nằm ở dimension).

Trong ví dụ ngân hàng, fact_giao_dich lưu mỗi giao dịch là một dòng, với measure so_tien, phi, và các FK khach_hang_key, tai_khoan_key, thoi_gian_key, chi_nhanh_key.

Dimension table — nơi chứa "ngữ cảnh"

Dimension table chứa các thuộc tính mô tả (attributes) — trả lời các câu hỏi ai, cái gì, ở đâu, khi nào, như thế nào. Đặc điểm:

• Ngắn hơn nhiều (ít dòng) nhưng rộng (nhiều cột mô tả).
• Đầy text: tên khách hàng, nhóm tuổi, tỉnh, loại sản phẩm, tên chi nhánh.
• Dùng làm điều kiện lọc (filter), nhóm (group by) và gắn nhãn (label) trên báo cáo.

Các dimension điển hình của ngân hàng: dim_khach_hang, dim_tai_khoan, dim_thoi_gian, dim_chi_nhanh.

Grain — khái niệm quan trọng nhất, hay bị bỏ qua

Grain (hạt mức) là định nghĩa một dòng trong fact table đại diện cho cái gì. Đây là quyết định đầu tiên và quan trọng nhất khi thiết kế.

Với fact_giao_dich, grain là: một dòng = một giao dịch tài chính. Rõ ràng, nguyên tử, không mơ hồ.

Vì sao grain quan trọng:

1. Grain quyết định measure nào hợp lệ. Nếu grain là "một giao dịch", thì so_tien của mỗi giao dịch là cộng được. Nếu lỡ trộn hai grain khác nhau (giao dịch + số dư cuối ngày) vào cùng bảng, mọi phép SUM sẽ sai.
2. Grain nên càng mịn (atomic) càng tốt. Lưu ở mức giao dịch nguyên tử cho phép tổng hợp lên bất kỳ mức nào (ngày, tháng, tỉnh) sau này. Nếu lưu sẵn ở mức "tổng theo ngày", bạn vĩnh viễn mất khả năng khoan sâu (drill-down) xuống từng giao dịch.
3. Mọi dimension gắn vào fact phải nhất quán với grain. Mỗi giao dịch có đúng một khách hàng, một tài khoản, một thời điểm, một chi nhánh — nên các FK này hợp lệ.

Nguyên tắc Kimball: luôn thiết kế ở grain nguyên tử nhất có thể. Bảng tổng hợp (aggregate) chỉ là phụ trợ tốc độ, không thay thế bảng nguyên tử.

Star Schema — ngôi sao kinh điển

Khi đặt một fact ở giữa, bao quanh bởi các dimension nối trực tiếp bằng FK, ta được Star Schema (lược đồ hình sao) — hình dạng giống một ngôi sao.

Đây là sơ đồ star schema cho bài toán giao dịch ngân hàng:

Đặc trưng của star schema:

• Phẳng, một tầng: từ fact đến bất kỳ thuộc tính mô tả nào chỉ qua đúng một JOIN.
• Dễ hiểu: người nghiệp vụ nhìn vào hiểu ngay "đo gì, cắt theo chiều nào".
• Nhanh: ít JOIN, các công cụ BI và tối ưu hoá truy vấn rất "ưa" hình dạng này.

Snowflake Schema — bông tuyết

Nếu ta chuẩn hoá tiếp các dimension — ví dụ tách tinh_thanh ra thành bảng dim_tinh riêng và cho dim_chi_nhanh trỏ tới nó — thì dimension không còn phẳng mà phân nhánh thành nhiều tầng, trông như bông tuyết (snowflake).

Bảng so sánh Star vs Snowflake

Tóm lại: mặc định chọn star. Chỉ snowflake một dimension khi nó quá lớn và có cấu trúc phân cấp rõ ràng cần tái sử dụng ở nhiều nơi. Lưu trữ ngày nay rẻ; tốc độ đọc và sự dễ hiểu mới là thứ đắt.

Phân loại measure: additive / semi-additive / non-additive

Không phải measure nào cũng cộng được theo mọi chiều. Phân biệt ba loại là kỹ năng then chốt để không tính sai số.

1. Additive (cộng được theo mọi chiều)

Cộng được qua tất cả các dimension, kể cả thời gian. Ví dụ: so_tien giao dịch, phi. Bạn có thể SUM theo ngày, theo khách hàng, theo chi nhánh — đều đúng. Đây là loại đẹp nhất, chiếm đa số.

2. Semi-additive (cộng được theo một số chiều, trừ thời gian)

Cộng được theo các chiều khác nhưng không cộng được theo thời gian. Ví dụ kinh điển trong ngân hàng: số dư tài khoản (balance).

• Cộng số dư của 100 tài khoản tại một thời điểm → hợp lệ (tổng tài sản).
• Cộng số dư cuối ngày của 30 ngày trong tháng → vô nghĩa. Số dư là một "snapshot" tại thời điểm, không phải dòng chảy. Theo thời gian, ta phải lấy giá trị cuối kỳ (last) hoặc trung bình, không SUM.

3. Non-additive (không cộng được theo chiều nào)

Không SUM được theo bất kỳ chiều nào — thường là tỷ lệ, phần trăm, đơn giá. Ví dụ: tỷ lệ nợ xấu, lãi suất, tỷ giá. Cộng hai phần trăm với nhau không có ý nghĩa.

Mẹo thiết kế: với measure non-additive, đừng lưu sẵn tỷ lệ. Hãy lưu tử số và mẫu số dưới dạng additive (ví dụ du_no_xau và tong_du_no), rồi tính tỷ lệ ở tầng truy vấn: SUM(du_no_xau) / SUM(tong_du_no). Như vậy mới tổng hợp đúng.

Slowly Changing Dimension (SCD) — xử lý lịch sử thay đổi

Đây là phần "hero" của bài. Thuộc tính dimension thay đổi theo thời gian một cách chậm rãi: khách hàng chuyển tỉnh, đổi phân khúc từ "Mass" lên "Priority", chi nhánh đổi tên. Câu hỏi cốt lõi: khi thuộc tính đổi, ta xử lý lịch sử thế nào?

Kimball định nghĩa các kỹ thuật Slowly Changing Dimension. Ba loại phổ biến nhất:

Bảng so sánh SCD Type 1 / 2 / 3

Type 1 — Ghi đè (Overwrite)

Đơn giản nhất: khi giá trị đổi, update đè lên. Lịch sử biến mất. Dùng khi giá trị cũ không có ý nghĩa phân tích (sửa lỗi gõ sai tên), hoặc khi nghiệp vụ chỉ quan tâm trạng thái hiện tại.

Nhược điểm: nếu khách hàng chuyển từ tỉnh A sang tỉnh B, mọi giao dịch trong quá khứ sẽ bị quy về tỉnh B — báo cáo doanh số theo tỉnh của các năm trước bỗng sai lệch.

Type 2 — Lưu lịch sử bằng dòng mới (phổ biến nhất)

Khi thuộc tính đổi, ta không sửa dòng cũ mà:

1. "Đóng" dòng cũ: gán ngay_het_hieu_luc và la_hien_tai = false.
2. Thêm một dòng mới cho cùng khách hàng với giá trị mới, ngay_hieu_luc = hôm nay, ngay_het_hieu_luc = NULL (hoặc một ngày xa như 9999-12-31), la_hien_tai = true.

Điểm mấu chốt: dòng mới có surrogate key mới (khach_hang_key). Fact giao dịch trỏ tới surrogate key đúng tại thời điểm phát sinh giao dịch. Nhờ đó:

• Giao dịch năm 2024 vẫn gắn với phiên bản "khách hàng ở tỉnh A".
• Giao dịch năm 2026 gắn với phiên bản "khách hàng ở tỉnh B".
• Báo cáo lịch sử luôn đúng theo bối cảnh tại thời điểm đó.

Đây là lý do dimension dùng surrogate key (khoá thay thế, số nguyên tự sinh) thay vì dùng trực tiếp ma_khach_hang (natural/business key). Một ma_khach_hang có thể tương ứng nhiều khach_hang_key qua các phiên bản lịch sử.

Type 3 — Lưu giá trị trước đó (Previous value)

Thêm một cột để giữ đúng một giá trị cũ: ví dụ phan_khuc_hien_tai và phan_khuc_truoc. Chỉ giữ được một bước lịch sử, không phải toàn bộ. Hiếm dùng, chỉ hợp khi nghiệp vụ cần so sánh "trước/sau" một lần tái cấu trúc (ví dụ đổi cách phân vùng kinh doanh).

DDL minh hoạ cho SCD Type 2

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE dim_khach_hang (
    khach_hang_key   INT PRIMARY KEY,      -- surrogate key (tự sinh)
    ma_khach_hang    VARCHAR(20),          -- natural/business key
    ho_ten           VARCHAR(100),
    nhom_tuoi        VARCHAR(20),
    phan_khuc        VARCHAR(20),
    tinh_thanh       VARCHAR(50),
    -- các cột phục vụ SCD Type 2:
    ngay_hieu_luc    DATE NOT NULL,
    ngay_het_hieu_luc DATE,                -- NULL hoặc 9999-12-31 nếu đang hiệu lực
    la_hien_tai      BOOLEAN NOT NULL
);

-- Một khách hàng chuyển tỉnh: hai dòng, cùng ma_khach_hang, khác key
-- key=101 | KH001 | ... | An Giang | 2023-01-01 | 2025-12-31 | false
-- key=540 | KH001 | ... | Cần Thơ  | 2026-01-01 | NULL       | true

Conformed Dimension — dimension dùng chung

Khi tổ chức có nhiều fact (giao dịch, dư nợ, khiếu nại...), một conformed dimension là dimension được chia sẻ và định nghĩa nhất quán giữa các fact đó. dim_thoi_gian và dim_khach_hang thường là conformed: cùng cấu trúc, cùng key, dùng được cho mọi fact.

Lợi ích: cho phép kết hợp số liệu chéo giữa các quá trình nghiệp vụ. Ví dụ, vì cả fact_giao_dich và fact_du_no đều dùng chung dim_khach_hang, ta có thể đối chiếu "khách hàng giao dịch nhiều" với "khách hàng có dư nợ cao" mà không sợ lệch định nghĩa. Conformed dimension chính là cái "keo" liên kết toàn bộ kho dữ liệu — Kimball gọi tập hợp này là bus matrix.

Date Dimension — vì sao bắt buộc phải có

Nhiều người mới hỏi: "Ngày tháng có sẵn trong fact rồi, sao cần dim_thoi_gian riêng?" Lý do:

1. Thuộc tính lịch không nằm trong một cột date. Date thô không cho biết "quý mấy", "ngày lễ không", "thứ mấy", "tuần thứ bao nhiêu trong năm", "có phải cuối tháng tài chính không". Date dimension lưu sẵn tất cả, nên báo cáo theo quý/lễ/thứ chỉ là một phép GROUP BY, không cần hàm xử lý ngày phức tạp lặp đi lặp lại.
2. Hỗ trợ lịch nghiệp vụ riêng. Năm tài chính, kỳ kế toán, ngày làm việc của ngân hàng — những thứ không suy ra được tự động từ ngày dương lịch.
3. Hiệu năng và nhất quán. GROUP BY trên một cột số nguyên thang/quy đã được tính sẵn nhanh và đồng nhất hơn việc mỗi truy vấn tự EXTRACT(QUARTER FROM ngay).
4. Xử lý "ngày không có giao dịch". Vì date dimension liệt kê mọi ngày trong khoảng, báo cáo chuỗi thời gian sẽ hiển thị cả những ngày doanh số bằng 0 — điều mà nếu chỉ dựa vào ngày trong fact sẽ bị "nhảy cóc".

Date dimension thường được nạp sẵn một lần cho nhiều năm tới, và là dimension hiếm hoi không bao giờ cần SCD.

Tóm tắt

• 3NF tối ưu cho ghi (OLTP), mô hình chiều tối ưu cho đọc (OLAP). Phân tích chấp nhận lặp dữ liệu để đổi lấy ít JOIN, dễ hiểu, đọc nhanh.
• Fact table chứa measure + FK, dài và hẹp; dimension table chứa thuộc tính mô tả, ngắn và rộng.
• Grain là quyết định quan trọng nhất: định nghĩa một dòng fact đại diện cho cái gì; nên chọn mức nguyên tử nhất (ví dụ "một giao dịch").
• Star schema phẳng, ít JOIN, dễ hiểu — mặc định nên dùng; snowflake chuẩn hoá sâu hơn, tiết kiệm dung lượng nhưng nhiều JOIN và khó hiểu hơn.
• Measure chia thành additive (cộng mọi chiều), semi-additive (như số dư — không cộng theo thời gian), non-additive (tỷ lệ — không cộng được; nên lưu tử/mẫu rồi tính sau).
• SCD xử lý thay đổi chậm của thuộc tính: Type 1 ghi đè (mất lịch sử), Type 2 thêm dòng mới với ngày hiệu lực + surrogate key (giữ trọn lịch sử — phổ biến nhất), Type 3 giữ một giá trị trước đó.
• Conformed dimension dùng chung giữa nhiều fact để kết hợp số liệu chéo; date dimension bắt buộc có để lưu thuộc tính lịch và đảm bảo nhất quán thời gian.

Tự kiểm tra

1. Một bảng fact_giao_dich có grain "một giao dịch" — vì sao bạn KHÔNG nên thêm cột "số dư cuối ngày" vào chính bảng này?
2. Số dư tài khoản thuộc loại measure nào, và tại sao SUM số dư của 30 ngày trong tháng lại vô nghĩa?
3. Khi một khách hàng chuyển từ tỉnh A sang tỉnh B, hãy mô tả điều gì xảy ra trong dimension nếu dùng SCD Type 1 so với Type 2, và ảnh hưởng tới báo cáo doanh số theo tỉnh của năm cũ.
4. Vì sao dimension dùng surrogate key thay vì dùng trực tiếp ma_khach_hang? Liên hệ điều này với SCD Type 2.
5. Nêu hai lý do cụ thể vì sao cần dim_thoi_gian riêng thay vì chỉ dùng cột date trong fact.
6. Trong tình huống nào bạn sẽ chọn snowflake thay vì star, và bạn đánh đổi điều gì?

Đọc tiếp

BI 4 — Metric & KPI