Chốt grain trước khi vẽ bảng: dựng star schema bên cạnh database giao dịch của khách

Tuần đầu tiên ở khách, giám đốc vận hành hỏi một câu tưởng rất dễ: doanh thu từng nhóm hàng, theo tỉnh, theo tháng. Bạn mở database thì thấy dữ liệu nằm rải ở sáu bảng, và cách nhanh nhất để có số là chạy một câu SQL dài lên chính database đang nhận đơn hàng.

Khách có dữ liệu, nhưng dữ liệu được tổ chức cho việc ghi giao dịch chứ không phải cho việc đọc để phân tích. Biết đọc schema hiện có và thiết kế được một mô hình phân tích bên cạnh nó là kỹ năng quyết định bạn đưa ra được dashboard trong hai tuần hay sa lầy hai tháng.

Database bán hàng không sinh ra để trả lời câu hỏi của sếp

Hệ thống giao dịch (OLTP) được thiết kế quanh chuẩn hóa. Mục tiêu của normalization là loại bỏ dữ liệu trùng lặp và lưu trữ hợp lý. Ở dạng 3NF, mọi thuộc tính không phải khóa chỉ phụ thuộc vào khóa chính, nên tên tỉnh nằm ở bảng tỉnh, tên nhóm hàng nằm ở bảng nhóm hàng, và đơn hàng chỉ giữ id.

Thiết kế đó rất tốt cho việc ghi. AWS mô tả OLTP xử lý tốt khối lượng giao dịch lớn nhưng không làm được truy vấn phức tạp; analyst cần một hệ OLAP để phân tích dữ liệu đa chiều. Vì thế câu hỏi “theo tỉnh, theo nhóm, theo tháng”, vốn là ba chiều, buộc bạn JOIN qua cả chuỗi bảng chuẩn hóa.

Khác biệt còn đi xuống tận cách dữ liệu nằm trên đĩa. Columnar database chỉ đọc những cột truy vấn cần, nên giảm I/O và thời gian cho truy vấn phân tích: thử hình dung bảng fact có 20 cột mà báo cáo chỉ cần 4, engine dạng cột chỉ chạm vào 4 cột đó.

Cái giá phải trả là columnar database ghi chậm và hỗ trợ ACID yếu hơn database lưu theo dòng. Nó hợp với dữ liệu ghi một lần, đọc nhiều lần, chứ không thay được hệ OLTP đang nhận đơn.

Fact là con số, dimension là ngữ cảnh

Dimensional model chia dữ liệu làm hai loại. Fact là số đo của hoạt động kinh doanh, thường là số, và bảng fact được chuẩn hóa, ít dư thừa. Dimension là ngữ cảnh mô tả: sản phẩm nào, cửa hàng nào, ngày nào.

Hình dạng hai loại bảng rất khác nhau. Bảng fact hẹp và dài, mỗi dòng là một sự kiện. Bảng dimension được denormalize, chứa chủ yếu các thuộc tính dạng text và mô tả. Một fact ở giữa, nhiều dimension xung quanh: đó là star schema theo định nghĩa của AWS, và cũng là lý do dimensional model hay được gọi luôn là star schema.

Thực tế ít khi chỉ có một ngôi sao. Một mô hình hoàn chỉnh thường có nhiều bảng fact dùng chung các dimension (conformed dimension), ví dụ fact bán hàng và fact tồn kho cùng dùng một bảng ngày và một bảng sản phẩm.

Làm thử: chuỗi nhà thuốc và bốn bước Kimball

Thử hình dung khách là một chuỗi nhà thuốc. Database OLTP có các bảng orders, order_items, products, categories, stores, provinces. Câu hỏi của giám đốc vận hành, viết trên schema gốc, trông như sau:

SELECT pv.name AS tinh, c.name AS nhom_hang,
       date_trunc('month', o.created_at) AS thang,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM order_items oi
JOIN orders o      ON o.id = oi.order_id
JOIN stores s      ON s.id = o.store_id
JOIN provinces pv  ON pv.id = s.province_id
JOIN products p    ON p.id = oi.product_id
JOIN categories c  ON c.id = p.category_id
GROUP BY 1, 2, 3;

Năm JOIN cho một câu hỏi, chạy trên database đang nhận đơn. Quy trình bốn bước Kimball đưa bạn ra khỏi đó: chọn business process, khai báo grain, chọn dimension, rồi chọn fact.

Business process ở đây là bán hàng tại quầy. Grain là mức chi tiết thấp nhất mà quy trình ghi lại. Hãy viết nó thành một câu: “mỗi dòng là một mặt hàng trên một hóa đơn”.

Vì sao không chọn grain là cả hóa đơn? Thử với một đơn giả định: 2 hộp thuốc giảm đau giá 30.000đ mỗi hộp, 1 lọ vitamin 120.000đ và 1 hộp khẩu trang 40.000đ.

Ở grain hóa đơn, bạn chỉ có một dòng 220.000đ và không thể tách ra 60.000đ thuộc nhóm giảm đau; ở grain dòng hàng, bạn có 3 dòng và trả lời được mọi câu hỏi theo nhóm.

