Kiến thức

22 phút đọc

Tổng Hợp Do Người Dùng Xác Định

1. Tạo bảng tổng hợp (aggregation tables)

Cách tạo bảng tổng hợp tùy thuộc vào nguồn dữ liệu: bảng, xem, truy vấn gốc hay bảng nhập. Dùng hộp thoại Quản lý tập hợp trong Power BI Desktop để chỉ định các cột tổng hợp, thuộc tính tóm tắt và bảng chi tiết.

Nguồn dữ liệu thứ nguyên sử dụng tổng hợp theo mối quan hệ. Nguồn dữ liệu lớn dựa trên Hadoop thường sử dụng tổng hợp GroupBy theo cột.

2. Quản lý tập hợp

Ở chế độ xem Modelling, nhấp chuột phải vào bất kỳ bảng tổng hợp. Sau đó chọn Manage aggregations.

Hộp thoại Manage aggregations hiển thị một hàng cho mỗi cột trong bảng.

Trong ví dụ sau, các truy vấn đến bảng chi tiết Sales được chuyển hướng nội bộ đến bảng tổng hợp Sales Agg.

Trong ví dụ tổng hợp dựa trên mối quan hệ này, các mục có lệnh GroupBy là tùy chọn. Ngoại trừ hàm DISTINCTCOUNT sẽ dùng để đọc nên không ảnh hưởng đến việc tổng hợp. Nếu không có các mục nhập GroupBy, các tập hợp vẫn sẽ bị ảnh hưởng tùy vào các mối quan hệ.

3. Xác thực (Validation)

Hộp thoại Manage aggregations sẽ tiến hành xác thực:

Cột Detail Column phải có cùng kiểu dữ liệu với Cột Aggregation Column. Ngoại trừ các hàm Count và Count table rows Summarization. Các hàng trong hai hàm trên chỉ khả dụng cho các cột tổng hợp số nguyên. Và nó không yêu cầu kiểu dữ liệu phù hợp.
Không được phép tổng hợp theo chuỗi bao gồm ba bảng trở lên. Ví dụ: Tập hợp trên Bảng A không thể tham chiếu Bảng B có tập hợp tham chiếu đến Bảng C.
Không được phép tổng hợp các tập hợp trùng lặp. Trong đó hai mục nhập sử dụng cùng một chức năng Summarization và tham chiếu đến cùng một Detail Table và Detail Column.
Bảng Detail Table phải sử dụng chế độ lưu trữ DirectQuery chứ không phải Nhập (Import).
Nhóm theo cột khóa ngoại được sử dụng bởi mối quan hệ không hoạt động. Và dựa vào hàm USERELATIONSHIP cho lần truy cập tổng hợp, không được hỗ trợ.
Các tập hợp dựa trên cột GroupBy có thể tận dụng mối quan hệ giữa các bảng tổng hợp. Nhưng mối quan hệ tác giả giữa các bảng tổng hợp không được hỗ trợ trong Power BI Desktop. Nếu cần thì mối quan hệ giữa các bảng tổng hợp bằng cách sử dụng công cụ của bên thứ ba. Hoặc giải pháp tạo tập lệnh thông qua các điểm cuối XMLA.

Hầu hết các xác thực được thực thi bằng cách vô hiệu hóa các giá trị thả xuống. Và nó hiển thị văn bản giải thích trong chú giải công cụ.

4. Bảng tổng hợp bị ẩn

Người dùng chỉ được truy cập dữ liệu chỉ đọc, không thể truy vấn các bảng tổng hợp. Điều này giúp tránh những lo ngại về bảo mật khi sử dụng row-level security. Không cần biết về bảng tổng hợp, người dùng có thể truy vấn bảng chi tiết.

Vì lý do này, các bảng tổng hợp được ẩn trong chế độ xem báo cáo. Nếu bảng chưa được ẩn, hộp thoại Manage aggregations sẽ ẩn bảng khi chọn Apply all.

5. Chế độ lưu trữ

