Pokyny na vzťahy typu many-to-many

Tento článok je určený pre modelárov údajových pracujúcich s aplikáciou Power BI Desktop. Popisuje tri rôzne scenáre modelovania typu many-to-many. Poskytuje tiež pokyny ako ich v modeloch úspešne navrhnúť.

Poznámka

Úvod o vzťahoch v modeloch nie je zahrnutý v tomto článku. Ak nie ste úplne oboznámení so vzťahmi, ich vlastnosťami alebo o tom, ako ich konfigurovať, odporúčame si najprv prečítať článok Modelové vzťahy v aplikácii Power BI Desktop .

Dôležité je aj to, aby ste pochopili návrh hviezdicovej schémy. Ďalšie informácie nájdete v téme Vysvetlenie hviezdicovej schémy a dôležitosti pre Power BI.

V skutočnosti existujú tri scenáre typu Many-to-many. Môžu sa vyskytnúť, keď potrebujete:

• Vytvoriť vzťah medzi dvomi tabuľkami dimenzií
• Vytvoriť vzťah medzi dvomi tabuľkami faktov
• Vytvoriť vzťah medzi tabuľkami faktov s vyššou granularnou, keď tabuľka faktov uchováva riadky na vyššej úrovni granularosti ako riadky tabuľky dimenzií

Poznámka

Služba Power BI teraz natívne podporuje vzťahy typu many-to-many. Ďalšie informácie nájdete v téme Použitie vzťahov typu many-many v aplikácii Power BI Desktop.

Vytvorenie vzťahov dimenzií typu Many-to-many

Uvažujme o prvom scenári typu Many-to-many s príkladom. Klasický scenár sa týka dvoch entít: zákazníkov banky a bankových kont. Predpokladajme, že zákazníci môžu mať viacero kont a kontá môžu mať viacero zákazníkov. Keď má konto viacero zákazníkov, bežne sa to nazýva vlastníci spoločného konta.

Modelovanie týchto entít je priamočiaré. Jedna tabuľka dimenzií uchováva kontá a iná tabuľka dimenzií uchováva zákazníkov. Ako je charakteristické pre tabuľky dimenzií, v každej tabuľke je stĺpec ID. Na modelovanie vzťahu medzi dvoma tabuľkami sa vyžaduje tretia tabuľka. Táto tabuľka sa bežne označuje ako premosťovacia tabuľka. V tomto príklade je cieľom uložiť jeden riadok pre každé priradenie zákazníka a konta. Zaujímavé je, že keď táto tabuľka obsahuje iba stĺpce ID, nazýva sa tabuľka faktov bez faktov.

Tu je zjednodušený modelový diagram troch tabuliek.

Prvá tabuľka sa nazýva Account a obsahuje dva stĺpce: AccountID a Account. Druhá tabuľka sa nazýva AccountCustomer a obsahuje dva stĺpce: AccountID a CustomerID. Tretia tabuľka sa nazýva Customer a obsahuje dva stĺpce: CustomerID a Customer. Medzi žiadnou z tabuliek neexistujú vzťahy.

Na vytvorenie vzťahu medzi tabuľkami sa pridajú dva vzťahy typu One-to-many. Tu je aktualizovaný diagram modelu súvisiacich tabuliek. Pridala sa tabuľka faktov s názvom Transaction . Zaznamenáva transakcie konta. Premosťovacia tabuľka a všetky stĺpce ID sa skryli.

S cieľom pomôcť opísať, ako funguje šírenie filtra vzťahov, bol diagram modelu upravený tak, aby sa zobrazili riadky tabuľky.

Poznámka

V diagrame modelu aplikácie Power BI Desktop nie je možné zobraziť riadky tabuľky. Robí sa to v tomto článku, aby sme podporili diskusiu s jasnými príkladmi.

Podrobnosti riadkov pre štyri tabuľky sú popísané v nasledujúcom zozname s odrážkami:

• Tabuľka Account má dva riadky:
  • AccountID 1 je pre Account-01
  • AccountID 2 je pre Account-02
