Cách xử lý mối quan hệ Many-to-many trong Power BI

Đối với ai đã đọc và tìm hiểu về Data modeling trong Power BI sẽ đều quen thuộc với việc set-up relationship. Chính việc liên kết relationship giữa các bảng khiến cho mô hình dữ liệu của chúng ta được xây dựng. Tuy nhiên, trong những bài viết về relationship có một phần luôn khiến chúng ta khó hiểu, hay thắc mắc nhưng mà thường bị tránh nhắc tới và nếu có nhắc tới cũng là khuyên chúng ta nên suy nghĩ theo một hướng khác. Không gì khác chính là mối quan hệ Many-to-many trong Power BI.

Một mối quan hệ mà chính Power BI cũng phải nhắc nhở chúng ta khi sử dụng.

Vậy mối quan hệ Many-to-many là gì và tại sao mọi người lại không hay đề cập tới mối quan hệ này. Trong bài viết này mình sẽ cùng mọi người đi từng bước để giải thích về mối quan hệ Many-to-many này nhé.

Mối quan hệ Many-to-many là gì?

Mối quan hệ Many-to-many là một loại quan hệ (relationship) giữa hai bảng trong Power BI mà cho phép các giá trị trong mỗi bảng có thể liên kết với nhiều giá trị trong bảng kia. 

Mối quan hệ Many-to-many có thể xảy ra khi hai bảng có một hoặc nhiều trường hoặc cột chung, và giá trị của mỗi trường hay cột này có thể xuất hiện ở nhiều dòng khác nhau trong cùng bảng đó. 

Các ví dụ điển hình về mối quan hệ Many-to-many thường xuất hiện: 

• Một tác giả có thể viết nhiều quyển sách; một quyển sách thì có nhiều tác giả.
• Một sản phẩm có thể thuộc về nhiều danh mục; một danh mục thì bao gồm nhiều sản phẩm. 
• Một học sinh thì tham gia nhiều lớp học; một lớp học thì có nhiều học sinh.

Chúng ta sẽ bắt đầu với một ví dụ Many-to-many thường hay gặp.

Nội dung: Với bộ dữ liệu Internetional Sales, bao gồm 2 bảng là Sales và Geography, chúng ta có dữ liệu tóm gọn như sau: 

Bảng Geography gồm các cột thông tin Zip, City, State, Region, District and Country.

Bảng Sales gồm các cột Product ID, Date, Zip, Units, Revenue và Country.

Ở đây chúng ta sẽ dễ dàng nhận ra, 2 bảng trên có một cột chung đó là cột Zip. Với việc có cột chung này, chúng ta hoàn toàn nghĩ tới việc liên kết 2 bảng thông qua cột Zip để xây dựng data model.

Tuy nhiên, bằng cơ chế duyệt nhanh cột của Power BI, chúng ta sẽ thấy cả 2 cột của 2 bảng trên có chứa những giá trị duplicate (khi so sánh total rows lớn hơn nhiều total distinct values).

Ở đây, chúng ta phân tích một chút tình huống trong 2 bảng: 

• Bảng Geography và Sales đều đang thể hiện cho ta thấy tình huống là một quốc gia sẽ có nhiều zip code và các quốc gia khác nhau sẽ có thể có cùng 1 zip code.  

Nếu chúng ta cứ sử dụng 2 cột này để xây dựng mối quan hệ giữa 2 bảng thì kết quả là chúng ta sẽ có mối quan hệ Many-to-many.

Và tất nhiên, với việc sử dụng mối quan hệ Many-to-many mà không có sự rõ ràng mạch lạc này sẽ khiến kết quả tính toán trong báo cáo của chúng ta bị sai và không có giá trị sử dụng. 

Chúng ta sẽ thấy kết quả từ visual table dưới đây với thông tin cột Country và Revenue (lấy từ bảng Sales) và cột Country (lấy từ bảng Geography) như dưới:

Các giá trị của bảng đã bị chồng chéo lên nhau (với cùng lựa chọn 1 ountry từ bảng Sales – ví dụ Australia cho ra cả giá trị Revenue của Mexico).

