Data Modeling 3 — Fact tables chuyên sâu

Fact table — trái tim đo lường của star schema

Trong bài Dimensional modeling theo Kimball chúng ta đã dựng khung: fact giữ các số đo (measure) của một sự kiện kinh doanh, dimension giữ ngữ cảnh mô tả. Bài này đào sâu riêng phía fact — nơi mà một quyết định thiết kế sai có thể khiến toàn bộ báo cáo cộng ra con số vô nghĩa (ví dụ cộng số dư tài khoản của 30 ngày để ra một con số không có ý nghĩa gì).

Trước khi nói về các loại fact, phải nhắc lại nguyên tắc số một của Kimball: grain là trung tâm của mọi bảng fact. Grain là câu trả lời chính xác cho câu hỏi "một dòng trong bảng này đại diện cho cái gì?". Grain phải được tuyên bố trước tiên, bằng ngôn ngữ nghiệp vụ, trước khi chọn dimension hay measure. "Một dòng cho mỗi giao dịch thẻ", "một dòng cho mỗi tài khoản mỗi cuối ngày", "một dòng cho mỗi hồ sơ vay" — đó là ba grain khác nhau, và chính sự khác biệt về grain sinh ra ba loại fact table kinh điển.

Quy ước: sandbox của loạt bài là PostgreSQL. Toàn bộ DDL/SQL dưới đây là (minh hoạ) — dùng để thấy cấu trúc và ý tưởng, không nhằm thực thi trực tiếp.

Ba loại fact table

Kimball xác định đúng ba loại fact table trong vùng trình bày (presentation area) của kho dữ liệu. Chúng bổ sung cho nhau chứ không loại trừ — cùng một quy trình nghiệp vụ thường được mô hình hoá bằng cả ba, mỗi loại phục vụ một câu hỏi khác nhau.

Transaction fact table

Grain là một dòng cho mỗi sự kiện đo lường tại một thời điểm. Đây là loại phổ biến và chi tiết nhất — "con dấu beep ở quầy siêu thị", một giao dịch chuyển khoản, một lần quẹt thẻ. Measure chỉ có ý nghĩa tại đúng thời điểm và đúng sự kiện đó.

Đặc điểm quan trọng:

• Bảng chỉ thêm dòng mới (insert-only), gần như không bao giờ update dòng cũ. Điều này khiến nó rất thân thiện với pipeline incremental (nối dbt incremental).
• Bảng lớn nhất trong ba loại — có thể hàng tỷ dòng. Nhưng vì mỗi dòng độc lập, nó phân vùng (partition) theo ngày rất tốt.
• Nhiều dòng có thể có measure rỗng nếu sự kiện không tạo ra đo lường — nhưng thường transaction fact luôn có ít nhất một measure (số tiền, số lượng).

-- Transaction fact: 1 dòng cho mỗi giao dịch
CREATE TABLE fact_giao_dich (
    giao_dich_sk    BIGINT PRIMARY KEY,     -- surrogate key của fact
    ngay_sk         INT     NOT NULL,        -- FK -> dim_ngay
    tai_khoan_sk    BIGINT  NOT NULL,        -- FK -> dim_tai_khoan
    khach_hang_sk   BIGINT  NOT NULL,        -- FK -> dim_khach_hang
    kenh_sk         INT     NOT NULL,        -- FK -> dim_kenh (ATM, online...)
    ma_giao_dich    VARCHAR(32) NOT NULL,    -- degenerate dimension
    so_tien         NUMERIC(18,2) NOT NULL,  -- ADDITIVE
    phi             NUMERIC(18,2) NOT NULL,  -- ADDITIVE
    thoi_diem       TIMESTAMP NOT NULL
);
-- Grain: mỗi dòng = một giao dịch. Insert-only.

Periodic snapshot fact table

