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.
Poznámka
Úvod do relací modelu není popsaný v tomto článku. Pokud nejste plně obeznámeni s relacemi, jejich vlastnostmi nebo jejich konfigurací, doporučujeme, abyste si nejprve přečetli článek Model relationships in Power BI Desktop.
Je také důležité, abyste porozuměli návrhu hvězdicového schématu. Další informace najdete v tématu Pochopení hvězdicového schématu a důležitosti pro Power BI.
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 Account
a obsahuje dva sloupce: AccountID
a Account
. Druhá tabulka má název AccountCustomer
a obsahuje dva sloupce: AccountID
a CustomerID
. Třetí tabulka má název Customer
a 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 sCustomerID
91 -
AccountID
1 je přidružená kCustomerID
92 -
AccountID
2 je přidružená kCustomerID
92
-
- Tabulka
Transaction
má tři řádky:-
Date
1. ledna 2019,AccountID
1,Amount
100 -
Date
2. února 2019,AccountID
2,Amount
200 -
Date
3. března 2019,AccountID
1,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,OrderID
1,OrderLine
1,ProductID
Prod-A,OrderQuantity
5,Sales
50 -
OrderDate
1. ledna 2019,OrderID
1,OrderLine
2,ProductID
Prod-B ,OrderQuantity
10,Sales
80 -
OrderDate
2. února 2019,OrderID
2,OrderLine
1,ProductID
Prod-B ,OrderQuantity
5,Sales
40 -
OrderDate
2. února 2019,OrderID
2,OrderLine
2,ProductID
Prod-C,OrderQuantity
1,Sales
20 -
OrderDate
3. března 2019,OrderID
3,OrderLine
1,ProductID
Prod-C,OrderQuantity
5,Sales
100
-
- Tabulka
Fulfillment
má čtyři řádky:-
FulfillmentDate
1. ledna 2019,FulfillmentID
50,OrderID
1,OrderLine
1,FulfillmentQuantity
2 -
FulfillmentDate
2. února 2019,FulfillmentID
51,OrderID
2,OrderLine
1,FulfillmentQuantity
5 -
FulfillmentDate
2. února 2019,FulfillmentID
52,OrderID
1,OrderLine
1,FulfillmentQuantity
3 -
FulfillmentDate
1. ledna 2019,FulfillmentID
53,OrderID
1,OrderLine
2,FulfillmentQuantity
10
-
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 Order
OrderID
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
,Product
aFulfillmentDate
. - Čtyři dodatečné tabulky jsou všechny tabulky dimenzí, kde relace 1:N souvisí s tabulkami faktů.
- Tabulka
OrderLine
obsahuje sloupecOrderLineID
, který ukládá hodnotuOrderID
vynásobenou hodnotou 100 a hodnotou sloupceOrderLine
– ID pro každý řádek objednávky. - Tabulky
Order
aFulfillment
teď obsahují sloupecOrderLineID
a už neobsahují sloupceOrderID
aOrderLine
. - Tabulka
Fulfillment
teď obsahuje sloupceOrderDate
aProductID
. - Tabulka
FulfillmentDate
má relaci pouze s tabulkouFulfillment
. - 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
,OrderDate
neboProduct
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
, Product
a 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
, TargetQuantity
a 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.
Spropitné
Při ukládání faktů s vyšší časovou granularitou než den nastavte datový typ sloupce na datum (nebo celé číslo, pokud používáte klíče dat). Ve sloupci uložte hodnotu představující první den časového období. Například roční období se zaznamenává jako 1. ledna roku a měsíční období se zaznamená jako první den daného měsíce.
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