Wskazówki dotyczące relacji wiele do wielu

Ten artykuł jest przeznaczony dla Ciebie jako modeler danych pracujący z programem Power BI Desktop. Opisuje trzy różne scenariusze modelowania wiele-do-wielu. Zawiera również wskazówki dotyczące pomyślnego projektowania ich w modelach.

Uwaga

Wprowadzenie do relacji modelu nie zostało omówione w tym artykule. Jeśli nie znasz całkowicie relacji, ich właściwości lub sposobu ich konfigurowania, zalecamy najpierw przeczytanie artykułu Relacje modelu w programie Power BI Desktop .

Ważne jest również, aby zrozumieć projekt schematu gwiazdy. Aby uzyskać więcej informacji, zobacz Omówienie schematu gwiazdy i znaczenia usługi Power BI.

Istnieją w rzeczywistości trzy scenariusze wiele do wielu. Mogą wystąpić, gdy są wymagane:

• Powiązanie dwóch tabel wymiarów
• Powiązanie dwóch tabel faktów
• Powiązanie tabel faktów o wyższym stopniu szczegółowość, gdy tabela faktów przechowuje wiersze o wyższym stopniu szczegółowość niż wiersze tabeli wymiarów

Uwaga

Usługa Power BI obsługuje teraz natywnie relacje wiele-do-wielu. Aby uzyskać więcej informacji, zobacz Stosowanie relacji wiele-do-wielu w programie Power BI Desktop.

Powiązanie wymiarów wiele-do-wielu

Rozważmy pierwszy typ scenariusza wiele do wielu z przykładem. Klasyczny scenariusz dotyczy dwóch jednostek: klientów bankowych i kont 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ą one często nazywane wspólnymi posiadaczami kont.

Modelowanie tych jednostek jest proste. Jedna tabela wymiarów przechowuje konta, a druga tabela wymiarów przechowuje klientów. Podobnie jak w przypadku tabel wymiarów, w każdej tabeli znajduje się kolumna ID. Aby modelować relację między dwiema tabelami, wymagana jest trzecia tabela. Ta tabela jest często nazywana tabelą pomostową. 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 tabelą faktów bez faktów.

Oto uproszczony diagram modelu trzech tabel.

Pierwsza tabela ma nazwę Account (Konto) i zawiera dwie kolumny: AccountID (Identyfikator konta) i Account (Konto). Druga tabela nosi nazwę AccountCustomer i zawiera dwie kolumny: AccountID i CustomerID. Trzecia tabela nosi nazwę Customer (Klient) i zawiera dwie kolumny: CustomerID (Identyfikator klienta) i Customer (Klient). 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( Transakcja ). Rejestruje transakcje kont. Tabela mostkowania i 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.

Uwaga

Nie można wyświetlić wierszy tabeli na diagramie modelu programu Power BI Desktop. W tym artykule wykonano obsługę dyskusji z jasnymi przykładami.

Szczegóły wiersza dla czterech tabel zostały opisane na poniższej liście punktowanej:

• Tabela Account (Konto) zawiera dwa wiersze:
  • AccountID 1 jest przeznaczony dla konta-01
  • AccountID 2 jest przeznaczony dla konta-02
• Tabela Customer (Klient) zawiera dwa wiersze:
  • CustomerID 91 jest przeznaczony dla klienta Customer-91
  • CustomerID 92 jest przeznaczony dla klienta Customer-92
• Tabela AccountCustomer zawiera trzy wiersze:
  • Identyfikator konta 1 jest skojarzony z identyfikatorem CustomerID 91
  • Identyfikator konta 1 jest skojarzony z identyfikatorem CustomerID 92
  • Identyfikator konta 2 jest skojarzony z identyfikatorem CustomerID 92
• Tabela Transaction (Transakcje) zawiera trzy wiersze:
  • Date 1 stycznia 2019, AccountID 1, Amount 100
  • Data 2 lutego 2019 r., AccountID 2, Amount 200
  • Data 3 marca 2019 r., AccountID 1, Amount -25

Zobaczmy, co się stanie po wysłaniu zapytania do modelu.

Poniżej przedstawiono dwie wizualizacje podsumowujące kolumnę Amount (Kwota) z tabeli Transaction (Transakcje). Pierwsza wizualizacja grupuje według konta, a więc suma kolumn Amount reprezentuje saldo konta. Druga wizualizacja grupuje według klienta, a więc suma kolumn Amount reprezentuje saldo klienta.

Pierwsza wizualizacja nosi tytuł Account Balance (Saldo konta) i ma dwie kolumny: Account (Konto) i Amount (Kwota). Zostanie wyświetlony następujący wynik:

• Kwota salda konta-01 wynosi 75
• Kwota salda konta-02 wynosi 200
• Suma to 275

Druga wizualizacja nosi tytuł Customer Balance i ma dwie kolumny: Customer (Klient) i Amount (Kwota). Zostanie wyświetlony następujący wynik:

• Kwota salda customer-91 wynosi 275
• Kwota salda customer-92 wynosi 275
• Suma to 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. Jest to spowodowane tym, że 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 wizualizacji Customer Balance ma takie samo saldo jak łączne saldo. Ten wynik może być poprawny tylko wtedy, gdy każdy klient był wspólnym właścicielem konta. Tak nie jest w tym przykładzie. Problem jest związany z propagacją filtru. Nie przepływa przez całą drogę do tabeli Transaction .

Postępuj zgodnie z instrukcjami filtrowania relacji z tabeli Customer (Klient) do tabeli Transaction (Transakcje). Powinno być oczywiste, że relacja między tabelą Account i AccountCustomer jest propagowana w niewłaściwym kierunku. Kierunek filtrowania dla tej relacji musi być ustawiony na Wartość Oba.

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

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

• Kwota salda customer-91 wynosi 75
• Kwota salda customer-92 wynosi 275
• Suma to 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 zrozumieć, że suma wizualna oznacza wszystkich klientów.

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

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

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

Jeśli istnieje relacja wiele-do-wielu między tabelami wymiarów, udostępniamy następujące wskazówki:

• Dodaj każdą jednostkę powiązaną wiele do wielu jako tabelę modelu, zapewniając, że ma on kolumnę unikatowego identyfikatora (ID)
• Dodawanie tabeli pomostowej do przechowywania skojarzonych jednostek
• Tworzenie relacji jeden do wielu między trzema tabelami
• Skonfiguruj jedną relację dwukierunkową, aby umożliwić propagację filtru w celu kontynuowania tabel faktów
• Jeśli nie ma brakujących wartości identyfikatorów, ustaw właściwość Is Nullable kolumn id na FALSE — odświeżanie danych zakończy się niepowodzeniem, jeśli brakuje wartości źródłowych
• Ukryj tabelę mostkową (chyba że zawiera dodatkowe kolumny lub miary wymagane do raportowania)
• Ukryj wszystkie kolumny identyfikatorów, które nie są odpowiednie do raportowania (na przykład gdy identyfikatory są kluczami zastępczymi)
• Jeśli warto pozostawić widoczną kolumnę ID, upewnij się, że znajduje się ona na slajdzie "jeden" relacji — zawsze ukrywa kolumnę po stronie "wiele". Skutkuje to najlepszą wydajnością filtru.
• 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 powiązania tabel wymiarów 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 będą zawierać zduplikowane wartości. Jest to jednak dobrze zaakceptowana praktyka projektowa, jednak tabele wymiarów mają kolumnę ID. Tabele wymiarów powinny zawsze używać kolumny ID jako strony "jeden" relacji.

Powiązanie faktów wiele do wielu

Drugi typ scenariusza 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 (Zamówienie) i Fulfillment (Realizacja). Tabela Order (Zamówienie) 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 dwie kolumny OrderID z propagacją filtru tylko z tabeli Order (Order filters Fulfillment).

Kardynalność relacji jest ustawiona na wiele-do-wielu, aby obsługiwać przechowywanie zduplikowanych wartości OrderID w obu tabelach. W tabeli Order mogą istnieć zduplikowane wartości OrderID, ponieważ kolejność może zawierać wiele wierszy. W tabeli Fulfillment mogą istnieć zduplikowane wartości OrderID, ponieważ zamówienia mogą mieć wiele wierszy, a wiersze zamówienia mogą być realizowane przez wiele przesyłek.

Przyjrzyjmy się teraz wierszom tabeli. W tabeli Fulfillment zwróć uwagę, że wiersze zamówienia mogą być realizowane przez wiele przesyłek. (Brak wiersza zamówienia oznacza, że zamówienie nie zostało jeszcze spełnione).

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

• Tabela Order (Zamówienie) zawiera pięć wierszy:
  • OrderDate 1 stycznia 2019, OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Sales 50
  • OrderDate 1 stycznia 2019, OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Sales 80
  • OrderDate 2 lutego 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
  • OrderDate 2 lutego 2019, OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Sales 20
  • OrderDate 3 marca 2019, OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Sales 100
• Tabela Fulfillment ma cztery wiersze:
  • FulfillmentDate 1 stycznia 2019, FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
  • FulfillmentDate 2 lutego 2019, FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
  • FulfillmentDate 2 lutego 2019, FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
  • FulfillmentDate 1 stycznia 2019, FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10

Zobaczmy, co się stanie po wysłaniu zapytania do modelu. Oto wizualizacja tabeli porównująca ilości zamówień i realizacji według kolumny OrderID tabeli Zamówień.

Wizualizacja przedstawia dokładny wynik. Jednak użyteczność modelu jest ograniczona — można filtrować lub grupować tylko według kolumny OrderID tabeli Zamówień.

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 zapewni elastyczności w zapewnieniu elastyczności w zapewnieniu sposobu filtrowania lub grupowania wizualizacji raportów. W tym przykładzie wizualizacje mogą filtrować lub grupować według kolumny Orderid tabeli OrderID. Dodatkową przyczyną jest jakość danych. Jeśli dane mają problemy z integralnością, niektóre wiersze mogą zostać pominięte podczas wykonywania zapytań ze względu na charakter ograniczonej relacji. Aby uzyskać więcej informacji, zobacz Relacje modelu w programie Power BI Desktop (ocena relacji).