Grain là một dòng cho mỗi thực thể tại mỗi cuối chu kỳ chuẩn (cuối ngày, cuối tuần, cuối tháng). Một dòng ở đây tổng kết nhiều sự kiện xảy ra trong kỳ, hoặc chụp lại trạng thái tích luỹ tại thời điểm chốt kỳ.

Loại này sinh ra vì nhiều câu hỏi nghiệp vụ không quan tâm từng giao dịch mà quan tâm mức độ: số dư tài khoản cuối ngày là bao nhiêu, dư nợ khách hàng cuối tháng là bao nhiêu, tồn kho cuối tuần bằng bao nhiêu. Bạn có thể suy ra số dư bằng cách cộng dồn toàn bộ transaction từ đầu — nhưng cực chậm và dễ sai; periodic snapshot vật chất hoá sẵn con số này.

Đặc điểm:

• Số dòng dự đoán được: số thực thể × số kỳ. Kể cả khi một tài khoản không phát sinh giao dịch nào trong ngày, nó vẫn có một dòng snapshot cho ngày đó (số dư giữ nguyên).
• Chứa nhiều measure kiểu semi-additive (số dư — cộng được theo tài khoản, KHÔNG cộng được theo thời gian).
• Bản chất "dày và đều" (dense) — khác với transaction fact "thưa và không đều" (sparse).

-- Periodic snapshot: 1 dòng cho mỗi tài khoản mỗi cuối ngày
CREATE TABLE fact_so_du_cuoi_ngay (
    ngay_sk           INT     NOT NULL,      -- FK -> dim_ngay
    tai_khoan_sk      BIGINT  NOT NULL,      -- FK -> dim_tai_khoan
    khach_hang_sk     BIGINT  NOT NULL,      -- FK -> dim_khach_hang
    so_du_cuoi_ngay   NUMERIC(18,2) NOT NULL,   -- SEMI-ADDITIVE (không cộng theo ngày)
    so_du_binh_quan   NUMERIC(18,2) NOT NULL,   -- SEMI-ADDITIVE
    tong_ghi_no       NUMERIC(18,2) NOT NULL,   -- ADDITIVE (dòng tiền trong ngày)
    tong_ghi_co       NUMERIC(18,2) NOT NULL,   -- ADDITIVE
    so_giao_dich      INT NOT NULL,             -- ADDITIVE
    PRIMARY KEY (ngay_sk, tai_khoan_sk)
);
-- Grain: mỗi dòng = trạng thái một tài khoản tại cuối một ngày.

Accumulating snapshot fact table

Grain là một dòng cho toàn bộ một lần chạy của một quy trình có nhiều mốc (một quy trình có đầu và cuối rõ ràng: xử lý đơn hàng, xét duyệt bồi thường, xử lý hồ sơ vay). Điểm khác biệt căn bản: dòng được UPDATE nhiều lần khi quy trình tiến triển qua từng mốc, thay vì thêm dòng mới.

Bảng có nhiều cột ngày — mỗi cột ứng với một mốc trong quy trình (ngày nộp, ngày duyệt, ngày giải ngân...). Khi hồ sơ mới nộp, chỉ có ngày nộp được điền; các cột ngày còn lại null. Khi hồ sơ được duyệt, dòng đó được update để điền ngày duyệt, và tính luôn khoảng thời gian (lag) giữa các mốc.

Đặc điểm:

• Bảng nhỏ nhất trong ba loại, vì mỗi quy trình chỉ có một dòng và được ghi đè.
• Trả lời cực tốt câu hỏi về hiệu suất quy trình: trung bình mất bao lâu từ nộp đến giải ngân? Bao nhiêu hồ sơ đang kẹt ở bước duyệt?
• Mỗi cột ngày thường là một role-playing dimension trỏ về cùng dim_ngay.

So sánh & khi nào dùng

Nguyên tắc chọn: quan tâm chi tiết sự kiện → transaction; quan tâm mức tích luỹ theo thời gian → periodic snapshot; quan tâm tiến độ và độ trễ của một luồng công việc → accumulating snapshot.