-> Các giá trị trong bảng của visual trên đã mắc lỗi không tường minh và kết quả thì đã ra không chính xác.

Vậy để giải quyết bài toán này, chúng ta sẽ phải làm sao? Hãy cùng mình sang phần kết tiếp, cách xử lý các trường hợp gặp phải Many-to-many relationship.

Cách xử lý từng trường hợp Many-to-many relationship 

Trong thực tế, Microsoft đã tổng kết 3 trường hợp Many-to-many relationship có thể xuất hiện. Đó là: 

• Liên kết giữa một bảng Fact và một bảng Dim 
• Liên kết giữa 2 bảng Dimension 
• Liên kết giữa 2 bảng Fact 
• Liên kết với một bảng Fact khác, có độ chi tiết cao hơn so với thông tin có ở trong các bảng Dimension 

Chúng ta sẽ cùng lần lượt xem từng trường hợp và cách giải quyết của nó nhé. 

Mối quan hệ Many-to-many với 1 bảng Fact và 1 bảng Dimension

Tiếp tục với ví dụ ở đầu bài, chúng ta đến với cách xử lý bài toán ví dụ của chúng ta, đó là mối quan hệ Many-to-many giữa 1 bảng Fact và 1 bảng Dimension.

Như mọi người đã xem ở phía trên, 2 bảng Sales và Geography đã xây dựng mối quan hệ Many-to-many giữa 2 bảng thông qua cột Zip.

Nếu cứ tiếp tục xây dựng mối quan hệ kiểu này thì gần như các giá trị tính toán phía sau của chúng ta đều bị nhầm lẫn và không có giá trị sử dụng.

-> Cách thức suy nghĩa của chúng ta sẽ là cố gắng biến mối quan hệ Many-to-many này trở lại thành mối quan hệ one to many hay many to one thân thuộc.

Ở 2 bảng trên ngoài cột giá trị Zip là một cột chung, chúng ta sẽ thấy cột giá trị khác là Country cũng xuất hiện ở cả 2 bảng.

Đào sâu vào nội dung bảng, chúng ta sẽ nhận ra là mỗi một quốc gia thì các giá trị zip code là độc nhất (unique) nhưng các quốc gia khác nhau có thể trùng giá trị zip code.

-> Đến đây, mình nghĩ mọi người đã nhận ra, chúng ta có thể từ 2 cột Country và Zip để tạo một cột mới chứa đó là cột Zip Country bao gồm toàn bộ các giá trị unique.

Trong bảng Geography, chúng ta tạo cột Zip Coutry như sau:

Trong bảng Sales, chúng ta tạo cột Zip Coutry như sau:

Lúc đó bảng Geography đã có cột giá trị Zip Country chứa toàn bộ là các giá trị unique value (Total Rows = Total Distinct Values).

Mối quan hệ giữa 2 bảng lúc này sẽ được cơ cấu lại (thay vì kết nối bằng cột Zip, chúng ta sẽ kết nối bằng cột Zip Country).

Quay lại visual table chúng ta đã tạo với giá trị Country và Revenue từ bảng Sales và giá trị Country từ bảng Geography.

Kết quả hiện tại đã không còn bị nhầm lẫn giữa các quốc gia.

-> Đến đây, chúng ta đã xử lý được mối quan hệ Many-to-many giữa 1 bảng Fact và 1 bảng Dimension. Bằng cách đào sâu vào nội dung bảng để tìm cách xây dựng cột giá trị unique giữa 2 bảng, từ đó xây dựng lại mối quan hệ one to many tường minh.

Mối quan hệ Many-to-many với 2 bảng Dimension

Chúng ta tiếp đến một trường hợp cũng hay xảy ra của mối quan hệ Many-to-many, đó là mối quan hệ Many-to-many với 2 bảng Dimension:

Giả định tình huống: Chúng ta có 4 bảng thông tin.

• Bảng Student và bảng Class: Chứa thông tin học sinh và lớp học.

• StudentClass – Với thông tin học sinh học ở từng lớp (1 lớp có 2 học sinh và 1 học sinh học 2 lớp).

