Pokyny k relacím M:N

Tento článek se zaměřuje na vás jako modelátor dat, který pracuje s Power BI Desktopem. Popisuje tři různé scénáře modelování M:N. Také vám poskytuje pokyny, jak pro ně úspěšně navrhnout ve svých modelech.

Existují tři různé scénáře M:N. K nim může dojít v případě, že potřebujete:

• Propojit dvě tabulky dimenzí
• Propojit dvě tabulky faktů
• Spojit tabulky faktů s jemnější úrovní detailu, když tabulka faktů ukládá řádky s jemnější úrovní detailu než řádky tabulky dimenzí.

Propojit mnoho-na-mnoho dimenze

Klasický scénář M:N se týká dvou entit, například bankovních zákazníků a bankovních účtů. Vezměte v úvahu, že zákazníci můžou mít více účtů a účty můžou mít více zákazníků. Pokud má účet více zákazníků, běžně se jim říká společní držitelé účtů.

Modelování těchto entit je jednoduché. Jedna dimenzionální tabulka ukládá účty a další dimenzionální tabulka ukládá zákazníky. Stejně jako u tabulek dimenzí je v každé tabulce jedinečný identifikátor (ID). K modelování relace mezi těmito dvěma tabulkami se vyžaduje třetí tabulka. Tato tabulka se běžně označuje jako přemosťovací tabulka . V tomto příkladu je účelem uložit jeden řádek pro každé přidružení účtu zákazníka. Zajímavé je, že pokud tato tabulka obsahuje pouze sloupce identifikátorů, nazývá se tabulka faktů bez faktů.

Tady je zjednodušený diagram tří tabulek modelu.

První tabulka má název Accounta obsahuje dva sloupce: AccountID a Account. Druhá tabulka má název AccountCustomera obsahuje dva sloupce: AccountID a CustomerID. Třetí tabulka má název Customera obsahuje dva sloupce: CustomerID a Customer. Relace mezi žádnou z tabulek neexistují.

K propojení tabulek se přidají dvě relace 1:N. Tady je aktualizovaný diagram modelu souvisejících tabulek. Byla přidána tabulka faktů s názvem Transaction. Zaznamenává transakce účtů. Tabulka přemostění a všechny sloupce identifikátorů jsou skryté.

Abychom mohli popsat, jak funguje šíření filtru relací, diagram modelu byl upraven tak, aby zobrazil řádky tabulky.

Podrobnosti o řádku pro čtyři tabulky jsou uvedeny v následujícím seznamu s odrážkami:

• Tabulka Account má dva řádky:
  • AccountID 1 je pro Account-01
  • AccountID 2 je pro Account-02
• Tabulka Customer má dva řádky:
  • CustomerID 91 je pro Customer-91
  • CustomerID 92 je customer-92
• Tabulka AccountCustomer má tři řádky:
  • AccountID 1 je spojován s CustomerID91
  • AccountID 1 je přidružená k CustomerID92
  • AccountID 2 je přidružená k CustomerID92
• Tabulka Transaction má tři řádky:
  • Date 1. ledna 2019, AccountID1, Amount100
  • Date 2. února 2019, AccountID2, Amount200
  • Date 3. března 2019, AccountID1, Amount-25

Pojďme se podívat, co se stane, když se model dotazuje.

Na následujícím obrázku jsou dva vizuály tabulky, které shrnují sloupec Amount tabulky Transaction. První vizuál seskupuje účty, a součet hodnot ve sloupcích Amount pak představuje zůstatek účtu. Druhý vizuál seskupuje podle zákazníka, a tak součet sloupců Amount představuje zůstatek zákazníka.

První vizuál tabulky (Zůstatek účtu) má dva sloupce: Account a Amount. Zobrazí následující výsledek:

• částka zůstatku účtu 01 je 75.
• částka zůstatku na účtu 02 je 200.
• Celkový počet je 275.