Tính cộng được (additivity) — chống tính sai

Đây là phần quan trọng nhất về mặt vận hành. Additivity mô tả một measure có thể cộng (SUM) an toàn theo những dimension nào. Hiểu sai chỗ này dẫn tới con số sai mà không hề báo lỗi.

Additive (cộng được hoàn toàn) — cộng được theo mọi dimension gắn với fact. Doanh thu, số tiền giao dịch, số lượng, phí. Doanh thu theo giờ cộng lại ra ngày, cộng nữa ra tháng, ra quý — luôn đúng. Đây là loại lý tưởng và phổ biến nhất ở transaction fact.

Semi-additive (cộng được một phần) — cộng được theo một số dimension nhưng KHÔNG cộng được theo thời gian. Kinh điển là số dư (balance) và tồn kho. Bạn cộng được số dư của nhiều tài khoản tại cùng một ngày để ra tổng tài sản → đúng. Nhưng cộng số dư của cùng một tài khoản qua 30 ngày → sai hoàn toàn. Với measure semi-additive, khi tổng hợp theo thời gian phải dùng last (giá trị cuối kỳ) hoặc average (trung bình), không dùng SUM.

Non-additive (không cộng được) — không cộng được theo bất kỳ dimension nào; điển hình là tỷ lệ, phần trăm, đơn giá (NIM, tỷ lệ nợ xấu, lãi suất, tỷ giá). Không bao giờ được SUM một tỷ lệ. Cách xử lý chuẩn của Kimball: lưu riêng tử số và mẫu số dưới dạng hai measure additive, cộng chúng riêng rồi mới chia ở bước cuối. Ví dụ đừng lưu ty_le_no_xau; hãy lưu du_no_xau và tong_du_no (đều additive), báo cáo tự tính SUM(du_no_xau) / SUM(tong_du_no).

Điều này quyết định trực tiếp cách viết measure ở tầng BI. Trong Power BI/DAX, một SUM ngây thơ trên số dư sẽ ra rác; phải dùng các hàm như LASTNONBLANK hoặc trung bình theo ngữ cảnh — mà muốn viết đúng thì phải hiểu cách filter context và row context vận hành (nối Power BI — DAX context). Tương tự với measure trong LookML/semantic layer. Nói cách khác: additivity là hợp đồng giữa data modeler và người viết measure.

Factless fact table

Có những sự kiện đáng đo nhưng không có measure số học nào. Ví dụ: một khách hàng đăng ký nhận thông báo, một sinh viên có mặt trong một buổi học, một sản phẩm được một chương trình khuyến mãi bao phủ (coverage). Bảng fact ghi các sự kiện này chỉ gồm các foreign key tới dimension, không có cột số đo — gọi là factless fact table.

Có hai kiểu dùng:

1. Ghi nhận sự kiện (event tracking): đếm số lần sự kiện xảy ra. Measure ngầm chính là COUNT(*). "Bao nhiêu khách hàng đã đăng ký eKYC trong tháng 6?" → đếm số dòng.
2. Ghi nhận quan hệ/coverage: trả lời câu hỏi về những gì đã KHÔNG xảy ra. Ví dụ bảng coverage "sản phẩm nào được khuyến mãi ở chi nhánh nào" cho phép so với bảng bán hàng để tìm sản phẩm được khuyến mãi nhưng không bán được dòng nào — điều mà bảng bán hàng đơn thuần không thể trả lời (vì không có dòng nào để đếm).

Nhiều người thêm một cột hằng số so_su_kien INT DEFAULT 1 để việc SUM trực quan hơn, nhưng về bản chất nó vẫn tương đương COUNT.

Degenerate dimension

Đôi khi một thuộc tính giống-dimension lại nằm thẳng trong fact table mà không có bảng dimension riêng — gọi là degenerate dimension (DD). Điển hình là các mã nghiệp vụ ở grain giao dịch: mã giao dịch, số hoá đơn, mã lô, số vận đơn.