• Tabuľka Customer má dva riadky:
  • CustomerID 91 je pre Customer-91
  • CustomerID 92 je pre Customer-92
• Tabuľka AccountCustomer má tri riadky:
  • AccountID 1 je priradený ku CustomerID 91
  • AccountID 1 je priradený ku CustomerID 92
  • AccountID 2 je priradený ku CustomerID 92
• Tabuľka Transaction má tri riadky:
  • Date 1. január 2019, AccountID 1, Amount 100
  • Date 2. február 2019, AccountID 2, Amount 200
  • Date 3. marec 2019, AccountID 1, Amount -25

Pozrime sa, čo sa stane, keď je model dotazovaný.

Nižšie sú uvedené dva vizuály, ktoré sumarizujú stĺpec Amount z tabuľky Transaction . Prvý vizuál zoskupuje podľa konta, a tak súčet stĺpcov Amount predstavuje zostatok konta. Druhý vizuál zoskupuje podľa zákazníka, a tak súčet stĺpcov Amount predstavuje zostatok zákazníka.

Prvý vizuál má názov Zostatok na konte a obsahuje dva stĺpce: Account a Amount. Zobrazí nasledujúci výsledok:

• Čiastka zostatku Account-01 je 75
• Čiastka zostatku Account-02 je 200
• Celkový súčet je 275

Druhý vizuál má názov Zostatok zákazníka a obsahuje dva stĺpce: Customer a Amount. Zobrazí nasledujúci výsledok:

• Čiastka zostatku Customer-91 je 275
• Čiastka zostatku Customer-92 je 275
• Celkový súčet je 275

Rýchly pohľad na riadky tabuľky a na vizuál Zostatok na konte ukazuje, že výsledok je správny pre každé konto a celkovú čiastku. Je to spôsobené tým, že každé zoskupenie kont má za následok šírenie filtra do tabuľky Transaction pre dané konto.

Napriek tomu sa však niečo nezobrazuje správne vo vizuáli Zostatok zákazníka. Každý zákazník vo vizuáli Zostatok zákazníka má rovnaký zostatok ako celkový zostatok. Tento výsledok by mohol byť správny len vtedy, ak by každý zákazník bol držiteľom spoločného konta každého konta. To nie je prípad tohto príkladu. Problém súvisí so šírením filtra. Neteču úplne do tabuľky Transaction .

Postupujte podľa pokynov filtra vzťahov z tabuľky Customer do tabuľky Transaction . Malo by byť zrejmé, že vzťah medzi tabuľkou Account a AccountCustomer je šírenie v nesprávnom smere. Smer filtra pre tento vzťah musí byť nastavený na hodnotu Oba.

Ako sa očakávalo, nedošlo k žiadnej zmene vo vizuáli Zostatok na konte.

Vizuály Zostatok zákazníka však teraz zobrazuje nasledujúci výsledok:

• Čiastka zostatku Customer-91 je 75
• Čiastka zostatku Customer-92 je 275
• Celkový súčet je 275

Vizuál Zostatok zákazníka teraz zobrazuje správny výsledok. Postupujte podľa pokynov na filtrovanie pre seba a pozrite sa, ako sa vypočítali zostatky zákazníkov. Taktiež pochopte, že súčet vizuálu znamená všetkých zákazníkov.

Niekto, kto nie je oboznámený so vzťahmi v modeli, môže vyvodiť záver, že výsledok je nesprávny. Môže sa pýtať: Prečo sa celkový zostatok pre Customer-91 a Customer-92 nerovná 350 (75 + 275)?

Odpoveď na ich otázku spočíva v pochopení vzťahu typu Many-to-many. Každý zostatok zákazníka môže predstavovať pridanie zostatkov viacerých kont, a tak zostatky zákazníkov sú bez pripočítania.

Pokyny na vytvorenie vzťahov dimenzií typu Many-to-many

Ak máte vzťah typu many-to-many medzi tabuľkami dimenzií, poskytujeme nasledujúce pokyny:

• Pridajte každú entitu so vzťahom Many-to-many ako tabuľku modelu a zabezpečte, aby mala stĺpec s jedinečným identifikátorom (ID)
• Pridajte premosťovaciu tabuľku na uchovávanie priradených entít
• Vytvorenie vzťahov typu One-to-many medzi tromi tabuľkami
• Nakonfigurujte jeden obojsmerný vzťah, čím povolíte šírenie filtrov, aby mohol pokračovať do tabuliek faktov
• Ak nie je vhodné, aby chýbali hodnoty ID, nastavte vlastnosť Is Nullable stĺpcov ID na hodnotu FALSE – obnovenie údajov zlyhá, ak sa hľadajú chýbajúce hodnoty
• Skryte premosťovaciu tabuľku (pokiaľ neobsahuje ďalšie stĺpce alebo mierky požadované na vytváranie zostáv)
• Skryte všetky stĺpce ID, ktoré nie sú vhodné na vytváranie zostáv (napríklad v prípade, keď ID sú náhradné kľúče)
• Ak dáva zmysel, aby bol stĺpec ID viditeľný, zabezpečte, aby bol na snímke vzťahu na strane "one". Vždy skryte stĺpec strany "many". Výsledkom bude najlepší výkon filtra.
• Ak sa chcete vyhnúť zmätku alebo nesprávnemu výkladu, vysvetlite to používateľmi zostavy. Môžete pridať popisy s textovými poľami alebo popisy hlavičky vizuálu

Neodporúčame, aby ste sa vytvárali vzťah medzi tabuľkami dimenzií typu Many-to-many priamo. Tento prístup vyžaduje konfiguráciu vzťahu s kardinalitou Many-to-many. Koncepčne sa to dá dosiahnuť, bude to však znamenať, že súvisiace stĺpce budú obsahovať duplicitné hodnoty. Všeobecne akceptovanou praxou pri návrhu je, že tabuľky dimenzií majú stĺpec s ID. Tabuľky dimenzií by mali vždy používať stĺpec ID ako stranu "one" vo vzťahu.

Vytvorenie vzťahov medzi faktami typu Many-to-many

Druhý scenár typu Many-to-many zahŕňa vytvorenie vzťahu medzi dvomi tabuľkami faktov. Medzi dvomi tabuľkami faktov je možné vytvoriť vzťah priamo. Táto technika navrhovania môže byť užitočná na rýchle a jednoduché skúmanie údajov. Aby sme však boli presní, vo všeobecnosti tento návrhový prístup neodporúčame. Nižšie v tejto časti vysvetlíme dôvod.

Zoberme si príklad, vomedzme si dve tabuľky faktov: Order a Fulfillment. Tabuľka Order obsahuje jeden riadok pre riadok objednávky a tabuľka Fulfillment môže obsahovať žiadny alebo viac riadkov pre riadok objednávky. Riadky v tabuľke Order predstavujú predajné objednávky. Riadky v tabuľke Fulfillment predstavujú položky objednávok, ktoré boli odoslané. Vzťah typu many-to-many vytvára vzťah medzi dvomi stĺpcami OrderID s šírením filtrov iba z tabuľky Order (Order filtruje Fulfillment).

Vzťahová kardinalita sa nastaví na hodnotu many-to-many na podporu ukladania duplicitných hodnôt OrderID v oboch tabuľkách. V tabuľke Order môžu existovať duplicitné hodnoty OrderID , pretože objednávka môže mať viacero riadkov. V tabuľke Fulfillment môžu existovať duplicitné hodnoty OrderID , pretože objednávky môžu mať viacero riadkov a riadky objednávky môžu byť splnené viacerými dodávkami.

Teraz sa pozrime na riadky tabuľky. V tabuľke Fulfillment si všimnite, že riadky objednávky môžu byť splnené viacerými dodávkami. (Absencia riadka objednávky znamená, že objednávka sa ešte musí vyplniť.)

Podrobnosti riadkov pre dve tabuľky sú popísané v nasledujúcom zozname s odrážkami:

