Pokyny na vzťahy typu many-to-many

Tento článok je určený pre modelárov údajov, ktorí pracujú 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úť.

Existujú tri rôzne 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 granularčiny ako riadky tabuľky dimenzií

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

Klasický scenár typu Many-to-many sa týka dvoch entít, napríklad 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, obvykle sa to nazýva vlastníci spoločného konta.

Modelovanie týchto entít je priamočiaré. V jednej tabuľke dimenzií sú uložené 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 s jedinečným identifikátorom (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 identifikátorov, nazýva sa tabuľka faktov bez faktov.

Tu je zjednodušený diagram troch tabuliek modelov.

Prvá tabuľka sa nazýva Accounta obsahuje dva stĺpce: AccountID a Account. Druhá tabuľka sa nazýva AccountCustomera obsahuje dva stĺpce: AccountID a CustomerID. Tretia tabuľka sa nazýva Customera 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 identifikátorov 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.

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

• Tabuľka Account má dva riadky:
  • AccountID 1 je určený pre Account-01
  • AccountID 2 je určený pre Account-02
• Tabuľka Customer má dva riadky:
  • CustomerID 91 je určený pre Customer-91
  • CustomerID 92 je určený pre Customer-92
• Tabuľka AccountCustomer má tri riadky:
  • AccountID 1 je priradený k CustomerID91
  • AccountID 1 je priradený k CustomerID92
  • AccountID 2 je priradený k CustomerID92
• Tabuľka Transaction má tri riadky:
  • 1. januára 2019 1100
  • Date 2. februára 2019AccountID2Amount200
  • Date 3. marca 2019AccountID1Amountdo 25

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

Na nasledujúcom obrázku sú dva vizuály tabuľky, ktoré sumarizujú Amount stĺpec 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 tabuľky (Zostatok na konte) má dva stĺpce: Account a Amount. Zobrazí nasledujúci výsledok:

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

Druhý vizuál tabuľky (Zostatok zákazníka) má dva stĺpce: Customer a Amount. Zobrazí nasledujúci výsledok:

• Čiastka zostatku Customer-91 je 275.
• Customer-92 čiastka zostatku 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 v tomto vizuáli 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. Vyskytol sa problém, ktorý súvisí so šírením filtra. Filtre netečú úplne do tabuľky Transaction.

Ak postupujete podľa pokynov filtra vzťahov z tabuľky Customer do tabuľky Transaction, môžete zistiť, že vzťah medzi Account a AccountCustomer tabuľkami sa šíri v nesprávnom smere. Smer filtra pre tento vzťah musí byť nastavený na Both.

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

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

• Customer-91 čiastka zostatku je 75.
• Customer-92 čiastka zostatku 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. Vezmite tiež do povedomia, že celkový 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 nie je celkový zostatok pre Customer-91 a Customer-92 rovný 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í, postupujte podľa týchto pokynov:

• Pridajte každú entitu so vzťahom Many-to-many ako tabuľku modelu a zabezpečte, aby mala stĺpec ID.
• Pridajte premosťovaciu tabuľku na uchovávanie priradených entít.
• Medzi troma tabuľkami vytvorte vzťahy typu one-to-many.
• Nastavte jeden obojsmerný vzťah, aby šírenie filtra pokračovalo do tabuľky faktov.
• Ak nie je vhodné, aby chýbali hodnoty ID, vypnite vlastnosť Is Nullable – obnovenie údajov zlyhá pri zdroji chýbajúcich hodnôt.
• Skryte premosťovaciu tabuľku (pokiaľ neobsahuje iné 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 keď stĺpce ukladajú hodnoty náhradného kľúča).
• Ak dáva zmysel, aby bol stĺpec ID viditeľný, zabezpečte, aby bol na strane "one" vzťahu. Vždy skryte stĺpec strany "many". Je to spôsobené tým, že filtre použité na snímke "one" majú za následok lepší 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 nastavenie vzťahu s kardinalitou Many-to-many. Koncepčne sa to dá dosiahnuť, bude to však znamenať, že súvisiace stĺpce môžu obsahovať duplicitné hodnoty. Prijatá prax pri návrhu je však to, ž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

Iný typ scenára 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, v Order a Fulfillmentdve tabuľky faktov. Tabuľka Order obsahuje jeden riadok na riadok objednávky a tabuľka Fulfillment môže obsahovať žiadny alebo viac riadkov na 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 OrderID stĺpcami v každej tabuľke, pričom šírenie filtra je len z tabuľky Order (čo znamená, že tabuľka Order filtruje tabuľku Fulfillment).

Kardinalita vzťahu je nastavená na Many-to-many na podporu ukladania duplicitných hodnôt OrderID stĺpcov v oboch tabuľkách. V tabuľke Order môžu existovať duplicitné hodnoty ID, pretože objednávka môže mať viacero riadkov. V tabuľke Fulfillment môžu existovať duplicitné hodnoty ID, 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 obsahuje päť riadkov:
  • OrderDate 1. januára 2019OrderID1OrderLineProductIDProd-AOrderQuantity5Sales50
  • OrderDate 1. januára 2019OrderID1OrderLineProductIDProd-BOrderQuantity10Sales80
  • OrderDate 2. februára 2019OrderID2OrderLine1ProductIDProd-BOrderQuantity5Sales40
  • OrderDate 2. februára 2019OrderID2OrderLineProductIDProd-COrderQuantity1Sales20
  • OrderDate 3. marca 2019OrderID3OrderLineProductIDProd-COrderQuantity5Sales100
• Tabuľka Fulfillment má štyri riadky:
  • FulfillmentDate 1. januára 2019FulfillmentID50OrderIDOrderLine1FulfillmentQuantity2
  • FulfillmentDate 2. februára 2019FulfillmentID51OrderID2OrderLine1FulfillmentQuantity5
  • 2. februára 20195213
  • 1. januára 2019 53210

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 Order tabuľky OrderID.

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

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

Vo všeobecnosti neodporúčame vytvoriť vzťah medzi dvomi tabuľkami faktov priamo pomocou kardinality Many-to-many. Hlavným dôvodom je, že model neposkytuje flexibilitu v spôsoboch filtrovania alebo skupiny vizuálov zostáv. V uvedenom príklade je možné iba to, že vizuály budú filtrovať alebo zoskupovať podľa Order tabuľky OrderID stĺpca. Ďalší dôvod sa týka kvality vašich údajov. Ak majú vaše údaje problémy s integritou, je možné, že počas dotazovania sa môžu vynechať niektoré riadky z dôvodu kardinality typu many-to-man a obmedzených vzťahov.

Namiesto toho, aby ste vytvorili priamo vzťah medzi tabuľkami faktov, odporúčame implementovať hviezdicovú schému návrh. To znamená, že pridáte tabuľky dimenzií. Tieto 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 efektívne poskytuje flexibilné možnosti vytvárania zostáv. Umožňuje filtrovať alebo zoskupovať pomocou ktoréhokoľvek stĺpca tabuľky dimenzií a sumarizovať stĺpce ľubovoľnej súvisiacej tabuľky faktov.

Vezmime do úvahy lepšie riešenie.

Všimnite si tieto zmeny návrhu:

• Model má teraz štyri tabuľky navyše: OrderLine, OrderDate, Producta FulfillmentDate.
• Všetky štyri tabuľky navyše sú tabuľky dimenzií, v ktorých ich vzťahy typu one-to-many súvisia s tabuľkami faktov.
• Tabuľka OrderLine obsahuje stĺpec OrderLineID, v ktorom je uložená hodnota OrderID vynásobená hodnotou 100 plus hodnota OrderLine stĺpca – ID pre každý riadok objednávky.
• Tabuľky Order a Fulfillment teraz obsahujú OrderLineID stĺpec a už neobsahujú stĺpce OrderID a OrderLine.
• Tabuľka Fulfillment teraz obsahuje OrderDate a ProductID stĺpce.
• Tabuľka FulfillmentDate má vzťah iba s tabuľkou Fulfillment.
• Všetky stĺpce ID sú skryté.

Ak si beriete čas na prijatie návrhu 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ým viditeľným stĺpcom z tabuliek faktov.
• Filtre použité na tabuľky OrderLine, OrderDatealebo 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 o vyhodnocovaní vzťahov nájdete v téme Modelové vzťahy v aplikácii Power BI Desktop.

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, Producta Target. tabuľkami Date a Product sú tabuľky dimenzií a vzťahy typu 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, TargetQuantitya 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 môžu tabuľky Target súvisieť s tabuľkami dimenzií.

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

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

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 by vizuály zostáv mohli 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. (V riadkoch matice je povolen á možnosť Zobraziť položky bez údajov.)

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.

Pozrite si nasledujúcu definíciu mierky, ktorá používa ISFILTERED funkciu DAX. Vráti hodnotu len vtedy, keď sa stĺpce Date a Month nebudú filtrovať.

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

Nasledujúci vizuál matice 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ánumerovým stĺpcom z tabuľky dimenzií a tabuľkou faktov (a táto je na vyššej úrovni granulaly ako tabuľka dimenzií), je potrebný iný prístup k návrhu.

Stĺpce Category (z Product a Target tabuliek) obsahujú duplicitné hodnoty. Takže pre vzťah typu one-to-many nie je k dispozícii žiadna strana "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 Product 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 však poskytuje 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í, sa tieto kategórie použijú na filtrovanie cieľov.

Ak sa chcete vyhnúť tomuto správaniu, ako je 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 Product stĺpce tabuľky, 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 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 riadky tabuľky dimenzií, postupujte podľa týchto pokynov:

• Pre dátumy faktov s vyššou granularnou
  • V tabuľke faktov uložte prvý dátum časového obdobia.
  • Vytvorte vzťah one-to-many medzi tabuľkou dátumov a tabuľkou faktov.
• Pre iné fakty s vyššou granularnou
  • Vytvorte vzťah typu many-to-many medzi tabuľkou dimenzií a tabuľkou faktov.
• pre obidva typy
  • Ovládajte súhrn pomocou logiky mierky – vráti sa hodnota BLANK, keď sa stĺpce dimenzie nižšej úrovne používajú na filtrovanie alebo zoskupovanie.
  • Skryte stĺpce tabuľky faktov, ktoré umožňujú sumarizovať tabuľku faktov, čím sa zabezpečí, že na sumarizáciu tabuľky faktov je možné použiť iba mierky.

Súvisiaci obsah

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

• modelové vzťahy 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
• Otázky? Skúste sa spýtať na komunity Fabric
• Návrhy? prispievať nápadmi na zlepšenie v látkach