Tại sao không tách ra dim riêng? Vì bản thân mã đó là định danh của grain, và mọi thuộc tính mô tả thú vị (khách hàng, kênh, ngày...) đã được đẩy hết vào các dimension khác rồi. Nếu tách dim_ma_giao_dich ra thì bảng đó sẽ có đúng một cột và số dòng bằng chính fact — vô nghĩa. DD thường dùng để: gom nhóm các dòng thuộc cùng một giao dịch cha (nhiều line item chung một số hoá đơn), hoặc truy vết ngược về hệ thống nguồn.

Trong ví dụ trên, ma_giao_dich chính là một degenerate dimension.

Khoá và kích thước fact — giữ fact hẹp

Vài nguyên tắc thiết kế cột cho fact:

• Foreign key phải là surrogate key trỏ tới dimension, không dùng khoá nghiệp vụ (natural key) trực tiếp. Surrogate key là số nguyên nhỏ, join nhanh, và cho phép dimension xử lý lịch sử thay đổi (SCD) độc lập với fact — chi tiết ở Dimensions & SCD.
• Giữ fact "hẹp" (narrow): chỉ chứa khoá (số) và measure (số). Đừng nhồi cột text mô tả vào fact ("tên khách hàng", "địa chỉ chi nhánh"); những thứ đó thuộc về dimension. Fact hẹp = ít byte mỗi dòng = quét nhanh hơn, nén tốt hơn ở kho dạng cột.
• Khoá chính của fact: transaction fact có thể dùng surrogate key riêng; snapshot fact thường dùng khoá tổ hợp của các FK định nghĩa grain (như (ngay_sk, tai_khoan_sk) ở trên).
• Xử lý null ở FK: không để null trong khoá — dùng một dòng dimension đặc biệt (ví dụ sk = -1, "Không xác định") để mọi join luôn là inner join sạch.

Mẫu thiết kế nâng cao

Không trộn nhiều grain trong một fact. Cám dỗ lớn nhất là nhét cả tổng đơn hàng và chi tiết dòng đơn hàng vào một bảng. Đừng. Mỗi grain là một bảng fact riêng. Trộn grain khiến measure bị nhân đôi khi join và không ai tin nổi con số nữa.

Aggregate / summary fact table. Bên cạnh fact chi tiết (atomic), người ta xây thêm các fact đã gộp trước theo grain thô hơn (doanh thu theo ngày × sản phẩm, thay vì từng giao dịch) để tăng tốc báo cáo. Đây chính là ý tưởng đằng sau materialized view và mô hình gộp trong dbt. Quy tắc vàng: aggregate fact phải cộng ra đúng số của fact chi tiết — nếu không sẽ có hai nguồn sự thật mâu thuẫn. Aggregate chỉ nên chứa measure additive/semi-additive xử lý cẩn thận; đừng bao giờ pre-aggregate một tỷ lệ.

Bridge table cho quan hệ n–n. Star schema chuẩn giả định mỗi dimension gắn với fact có đúng một giá trị nhất quán với grain. Nhưng có những dimension multi-valued (đa trị) hợp lệ: một tài khoản đồng sở hữu bởi nhiều khách hàng, một giao dịch gắn nhiều chẩn đoán/nhãn. Khi đó ta chèn một bridge table giữa fact và dimension, với một dòng cho mỗi phần tử trong nhóm, thường kèm cột trọng số (weight) để phân bổ measure. Bridge table trong bối cảnh nhiều-nhiều giữa tài khoản và khách hàng thường phải xây trên dimension SCD type 2 để giữ đúng lịch sử. Lưu ý: nhân với bridge table có thể gây double-count nếu không dùng weight — phải rất cẩn thận.

Use case thực tế (ngân hàng)

