Wskazówki dotyczące relacji wiele do wielu

Ten artykuł jest przeznaczony dla Ciebie jako modeler danych, który współpracuje z programem Power BI Desktop. Opisuje trzy różne scenariusze modelowania wiele-do-wielu. Zawiera również wskazówki dotyczące pomyślnego projektowania modeli z uwzględnieniem ich potrzeb.

Notatka

Wprowadzenie do relacji modelu nie zostało omówione w tym artykule. Jeśli nie jesteś w pełni zaznajomiony z relacjami, ich właściwościami lub sposobem ich konfigurowania, zalecamy najpierw przeczytać artykuł Model relacji w programie Power BI Desktop.

Również ważne jest posiadanie zrozumienia projektu schematu gwiazdy. Aby uzyskać więcej informacji, zobacz Zrozum schemat gwiazdy i jego znaczenie dla Power BI.

Istnieją trzy różne scenariusze typu wielu-do-wielu. Mogą wystąpić, gdy jesteś zobowiązany do:

• Powiązać dwie tabele wymiarów
• Powiąż dwie tabele faktów
• Powiąż tabele faktów o wyższej granularności, gdy tabela faktów przechowuje wiersze o wyższej granularności niż wiersze tabeli wymiarów

Powiązanie wymiarów wiele-do-wielu

Klasyczny scenariusz wielu do wielu dotyczy dwóch podmiotów, na przykład klientów banku i rachunków bankowych. Należy wziąć pod uwagę, że klienci mogą mieć wiele kont, a konta mogą mieć wielu klientów. Gdy konto ma wielu klientów, są oni często nazywani wspólnymi posiadaczami kont.

Modelowanie tych jednostek jest proste. Jedna tabela wymiarowa przechowuje konta, a druga tabela wymiarowa przechowuje klientów. Podobnie jak w przypadku tabel wymiarów, w każdej tabeli znajduje się kolumna unikatowego identyfikatora (ID). Aby modelować relację między dwiema tabelami, wymagana jest trzecia tabela. Ta tabela jest często nazywana tabelą mostkową . W tym przykładzie celem jest przechowywanie jednego wiersza dla każdego skojarzenia konta klienta. Co ciekawe, jeśli ta tabela zawiera tylko kolumny identyfikatorów, jest nazywana tabela faktów bez faktów.

Oto uproszczony diagram trzech tabel modelu.

Pierwsza tabela ma nazwę Accounti zawiera dwie kolumny: AccountID i Account. Druga tabela nosi nazwę AccountCustomeri zawiera dwie kolumny: AccountID i CustomerID. Trzecia tabela nosi nazwę Customeri zawiera dwie kolumny: CustomerID i Customer. Relacje nie istnieją między żadną z tabel.

Dwie relacje jeden do wielu są dodawane w celu powiązania tabel. Oto zaktualizowany diagram modelu powiązanych tabel. Dodano tabelę faktów o nazwie Transaction. Rejestruje transakcje kont. Ta tabela łącząca i te wszystkie kolumny identyfikatorów zostały ukryte.

Aby ułatwić opisanie sposobu działania propagacji filtru relacji, diagram modelu został zmodyfikowany w celu wyświetlenia wierszy tabeli.

Szczegóły wiersza dla czterech tabel są prezentowane na następującej liście punktowanej:

• Tabela Account ma dwa wiersze:
  • AccountID 1 jest przeznaczony dla konta Account-01
  • AccountID 2 jest przeznaczony dla konta Account-02
• Tabela Customer ma dwa wiersze:
  • CustomerID 91 jest przeznaczony dla Customer-91
  • CustomerID 92 jest przeznaczony dla Customer-92
• Tabela AccountCustomer zawiera trzy wiersze:
  • AccountID 1 jest skojarzony z CustomerID91
  • AccountID 1 jest skojarzony z CustomerID92
  • AccountID 2 jest skojarzony z CustomerID92
• Tabela Transaction zawiera trzy wiersze:
  • Date 1 stycznia 2019 r., AccountID1, Amount100
  • Date 2 lutego 2019 r., AccountID2, Amount200
  • Date 3 marca 2019 r., AccountID1, Amount-25

Zobaczmy, co się stanie, gdy zapytamy model.

Na poniższej ilustracji przedstawiono dwie wizualizacje tabeli podsumowujące kolumnę Amount tabeli Transaction. Pierwsza wizualizacja grupuje według konta, dlatego suma kolumn Amount reprezentuje saldo konta . Druga wizualizacja grupuje według klienta, a więc suma kolumn Amount reprezentuje saldo klienta.

