Dimension Hierarchy Techniques
Trong thực tế, dimension luôn có phân cấp (hierarchy). Nhìn bề ngoài nó có vẻ rất đơn giản, ví dụ Country - Region - City/Province - District. Nếu ngây thơ, ta nghĩ chỉ cần flatten vào một dimension là xong, nhưng thực tế thì không sạch sẽ như thế. Phân cấp trong dimension có thể phải đối mặt với các vấn đề như sau:
- Ragged / Unbalanced Levels: Mỗi entity có số cấp khác nhau, nên không thể flatten vì số cột khác nhau.
- Missing Levels: Một số entity bị thiếu cấp, dẫn đến roll-up bị sai hoặc bỏ sót cấp.
- Extra / Irregular Levels: Một số entity có thêm cấp lạ không nằm trong cấu trúc chuẩn. Do đó không thể tạo được hierarchy chung cho toàn dimension.
- Multi-Parent Relationships: Một cấp có thể có 2 hoặc nhiều cấp cha, gây duplicate rows, snowflake gây fan-out và double-count trong fact.
- Recursive / Unknown Depth: Độ sâu phân cấp không cố định, không thể định nghĩa level 1, level 2,… cố định.
- Hierarchy Changes Over Time (SCD): Cấu trúc cấp thay đổi theo thời gian, dữ liệu lịch sử bị “kéo lệch” nếu không SCD-aware.
- Cycles / Loops (Dirty Data): Dữ liệu nhập sai tạo vòng lặp làm query đệ quy treo, drill-down sai hoặc ETL bị kẹt.
- Grain Mismatch: Fact grain không đủ chi tiết để phân tích ở level thấp hơn, drill-down bất khả thi dù dimension có chứa thêm level mới.
Và nhóm kĩ thuật trong chương này, được tác giả giới thiệu để sống sót trong thực tế phân cấp lộn xộn.
Fixed Depth Positional Hierarchies
Fixed Depth Positional Hierarchy là kỹ thuật đơn giản nhất, ưu tiên nhất và sạch nhất trong tất cả các loại hierarchy Kimball đề cập. Nó được tin tưởng sử dụng khi:
- Cấp phân cấp cố định (fixed depth).
- Mỗi cấp có tên rõ ràng.
- Ít thay đổi theo thời gian.
- Quan hệ many-to-one sạch, mỗi cấp chỉ có 1 cha.
Cách triển khai loại này tương đối đơn giản, flatten tất cả vào một bảng. Tuy nhiên để đảm bảo hiệu quả thì nên đảm bảo conformed dimension, thống nhất tên, số level, tránh null trên toàn bộ business process.
Tránh sử dụng đặt tên trừu tượng cho Fixed Depth Positional Hierarchies
Kimball có lưu ý rằng không nên sử dụng các tên như level_1 , level_2,... cho fixed depths vì chúng tối nghĩa. Chúng sẽ làm mất đi ý nghĩa cố định của từng level, BI user không thể hiểu, filter, hay drill-down đúng được.
Slightly Ragged/Variable Depth Hierarchies
Slightly ragged - hơi rách rưới, ám chỉ dimension có phân cấp không cố định số level (variable depth), nhưng cũng không quá loạn, không đẹp đẽ nhưng không quá tệ. Tức là cấp của các entity hơi lệch nhau nhưng không quá lớn, vẫn thuộc cùng một loại hierarchy.
Đối với loại này Kimball lựa chọn Force-fit hierarchy vào positional columns thông qua các bước:
Xác định max depth.
Xác định entity có depth lớn nhất và lấy nó làm chuẩn.
Geographic hierarchy có 3 dạng depth:
3 cấp: Country → Region → City
4 cấp: Country → State → County → City
5 cấp: Country → Province → City → District → Ward
Chúng không có đô sâu cấp cố định, tuy nhiên độ lệch nhỏ và chọn max depth là 5 làm chuẩn.
Tạo cố định số cột positional tương ứng
Sử dụng positional attributes đại diện cho level, thay vì đại diện cho tên thật của cấp. Ví dụ: level_1, level_2 , level_3 ,... hoặc geo_level_1, geo_level_2, geo_level_3,... hoặc hier_level_1, hier_level_2, hier_level_3,...
Tạo cột positional cho geographic hierarchy
Ở ví dụ trên, ta tạo một bảng có 5 cột positional attributes geo_level_x nhằm đại diện cho 5 level.
Điền các cấp vào bảng dimension
Lần lượt map từng cấp từ trên xuống vào đúng vị trí dựa trên business rules, phần thừa dùng NULL hoặc placeholder.
Với ví dụ trên, ta sẽ thu được bảng dimension như sau:
| geo_key | geo_level_1 | geo_level_2 | geo_level_3 | geo_level_4 | geo_level_5 |
|---|---|---|---|---|---|
| 101 | CountryA | RegionA | CityA | NULL | NULL |
| 102 | CountryB | StateB | CountyB | CityB | NULL |
| 103 | CountryC | ProvinceC | CityC | DistrictC | WardC |
Ragged/Variable Depth Hierarchies with Hierarchy Bridge Tables
Khi phân cấp rắc rối thực sự, ở đó:
- Số cấp khác nhau rất lớn giữa các entity (3 cấp, 5 cấp, 7 cấp...)
- Có cấp thiếu, cấp thừa, hoặc cấp lạ
- Một entity có thể thuộc nhiều cấp cha (multi-parent)
- Cấp có thể thay đổi theo thời gian
- Độ sâu hierarchy không biết trước (kiểu parent–child recursive)
Do đó ta không thể flatten positional, không thể dùng fixed-depth, không thể force-fit slightly ragged. Nếu cố flatten sẽ làm mất ý nghĩa của level, drill-down hỏng, fact double-count.
Khi đó, tác giả đề xuất sử dụng Hierarchy Bridge Tables để giải quyết bài toán này. Cụ thể, Hierarchy Bridge Table là một bảng mô tả mọi quan hệ ancestor–descendant (không phải cha-con/parent-child đơn thuần) trong hierarchy. Hay nói cách khác, bảng này chứa một dòng cho mỗi đường đi có thể có trong cây phân cấp. Bằng cách này, mọi dạng duyệt phân cấp (hierarchy traversal) đều có thể được thực hiện bằng SQL đơn thuần mà không cần dùng đến các extension hay công cụ đặc biệt.
Tóm lại Bridge Table rất rất mạnh - kiểu đọc tới đoạn này tớ phải thốt lên “ơ thế hoá ra làm được đến mức này à?”. Nó xử lý được depth biến thiên, drill-down ngược xuôi, thậm chí hỗ trợ cả SCD-hierarchy. Nhưng mạnh nào cũng có giá. Nó không hề dễ nhai. Ngay cả BI chưa chắc đã hiểu hay Modeler tớ nghĩ đôi khi còn phải gãi đầu, và thú thật tớ cũng chỉ hiểu nôm na là như thế chứ chưa kể đến mấy trick nâng cao phía sâu hơn.
Nếu cậu không để ý thì cái Bridge Table này có khả năng sẽ phình rất to, ETL chưa chắc đã dễ nữa, nói chung là không có gì ngon, bổ, rẻ hết.
Ragged/Variable Depth Hierarchies with Pathstring Attributes
Đôi khi dùng Bridge Table hơi overkill thật, đâu phải business nào cũng cần phân tích tới mức ancestor–descendant nặng đô như vậy. Nếu mục tiêu chỉ là xác định đường đi của node trong hierarchy, không yêu cầu roll-up linh hoạt, không multi-parent, không historical lineage thì ta có thể dùng Pathstring Attribute thay vì Bridge.
Ý tưởng của Kimball như sau: Mỗi dòng trong dimension lưu một string mô tả toàn bộ tuyến phân cấp (Pathstring Attribute) từ root > … > node. Sau đó sử dụng các toán tử LIKE / REGEXP / SPLIT để phân tích.
Ví dụ về Pathstring Attribute
Với kĩ thuật này, ta có thể tạo bảng dim_employee_with_path dimension như sau:
| emp_key | emp_name | title | path_string |
|---|---|---|---|
| 1 | Alice | CEO | CEO |
| 2 | Bob | VP Finance | CEO>Finance |
| 3 | Carol | Finance Manager | CEO>Finance>Manager |
| 4 | David | Accountant | CEO>Finance>Manager>Accountant |
| 5 | Emma | VP Sales | CEO>Sales |
| 6 | Felix | Regional Lead | CEO>Sales>RegionalLead |
| 7 | Grace | Sales Rep | CEO>Sales>SalesRep |
Và với một số câu query phân tích:
-
Lấy toàn bộ nhân viên dưới Finance:
SELECT emp_name, title FROM dim_employee_with_path WHERE path_string LIKE 'CEO>Finance%'; -
Xác định level của một nhân viên (bằng số “>”):
SELECT emp_name, LENGTH(path_string) - LENGTH(REPLACE(path_string,'>','')) + 1 AS depth_level FROM dim_employee_with_path; -
Lấy doanh thu toàn bộ dưới CEO (JOIN bằng path):
SELECT SUM(f.amount) AS total_revenue FROM fact_sales f JOIN dim_employee_with_path d ON f.emp_key = d.emp_key WHERE d.path_string LIKE 'CEO%';
Ưu điểm của Pathstring:
- Không cần Bridge: Không thêm bảng, không tốn storage, ETL dễ hơn rất nhiều.
- Không cần nhiều join: Truy vấn đơn giản, BI dễ dùng.
- Dễ lưu trữ: Chỉ 1 cột string.
Nhưng nó vẫn yếu hơn Bridge khi thiếu sót:
- Không support được multi-parent.
- Không hỗ trợ SCD-hierarchy tốt.
- Không drill-up từ descendant về ancestor hiệu quả.
My Summary
Có những thứ nhìn thì tưởng quen, tưởng đơn giản, nhưng đi sâu xuống mới thấy trước giờ mình hơi ngây thơ. Mỗi kỹ thuật trong phần này - kể cả chưa chạm tới advanced - đều làm tớ “wooooow” không chỉ một lần. Có cái nhẹ nhàng, có cái xoắn não, nhưng điểm chung là mở mắt rất nhiều.
Đúng là trước giờ nghe nói Kimball GOAT cũng chỉ gật gù cho vui. Đọc đến đoạn này mới thấy ờ… người ta nói không sai. Càng đi xuống càng rộng, càng nhức đầu, càng thấy ổng nghĩ xa và sâu đến mức khó tin. Một cái kỹ thuật tưởng chỉ là “phân cấp thôi mà” mà cũng có cả tá biến thể, trade-off, edge cases và cách giải khác nhau.
Thú thật đến đây tớ mới hiểu tại sao quyển này được gọi là kinh thánh — đọc mấy chương đầu tưởng nhẹ nhàng, tới phần hierarchy là não đúng kiểu bùng nổ.
Giờ thì không còn “nghe phong thanh” nữa — mà tớ chính thức công nhận Kimball đúng là GOAT.