Druhý vizuál tabulky (Customer Balance) má dva sloupce: Customer a Amount. Zobrazí následující výsledek:

• částka zůstatku zákazníka-91 je 275.
• Zůstatek zákazníka Customer-92 je 275.
• Celkový počet je 275.

Rychlý pohled na řádky tabulky a vizuál Zůstatek účtu odhalí, že výsledek je správný pro každý účet a celkovou částku. Důvodem je to, že každé seskupení účtů vede k šíření filtru do tabulky Transaction pro daný účet.

U vizuálu Customer Balance se ale něco nezobrazuje správně. Každý zákazník v tomto vizuálu má stejný zůstatek jako celkový zůstatek. Tento výsledek by mohl být správný pouze v případě, že každý zákazník byl společným držitelem účtu každého účtu. To není případ v tomto příkladu. Došlo k problému a souvisí s šířením filtru. Filtry neprotékají úplně do tabulky Transaction.

Pokud postupujete podle pokynů filtru relací z tabulky Customer do tabulky Transaction, můžete určit, že relace mezi tabulkami Account a AccountCustomer se šíří nesprávným směrem. Směr filtru pro tuto relaci musí být nastaven na Both.

Podle očekávání nedošlo k žádné změně vizuálu Zůstatek účtu.

Vizuál Customer Balance ale teď zobrazuje následující výsledek:

• zůstatek na účtu zákazníka-91 činí 75.
• částka zůstatku Customer-92 je 275.
• Celkový počet je 275.

Vizuál Customer Balance teď zobrazuje správný výsledek. Postupujte podle pokynů filtru pro sebe a podívejte se, jak se vypočítaly zůstatky zákazníků. Mějte také na vědomí, že celkový součet vizuálu znamená všechny zákazníky.

Někdo neznámý s relacemi modelu by mohl dojít k závěru, že výsledek je nesprávný. Mohou se zeptat: Proč není celkový zůstatek pro Customer-91 a Customer-92 roven 350 (75 + 275)?

Odpověď na jejich otázku spočívá v pochopení mnohočetného vztahu. Každý zůstatek zákazníka může představovat sčítání více zůstatků účtů, takže zůstatek zákazníka nesčítá.

Pokyny k relacím dimenzí M:N

Pokud máte mezi tabulkami dimenzí relaci M:N, postupujte podle těchto pokynů:

• Přidejte každou entitu související s M:N jako tabulku modelu a zajistěte, aby byla ve sloupci ID.
• Přidejte přemosťovací tabulku k uložení přidružených entit.
• Vytvořte relace jedna ku mnoha mezi třemi tabulkami.
• Nastavte jednu obousměrnou relaci, aby mohlo šíření filtru pokračovat do tabulky faktů.
• Pokud není vhodné mít chybějící hodnoty ID, zakažte vlastnost Is Nullable – aktualizace dat se nezdaří, když se vytvoří chybějící hodnoty.
• Skryjte přemostovací tabulku (pokud obsahuje jiné sloupce nebo míry požadované pro vytváření sestav).
• Skryjte všechny sloupce ID, které nejsou vhodné pro reportování (například když sloupce ukládají surrogátní hodnoty klíče).
• Pokud má smysl ponechat sloupec ID viditelný, ujistěte se, že je na straně 1 relace – vždy skryjte sloupec na straně N. Je to proto, že filtry použité na snímku označeném jako "jeden" vedou k lepšímu výkonu filtrů.
• Abyste se vyhnuli nejasnostem nebo nesprávné interpretaci, sdělte uživatelům sestavy vysvětlení – můžete přidat popisy s textovými poli nebo popisy záhlaví vizuálu.

Nedoporučujeme přímo vytvářet vztah mezi tabulkami dimenzí M:N. Tento přístup návrhu vyžaduje nastavení relace s kardinalitou M:N. Koncepčně se dá dosáhnout, ale znamená to, že související sloupce můžou obsahovat duplicitní hodnoty. Jedná se ale o dobře uznávaný postup návrhu, že tabulky dimenzí mají sloupec ID. Tabulky dimenzí by měly vždy používat sloupec ID jako tu "jednu" stranu vztahu.