Pierwsza wizualizacja tabeli (Account Balance) ma dwie kolumny: Account i Amount. Zostanie wyświetlony następujący wynik:

• Account-01 saldo wynosi 75.
• konto-02 saldo wynosi 200.
• Suma wynosi 275.

Druga wizualizacja tabeli (Customer Balance) zawiera dwie kolumny: Customer i Amount. Zostanie wyświetlony następujący wynik:

• Saldo klienta Customer-91 wynosi 275.
• Customer-92 kwota salda wynosi 275.
• Suma wynosi 275.

Szybki rzut oka na wiersze tabeli i wizualizację Account Balance (Saldo konta) pokazuje, że wynik jest poprawny dla każdego konta i łącznej kwoty. Dzieje się tak, ponieważ każde grupowanie kont powoduje propagację filtru do tabeli Transaction dla tego konta.

Jednak coś nie jest poprawne w wizualizacji Customer Balance. Każdy klient w tej wizualizacji ma takie samo saldo jak łączne saldo. Ten wynik może być poprawny tylko wtedy, gdy każdy klient był współwłaścicielem każdego konta. Tak nie jest w tym przykładzie. Występuje problem i jest związany z propagacją filtru. Filtry nie przepływają do tabeli Transaction.

Jeśli stosujesz wskazówki dotyczące filtrowania relacji z tabeli Customer do tabeli Transaction, możesz określić, że relacja między tabelami Account i AccountCustomer jest propagowana w niewłaściwym kierunku. Kierunek filtrowania dla tej relacji musi być ustawiony na Both.

Zgodnie z oczekiwaniami nie wprowadzono żadnych zmian w wizualizacji Saldo konta.

Wizualizacja Customer Balance wyświetla jednak następujący wynik:

• Customer-91 saldo wynosi 75.
• Customer-92 saldo wynosi 275.
• Suma wynosi 275.

Wizualizacja Customer Balance wyświetla teraz prawidłowy wynik. Postępuj zgodnie z instrukcjami filtrowania dla siebie i zobacz, jak zostały obliczone salda klientów. Ponadto, należy rozumieć, że wizualna suma oznacza wszystkich klientów.

Ktoś nieznany relacjom modelu może stwierdzić, że wynik jest niepoprawny. Mogą zapytać: Dlaczego łączne saldo dla Customer-91 i Customer-92 nie jest równe 350 (75 + 275)?

Odpowiedź na ich pytanie tkwi w zrozumieniu relacji wiele do wielu. Każde saldo klienta może reprezentować dodanie wielu sald kont, a więc salda klientów są nie addytywne.

Wskazówki dotyczące powiązania wymiarów wiele-do-wielu

Jeśli masz relację wiele-do-wielu pomiędzy tabelami wymiarowymi, postępuj zgodnie z poniższymi wskazówkami:

• Dodaj każdą jednostkę powiązaną w relacji wiele do wielu jako tabelę modelu, upewniając się, że zawiera kolumnę z identyfikatorem (ID).
• Dodaj tabelę mostkową do przechowywania skojarzonych jednostek.
• Utwórz relacje jeden do wielu między trzema tabelami.
• Ustaw jedną dwukierunkową relację, aby umożliwić propagację filtru do tabeli faktów.
• Kiedy nie jest odpowiednie, aby brakowało wartości identyfikatorów, wyłącz właściwość Is Nullable — odświeżanie danych zakończy się niepowodzeniem, gdy brakuje wartości.
• Ukryj tabelę mostkową (chyba że zawiera ona inne kolumny lub miary wymagane do raportowania).
• Ukryj wszystkie kolumny identyfikatorów, które nie są odpowiednie do raportowania (na przykład gdy kolumny przechowują wartości klucza zastępczego).
• Jeśli ma sens pozostawienie widocznej kolumny ID, upewnij się, że znajduje się ona po stronie "jeden" w relacji — zawsze ukrywaj kolumnę po stronie "wiele". Wynika to z tego, że filtry użyte w slajdzie „jeden” poprawiają wydajność filtrowania.
• Aby uniknąć nieporozumień lub błędnej interpretacji, przekaż wyjaśnienia użytkownikom raportu — możesz dodać opisy z polami tekstowymi lub etykietkami narzędzi nagłówka wizualizacji .