• Tabuľka Order má päť riadkov:
  • OrderDate 1. január 2019, OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Sales 50
  • OrderDate 1. január 2019, OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Sales 80
  • OrderDate 2. február 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
  • OrderDate 2. február 2019, OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Sales 20
  • OrderDate 3. marec 2019, OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Sales 100
• Tabuľka Fulfillment má štyri riadky:
  • FulfillmentDate 1. január 2019, FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
  • FulfillmentDate 2. február 2019, FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
  • FulfillmentDate 2. február 2019, FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
  • FulfillmentDate 1. január 2019, FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10

Pozrime sa, čo sa stane, keď je model dotazovaný. Tu je vizuál tabuľky, ktorý porovnáva množstvá objednávky a plnenia podľa stĺpca OrderID tabuľky Order.

Vizuál predstavuje presný výsledok. Užitočnosť modelu je však obmedzená. Môžete filtrovať alebo zoskupiť len podľa stĺpca OrderID tabuľky Order.

Pokyny na vytvorenie vzťahov medzi faktami typu Many-to-many

Vo všeobecnosti neodporúčame, aby sa vytvorili vzťahy medzi dvomi tabuľkami faktov priamo pomocou kardinality Many-to-many. Hlavným dôvodom je, že model neposkytuje flexibilitu v spôsoboch, akými sa nahlási filter alebo skupina vo vizuáloch. V príklade je možné iba to, že vizuály budú filtrovať alebo zoskupovať podľa stĺpca OrderID tabuľky Order. Ďalší dôvod sa týka kvality vašich údajov. Ak majú vaše údaje problémy s integritou, môže sa vyskytnúť, že počas dotazovania sa môžu vynechať niektoré riadky v dôsledku povahy obmedzeného vzťahu. Ďalšie informácie nájdete v téme Modelové vzťahy v aplikácii Power BI Desktop (Vyhodnocovanie vzťahov).

Namiesto toho, aby ste vytvorili priamo vzťah medzi tabuľkami faktov, odporúčame prijať princípy návrhu Hviezdicová schéma. Vykonáte to tak, že pridáte tabuľky dimenzií. Tabuľky dimenzií potom súvisia s tabuľkami faktov pomocou vzťahov typu One-to-many. Tento prístup návrhu je robustný, pretože poskytuje flexibilné možnosti vytvárania zostáv. Umožňuje filtrovať alebo zoskupovať pomocou ktoréhokoľvek stĺpca dimenzie a sumarizovať ľubovoľnú súvisiacu tabuľku faktov.

Vezmime do úvahy lepšie riešenie.

Všimnite si tieto zmeny návrhu:

• Model má teraz štyri ďalšie tabuľky: OrderLine, OrderDate, Product, a FulfillmentDate
• Všetky štyri ďalšie tabuľky sú tabuľkami dimenzií a vzťahy typu One-to-many vytvárajú vzťah medzi týmito tabuľkami a tabuľkami faktov
• Tabuľka OrderLine obsahuje stĺpec OrderLineID, ktorý predstavuje hodnotu OrderID vynásobenú hodnotou 100, plus hodnotu OrderLine – jedinečný identifikátor pre každý riadok objednávky
• Tabuľky Order a Fulfillment teraz obsahujú stĺpec OrderLineID a už viac neobsahujú stĺpce OrderID a OrderLine
• Tabuľka Fulfillment teraz obsahuje stĺpce OrderDate a ProductID
• Tabuľka FulfillmentDate má vzťah iba k tabuľke Fulfillment
• Všetky stĺpce jedinečného identifikátora sú skryté

Ak si vezmete čas na aplikovanie princípu hviezdicovej schémy, získate nasledujúce výhody:

• Vizuály vašej zostavy môžu filtrovať alebo zoskupovať podľa ľubovoľného viditeľného stĺpca z tabuliek dimenzií.
• Vizuály vašej zostavy môžu sumarizovať ľubovoľný viditeľný stĺpec z tabuliek faktov
• Filtre použité v tabuľkách OrderLine, OrderDate alebo Product sa rozšíria do oboch tabuliek faktov
• Všetky vzťahy sú typu One-to-many a každý vzťah je pravidelným vzťahom. Problémy s integritou údajov nebudú maskované. Ďalšie informácie nájdete v téme Modelové vzťahy v aplikácii Power BI Desktop (Vyhodnocovanie vzťahov).