Vzájemně propojte mnohomnožné fakta

Jiný typ scénáře M:N zahrnuje korelaci dvou tabulek faktů. Dvě tabulky faktů můžou souviset přímo. Tato technika návrhu může být užitečná pro rychlé a jednoduché zkoumání dat. Obecně ale tento přístup k návrhu nedoporučujeme. Vysvětlíme si, proč později v této části.

Podívejme se na příklad, který zahrnuje dvě tabulky faktů: Order a Fulfillment. Tabulka Order obsahuje jeden řádek na řádek objednávky a tabulka Fulfillment může obsahovat nula nebo více řádků na řádek objednávky. Řádky v tabulce Order představují prodejní objednávky. Řádky v tabulce Fulfillment představují položky objednávky, které byly odeslány. Vztah mnoho-na-mnoho souvisí se sloupci OrderID v každé tabulce, přičemž šíření filtru probíhá pouze z tabulky Order (to znamená, že tabulka Order filtruje tabulku Fulfillment).

Kardinalita relace je nastavená na Many-to-many, aby podporovala ukládání duplicitních OrderID hodnot sloupců v obou tabulkách. V tabulce Order mohou existovat duplicitní hodnoty ID, protože pořadí může mít více řádků. V tabulce Fulfillment mohou existovat duplicitní hodnoty ID, protože objednávky mohou mít více řádků a splnění řádků objednávek může být zajištěno mnoha zásilkami.

Teď se podíváme na řádky tabulky. V tabulce Fulfillment si všimněte, že řádky objednávek mohou být splněny několika zásilkami. (Absence řádku objednávky znamená, že objednávka ještě není splněna.)

Podrobnosti řádku pro dvě tabulky jsou popsány v následujícím seznamu s odrážkami:

• Tabulka Order obsahuje pět řádků:
  • OrderDate 1. ledna 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate 1. ledna 2019, OrderID1, OrderLine2, ProductIDProd-B , OrderQuantity10, Sales80
  • OrderDate 2. února 2019, OrderID2, OrderLine1, ProductIDProd-B , OrderQuantity5, Sales40
  • OrderDate 2. února 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 3. března 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• Tabulka Fulfillment má čtyři řádky:
  • FulfillmentDate 1. ledna 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate 2. února 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2. února 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate 1. ledna 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Pojďme se podívat, co se stane, když model dotazujeme. Tady je vizuál tabulky, který porovnává množství objednávek a plnění podle Order tabulky OrderID sloupce.

Vizuál představuje přesný výsledek. Užitečnost modelu je však omezená, protože můžete filtrovat nebo seskupovat pouze podle tabulky OrderOrderID sloupci.

Pokyny k relacím M:N

Obecně nedoporučujeme, abyste přímo propojovali dvě tabulky faktů pomocí kardinality mnoho ku mnoha. Hlavním důvodem je to, že model nebude poskytovat flexibilitu ve způsobech, jak vizuály sestav filtrují nebo seskupují. V tomto příkladu je možné vizuály filtrovat nebo seskupovat jenom podle sloupce OrderID v tabulce Order. Dalším důvodem je kvalita vašich dat. Pokud mají vaše data problémy s integritou, je možné, že během dotazování budou některé řádky vynechány kvůli kardinalitě M:man a omezené relace.

Místo přímého propojení tabulek faktů doporučujeme implementovat návrh hvězdicového schématu. To znamená, že přidáte tabulky dimenzí. Tyto tabulky dimenze souvisejí s tabulkami faktů prostřednictvím relací typu jedna ku mnoha. Tento přístup k návrhu je robustní, protože efektivně poskytuje flexibilní reportovací možnosti. Umožňuje filtrovat nebo seskupovat pomocí libovolného sloupce tabulky dimenzí a sumarizovat sloupce všech souvisejících tabulek faktů.

