Trong bài viết trước, chúng ta đã biết Synthetic Key là một giải pháp tự động hiệu quả của Qlik engine để xử lý composite key. Synthetic Key nhìn chung không sử dụng nhiều tài nguyên hơn cách tạo key thủ công bằng cách ghép chuỗi và hàm AutoNumber().
Tuy nhiên, lời khuyên chung khi thiết kế data model vẫn là nên tránh Synthetic Key nếu có thể. Bài viết hôm nay sẽ trình bày một số lý do cho việc này và các cách loại bỏ Synthetic Key trong data model.
Vì sao vẫn nên tránh Synthetic Key?
Sau khi reload một application, nếu gặp cảnh báo có Synthetic Key trong data model sau, bạn nên rà soát lại data model và cố gắng loại bỏ Synthetic Key vì các lý do sau:
- Synthetic Key Là dấu hiệu của data model được thiết kế không tốt hoặc script được viết không cẩn thận. Trong thực tế, khi có hàng loạt Synthetic Key được tạo ra, thường là do các field ngẫu nhiên trùng tên trong các bảng hơn là thực sự có composite key trong dữ liệu ban đầu. Đặc biệt là khi data model của bạn chứa Synthetic Key được tạo ra từ 2 hay nhiều Synthetic Key khác.
- Với một data warehouse được tổ chức tốt, thường đã có sẵn một bảng lưu trữ mối liên hệ giữa các primary key (thường là fact table trong data model). Vì vậy, sự xuất hiện của Synthetic Key có thể là dấu hiệu cho thấy dashboard đang được dùng khác với mục đích thiết kế của data warehouse.
- Tự dùng concatenated key (xem ví dụ trong bài viết trước) thay vì Synthetic Key là một cách để buộc người thiết kế phải rà soát lại data model và đảm bảo composite key chỉ được dùng khi cần thiết.
Tóm lại, với mỗi Synthetic Key xuất hiện trong data model, bạn cần đặt cho mình câu hỏi: Synthetic Key đó có giải quyết vấn đề composite key giữa 2 bảng hay không, hay chỉ là 2 bảng chứa các field trùng tên nhưng không có ý nghĩa làm key?
Loại bỏ Synthetic Key trong data model
Có nhiều cách khác nhau để loại bỏ Synthetic Key. Việc lựa chọn cách thức phù hợp tùy thuộc vào ý nghĩa của dữ liệu nguồn và yêu cầu phân tích. Ở đây VietQlikies tạm chia ra 2 nhóm tương ứng với 2 trường hợp các filed cùng tên trong data model của bạn có thực sự là composite key hay không.
1. Nếu không phải composite key, chỉ là field ngẫu nhiên trùng tên:
Khi xây dựng data model, bạn nên chú ý đến các thuộc tính phổ biến của các đối tượng trong dữ liệu nguồn. Ví dụ, trong một hệ CSDL quan hệ, các thuộc tính như ID, Name, Date, Address, Quantity, Amount, v.v rất thường xuyên được sử dụng làm tên field ở nhiều bảng khác nhau, và thường tạo ra Synthetic Key không mong muốn khi load dữ liệu.
Một khi xác định các field trùng tên này, bạn có các cách sau để ngăn associative engine của Qlik tự động tạo Synthetic Key:
1.1. Loại bỏ các field chung
Cách này được sử dụng khi một trong các field trùng tên không dùng đến trong quá trình xây dựng dashboard. Ví dụ, bảng Supplier có field Address lưu địa chỉ các nhà cung cấp nguyên liệu. Bạn không có nhu cầu phân tích vị trí của các nhà cung cấp trong dashboard. Vì vậy, bạn có thể bỏ qua không load field này để tránh tạo thành Synthetic Key với field Address trong một bảng khác, ví dụ Customer.
Đây là cách đơn giản nhất để loại bỏ Synthetic Key, cũng là dịp để tinh giản data model của bạn bằng cách chỉ giữ lại các field cần thiết cho nhu cầu phân tích.
1.2. Đổi tên field
Bạn có thể đổi tên của các field chung bằng từ khóa AS trong câu lệnh LOAD. Ví dụ:
[Employees]:
LOAD
ID AS EmployeeID,
Company,
Name AS EmployeeName,
...
FROM [lib://data/Sales.xlsx] (ooxml, embedded labels, table is Employees);
[Inventory]:
LOAD
ID AS ItemID,
Name AS ItemName,
CostOfGoodsSold,
...
FROM [lib://data/Sales.xlsx] (ooxml, embedded labels, table is Employees);
1.3. Qualify table
Khi sử dụng từ khóa Qualify, Qlik engine tự động thêm (prefix) tên bảng vào trước tên mỗi field trong bảng đó, đảm bảo không có tên field nào trùng nhau trong data model. Qualify sẽ có hiệu lực đến khi gặp từ khóa Unqualify trong reload script.
Bạn có thể dùng * sau Qualify và Unqualify để các từ khóa này có tác dụng trên tất cả các field, hoặc chỉ định 1 danh sách các tên field cần qualify/ unqualify. Ví dụ, đoạn script sau
Qualify *; Orders: LOAD * INLINE [ OrderDate, OrderID, SalesPerson ... ]; Unqualify Product, Price; OrderDetails: LOAD * INLINE [ OrderDate, OrderID, Product, Price ... ];
cho ra các tên field sau trong data model:
2. Nếu thực sự là composite key
Ngoài cách tự tạo 1 field composite key mới bằng cách ghép giá trị các key field và hàm AutoNumber() như đã trình bày ở ví dụ trong bài viết trước, bạn còn có thể cân nhắc các cách sau:
2.1. Join 2 bảng
Giải pháp join 2 bảng được sử dụng khi các bảng dữ liệu nguồn mang các fact hoàn toàn khác nhau về bản chất hoặc mức độ chi tiết (granularity). Như ở ví dụ trong bài viết trước, bảng OrderDetails mang thông tin chi tiết hơn bảng Orders. Mỗi dòng của bảng Orders chứa thông tin của 1 đơn hàng, trong khi mỗi dòng của bảng OrderDetails thể hiện 1 mặt hàng được mua trong đơn hàng đó. 2 bảng có thể được join như sau:
Orders: LOAD * INLINE [ OrderDate, OrderID, SalesPerson 20190901, 1, John 20190901, 2, David 20190901, 3, Susan 20190902, 1, John 20190902, 2, Susan 20190903, 1, David ]; OUTER JOIN (Orders) OrderDetails: LOAD * INLINE [ OrderDate, OrderID, Product, Price 20190901, 1, T-shirt, 100 20190901, 1, Jacket, 150 20190901, 2, Polo, 80 20190902, 1, Sneaker, 200 20190902, 1, Polo, 160 20190902, 2, Jacket, 70 ];
Join 2 bảng dữ liệu với mức độ chi tiết khác nhau giúp loại bỏ được Synthetic Key, nhưng làm một số giá trị lặp lại nhiều lần trong bảng dữ liệu tổng hợp (trong ví dụ này là cột SalesPerson)
Tuy nhiên, với cơ chế index tất cả các filed của Qlik engine (xem bài viết Qlik engine lưu trữ dữ liệu như thế nào? ), việc lặp dữ liệu trong bảng không phải là vấn đề quá lớn vì không làm tăng đáng kể dung lượng bộ nhớ cần thiết để lưu trữ bảng dữ liệu.
2.2. Concatenate 2 bảng
Concatenate được dùng khi bản chất và mức độ chi tiết của mỗi dòng ở 2 bảng là như nhau, nhưng lại khác nhau ở một số thuộc tính. Xét ví dụ sau:
- Giao dịch bán hàng của một công ty được xác định duy nhất bởi mã mặt hàng (ProductID) và thời gian bán hàng (SalesTime)
- Giao dịch bán hàng có thể được thực hiện bởi nhân viên của công ty hoặc đại lý (agent)
Hệ thống bán hàng của công ty lưu các giao dịch bán hàng bởi nhân viên và bởi đại lý trong 2 fact table riêng biệt:
Qlik engine chỉ tự động concatenate 2 bảng nếu (1) các cột trong 2 bảng là hoàn toàn giống nhau hoặc (2) bảng B chứa toàn bộ các cột của bảng A. Trong trường hợp này, 2 bảng khác nhau ở cột Salesman và AgentName, vì vậy Qlik engine load 2 bảng riêng biệt và hình thành Synthetic Key do các field chung (SalesTime, ProductID, Quantity).
Bản chất và mức độ chi tiết của 2 bảng là hoàn toàn như nhau: mỗi dòng đều lưu trữ thông tin của 1 giao dịch bán hàng độc lập, chỉ khác nhau ở thuộc tính người thực hiện giao dịch là Salesman hay Agent. Vì vậy, có thể loại bỏ Synthetic Key bằng cách concatenate 2 bảng như sau:
InternalSales: LOAD * INLINE [ SalesTime, ProductID, Quantity, Salesman 20190101T081914, P001, 15, Albert 20190102T104532, P002, 20, Bob 20190103T150217, P003, 22, Carlos ]; Concatenate (InternalSales) AgentSales: LOAD * INLINE [ SalesTime, ProductID, Quantity, AgentName 20190101T092911, P001, 35, AgentA 20190102T112321, P002, 19, AgentB 20190103T135234, P003, 27, AgentC ];
Kết quả thu được là một bảng fact table duy nhất có số hàng bằng tổng số hàng 2 bảng ban đầu. Những hàng có giá trị Salesman thì AgentName nhận giá trị NULL và ngược lại
VietQlikies 