Vytvorenie vzťahov faktov s vyššou granularnou

Tento scenár typu Many-to-many je veľmi odlišný od ostatných dvoch, ktoré sú už popísané v tomto článku.

Vezmime si príklad, ktorý zahŕňa štyri tabuľky: Date, Sales, Product a Target. Tabuľky Date a Product sú tabuľky dimenzií a vzťahy one-to-many vytvárajú vzťah každej z nich k tabuľke faktov Sales . Zatiaľ to predstavuje dobrý návrh hviezdicovej schémy. Tabuľka Target však zatiaľ nemá vzťah s inými tabuľkami.

Tabuľka Target obsahuje tri stĺpce: Category, TargetQuantity a TargetYear. Riadky tabuľky odhaľujú granularitu kategórie rok a produkt. Inými slovami, ciele , ktoré sa používajú na meranie výkonu predaja, sú nastavené každý rok pre každú kategóriu produktov.

Keďže tabuľka Target ukladá údaje na vyššej úrovni ako tabuľky dimenzií, vzťah typu One-to-many sa nedá vytvoriť. Je to však pravda len pre jeden zo vzťahov. Pozrime sa, ako pre tabuľku Target môžeme vytvoriť vzťah s tabuľkami dimenzií.

Vytvorenie vzťahov časových období na vyššej granuli

Vzťah medzi tabuľkami Date a Target by mal byť vzťah typu One-to-many. Dôvodom je, že hodnoty stĺpca TargetYear sú dátumy. V tomto príklade je každá hodnota stĺpca TargetYear prvým dátumom cieľového roka.

Prepitné

Pri ukladaní faktov na vyššej úrovni granularity času ako je deň, nastavte typ údajov stĺpca na hodnotu Date (alebo Kto číslo, ak používate kľúče dátumov). V stĺpci uložte hodnotu predstavujúcu prvý deň časového obdobia. Napríklad obdobie roka sa zaznamenáva ako 1. január roka a mesačné obdobie sa zaznamená ako prvý deň v danom mesiaci.

Treba však dbať na to, aby sa zabezpečilo, že filtre na úrovni mesiaca alebo dátumu dajú zmysluplný výsledok. Bez akejkoľvek špeciálnej výpočtovej logiky môžu vizuály zostáv nahlásiť, že cieľové dátumy sú doslova prvým dňom každého roka. Všetky ostatné dni (a všetky mesiace okrem januára) budú sumarizovať cieľové množstvo ako PRÁZDNE.

Nasledujúci vizuál matice ukazuje, čo sa stane, keď používateľ zostavy prejde z roka na príslušné mesiace. Vizuál sumarizuje stĺpec TargetQuantity . (The Funkcia Zobraziť položky bez údajov bola pre riadky matice povolená.)

Ak sa chcete vyhnúť tomuto správaniu, odporúčame vám ovládať súhrn údajov o faktoch pomocou mierok. Jedným zo spôsobov, ako ovládať súhrn, je vrátiť hodnotu BLANK, keď sa dotazujú časové obdobia nižšej úrovne. Ďalším spôsobom, ktorý je definovaný pomocou zložitého jazyka DAX, je rozdelenie hodnôt v rámci časových období nižšej úrovne.

Zoberme si nasledujúcu definíciu mierky, ktorá používa funkciu ISFILTERED jazyka DAX. Vráti hodnotu len vtedy, keď sa stĺpce Date alebo Month nefiltrujú.

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

Nasledujúci vizuál matice teraz používa mierku Target Quantity . Ukazuje, že všetky mesačné cieľové množstvá sú PRÁZDNE.

Vytvorenie vzťahu s vyššou granularnou (nie dátumom)