Tính năng tổng hợp tương tác với các chế độ lưu trữ bảng. Chế độ lưu trữ có thể là DirectQuery, Import, hoặc Dual storage. DirectQuery truy vấn trực tiếp chương trình backend, Nhập lưu dữ liệu vào bộ nhớ cache. Tất cả các nguồn dữ liệu DirectQuery không đa chiều và Nhập Power BI có thể hoạt động với các tập hợp.

Để thiết lập chế độ lưu trữ bảng tổng hợp là Nhập (Import) để tăng tốc độ truy vấn, trong dạng xem Power BI Desktop Model chọn bảng tổng hợp. Trong ngăn Properties, mở rộng Advanced, chọn Storage mode và chọn Import.

6. RLS cho tập hợp

Để dùng RLS cho tập hợp, cả bảng tổng hợp và chi tiết phải được lọc.

Trong ví dụ này, RLS trên bảng Geography dùng cho tập hợp vì nó điều chỉnh mối quan hệ của bảng Sales và Sales Agg. Cả hai truy vấn chạm tổng hợp và không chạm sẽ dùng RLS thành công.

Biểu thức RLS trên bảng Product chỉ lọc bảng Sales chi tiết, không lọc bảng Sales Agg. Vì bảng tổng hợp là một biểu diễn khác của dữ liệu trong bảng chi tiết nên sẽ không an toàn khi trả lời các truy vấn từ bảng tổng hợp nếu không thể áp dụng bộ lọc RLS. Không nên chỉ lọc bảng chi tiết vì các truy vấn của người dùng từ vai trò này sẽ không được hưởng lợi từ các lần truy cập tổng hợp.

Biểu thức RLS chỉ lọc bảng tổng hợp Sales Agg chứ không phải bảng chi tiết Sales không được phép.

Đối với các tổng hợp dựa trên các cột GroupBy, một biểu thức RLS được áp dụng cho bảng chi tiết có thể được sử dụng để lọc bảng tổng hợp vì tất cả các cột GroupBy trong bảng tổng hợp đều được bao phủ bởi bảng chi tiết. Mặt khác, không thể áp dụng bộ lọc RLS trên bảng tổng hợp cho bảng chi tiết, vì vậy không được phép.

Xem thêm: Tạo Bảng Thời Gian Trong Power BI Desktop

7. Tập hợp dựa trên các mối quan hệ

Các mô hình thứ nguyên thường sử dụng các tập hợp dựa trên các mối quan hệ. Bộ dữ liệu Power BI từ kho dữ liệu và siêu thị dữ liệu giống với lược đồ hình sao/bông tuyết, với mối quan hệ giữa bảng thứ nguyên và bảng dữ kiện.

Trong ví dụ sau, mô hình lấy dữ liệu từ một nguồn dữ liệu duy nhất. Các bảng đang sử dụng chế độ lưu trữ DirectQuery. Bảng Sales dữ kiện chứa hàng tỷ hàng. Việc đặt chế độ lưu trữ của Sales thành Import để lưu vào bộ nhớ đệm sẽ tiêu tốn đáng kể bộ nhớ và tài nguyên chung.

Thay vào đó, hãy tạo bảng tổng hợp Sales Agg. Trong bảng Sales Agg, số hàng bằng tổng của SalesAmount được nhóm theo CustomerKey, DateKey và ProductSubcategoryKey. Bảng Sales Agg có độ chi tiết cao hơn so với Sales, do đó, thay vì hàng tỷ, bảng này có thể chứa hàng triệu hàng, dễ quản lý hơn nhiều.

Nếu các bảng thứ nguyên sau được sử dụng phổ biến nhất cho các truy vấn có giá trị kinh doanh cao, thì chúng có thể lọc Sales Agg bằng cách sử dụng mối quan hệ một-nhiều hoặc nhiều-một .

Geography
Customer
Date
Product Subcategory
Product Category

Hình ảnh sau đây cho thấy mô hình này.

Bảng sau đây hiển thị các tổng hợp cho bảng Sales Agg.

Bảng Sales Agg giống như bất kỳ bảng nào. Nó có tính linh hoạt khi được tải theo nhiều cách khác nhau. Có thể thực hiện tổng hợp trong cơ sở dữ liệu nguồn bằng ETL/ELT hoặc M. Bảng tổng hợp có thể sử dụng lưu trữ nhập và DirectQuery, có hoặc không có Làm mới gia tăng. Tối ưu cho truy vấn nhanh bằng cách sử dụng chỉ mục lưu trữ cột. Linh hoạt cho phép phân bổ tải truy vấn để tránh tắc nghẽn.