Pojďme se podívat na lepší řešení.

Všimněte si následujících změn návrhu:

• Model má nyní čtyři další tabulky: OrderLine, OrderDate, Producta FulfillmentDate.
• Čtyři dodatečné tabulky jsou všechny tabulky dimenzí, kde relace 1:N souvisí s tabulkami faktů.
• Tabulka OrderLine obsahuje sloupec OrderLineID, který ukládá hodnotu OrderID vynásobenou hodnotou 100 a hodnotou sloupce OrderLine – ID pro každý řádek objednávky.
• Tabulky Order a Fulfillment teď obsahují sloupec OrderLineID a už neobsahují sloupce OrderID a OrderLine.
• Tabulka Fulfillment teď obsahuje sloupce OrderDate a ProductID.
• Tabulka FulfillmentDate má relaci pouze s tabulkou Fulfillment.
• Všechny sloupce ID jsou skryté.

Přijetí návrhu hvězdicového schématu přináší následující výhody:

• Vizuály sestavy můžou filtrovat nebo seskupovat podle jakéhokoli viditelného sloupce z tabulek dimenzí.
• Vizuály sestavy můžou shrnout libovolný viditelný sloupec z tabulek faktů.
• Filtry použité na tabulky OrderLine, OrderDatenebo Product se šíří do obou tabulek faktů.
• Všechny vztahy jsou 1:N a každý vztah je pravidelný vztah. Problémy s integritou dat nebudou maskovány. Další informace o vyhodnocení vztahů najdete v tématu Modelové relace v Power BI Desktop.

Uveďte podrobnější fakta

Tento scénář M:N se velmi liší od ostatních dvou popsaných v tomto článku.

Podívejme se na příklad zahrnující čtyři tabulky: Date, Sales, Producta Target. Tabulky Date a Product jsou tabulky dimenzí a každá z nich má vazbu typu 1:N na tabulku faktů Sales. Zatím představuje dobrý návrh hvězdicového schématu. Tabulka Target ale zatím nesouvisí s ostatními tabulkami.

Tabulka Target obsahuje tři sloupce: Category, TargetQuantitya TargetYear. Řádky tabulky odhalí členitost roku a kategorie produktů. Jinými slovy, cíle používané k měření prodejního výkonu se pro každou kategorii produktů nastavují každý rok.

Vzhledem k tomu, že tabulka Target ukládá data na vyšší úrovni než tabulky dimenzí, nelze vytvořit relaci jedna ku mnoha. No, je to pravda jen pro jednu z těchto vztahů. Pojďme se podívat, jak může tabulka Target souviset s tabulkami dimenzí.

Spojit vyšší časová období

Relace mezi tabulkami Date a Target by měla být relace 1:N. Je to proto, že hodnoty TargetYear sloupců jsou kalendářní data. V tomto příkladu každý sloupec TargetYear ukládá první datum cílového roku.

Je však potřeba dbát na to, aby filtry na úrovni měsíců nebo kalendářních dat měly smysluplný výsledek. Bez jakékoli zvláštní logiky výpočtu můžou vizuály sestav hlásit, že cílová data jsou doslova prvním dnem každého roku. Všechny ostatní dny a všechny měsíce kromě ledna shrnou cílové množství jako BLANK.

Následující vizuál matice ukazuje, co se stane, když uživatel sestavy přejde z roku na jednotlivé měsíce. Vizuál shrnuje sloupec TargetQuantity. (Možnost Zobrazit položky bez dat byla pro řádky matice povolena.)

Abyste se tomuto chování vyhnuli, doporučujeme řídit souhrn dat faktů pomocí měr. Jedním ze způsobů, jak řídit sumarizaci, je vrátit prázdnou hodnotu při dotazování na časová období nižší úrovně. Dalším způsobem, který je definovaný pomocí sofistikovaného jazyka DAX, je rozdělit hodnoty mezi nižší časová období.