Keď sa vytvára vzťah medzi nedákonovým stĺpcom z tabuľky dimenzií a tabuľkou faktov (a táto je na vyššej úrovni granulrivity ako tabuľka dimenzií), je potrebný iný prístup k návrhu.

Stĺpce Category (z tabuliek Product aj Target) obsahujú duplicitné hodnoty. Takže pre vzťah typu One-to-many nie je k dispozícii žiadna možnosť "one". V tomto prípade budete musieť vytvoriť vzťah typu Many-to-many. Vzťah by mal šíriť filtre v jednom smere z tabuľky dimenzií do tabuľky faktov.

Teraz sa pozrime na riadky tabuľky.

V tabuľke Target sú štyri riadky: dva riadky pre každý cieľový rok (2019 a 2020) a dve kategórie (Oblečenie a Doplnky). V tabuľke Produkt sú tri produkty. Dve patria do kategórie oblečenia a jedna patrí do kategórie doplnkov. Jedna z farieb oblečenia je zelená a zvyšné dve sú modré.

Zoskupenie vizuálu tabuľky podľa stĺpca Category z tabuľky Product vytvorí nasledujúci výsledok.

Tento vizuál dáva správny výsledok. Pozrime sa teraz na to, čo sa stane, keď sa na zoskupenie cieľového množstva použije stĺpec Color z tabuľky Product.

Vizuál vytvára nesprávne zobrazenie údajov. Čo sa tu deje?

Výsledkom filtra v stĺpci Color z tabuľky Product sú dva riadky. Jeden z riadkov je pre kategóriu Oblečenie a druhý je pre kategóriu Doplnky. Tieto dve hodnoty kategórie sa rozšíria ako filtre do tabuľky Target . Inými slovami, keďže modrá farba sa používa v produktoch z dvoch kategórií, tieto kategórie sa použijú na filtrovanie cieľov.

Ak sa chcete vyhnúť tomuto správaniu, ako je to popísané vyššie, odporúčame vám ovládať súhrn údajov o faktoch pomocou mierok.

Zoberme si nasledujúcu definíciu mierky. Všimnite si, že všetky stĺpce tabuľky Product , ktoré sú pod úrovňou kategórie, sa testujú na filtre.

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

Nasledujúci vizuál tabuľky teraz používa mierku Target Quantity . Ukazuje, že všetky cieľové množstvá farby sú PRÁZDNE.

Finálny návrh modelu vyzerá takto.

Pokyny na vytvorenie vzťahu medzi faktami s vyššou granulabilitou

Ak potrebujete vytvoriť vzťah medzi tabuľkou dimenzií a tabuľkou faktov a tabuľka faktov uchováva riadky na vyššej úrovni granularcie ako sú riadky tabuľky dimenzií, k dispozícii sú nasledujúce pokyny:

• Pre dátumy faktov s vyššou granularnou:
  • V tabuľke faktov uložte prvý dátum časového obdobia
  • Vytvorenie vzťahu one-to-many medzi tabuľkou dátumov a tabuľkou faktov
• Pre iné fakty s vyššou granularnou:
  • Vytvorenie vzťahu typu many-to-many medzi tabuľkou dimenzií a tabuľkou faktov
• Pre obidva typy:
  • Ovládanie súhrnu pomocou logiky mierky – vráti sa hodnota BLANK, keď sa stĺpce dimenzie nižšej úrovne používajú na filtrovanie alebo zoskupovanie
  • Skryte sumarizovateľné stĺpce tabuľky faktov. Týmto spôsobom možno na sumarizáciu tabuľky faktov použiť iba mierky

Súvisiaci obsah

Ďalšie informácie súvisiace s týmto článkom nájdete v nasledujúcich zdrojoch:

• Vzťahy modelov v aplikácii Power BI Desktop
• Vysvetlenie hviezdicovej schémy a jej dôležitosti pre Power BI
• Pokyny na riešenie problémov so vzťahmi
• Máte nejaké otázky? Skúste sa spýtať Komunita Power BI
• Návrhy? Prispejte nápadmi na zlepšenie služby Power BI