Nie zalecamy bezpośredniego łączenia tabel wymiarowych w relacjach wiele-do-wielu. Takie podejście projektowe wymaga skonfigurowania relacji z kardynalnością wiele do wielu. Koncepcyjnie można to osiągnąć, ale oznacza to, że powiązane kolumny mogą zawierać zduplikowane wartości. Jest to jednak dobrze zaakceptowana praktyka projektowania, że tabele wymiarów mają kolumnę ID. Tabele wymiarów powinny zawsze używać kolumny ID jako „jednej” strony relacji.

Relacjonowanie faktów wiele-do-wielu

Inny typ scenariusza relacji wiele do wielu obejmuje powiązanie dwóch tabel faktów. Dwie tabele faktów mogą być powiązane bezpośrednio. Ta technika projektowania może być przydatna do szybkiej i prostej eksploracji danych. Jednak i aby było jasne, ogólnie nie zalecamy tego podejścia projektowego. Wyjaśnimy, dlaczego w dalszej części tej sekcji.

Rozważmy przykład obejmujący dwie tabele faktów: Order i Fulfillment. Tabela Order zawiera jeden wiersz na wiersz zamówienia, a tabela Fulfillment może zawierać zero lub więcej wierszy na wiersz zamówienia. Wiersze w tabeli Order reprezentują zamówienia sprzedaży. Wiersze w tabeli Fulfillment reprezentują elementy zamówienia, które zostały wysłane. Relacja wiele do wielu wiąże kolumny OrderID w każdej tabeli, przy czym propagacja filtru odbywa się tylko z tabeli Order, co oznacza, że tabela Order filtruje tabelę Fulfillment.

Kardynalność relacji jest ustawiona na Many-to-many w celu obsługi przechowywania zduplikowanych wartości kolumn OrderID w obu tabelach. W tabeli Order mogą istnieć zduplikowane wartości identyfikatorów, ponieważ kolejność może zawierać wiele wierszy. W tabeli Fulfillment mogą istnieć zduplikowane wartości identyfikatorów, ponieważ zamówienia mogą mieć wiele wierszy, a wiersze zamówienia mogą być realizowane wieloma przesyłkami.

Przyjrzyjmy się teraz wierszom tabeli. W tabeli Fulfillment zwróć uwagę, że linie zamówień mogą być realizowane za pośrednictwem wielu przesyłek. (Brak wiersza zamówienia oznacza, że zamówienie nie zostało jeszcze spełnione).

Szczegóły wierszy dla dwóch tabel zostały opisane na następującej liście wypunktowanej:

• Tabela Order zawiera pięć wierszy:
  • OrderDate 1 stycznia 2019 r., OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate 1 stycznia 2019 r., OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate 2 lutego 2019 r., OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2 lutego 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 3 marca 2019 r., OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• Tabela Fulfillment zawiera cztery wiersze:
  • FulfillmentDate 1 stycznia 2019 r., FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate 2 lutego 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2 lutego 2019 r., FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate 1 stycznia 2019 r., FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Zobaczmy, co się stanie po wysłaniu zapytania do modelu. Oto wizualizacja tabeli w postaci porównania ilości zamówień i realizacji według kolumny Order tabeli OrderID.

Wizualizacja przedstawia dokładny wynik. Jednak użyteczność modelu jest ograniczona, ponieważ można filtrować lub grupować tylko według Order tabeli OrderID kolumny.

Wskazówki dotyczące relacji wiele do wielu faktów

Ogólnie rzecz biorąc, nie zalecamy bezpośredniego powiązania dwóch tabel faktów przy użyciu kardynalności wiele-do-wielu. Głównym powodem jest to, że model nie zapewnia elastyczności w sposobach filtrowania lub grupowania wizualizacji raportu. W tym przykładzie wizualizacje mogą jedynie filtrować lub grupować według kolumny Order tabeli OrderID. Innym powodem jest jakość danych. Jeśli dane mają problemy z zachowaniem integralności, niektóre wiersze mogą zostać pominięte podczas wykonywania zapytań ze względu na kardynalność wiele-do-wielu i ograniczone powiązania.

Zamiast odnosić tabele faktów bezpośrednio, zalecamy zaimplementowanie projektu schematu gwiazdy . Oznacza to, że dodajesz tabele wymiarów. Te tabele wymiarów odnoszą się następnie do tabel faktów przy użyciu relacji jeden do wielu. Takie podejście projektowe jest niezawodne, ponieważ efektywnie zapewnia elastyczne opcje raportowania. Umożliwia ona filtrowanie lub grupowanie przy użyciu dowolnej kolumny tabeli wymiarów oraz podsumowywanie kolumn powiązanych tabel faktów.