Số dư & dư nợ → periodic snapshot semi-additive. Nghiệp vụ luôn hỏi "tổng dư nợ toàn hàng cuối tháng", "số dư huy động cuối ngày". Ta dùng fact_so_du_cuoi_ngay như trên: mỗi tài khoản một dòng mỗi ngày. Số dư là semi-additive — tổng theo tài khoản trong một ngày thì SUM, nhưng nhìn xu hướng theo thời gian thì lấy giá trị cuối kỳ hoặc bình quân, tuyệt đối không SUM qua các ngày. Đồng thời lưu sẵn so_du_binh_quan để tính lãi. Còn tỷ lệ nợ xấu thì không lưu như một cột %; lưu du_no_xau và tong_du_no (additive) rồi để BI chia — non-additive xử lý đúng chuẩn.

Hồ sơ vay → accumulating snapshot. Một hồ sơ đi qua các mốc: nộp → thẩm định → phê duyệt → ký hợp đồng → giải ngân (và có thể tất toán). Mỗi hồ sơ là một dòng, được update dần. Bảng cho phép trả lời ngay: thời gian trung bình từ nộp đến giải ngân (SLA), tỷ lệ chuyển đổi giữa các bước, số hồ sơ đang kẹt ở mỗi mốc.

-- Accumulating snapshot: 1 dòng cho mỗi hồ sơ vay, update dần theo mốc
CREATE TABLE fact_ho_so_vay (
    ho_so_sk            BIGINT PRIMARY KEY,
    khach_hang_sk       BIGINT NOT NULL,
    san_pham_vay_sk     INT    NOT NULL,
    ma_ho_so            VARCHAR(32) NOT NULL,     -- degenerate dimension
    ngay_nop_sk         INT,                      -- role-playing dim_ngay
    ngay_duyet_sk       INT,
    ngay_giai_ngan_sk   INT,
    so_tien_de_nghi     NUMERIC(18,2),            -- additive
    so_tien_giai_ngan   NUMERIC(18,2),            -- additive
    lag_nop_den_duyet   INT,   -- số ngày, tính khi update mốc duyệt
    lag_duyet_den_gn    INT,
    trang_thai_hien_tai VARCHAR(20)
);
-- Grain: mỗi dòng = một hồ sơ. Update mỗi khi hồ sơ sang mốc mới.

Song song, mọi lần giải ngân/thu nợ vẫn được ghi vào một transaction fact riêng để phục vụ đối soát chi tiết. Ba loại fact cùng tồn tại, mỗi loại một mục đích.

Ghi nhớ

• Grain trước tiên. Tuyên bố grain bằng lời nghiệp vụ trước khi chọn dimension/measure. Không trộn nhiều grain trong một fact.
• Ba loại fact bổ sung nhau: transaction (chi tiết, insert-only), periodic snapshot (trạng thái theo kỳ, semi-additive), accumulating snapshot (quy trình, có update, nhiều cột ngày).
• Additivity là hợp đồng chống tính sai: additive SUM thoải mái; semi-additive không SUM theo thời gian (dùng last/avg); non-additive không bao giờ SUM — lưu tử/mẫu riêng.
• Factless fact đếm sự kiện/quan hệ; degenerate dimension giữ mã nghiệp vụ ngay trong fact.
• Giữ fact hẹp: chỉ số (khoá surrogate + measure), đẩy mọi text vào dimension.
• Aggregate fact tăng tốc nhưng phải cộng-ra-đúng; bridge table xử lý n–n nhưng coi chừng double-count.

Bài tiếp theo đi sâu phía dimension và cách giữ lịch sử thay đổi (Dimensions & SCD); và nếu bạn tò mò vì sao thời hiện đại nhiều nơi làm phẳng tất cả thành một bảng to (OBT), xem Modern data modeling & OBT/metrics.

Nguồn tham khảo: Kimball — Fact Tables, Additive, Semi-Additive, and Non-Additive Facts, Periodic Snapshot Fact Tables, Multivalued Dimensions and Bridge Tables.