• StudentFee – Học sinh thanh toán tiền học phí theo tháng.

Với bài toán cần phải tính Total Fee theo class hoặc theo Student, chúng ta có thể cân nhắc hai phương hướng suy nghĩ.

Cách 1: Tạo BI-directional relationship giữa 2 bảng.

Sau khi xây dựng xong các bảng, các mối liên kết one – many liên kết giữa các bảng Dim với bảng cầu nối và các bảng Dim với nhau.

Ở đây, chúng ta đặt ra bài toán tính toán chi phí theo học sinh và theo lớp. Với bảng đầu tiên là Student Balance và bảng thứ 2 là Class Balance. Mọi người có thấy vấn đề gì không?

Nếu nhìn vào giá trị trong visual Student Balance, con số có vẻ đúng. Nhưng sang tới bảng Class Balance nếu chúng ta tính toán chi phí theo lớp học, đã có một sự sai sót khi cả 2 lớp class A và class B đều ra kết quả là 275.

Lý do vì sao? Nhìn trên mô hình bạn sẽ dễ dàng nhận ra là mũi tên liên kết giữa bảng class với bảng StudentFee đang không hợp lý. Với liên kết giữa bảng Student và bảng StudentClass là mối quan hệ một hướng từ bảng Student tới bảng StudentClass nên tất nhiên khi yêu cầu tính toán từ bảng Class sang bảng StudentFee sẽ không được triển khai.

Phương án giải quyết:  

Chúng ta sẽ tạo thêm một liên kết BI-directional cho 2 bảng Student và StudentClass.

-> Sau khi đã thực hiện xong việc thay đổi kết nối, kết quả bài toán ra đã hợp lý hơn, khi giá trị class A là 75 và class B là 275.

Nguyên tắc chung khi gặp trường hợp xây dựng bảng cầu để là: 

• Kết nối 2 bảng dim với một bảng bridge table (với đầy đủ giá trị chung của cả 2 bảng).
• Điều chỉnh hướng lọc dữ liệu phù hợp.

Cách 2: Xây dựng mối quan hệ Many-to-many 

Chúng ta xây dựng mối quan hệ Many-to-many trực tiếp giữa bảng StudentClass và bảng StudentFee; các bảng Student và Class liên kết với bảng StudentClass trong mối quan hệ one-many. 

Với bài toán tính học phí theo class và theo học sinh, chúng ta có thể kéo thả các trường Student và Class để thể hiện total fee với kết quả như sau: 

-> Kết quả ra giống với kết quả đúng ở trường hợp trên.

Với bài toán đầu tiên chúng ta đã tìm ra 2 cách để giải quyết bài toán đầu tiên. Do mô hình của mình xây dưng trong bài viết khá đơn giản nên vấn đề hiệu suất giữa 2 cách giải quyết này đều như nhau. Tuy nhiên trong thực tế, khi bài toán trở nên phức tạp hơn thì bạn nên linh hoạt lựa chọn cách thức triển khai để nâng cao hiệu suất của báo cáo.

Mối quan hệ Many-to-many giữa 2 bảng Fact

Mối quan hệ thứ 2 trong tình huống quan hệ Many-to-many cũng thường xuyên gặp và hay gây khó khăn cho các bạn mới tiếp xúc là mối quan hệ giữa 2 bảng Fact.

Tình huống: Chúng ta có 2 bảng Fact là Order và Fulfillment với bảng dữ liệu như bên dưới: 

2 bảng Order vs Fulfillment liên kết với nhau bằng mối quan hệ Many-to-many thông qua cột chung là cột order ID:

Hãy xem điều gì sẽ xảy ra khi mà chúng ta muốn tính số lượng đơn hàng Order Quantity và số lượng đơn hàng được đáp ứng Fulfillment Quantity dựa theo cột Order ID của bảng Order nhé.  

-> Kết quả này đúng. Chúng ta có thể dễ dàng nhìn vào nội dung 2 bảng trên và tính để ra đáp án như visual là đúng. 

Tuy nhiên, nếu chúng ta thay đổi một chút về đề bài thì sao thì sao. Mình sẽ tính toán Order Quantity và Fulfilment Quantity nhưng sử dụng cột OrderID nhưng từ bàng Fullfilment Order.