Việc thay đổi chế độ lưu trữ của bảng Sales Agg thành Import sẽ mở ra một hộp thoại cho biết rằng các bảng thứ nguyên liên quan có thể được đặt thành chế độ lưu trữ Dual

Đặt bảng thứ nguyên trong chế độ Dual cho phép hoạt động như Import hoặc DirectQuery tùy truy vấn con. Ví dụ:

Truy vấn tổng hợp số liệu từ bảng Sales Agg nhóm theo các thuộc tính từ các bảng Kép (Duals) có liên quan trả về từ bộ nhớ đệm trong bộ nhớ.
Truy vấn tổng hợp số liệu từ bảng Sales DirectQuery nhóm theo các thuộc tính từ các Dual tables có liên quan trả về trong chế độ DirectQuery. Logic truy vấn, bao gồm thao tác GroupBy, được chuyển xuống cơ sở dữ liệu nguồn.

8. Ví dụ về truy vấn tổng hợp dựa trên mối quan hệ

Truy vấn sau đây là một ví dụ về truy vấn tổng hợp, vì các cột trong bảng Date ở mức độ chi tiết. Cột SalesAmount sử dụng hàm tổng hợp Sum.

Truy vấn không thực hiện được tổng hợp. Yêu cầu tổng SalesAmount nhưng thao tác GroupBy trên cột Product không chi tiết đủ. Mô hình có nhiều hàng Product trong danh mục con sản phẩm. Truy vấn không thể xác định sản phẩm cần tổng hợp. Trong trường hợp này, truy vấn trở lại DirectQuery và gửi truy vấn SQL tới nguồn dữ liệu.

Không chỉ tính tổng đơn giản, tập hợp cũng hỗ trợ phép tính phức tạp. Phép tính phức tạp chia nhỏ thành truy vấn con cho SUM, MIN, MAX, COUNT. Đánh giá mỗi truy vấn con để xem nó có thể tổng hợp hay không. Tuy nhiên, logic này không hoạt động trong tất cả các trường hợp vì tối ưu hóa kế hoạch truy vấn. Nhưng thông thường, có thể áp dụng logic này. Ví dụ:

Hàm COUNTROWS có thể được hưởng lợi từ các tập hợp. Truy vấn sau chạm vào tập hợp vì có một tập hợp các hàng trong bảng Count table rows được xác định cho bảng Sales:

Hàm AVERAGE có thể được hưởng lợi từ các tập hợp. Truy vấn sau chạm vào tập hợp vì AVERAGE bên trong được gấp lại thành SUM chia cho COUNT. Vì cột UnitPrice có các tập hợp được xác định cho cả SUM và COUNT, nên tập hợp được nhấn.

Hàm DISTINCTCOUNT có thể dùng được với tập hợp. Truy vấn dùng GroupBy CustomerKey trong bảng tổng hợp. Có thể dùng khi có 2-5 triệu giá trị khác nhau trong bảng chi tiết có hàng tỷ hàng, nhưng 2-5 triệu giá trị khác nhau trong cột. DISTINCTCOUNT có thể nhanh hơn khi dùng với bảng lưu trữ trong bộ nhớ đệm.

Các chức năng thông minh về thời gian của DAX có nhận biết tổng hợp. Truy vấn sau chạm vào tập hợp vì hàm DATESYTD tạo ra một bảng các giá trị CalendarDay và bảng tổng hợp ở mức độ chi tiết được bao phủ cho các cột theo nhóm trong bảng Date Table. Đây là một ví dụ về bộ lọc có giá trị bảng cho hàm CALCULATE, có thể hoạt động với các tập hợp.

9. Tổng hợp dựa trên các cột GroupBy