Zamiast bezpośrednio odnosić tabele faktów, zalecamy zastosowanie zasad projektowania schematu gwiazdy. Można to zrobić, dodając tabele wymiarów. Tabele wymiarów są następnie powiązane z tabelami faktów przy użyciu relacji jeden do wielu. Takie podejście projektowe jest niezawodne, ponieważ zapewnia elastyczne opcje raportowania. Umożliwia ona filtrowanie lub grupowanie przy użyciu dowolnej kolumny wymiarów i podsumowywanie dowolnej powiązanej tabeli faktów.

Rozważmy lepsze rozwiązanie.

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

• Model ma teraz cztery dodatkowe tabele: OrderLine, OrderDate, Product i FulfillmentDate
• Cztery dodatkowe tabele to wszystkie tabele wymiarów, a relacje jeden do wielu wiążą te tabele z tabelami faktów
• Tabela OrderLine zawiera kolumnę OrderLineID, która reprezentuje wartość OrderID pomnożona przez 100 oraz wartość OrderLine — unikatowy identyfikator dla 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 jest powiązana tylko z tabelą Fulfillment
• Wszystkie kolumny unikatowych identyfikatorów są ukryte

Zastosowanie zasad projektowania schematu gwiazdy daje następujące korzyści:

• Wizualizacje raportu mogą filtrować lub grupować według dowolnej widocznej kolumny z tabel wymiarów
• Wizualizacje raportu mogą podsumowywać dowolną widoczną kolumnę z tabel faktów
• Filtry zastosowane do tabel OrderLine, OrderDate lub Product będą propagowane do obu tabel faktów
• Wszystkie relacje to jeden do wielu, a każda relacja jest zwykłą relacją. Problemy z integralnością danych nie będą maskowane. Aby uzyskać więcej informacji, zobacz Relacje modelu w programie Power BI Desktop (ocena relacji).

Powiązanie faktów o wyższym stopniu szczegółowość

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

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

Tabela Target zawiera trzy kolumny: Category (Kategoria), TargetQuantity (TargetQuantity) i TargetYear (TargetYear). Wiersze tabeli pokazują stopień szczegółowości 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.

Wiązanie okresów o wyższym stopniu szczegółowość

Relacja między tabelami Date i Target powinna być relacją jeden do wielu. Jest to spowodowane tym , że wartości kolumn TargetYear są datami. W tym przykładzie każda wartość kolumny TargetYear jest pierwszą datą roku docelowego.

Napiwek

Podczas przechowywania faktów o wyższym poziomie szczegółowości czasu niż dzień ustaw typ danych kolumny na Date (lub KtoTo le number, jeśli używasz kluczy dat). 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 podsumowuje kolumnę TargetQuantity . (Pokaż elementy bez opcji danych 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. Innym sposobem — zdefiniowanym za pomocą zaawansowanego języka DAX — jest podział wartości między okresami niższego poziomu.

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

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

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

Powiązanie wyższego ziarna (bez daty)

W przypadku powiązania kolumny innej niż data z tabeli wymiarów z tabelą faktów wymagana jest inna metoda projektowania (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 "jednego" dla relacji jeden do wielu. W takim przypadku należy utworzyć relację 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 (Produkt) 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.

Wizualizacja tabeli grupowania według kolumny Category (Kategoria ) z tabeli Product (Produkt ) generuje następujący wynik.

Ta wizualizacja generuje prawidłowy wynik. Przyjrzyjmy się teraz, 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 (Kolor) z tabeli Product (Produkt) 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 teraz 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ść niż wiersze tabeli wymiarów, udostępniamy następujące wskazówki:

• W przypadku dat faktów o wyższym stopniu szczegółowość:
  • W tabeli faktów zapisz pierwszą datę okresu
  • Tworzenie relacji jeden do wielu między tabelą dat a tabelą faktów
• W przypadku innych faktów o wyższym stopniu szczegółowość:
  • Tworzenie relacji wiele-do-wielu między tabelą wymiarów a tabelą faktów
• W przypadku obu typów:
  • Kontrolowanie podsumowania za pomocą logiki miary — zwraca wartość BLANK, gdy kolumny wymiarów niższego poziomu są używane do filtrowania lub grupowania
  • Ukryj kolumny tabeli faktów z możliwością podsumowania — w ten sposób można podsumować tabelę faktów tylko miary

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
• Omówienie schematu gwiazdy i znaczenia usługi Power BI
• Wskazówki dotyczące rozwiązywania problemów z relacjami
• Pytania? Spróbuj zadać Społeczność usługi Power BI
• Sugestie? Współtworzenie pomysłów na ulepszanie usługi Power BI