-> Đáp án giờ đã không hợp lý nữa. OrderID bị thiếu một dòng OrderID 3 và thiếu cả giá trị OrderQuantity nhưng giá trị total thì vẫn ra đúng.

Thực tế là với trường hợp 2 bảng với mối liên hệ như trên chỉ có thể filter hoặc tính toán trên cột OrderID của bảng Order là chính xác, còn sử dụng các cột khác cùng bảng hoặc khác bảng đều gây ra sự hiểu nhầm nếu chũng ta không hiểu rõ số liệu.

Phương án giải quyết: 

Thay vì liên kết trực tiếp giữa 2 bảng Fact, chúng ta sẽ cùng xây dựng các bảng Dim theo mô hình ngôi sao <star schema> và để các bảng Dim này sẽ liên kết với bảng Fact theo mối quan hệ one to many.  

Với cách thiết kế này chúng ta có thể linh hoạt lựa chọn các trường cần tính toán theo Order ID, Order Line, Order Date, Fulfillment Date, Product cho cả 2 bảng Fact.

Nguyên tắc chung khi gặp mối quan hệ dạng là: 

• Không tạo liên kết trực tiếp giữa 2 bảng Fact.
• Xây dựng các bảng Dim để tạo kết nối one to many với từng bảng Fact. Để từ đó tính toán các trường giá trị theo bảng Dim. 

Liên kết với một bảng Higher Grain Facts 

Đầu tiên, cần phải giải thích một chút là bảng Higher Grain Facts là những bảng Fact bao gồm các thông tin chi tiết được thể hiên ở bậc cao hơn so với các thông tin bậc cơ bản của các bảng Dimension.

Tình huống: Bảng Target được gọi là bảng Higher Grain Facts do chứa 2 trường thông tin là Category và Target Year. 

• Với trường Target Year: Target ở đây là Target cho cả năm. So với trường Date ở bảng Dim Date thì trường Year ở bảng Target Year nằm ở bậc cao hơn. Khi biểu diễn date ở Power BI, bặt buộc phải hiển thị theo ngày nên ở đây trường target year thể hiện ngày đầu tiên của năm. 
• Với trường Category: Category là bậc cao hơn khi so với cột Product của bảng Product. 

Khi tiến hành kết nối giữa 2 bảng thì mối quan hệ với các bảng Dim sẽ được tính là mối quan hệ Many-to-many.

Chúng ta sẽ xử lý lần lượt 2 mối quan hệ Many-to-many: 

• Giữa bảng Target với bảng Date (Liên quan tới thời gian).
• Giữa bảng Target với bảng Product (Không liên quan tới thời gian). 

Cách giải quyết đối với kết nối giữa bảng Target và bảng Date (Liên quan tới thời gian).

Với việc xây dựng mối quan hệ Many-to-many giữa trường Date với trường Target – thông qua key là Year – theo hướng lọc 2 chiều ta có mô hình như bên dưới. 

Chúng ta sẽ tính target Quantity theo trường thời gian Date để xem có kết quả nào không? 

Giá trị duy nhất Target Quantity hiện thỉ là giá trị total năm, bảng Dim Date đã không thể filter cho bảng Target Quantity.

Cách liên kết Many-to-many này có vẻ không thể thực hiện được. 

Phương hướng giải quyết: 

Chúng ta bước đầu xây dựng liên kết giữa bảng Target với bảng Date theo mối quan hệ one to many với cột Target Year và cột Date (Không liên kết giữa cột Year bảng Date với cột Year bảng target). 

Với việc coi giá trị target year vào ngày đầu tiên của tháng, chúng ta đã tránh được sự mơ hồ khi lựa chọn mối quan hệ Many-to-many và với cách liên kết này thì cách thức tính toán của chúng ta trở lại thành cách thức one to many hiệu quả.  

Với kiểu liên kết one to many này, chúng ta có thể dễ dàng tính toán các hàm tính Target Quantity theo năm và kết quả ra chính xác.  