Mô hình dữ liệu lớn dựa trên Hadoop khác với mô hình dữ liệu thứ nguyên. Để tránh liên kết giữa các bảng lớn, các mô hình này thường không sử dụng quan hệ nhưng không chuẩn hóa thuộc tính thứ nguyên. Các mô hình dữ liệu lớn có thể mở khóa để phân tích tương tác qua GroupBy. Bảng chứa cột Movement sẽ được tổng hợp. Tất cả các cột khác là thuộc tính GroupBy. Bảng chứa dữ liệu IoT và nhiều hàng. Chế độ lưu trữ là DirectQuery. Truy vấn trên nguồn dữ liệu tổng hợp toàn bộ tập dữ liệu bị chậm vì khối lượng quá lớn.

Để phân tích tương tác trên dữ liệu, thêm bảng tổng hợp theo các thuộc tính, ngoại trừ các thuộc tính có số lượng bản đồ cao như kinh độ và vĩ độ. Điều này giảm đáng kể số lượng hàng và nhỏ đủ cho bộ đệm trong bộ nhớ.

Xác định ánh xạ tổng hợp (aggregation mappings) cho bảng Driver Activity Agg của trình điều khiển trong hộp thoại Manage aggregations.

Trong tập hợp dựa trên cột GroupBy, các mục nhập GroupBy không phải là tùy chọn. Không có chúng, các tập hợp sẽ không chạy. Điều này khác với việc sử dụng tập hợp dựa trên mối quan hệ, trong đó các mục nhập GroupBy là tùy chọn.

Bảng sau đây hiển thị các tập hợp cho bảng Driver Activity Agg của trình điều khiển .

Bạn có thể đặt chế độ lưu trữ của bảng Driver Activity Agg trình điều khiển tổng hợp thành Import.

10. Ví dụ truy vấn tổng hợp GroupBy

Truy vấn sau chạm vào tập hợp, bởi vì cột Activity Date được bao phủ bởi bảng tổng hợp. Hàm COUNTROWS sử dụng tập hợp các bảng Count table rows.

Đặc biệt đối với các mô hình có chứa các thuộc tính bộ lọc trong các bảng dữ kiện, bạn nên sử dụng các tập hợp các hàng trong bảng Count table rows. Power BI có thể gửi truy vấn tới tập dữ liệu bằng cách sử dụng COUNTROWS trong trường hợp người dùng không yêu cầu rõ ràng. Ví dụ, hộp thoại bộ lọc hiển thị số lượng hàng cho mỗi giá trị.

11. Kỹ thuật tổng hợp kết hợp

Các mối quan hệ và hàm GroupBy có thể được sử dụng để tổng hợp dữ liệu. Những tập hợp này có thể yêu cầu bảng gốc phải được chia thành nhiều bảng khác. Nếu việc này không hiệu quả hoặc không thực tế cho bảng gốc cụ thể, có thể sao chép các thuộc tính cần thiết vào bảng tổng hợp và sử dụng mối quan hệ cho các bảng khác. Ví dụ: trong mô hình dưới đây, thuộc tính Month, Quarter, Semester và Year trong bảng Sales Agg được sao chép. Không có mối quan hệ giữa Sales Agg và bảng Date, nhưng có mối quan hệ với bảng Customer và Product Subcategory. Chế độ lưu trữ của Sales Agg là chế độ nhập (Import).

Bảng sau đây hiển thị mục đặt trong hộp thoại quản lý tập hợp cho bảng Sales Agg. Date là bảng chi tiết bắt buộc để tạo tập hợp truy vấn nhóm theo thuộc tính Date. Các mục nhập GroupBy CustomerKey và ProductSubcategoryKey không ảnh hưởng đến lần truy cập tổng hợp trừ khi có mối quan hệ DISTINCTCOUNT.

12. Ví dụ truy vấn tổng hợp kết hợp

Truy vấn sau dùng để tổng hợp các bảng bao gồm CalendarMonth và CategoryName có thể truy cập thông qua mối quan hệ một-nhiều. SalesAmount sử dụng tổng hợp SUM.

Truy vấn sau đây không tổng hợp được vì bảng tổng hợp không bao gồm CalendarDay.

Truy vấn thông minh về thời gian sau đây không đạt được tổng hợp, bởi vì hàm DATESYTD tạo một bảng các giá trị CalendarDay và bảng tổng hợp không bao gồm CalendarDay.