Představte si následující definici míry, která používá funkci ISFILTERED DAX. Vrátí hodnotu pouze v případech, kdy Date a Month sloupce nejsou filtrované.

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

Následující vizuál matice využívá míru Target Quantity. To ukazuje, že všechna měsíční cílová množství jsou PRÁZDNÁ.

Spojit vyšší úroveň jemnosti (ne datum)

Při propojení nesloupce s datem z tabulky dimenzí s tabulkou faktů (když je na vyšší úrovni než tabulka dimenzí) je třeba jiný návrhový přístup.

Sloupce Category (z tabulek Product i Target) obsahují duplicitní hodnoty. Takže neexistuje žádná "jedna strana" pro relaci 1:N. V tomto případě budete muset vytvořit relaci mnoho-ku-mnoha. Relace by měla šířit filtry v jednom směru z tabulky dimenzí do tabulky faktů.

Nyní se podíváme na řádky tabulky.

V Target tabulce jsou čtyři řádky: dva řádky pro každý cílový rok (2019 a 2020) a dvě kategorie (Oblečení a příslušenství). V tabulce Product jsou tři produkty. Dvě patří do kategorie oblečení a jedna patří do kategorie příslušenství. Jedna z barev oblečení je zelená a zbývající dvě barvy jsou modré.

Výsledkem vizuálního seskupení v tabulce podle sloupce Category z tabulky Product je následující. Tento vizuál ale vytvoří správný výsledek. Teď se podíváme na to, co se stane, když se k seskupení cílového množství použije sloupec Color z tabulky Product.

Vizuál vytvoří nesprávnou reprezentaci dat. Co se tady děje?

Výsledkem filtru ve sloupci Color z tabulky Product jsou dva řádky. Jeden z řádků je pro kategorii Oblečení a druhý je pro kategorii Příslušenství. Tyto dvě hodnoty kategorií se šíří jako filtry do tabulky Target. Jinými slovy, protože produkty ze dvou kategorií používají barevnou modrou barvu, tyto kategorie se používají k filtrování cílů.

Chcete-li se tomuto chování vyhnout, jak je popsáno výše, doporučujeme řídit souhrn dat faktů pomocí měr.

Zvažte následující definici míry. Všimněte si, že filtry testují všechny sloupce tabulky Product, které jsou pod úrovní kategorií.

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

Následující vizualizace tabulky používá míru označenou jako Target Quantity. Ukazuje, že všechna cílová množství barev jsou PRÁZDNÁ.

Konečný návrh modelu vypadá takto:

Související doprovodné materiály k vyšším podrobnostem

Pokud potřebujete spojit tabulku dimenzí s tabulkou faktů a tabulka faktů ukládá řádky na vyšší úrovni podrobnosti než řádky tabulky dimenzí, postupujte podle těchto doporučení:

• Pro data vyšších podrobností
  • V tabulce faktů uložte první datum časového období.
  • Vytvořte relaci 1:N mezi tabulkou dat a tabulkou faktů.
• Pro jiná podrobnější data
  • Vytvořte relaci M:N mezi tabulkou dimenzí a tabulkou faktů.
• pro oba typy
  • Řízení shrnutí pomocí logiky měření – vrátí prázdnou hodnotu, pokud se sloupce nižší úrovně dimenzí používají k filtrování nebo seskupování.
  • Skryjte sumarizovatelné sloupce tabulky faktů– tím zajistíte, že k shrnutí tabulky faktů lze použít pouze míry.

Související obsah

Další informace týkající se tohoto článku najdete v následujících zdrojích informací:

• Vztahy modelů v Power BI Desktop
• Pochopte hvězdicové schéma a jeho význam pro Power BI
• Pokyny k řešení potíží ve vztazích
• Otázky? Zkuste se obrátit na Fabric komunitu
• Návrhy? Přispívat nápady k vylepšení Fabric