Cách giải quyết đối với dữ liệu bậc cao là dữ liệu khác, không liên quan tới thời gian.

Chúng ta liên kết giữa 2 bảng Target với bảng Product với nhau theo trường duy nhất chung giữa 2 bảng là trường Category và mối liên hệ này sẽ là mối liên hệ Many-to-many (vì cả 2 bảng đều có các giá trị lặp category).

Hãy nhìn kỹ hơn một chút về các giá trị trong 2 bảng Product và bảng Target.

Chúng ta kiểm tra một chút về giá trị tính toán của bảng. Với việc xây dựng visual là Category Target để tính Target Quantity như bảng dưới:

-> Các tính toán và hiển thị trên hợp lý. Đúng với bản chất của số liệu.

Thử xoay sang một ví dụ khác: đó là tính Target Quantity theo Color thì sẽ ra sao? 

-> Hình như có gì đó sai ở bảng trên. Các giá trị tính toán có vẻ không được đúng lắm. Ở đây giá trị tính toán theo màu sắc, đang gộp lại hết các giá trị bảng Category và bị chồng chéo lên nhau. Mặc dù đáp án ra con số, nhưng những số này sai về mặt ý nghĩa và logic. 

-> Một bảng thể hiện rõ bản chất không rõ ràng của dữ liêu. Khi total = 291 và và các dòng khác đều không thể hiện gì (đúng bản chất của dữ liệu không đầy đủ).

Một vài lưu ý khi sử dụng mối quan hệ Many-to-many 

Tất nhiên là khi làm việc với mối quan hệ many-to-many trong Power BI, có một số vấn đề cần lưu ý để đảm bảo tính chính xác và hiệu suất của báo cáo. Dưới đây là một số vấn đề cần lưu ý: 

• Kích thước dữ liệu: Mối quan hệ many-to-many có thể tạo ra các bảng dữ liệu lớn và phức tạp. Do đó, bạn cần đảm bảo rằng kích thước dữ liệu không ảnh hưởng đến hiệu suất của báo cáo.
• Sự trùng lặp dữ liệu: Khi sử dụng mối quan hệ many-to-many, có thể xảy ra sự trùng lặp dữ liệu trong bảng cầu. Điều này có thể dẫn đến các lỗi tính toán và hiển thị sai kết quả trên báo cáo của bạn. Bạn nên đảm bảo rằng các giá trị trong bảng cầu đều là duy nhất. 
• Tối ưu hóa hiệu suất: Bạn nên tối ưu hóa hiệu suất của báo cáo bằng cách sử dụng các bộ lọc và định dạng dữ liệu hợp lý. Nếu báo cáo của bạn chậm hoặc không đáp ứng được yêu cầu của người dùng, bạn có thể xem xét sử dụng các kỹ thuật tối ưu hóa hiệu suất khác như tạo các chỉ mục hoặc sử dụng bảng dữ liệu tạm thời. 
• Tập trung vào tính chính xác: Mối quan hệ many-to-many có thể dẫn đến sai sót tính toán và kết quả trên báo cáo của bạn. Bạn nên đảm bảo rằng dữ liệu đầu vào của bạn là chính xác và đúng đắn để đảm bảo tính chính xác của báo cáo. 
• Kiểm tra báo cáo: Khi làm việc với mối quan hệ many-to-many, bạn nên kiểm tra báo cáo của mình thường xuyên để đảm bảo rằng nó đang hoạt động đúng và đáp ứng được yêu cầu của người dùng. 

Tổng kết 

Chúng ta đã cùng trải qua với nhau toàn bộ các phần về mối quan hệ Many-to-many trong relationship, cách xử lý trong từng trường hợp và những lưu ý khi xử lý ở từng phần.

Hy vọng bài viết này đã giải đáp được thắc mắc của các bạn về việc sử dụng Many-to-many, và nếu như từ giờ có ai đó bảo bạn không nên dùng many-to-many thì bạn hãy giải thích thật kỹ cho họ nhé.

Bài viết tham khảo: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships 

Bài viết cùng chủ đề: Tạo quan hệ many-to-many trong Power BI

My Đào Thị Trà