Rozważmy lepsze rozwiązanie.

Zwróć uwagę na następujące zmiany projektowe:

• Model ma teraz cztery dodatkowe tabele: OrderLine, OrderDate, Producti FulfillmentDate.
• Cztery dodatkowe tabele to wszystkie tabele wymiarów, w których relacje jeden do wielu odnoszą się do tabel faktów.
• Tabela OrderLine zawiera kolumnę OrderLineID, która przechowuje wartość OrderID pomnożona przez 100 oraz wartość kolumny OrderLine — identyfikator każdego wiersza zamówienia.
• Tabele Order i Fulfillment zawierają teraz kolumnę OrderLineID i nie zawierają już kolumn OrderID i OrderLine.
• Tabela Fulfillment zawiera teraz kolumny OrderDate i ProductID.
• Tabela FulfillmentDate ma relację tylko z tabelą Fulfillment.
• Wszystkie kolumny identyfikatorów są ukryte.

Poświęcenie czasu na wdrożenie projektu schematu gwiazdy zapewnia następujące korzyści:

• Wizualizacje raportu mogą filtrować lub grupować według dowolnej dostępnej kolumny z tabel wymiarów.
• Wizualizacje raportu mogą podsumować dowolną widoczną kolumnę z tabel faktów.
• Filtry stosowane do tabel OrderLine, OrderDatelub Product są propagowane do obu tabel faktów.
• Wszystkie relacje są typu "jedno-do-wiele", a każda z nich jest relacją regularną. Problemy z integralnością danych nie będą maskowane. Aby uzyskać więcej informacji na temat oceny relacji, zobacz relacje modelu w programie Power BI Desktop.

Powiązanie bardziej szczegółowych faktów

Ten scenariusz wiele-do-wielu bardzo różni się od pozostałych dwóch opisanych w tym artykule.

Rozważmy przykład obejmujący cztery tabele: Date, Sales, Producti Target. Tabele Date i Product to tabele wymiarów, a relacje jeden do wielu odnoszą się do tabeli faktów Sales. Dotychczas stanowi dobry projekt schematu gwiazdy. Jednak tabela Target nie jest jeszcze powiązana z innymi tabelami.

Tabela Target zawiera trzy kolumny: Category, TargetQuantityi TargetYear. Wiersze tabeli ujawniają szczegółowość roku i kategorii produktów. Innymi słowy, cele — używane do mierzenia wydajności sprzedaży — są ustawiane każdego roku dla każdej kategorii produktów.

Ponieważ tabela Target przechowuje dane na wyższym poziomie niż tabele wymiarów, nie można utworzyć relacji jeden do wielu. Cóż, to prawda tylko dla jednego z relacji. Przyjrzyjmy się, jak tabela Target może być powiązana z tabelami wymiarów.

Łączenie bardziej ogólnych okresów czasu

Relacja między tabelami Date i Target powinna być relacją jeden do wielu. Dzieje się tak, ponieważ wartości kolumny TargetYear są datami. W tym przykładzie każda kolumna TargetYear przechowuje pierwszą datę roku docelowego.

Napiwek

Podczas przechowywania faktów o wyższym poziomie szczegółowości czasu niż dzień ustaw typ danych kolumny na data (lub liczba całkowita, jeśli używasz kluczy daty). W kolumnie zapisz wartość reprezentującą pierwszy dzień okresu. Na przykład okres roku jest rejestrowany jako 1 stycznia roku, a okres miesiąca jest rejestrowany jako pierwszy dzień tego miesiąca.

Należy jednak zadbać o to, aby filtry na poziomie miesiąca lub daty wygenerowały znaczący wynik. Bez żadnej specjalnej logiki obliczeń wizualizacje raportów mogą zgłaszać, że daty docelowe są dosłownie pierwszym dniem każdego roku. Wszystkie pozostałe dni — i wszystkie miesiące z wyjątkiem stycznia — będą podsumowywać ilość docelową jako PUSTĄ.

Poniższa wizualizacja macierzy pokazuje, co się stanie, gdy użytkownik raportu przejdzie do szczegółów z roku na miesiące. Wizualizacja stanowi podsumowanie kolumny TargetQuantity. (Opcja Pokaż elementy bez danych została włączona dla wierszy macierzy).

