Hai kiểu hệ thống, hai cách thiết kế
| OLTP (app bán hàng) | OLAP (kho phân tích) | |
|---|---|---|
| Việc chính | Ghi từng đơn, nhanh và đúng | Đọc hàng triệu dòng để tổng hợp |
| Thiết kế | Chuẩn hoá: mỗi thông tin ở đúng một chỗ | Phi chuẩn hoá có chủ đích: ít join, dễ đọc |
Silver vẫn gần với nguồn. Để phân tích, ta sắp xếp lại thành star schema: một bảng fact ở giữa, các bảng dimension xung quanh.
Fact và dimension
- Fact ghi một sự kiện đo được: mỗi dòng của
fact_orderslà một đơn hàng. Fact chủ yếu có hai loại cột: khoá trỏ tới dimension (customer_id,product_id,date_key) và measure là các con số (quantity,revenue). - Dimension cho ngữ cảnh: ai (
dim_customer), cái gì (dim_product), khi nào (dim_date, bài sau). Mỗi dòng là một thực thể, dù đã có đơn hay chưa.
Mẹo phân biệt: cộng cột này lại có nghĩa không? Cộng revenue của 100 đơn ra tổng doanh thu: measure, nằm ở fact. Cộng category thì vô nghĩa: thuộc tính mô tả, nằm ở dimension.
Mẹo này dành cho thuộc tính của khách, sản phẩm, ngày. Fact vẫn giữ vài cột không cộng được nhưng mô tả chính đơn hàng: order_id là degenerate dimension (mã giao dịch, không có bảng dimension riêng); status, channel là thuộc tính của chính đơn, để ở fact cho khỏi join; unit_price là non-additive: cộng đơn giá của nhiều đơn không ra con số có nghĩa.
flowchart LR DP[dim_product · 60] --- F[fact_orders · 4972] DC[dim_customer · 500] --- F DD[dim_date · bài 3.2] --- F
Vì sao không gộp thành một bảng phẳng?
Bảng phẳng gộp mọi cột của đơn, khách, sản phẩm. Ba vấn đề:
- Lặp dữ liệu. Chữ "Điện thoại" bị lưu lại ở mọi đơn điện thoại thay vì 1 lần trong
dim_product. Đổi tên danh mục thì phải sửa hàng nghìn dòng. - Không mô tả được thứ chưa xảy ra. Sản phẩm chưa ai mua không có dòng nào. Bảng phẳng chỉ biết những gì đã xảy ra.
- Lịch sử dễ vỡ. Khách chuyển vùng thì sửa dòng nào? Bài 3.3 bàn kỹ.
Bảng phẳng vẫn ổn khi dữ liệu nhỏ và chỉ phục vụ một báo cáo; thường nó là đầu ra dựng từ star schema cho dashboard, không phải nơi lưu chính.
Ví dụ: sản phẩm nào không bán được?
"Sản phẩm nào không có đơn nào ở cửa hàng (store) trong tháng 3?" Bảng phẳng không trả lời được, vì sản phẩm không bán được thì không có dòng nào. Star schema đi từ dim_product, nơi có đủ 60 sản phẩm, rồi LEFT JOIN sang fact:
SELECT p.category, count(*) AS khong_ban
FROM dim_product p
LEFT JOIN fact_orders f
ON f.product_id = p.product_id
AND f.channel = 'store'
AND f.date_key BETWEEN DATE '2025-03-01' AND DATE '2025-03-31'
WHERE f.order_id IS NULL
GROUP BY 1 ORDER BY 1;
Kết quả: 9 sản phẩm. Sản phẩm không khớp đơn nào vẫn giữ dòng, với cột của f là NULL. Điều kiện kênh và ngày nằm ở ON (bài 3.2 giải thích).
Fact có được lọc dòng không?
fact_orders giữ mọi đơn của silver (4972), kể cả cancelled và pending, và cả 102 đơn mồ côi đã gắn cờ ở Module 02. "Doanh thu chỉ tính đơn completed" là định nghĩa nghiệp vụ, để tầng gold quyết định (bài 3.4). Lọc ngay ở fact thì không còn trả lời được "tỉ lệ huỷ bao nhiêu?".
Bài này cũng dựng silver_products (ép list_price sang DECIMAL), còn dim_customer ép signup_date sang DATE và bỏ hai cột metadata: dimension là nơi người phân tích đọc, kiểu phải đúng ở đây.
Lệnh hay dùng
| Lệnh | Để làm gì |
|---|---|
CREATE OR REPLACE TABLE dim_x AS SELECT <cột mô tả> FROM silver_x |
dựng dimension |
CREATE OR REPLACE TABLE fact_x AS SELECT <khoá>, <measure> FROM silver_x |
dựng fact |
order_ts::DATE AS date_key |
khoá ngày để nối với dim_date |
FROM dim LEFT JOIN fact ON ... WHERE fact.<khoá> IS NULL |
tìm thực thể không có sự kiện |
Lỗi hay gặp
- Đặt thuộc tính của khách hay sản phẩm vào fact (
category,region) "cho khỏi join". Fact phình to và đổi tên danh mục phải sửa hàng nghìn dòng. - Đi từ fact khi hỏi về thứ không xảy ra.
FROM fact_orderskhông bao giờ thấy sản phẩm chưa bán; phải bắt đầu từ dimension.