Dimension suy ra từ những chữ “theo” trong câu hỏi: dim_date, dim_store (gồm luôn tên tỉnh), dim_product (gồm luôn nhóm hàng, hoạt chất, nhà sản xuất), thêm dim_customer nếu có thẻ thành viên. Fact là các con số tại grain đó: quantity, revenue, discount.

Sau bốn bước, hãy làm thêm một việc kiểm tra: viết lại chính câu hỏi của khách trên mô hình mới.

SELECT st.province, pr.category, d.year_month,
       SUM(f.revenue) AS doanh_thu
FROM fact_sales_line f
JOIN dim_store   st ON st.store_key   = f.store_key
JOIN dim_product pr ON pr.product_key = f.product_key
JOIN dim_date    d  ON d.date_key     = f.date_key
GROUP BY 1, 2, 3;

Mỗi chiều phân tích chỉ còn một JOIN, và câu SQL đọc gần giống câu hỏi của sếp. Đó là giá trị thật của star schema: analyst phía khách tự viết được truy vấn mà không cần bạn ngồi cạnh.

Star hay snowflake: khi nào tách tiếp dimension?

IBM mô tả snowflake schema là phần mở rộng logic của star schema, trong đó dimension được tách thêm thành các bảng phụ. Với nhà thuốc, dim_product sẽ trỏ sang dim_category, và dim_store trỏ sang dim_province.

	Star	Snowflake
Dimension	Denormalize, một bảng phẳng	Chuẩn hóa thêm thành nhiều bảng
Lưu trữ	Lặp lại text như tên nhóm hàng	Tiết kiệm hơn
Truy vấn	Ít JOIN, analyst dễ viết	Nhiều JOIN, phức tạp hơn
Hợp khi	Người dùng cuối tự truy vấn, BI tool	Dimension rất lớn, thuộc tính cấp trên thay đổi riêng

Lời khuyên thực dụng: mặc định chọn star. Tên nhóm hàng lặp lại vài nghìn lần trong dim_product hiếm khi là vấn đề lưu trữ đáng kể, còn mỗi JOIN thêm vào là một chỗ analyst có thể viết sai.

Những lỗi khiến mô hình phải đập đi làm lại

Lỗi đắt nhất là trộn grain. Đặt phí vận chuyển của cả hóa đơn vào bảng fact cấp dòng hàng thì mỗi lần SUM sẽ cộng phí đó lặp lại theo số dòng. Nếu một số đo sống ở cấp khác, nó cần một bảng fact khác.

Lỗi thứ hai là denormalize tràn lan. Denormalization thêm dữ liệu dư thừa để truy vấn nhanh hơn, nên chỉ áp dụng ở các mô hình dẫn xuất phục vụ BI chứ không ở warehouse lõi. Giữ lớp lõi sạch thì khi khách đổi yêu cầu, bạn dựng lại lớp trên mà không đụng nền.

Lỗi thứ ba là khóa cứng mô hình quá sớm. AWS lưu ý OLAP cube cứng nhắc: đã mô hình hóa rồi thì không đổi được dimension và dữ liệu bên dưới. Nếu khách vẫn đang khám phá câu hỏi, hãy dựng star schema trên bảng thường trước khi nghĩ đến cube.

Lỗi cuối nằm ở chỗ nói không rõ phạm vi dự án. Data mart là tập con của warehouse cho một mảng nghiệp vụ hay phòng ban, còn warehouse trải rộng toàn tổ chức.

Dự án bắt đầu từ phòng vận hành của chuỗi nhà thuốc là một data mart; nói rõ điều đó với khách giúp tránh kỳ vọng rằng bạn đang xây kho dữ liệu cho cả công ty.

Thể hiện kỹ năng này trong CV và buổi phỏng vấn

Khi đọc JD của các vị trí FDE, nếu gặp những cụm như “analytics”, “data warehouse”, “BI”, “dbt”, hãy coi đó là tín hiệu nên ôn kỹ phần mô hình hóa dữ liệu trước khi nộp đơn.

Trong CV, đừng chỉ ghi “thiết kế data warehouse”. Hãy viết theo dạng: chọn grain gì, bao nhiêu fact và dimension dùng chung, và câu hỏi nghiệp vụ nào trước đây cần năm JOIN trên production giờ analyst tự trả lời được. Cách viết đó cho người đọc thấy bạn đi từ câu hỏi nghiệp vụ đến mô hình, thay vì chỉ liệt kê công cụ.

Để luyện cho buổi phỏng vấn, hãy tự ra cho mình hai đề và trả lời thành tiếng. Đề một: cho bộ bảng orders, order_items, products, stores, hãy khai báo grain cho fact bán hàng bằng một câu và giải thích vì sao không chọn grain hóa đơn.

Đề hai: khách muốn thêm phí vận chuyển tính theo cả hóa đơn, bạn đặt nó vào đâu để SUM không bị nhân lên?

Lần tới có ai hỏi “doanh thu theo X, theo Y”, đừng vội mở trình soạn SQL; hãy hỏi lại một dòng dữ liệu của họ thực sự là gì.