Một trong những yêu cầu căn bản của Business Intelligence dashboard là khả năng phân tích dữ liệu theo thời gian. Một hệ thống data warehouse điển hình thường chứa một hoặc nhiều chiều thời gian, ví dụ ngày lập đơn hàng, ngày giao hàng, v.v. Dữ liệu biến đổi theo thời gian (time-variant data) cũng là một trong những đặc điểm căn bản của data warehouse so với các hệ thống thông tin khác (xem bài viết Triển khai Data Warehouse với Qlik (P1) )
Tuy nhiên, các trường dữ liệu ngày tháng được load trực tiếp từ hệ thống nguồn thường không đáp ứng được các yêu cầu phân tích theo thời gian phức tạp trong BI dashboard. Lý do chủ yếu là các hệ thống nghiệp vụ thường ghi nhận thông tin khi xảy ra hoạt động (activity), nhưng không được thiết kế để ghi nhận thời gian không có hoạt động (inactivity). Vì vậy, dữ liệu nguồn thường chứa các mốc thời gian rời rạc và không tạo thành một chiều thời gian liền mạch.
Như trong ví dụ trong hình dưới đây, doanh thu có phát sinh vào các ngày 21-26/11/2014, trừ ngày 25. Vì vậy, khi vẽ biểu đồ doanh thu theo thời gian, đường thẳng nối liền từ ngày 24 đến ngày 26:
Biểu đồ này cung cấp thông tin sai lệch vì không thể hiện được thực tế rằng ngày 25 không có hoạt động bán hàng. Biểu đồ chính xác phải có ngày 25 trên trục thời gian với doanh thu bằng 0. Tuy nhiên, cột Order Date trong dữ liệu nguồn lại không có giá trị 25/11/2014.
Trong Qlik, vấn đề này thường được giải quyết bằng các tạo Master Calendar cho chiều thời gian. Master Calendar căn bản là một bảng chứa tất cả các ngày có thể có trong một khoảng thời gian định trước. Ngoài mục đích lấp đầy vào các ngày không được ghi nhận trong hệ thống nguồn, Master Calendar còn phục vụ mục tiêu phân tích dữ liệu thời gian theo các mức độ chi tiết (granularity) khác nhau, ví dụ tuần, tháng, quý, năm tài chính, v.v.
Bài viết hôm nay của VietQlikies sẽ trình bày từng bước xây dựng Master Calendar với một ví dụ cụ thể.
Chuẩn bị dữ liệu
Ví dụ sau đây cũng là dịp để thực hành một kết nối dữ liệu mà blog VietQlikies chưa có dịp trình bày: OLE DB (xem giới thiệu sơ lược về OLE DB tại bài viết Kết nối Qlik với nguồn dữ liệu (P2) ). Nguồn dữ liệu là một file MS Access.
Trước tiên, tải file SOP.mdb từ VietQlikies Public Share – Blog 34 và lưu vào một folder, ví dụ C:\Temp\SalesData. Trong Qlik Sense Desktop, tạo một app mới, mở Data Load Editor > Create new connection > OLE DB. Cấu hình OLE DB connector như sau:
- Name = SalesData
- Provider = Microsoft JET 4.0 OLE DB Provider (32-bit)
- Data source = đường dẫn đến file SOP.mdb
Nhấn Select Data 
trên connector vừa tạo. Trong cửa sổ Select data to load, chọn các bảng sau: orders, orderdetails, products, customers. Nhấn Insert script để tạo reload script, và Load data để load dữ liệu vào Qlik Sense.
Quan sát dữ liệu sau khi load, ta thấy:
- Data model chứa một synthetic key (key field được tạo thành từ 2 cột dữ liệu trở lên – VietQlikies sẽ trình bày sâu hơn về synthetic key trong một bài viết khác). Nguyên do là 2 bảng Products và OrderDetails đều chứa 2 cột ProductID và UnitPrice. Chúng ta sẽ loại bỏ synthetic key bằng cách đổi tên cột UnitPrice trong bảng Products thành OriginalPrice
- Các giá trị trong cột OrderDate chính xác đến giờ phút giây. Master Calendar hoạt động trên các giá trị ngày tháng, vì vậy ta sẽ dùng hàm Floor() để loại bỏ phần giờ phút giây.
- OrderDate phân bổ trong khoảng từ năm 2009-2014. Trong Master Calendar mà chúng ta sẽ xây dựng có một số filed có ý nghĩa gắn với năm hiện tại như YTD (YearToDate). Vì vậy chúng ta sẽ dùng hàm AddYears() để cộng thêm 5 năm vào tất cả các ngày trong cột OrderDate để có được một bộ dữ liệu bao phủ năm hiện tại (2014-2019).
Tạo Master Calendar
Một Master Calendar căn bản thường được tạo thành qua 3 bước như trong hình vẽ dưới đây. Trong đó, bước 1 và 2 ( tìm ra 2 mốc thời gian lớn nhất và nhỏ nhất, và tạo ra tất cả các ngày có thể giữa 2 mốc thời gian này) hoàn toàn tương tự với cách làm mà VietQlikies đã có dịp trình bày trong bài viết Xử lý tập dữ liệu thưa (sparse data) trong Qlik
Bước 1 – Xác định khoảng thời gian mà Master Calendar cần bao phủ
Tuy Master Calendar là bảng dữ liệu chứa tất cả các ngày tháng có thể có, nhưng người dùng dashboard thường chỉ có nhu cầu phân tích dữ liệu trong khoảng thời gian từ lúc bắt đầu phát sinh giao dịch nghiệp vụ đầu tiên. Vì vậy, trong phần lớn trường hợp, khoảng thời gian của Master Calendar thường được chọn từ ngày đầu tiên đến ngày cuối cùng được ghi nhận trong cột thời gian.
Ta có thể nhặt ra 2 mốc thời gian này bằng hàm Min() và Max(), sau đó lưu vào 2 biến vMinDate và vMaxDate để sử dụng ở bước tiếp theo.
MinMaxDate:
LOAD
MIN(OrderDate) AS MinDate,
MAX(OrderDate) AS MaxDate
RESIDENT Orders;
LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMaxDate'));
LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMaxDate'));
Bước 2 – Tạo ra tất cả các ngày trong khoảng thời gian đã xác định
Với 2 mốc thời gian lớn nhất và nhỏ nhất đã xác định trong 2 biến vMinDate và vMaxDate , ta sẽ dùng câu lệnh LOAD … AUTOGENERATE để sinh ra tất cả các ngày giữa 2 mốc thời gian này và lưu vào một bảng tạm:
TempCal:
LOAD
DATE($(vMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
Bước 3 – Tạo bảng Master Calendar và các field thời gian liên quan
Với mỗi giá trị ngày tháng giữa vMinDate và vMaxDate trong bảng tạm, chúng ta có thể dùng các hàm ngày tháng mà reload script của Qlik hỗ trợ để suy ra các giá trị nhóm ngày (tháng, quý, năm…) tương ứng. Các giá trị này hoàn toàn phụ thuộc vào nhu cầu phân tích của người dùng cuối. Đoạn script dưới đây tạo ra các nhóm ngày phổ biến:
MasterCalendar:
LOAD
TempDate AS OrderDate,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
YearToDate([TempDate])-1 AS CurrentYTDFlag,
YearToDate([TempDate],-1)-1 AS LastYTDFlag,
InYear(TempDate, $(vMaxDate),-1)*-1 AS LastYearFlag,
YearName(TempDate,0,4) AS FiscalYear,
WEEKDAY (TempDate) AS Weekday,
'Q' & CEIL(MONTH(TempDate) / 3) AS Quarter,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear
RESIDENT TempCal;
Trong đoạn script trên, ngoài các nhóm ngày thường gặp (Day, Week, Month, Year, Quarter) có thể suy ra trực tiếp từ giá trị ngày tháng, ta còn có thể thấy các nhóm sau:
- CurrentYTDFlag: Giá trị boolean (1/0) cho biết ngày có thuộc năm tính tới thời điểm hiện tại (year-to-date) không. Lưu ý hàm YearToDate tính từ đầu năm hiện tại đến thời điểm gần nhất mà app được reload.
- LastYTDFlag: Tương tự như CurrentYTDFlag, nhưng tính tới thời điểm hiện tại (lần reload gần nhất) của năm ngoái. Flag này rất hữu ích để làm so sánh dạng “cùng kỳ năm ngoái”
- LastYearFlag: Giá trị boolean (1/0) cho biết ngày có thuộc năm ngoái hay không (không phụ thuộc vào lần reload gần nhất)
- FiscalYear: Năm tài chính (hay tài khóa), dùng trong các phân tích tài chính. Ví dụ, nếu công ty bạn quy định năm tài chính kết thúc ngày 31/3 hàng năm, thì ngày 20/2/2019 được xem là thuộc năm tài chính 2018-2019.
- Weekday: Tên viết tắt của ngày trong tuần. Bạn có thể set giá trị cho biến DayNames để hiển thị tên ngày theo ngôn ngữ mong muốn.
- MonthYear: Tháng trong năm, ví dụ Apr-2019
- WeekYear: Tuần trong năm, ví dụ 51-2018
Bước cuối cùng là drop các table tạm:
DROP TABLES MinMaxDate, TempCal;
Sau khi reload, data model thu được như trong hình dưới. Có thể thấy bảng MasterCalendar đã được tạo ra và liên kết với bảng Orders ban đầu thông qua field OrderDate.
Xây dựng dashboard
Với MasterCalendar, bạn có rất nhiều lựa chọn cột dữ liệu làm chiều thời gian khi tạo bảng và biểu đồ. Việc tất cả các ngày được lấp đầy giữa 2 mốc cũng đồng thời đảm trục thời gian trên biểu đồ của bạn là một trục liên tục (continuous scale) giúp bạn có thể phóng to/ thu nhỏ (zoom in/out) biểu đồ đến mức độ chi tiết mình muốn.
Trong app mẫu, mình chỉ tạo một biểu đồ đường thẳng (line chart) với rất nhiều alternative dimension để minh họa cho các chiều thời gian đã được tạo ra trong Master Calendar.
Tham khảo ứng dụng mẫu tại VietQlikies Public Share – Blog 34
VietQlikies 