Aby uniknąć tego zachowania, zalecamy kontrolowanie podsumowania danych faktów przy użyciu miar. Jednym ze sposobów kontrolowania podsumowania jest zwrócenie wartości BLANK w przypadku wykonywania zapytań dotyczących okresów niższego poziomu. Alternatywnym rozwiązaniem, zdefiniowanym przy użyciu zaawansowanego języka DAX, jest rozdzielenie wartości na niższe okresy czasowe.

Rozważmy następującą definicję miary, która używa ISFILTERED funkcji języka DAX. Zwraca tylko wartość, gdy kolumny Date i Month nie są filtrowane.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

Poniższa wizualizacja macierzy używa miary Target Quantity. Pokazuje, że wszystkie miesięczne ilości docelowe są puste.

Powiązanie wyższego ziarna (bez daty)

Inne podejście projektowe jest wymagane w przypadku powiązania kolumny innej niż data z tabeli wymiarów z tabelą faktów (i jest ona bardziej szczegółowa niż tabela wymiarów).

Kolumny Category (z tabel Product i Target) zawierają zduplikowane wartości. Tak więc nie ma strony "jeden" dla relacji jeden do wielu. W takim przypadku należy utworzyć relację typu wiele-do-wielu. Relacja powinna propagować filtry w jednym kierunku z tabeli wymiarów do tabeli faktów.

Przyjrzyjmy się teraz wierszom tabeli.

W tabeli Target istnieją cztery wiersze: dwa wiersze dla każdego roku docelowego (2019 i 2020) oraz dwie kategorie (Odzież i akcesoria). W tabeli Product znajdują się trzy produkty. Dwie należą do kategorii odzieżowej, a jedna należy do kategorii akcesoriów. Jeden z kolorów odzieży jest zielony, a pozostałe dwa są niebieskie.

Grupowanie wizualne tabeli według kolumny Category z tabeli Product daje następujący wynik. Jednak ta wizualizacja generuje prawidłowy wynik. Teraz zastanówmy się, co się stanie, gdy kolumna Color z tabeli Product jest używana do grupowania ilości docelowej.

Wizualizacja tworzy błędne przedstawianie danych. Co się tutaj dzieje?

Filtr w kolumnie Color z tabeli Product powoduje wyświetlenie dwóch wierszy. Jeden z wierszy dotyczy kategorii Odzież, a drugi dotyczy kategorii Akcesoria. Te dwie wartości kategorii są propagowane jako filtry do tabeli Target. Innymi słowy, ponieważ kolor niebieski jest używany przez produkty z dwóch kategorii, te kategorie są używane do filtrowania obiektów docelowych.

Aby uniknąć tego zachowania, zgodnie z wcześniejszym opisem, zalecamy kontrolowanie podsumowania danych faktów przy użyciu miar.

Rozważmy następującą definicję miary. Zwróć uwagę, że wszystkie kolumny tabeli Product znajdujące się poniżej poziomu kategorii są testowane pod kątem filtrów.

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

Poniższa wizualizacja tabeli używa miary Target Quantity. Pokazuje, że wszystkie ilości docelowe koloru są puste.

Ostateczny projekt modelu wygląda następująco.

Wskazówki dotyczące powiązania faktów o wyższym stopniu szczegółowość

Jeśli musisz powiązać tabelę wymiarów z tabelą faktów, a tabela faktów przechowuje wiersze o wyższym stopniu szczegółowości niż wiersze tabeli wymiarów, postępuj zgodnie z poniższymi wskazówkami:

• W przypadku dat faktów o wyższym stopniu szczegółowości
  • W tabeli faktów zapisz pierwszą datę okresu.
  • Utwórz relację jeden do wielu między tabelą dat a tabelą faktów.
• Dla innych faktów o wyższym stopniu szczegółowości
  • Utwórz relację wiele-do-wielu między tabelą wymiarów a tabelą faktów.
• Dla obu typów
  • Kontroluj podsumowanie przy użyciu logiki miary — zwracaj wartość BLANK, gdy kolumny wymiaru niższego poziomu są używane do filtrowania lub grupowania.
  • Ukryj kolumny tabeli faktów z możliwością podsumowania — dzięki temu tylko miary mogą służyć do podsumowania tabeli faktów.

Powiązana zawartość

Aby uzyskać więcej informacji związanych z tym artykułem, zapoznaj się z następującymi zasobami:

• relacje modelu w programie Power BI Desktop
• Zrozumienie schematu gwiazdy i jego znaczenia dla Power BI
• wskazówki dotyczące rozwiązywania problemów z relacjami
• Pytania? Spróbuj zwrócić się do społeczności Fabric
• Sugestie? Wnoszenie pomysłów na ulepszanie Fabric