13. Ưu tiên tổng hợp

Quyền ưu tiên tổng hợp cho phép nhiều bảng xem xét bởi một truy vấn con.

Ví dụ sau đây là một mô hình tổng hợp chứa nhiều nguồn:

Bảng Driver Activity về hoạt động của trình điều khiển, Bảng Driver Activity Agg là bảng trung gian trong chế độ DirectQuery, Bảng Driver Activity Agg2 có độ chi tiết cao. DirectQuery sử dụng nguồn dữ liệu khác với bảng chi tiết. Các bảng tổng hợp DirectQuery chỉ được hỗ trợ từ SQL Server, Azure SQL hoặc Azure Synapse Analytics. Các bảng chi tiết được sử dụng cho các truy vấn nhanh. Và các bảng tổng hợp cho các truy vấn chi tiết.

Bảng tổng hợp DirectQuery sử dụng nguồn dữ liệu khác nhau và chỉ được hỗ trợ khi đến từ SQL Server, Azure SQL, hoặc Azure Synapse Analytics (trước đây là SQL Data Warehouse). Dung lượng bộ nhớ của mô hình này nhỏ, nhưng có khả năng mở ra tập dữ liệu lớn. Đây là mô hình cân bằng vì nó trải tải truy vấn trên các thành phần của kiến trúc, sử dụng ưu điểm của chúng.

Cài đặt ưu tiên cao hơn cho hộp thoại tập hợp lái xe Agg2 trong Manage Aggregations. Có nghĩa là truy vấn sử dụng Agg2 sẽ được ưu tiên trước. Tuy nhiên, các truy vấn chi tiết không thể trả lời bởi Agg2 sẽ được chuyển tới Agg. Bảng chi tiết được chỉ định là Driver Activity. Nó không phải là Agg, vì không được phép tập hợp theo chuỗi.

Bảng sau đây hiển thị Driver Activity Agg2 của Trình điều khiển.

14. Phát hiện xem truy vấn đạt hay bỏ lỡ tập hợp

SQL Profiler có thể phát hiện xem các truy vấn được trả về từ công cụ lưu trữ bộ nhớ cache trong bộ nhớ hay được DirectQuery đẩy tới nguồn dữ liệu. Bạn có thể sử dụng quy trình tương tự để phát hiện xem các tập hợp có bị tấn công hay không.

SQL Profiler cũng cung cấp Query ProcessingAggregate Table Rewrite Query sự kiện mở rộng.

Đoạn mã JSON sau đây hiển thị ví dụ về đầu ra của sự kiện khi sử dụng tập hợp:

matchResult cho thấy rằng truy vấn con đã sử dụng một sự tổng.
dataRequest hiển thị (các) cột GroupBy và (các) cột tổng hợp mà truy vấn con đã sử dụng.
mappings hiển thị các cột trong bảng tổng hợp đã được ánh xạ tới

Phát hiện xem truy vấn đạt hay bỏ lỡ tập hợp

15. Đồng bộ hóa bộ nhớ cache

Các chế độ DirectQuery, Nhập và Dual Storage có thể trả về dữ liệu khác nhau khi bộ nhớ cache không đồng bộ với dữ liệu nguồn. Ví dụ: truy vấn sẽ không cố gắng lọc kết quả DirectQuery để khớp với giá trị trong cache. Có kỹ thuật để xử lý vấn đề như vậy tại nguồn. Tối ưu hóa hiệu suất chỉ nên dùng không làm ảnh hưởng đến khả năng đáp ứng kinh doanh. Bạn phải biết các luồng dữ liệu và thiết kế phù hợp.

Trên đây là những thông tin cần thiết để biết cách tổng hợp do người dùng xác định trong Power BI Desktop. Để tìm hiểu thêm về Power BI Desktop, các bạn có thể tham khảo series Power BI Desktop tại Mastering Data Analytics. Cảm ơn các bạn đã đọc bài!

Xem thông tin khai giảng Khóa học Business Intelligence sớm nhất tại Mastering Data Analytics. Với mọi thắc mắc xin liên hệ hotline 0961 48 66 48 hoặc inbox Fanpage Mastering Data Analytics để đăng ký nhanh nhất nhé!