Obousměrná propagace filtrů mezi tabulkami může způsobovat jednak výkonnostní problémy, a dále za určitých okolností také nejednoznačnost modelu, což je situace, které bychom se měli za každou cenu vyhnout. V tomto příspěvku si proto ukážeme DAX vzory, pomocí kterých se můžeme vyhnout obousměrným relacím v modelu.
K tomuto tématu je k dispozici také video:
V příkladech budeme pracovat s velmi jednoduchým modelem, který obsahuje pouze 4 tabulky.
Model zobrazený na obrázku výše je vytvořený podle pravidel Star schématu, a jako takový představuje ideální model pro práci v Power BI. Přesto můžeme i při práci s tímto modelem snadno dojít do situace, kdy některé jednoduché výpočty nemusí fungovat podle očekávání.
Uvažujme například následující report.
V řádcích vizuálu matice můžeme vidět názvy produktů z tabulky 'Product', a v hodnotách měřítko [Prodeje], ve kterém jednoduše sčítáme hodnoty ze sloupce 'Sales'[Sales Amount].
Předpokládejme nyní jednoduchý požadavek, ve kterém potřebujeme zjistit, v kolika státech se každý produkt prodával. Státy, ve kterých se nachází jednotlivé obchody, najdeme v tabulce 'Reseller' ve sloupci 'Reseller'[State-Province].
Samotné měřítko, které bude vracet počet států z tohoto sloupce je jednoduché.
Měřítko:
Funkce DISTINCTCOUT() vrací počet jedinečných hodnot ze sloupce v aktuálním kontextu filtru, protože funkce DISTINCTCOUT(), stejně jako kterákoliv jiná agregační funkce, respektuje kontext filtru, a ignoruje kontext řádku.
Hodnoty v řádcích vizuálu, v našem případě názvy produktů, tvoří kontext filtru. Pokud ale nové měřítko vložíme do vizuálu matice, výsledkem bude stejná hodnota v každém řádku. Číslo 66 představuje počet všech států ze sloupce 'Reseller'[State-Province], bez ohledu na to, zda se aktuální produkt v daném státě prodával nebo ne.
Důvod, proč měřítko [Počet států] vrací v každém řádku stejnou hodnotu má jednoduché vysvětlení, které objevíme při pohledu na diagram modelu.
Produkty, které se nachází v řádcích vizuálu, jsou z tabulky 'Product'. Filtry z tabulky 'Product' se dostanou do tabulky 'Sales', odkud sčítáme hodnoty ze sloupce 'Sales'[Sales Amount] v měřítku [Prodeje], ale z tabulky 'Sales' se už tyto filtry do tabulky 'Reseller' nedostanou.
Filtry z řádků vizuálu nedoputují k tabulce 'Reseller' a proto funkce DISTINCTCOUT() načte všechny hodnoty ze sloupce 'Reseller'[State-Province]. Proto vidíme v každém řádku vizuálu stejnou hodnotu, která představuje počet všech států.
Do tabulky 'Reseller' by se filtry z tabulky 'Product' dostaly pouze v případě, kdy bychom nastavili relaci mezi tabulkou 'Sales' a tabulkou 'Reseller' jako obousměrnou.
Nastavení obousměrných relací by v tomto jednoduchém modelu nezpůsobovalo žádné problémy. V reálném světě ale obvykle pracujeme se složitějšími modely, ve kterých už může nastavení obousměrných relací dříve nebo později způsobovat problémy. Proto si v následující části příspěvku ukážeme, jak se můžeme obousměrným relacím v modelu vyhnout.
Možnosti nahrazení obousměrné relace pomocí DAX výpočtů
V jazyku DAX máme několik možností, jak přesunout filtry proti směru nastavené filtrace mezi tabulkami. První volbou je většinou funkce CROSSFILTER(). Pomocí funkce CROSSFILTER() můžeme mimo jiné aktivovat obousměrnou propagaci filtrů, ale pouze dočasně, v době vyhodnocení výpočtu, bez nutnosti upravovat model. V jazyku DAX ale máme i další možnosti jak dostat filtry proti směru vytvořené relace, které je dobré znát, protože se mohou lépe hodit pro určité druhy výpočtů.
Začneme funkcí CROSSFILTER(), pomocí které řekneme, že relaci vytvořenou mezi sloupci 'Sales'[ReselerKey] a 'Reseller'[ResellerKey] nastavíme před vyhodnocením měřítka [Počet států] na obousměrnou.
Měřítko:
Pokud nové měřítko vložíme do vizuálu, uvidíme opravdu počet států, ve kterých se prodával aktuální produkt v aktuálním řádku vizuálu, a to bez nutnosti měnit model.
Jak můžeme vidět na obrázku výše, tak ne každý produkt se prodával v každém státě. Současně, celkově se produkty v roce 2018 prodávaly pouze v 52 státech, což můžeme vidět v řádku souhrnů.
Než se přesuneme k další možnosti, jak se vyhnout obousměrné relaci v modelu, vrátíme se na okamžik k diagramu modelu.
Ve druhém příkladu totiž budeme pracovat s konceptem rozšířených tabulek. Tomuto tématu je sice věnován samostatný příspěvek, nicméně alespoň ve stručnosti si koncept rozšířených tabulek popsat můžeme i zde.
V jazyku DAX, kdykoliv použijeme ve filtrech funkce CALCULATE() celou tabulku, používáme ve skutečnosti její rozšířenou verzi. Rozšířená verze tabulky pak obsahuje všechny sloupce z těch tabulek, které jsou s použitou tabulkou propojeny pomocí relací a jsou v rámci relace na straně 1. Zjednodušeně, do rozšířené verze tabulky patří všechny sloupce z dané tabulky a dále všechny sloupce, ke kterým můžeme přistupovat pomocí funkce RELATED(). Další funkce, která pracuje s konceptem rozšířených tabulek, je funkce SUMMARIZE(). Funkce SUMMARIZE() přijímá v prvním argumentu tabulku, a v dalších argumentech se můžeme odkazovat na kterýkoliv sloupec z rozšířené verze této tabulky. Koncept rozšířených tabulek v jazyku DAX je určitě dobré znát, protože se s ním setkáváme, většinou nevědomky, velmi často.
Pokud se ale vrátíme zpět k našemu modelu, tak všechny tabulky mají relaci s tabulkou 'Sales', a současně tabulka 'Sales' má jako jediná na druhé straně relací číslo 1. Pokud se zaměříme třeba vztah tabulky 'Sales' a tabulky 'Product', tak pro každý záznam v tabulce 'Sales' existuje pouze jeden konkrétní záznam v tabulce 'Product'. Proto můžeme bez jakýchkoliv pochybností přiřadit ke každému řádku v tabulce 'Sales' odpovídající hodnotu z kteréhokoliv sloupce v tabulce 'Product'. Totéž platí pro všechny ostatní dimenzní tabulky, které jsou všechny s tabulkou 'Sales' ve vztahu M:1 na straně 1. Do rozšířené verze tabulky 'Sales' proto patří všechny sloupce z celého modelu.
Protože do rozšířené verze tabulky 'Sales' patří všechny sloupce z celého modelu, včetně sloupců z tabulky 'Reseller', tak použitím tabulky 'Sales' ve filtru funkce CALCULATE() dosáhneme podobného efektu jako s funkcí CROSSFILTER(), taktéž bez nutnosti aktivace obousměrné propagace filtrů v modelu.
Měřítko:
Pokud totiž použijeme ve filtru funkce CALCULATE() celou tabulku 'Sales', používáme její rozšířenou verzi, včetně všech sloupců z tabulky 'Reseller'. I když se na první pohled může zdát koncept rozšířených tabulek oprávněně hodně abstraktní, funguje a často umožňuje tvořit jednoduchým způsobem velmi pokročilé výpočty.
Problémem předchozího výpočtu ale je, že z výkonnostních důvodů není vůbec efektivní používat celé tabulky ve filtrech funkce CALCULATE(). To si můžeme dovolit jen v malých a jednoduchých modelech. Později si proto ukážeme alternativní výpočet, ve kterém budeme také využívat koncept rozšířených tabulek, ale bez nutnosti používat ve filtrech funkce CALCULATE() celou tabulku 'Sales'.
Nyní je ale důležité říct si, že měřítka [Počet států (CROSSFILTER)] a [Počet států (Bridge table)] nejsou významově stejné.
V měřítku [Počet států (CROSSFILTER)] jsou propagovány vnější filtry až k tabulce 'Reseller', protože jsme v tomto měřítku dočasně aktivovali obousměrnou propagaci filtrů. Pokud ale na měřítko nepůsobí žádné vnější filtry, výsledkem bude počet všech hodnot ze sloupce 'Reseller'[State-Province], protože není jaké filtry k tabulce 'Reseller' přesouvat.
Na druhou stranu, v měřítku [Počet států (Bridge table)] aktivně používáme ve filtr funkce CALCULATE() celou tabulku 'Sales', přirozeně v její rozšířené verzi, a to vždy. Pokud bude toto měřítko vyhodnoceno bez jakýchkoliv vnějších filtrů, bude stále vracet pouze počet těch států, pro které existuje záznam o prodejích ve faktové tabulce 'Sales'.
Protože v našem cvičném modelu existuje v tabulce 'Reseller' jeden stát, ve kterém se nikdy nic neprodalo, obě měřítka budou vracet jiné hodnoty v prázdném kontextu filtru. Rozdíl můžeme zaznamenat v řádku souhrnů na obrázku níže, kde je oproti předcházejícímu obrázku zrušený filtr v průřezu na rok 2018, a v řádku souhrnů proto na měřítka nepůsobí žádné filtry.
Pokud ale vybereme v průřezu všechny roky, obě měřítka opět budou vracet stejné výsledky.
Pokud totiž vybereme v průřezu všechny roky, tak filtry z tabulky 'Date' musí jít do tabulky 'Reseller' přes tabulku 'Sales', a proto vidíme také u měřítka [Počet států (CROSSFILTER)] v řádku souhrnů hodnotu 65, což je počet států, pro které existuje alespoň jeden záznam v tabulce 'Sales'.
Při porovnání chování měřítek [Počet států (CROSSFILTER)] a [Počet států (Bridge table)] bylo mimo jiné možné vidět, že za určitých okolností může být významový rozdíl mezi "prázdným filtrem" a "filtrem všeho". Porozumění těmto drobným detailům je často velmi důležité pro výběr správného řešení.
Jak již bylo uvedeno výše, filtr v podobě celé tabulky 'Sales' v měřítku [Počet států (Bridge table)] je z pohledu výkonnosti problematický. Proč může být za určitých okolností problém použít celou tabulku ve filtru funkce CALCULATE() není složité si představit. Filtrovat model na základě hodnot ze všech sloupců, které patří do rozšířené verze tabulky 'Sales', zabere určité místo v paměti a také odpovídající výpočetní výkon. Ve většině situacích sice dojde k optimalizaci výpočtů a reálně nejsou použity všechny aktuální hodnoty ze všech sloupců, ale to neplatí vždy a určitě na to nemůžeme spoléhat. Proto je vždy lepší používat ve filtrech ve funkci CALCULATE() co nejmenší počet sloupců.
Následující výpočet bude vracet za všech okolností stejné výsledky jako výpočet v měřítku [Počet států (Bridge table)], nicméně filtr ve funkci CALCULATE() bude obsahovat pouze jeden sloupec.
Měřítko:
Měřítko [Počet států (SUMMARIZE)] je významově totožné s měřítkem [Počet států (Bridge table)]. Velmi důležité ale je myslet na to, že funkce SUMMARIZE() musí vracet sloupec z navázané tabulky, který je použitý pro vytvoření relace (sloupec s jedinečnými hodnotami v dimenzní tabulce). Pouze se sloupcem, který je na nejvyšší úrovni granularity (největší detail) v tabulce, můžeme bezpečně agregovat všechny ostatní hodnoty z ostatních sloupců. Na stejném principu ostatně tvoříme také fyzické relace v modelu.
Jak můžeme vidět na obrázku výše, měřítko [Počet států (SUMMARIZE)] vrací stejné hodnoty jako měřítko [Počet států (Bridge table)]. V tomto případě bude vždy lepší použít variantu s funkcí SUMMARIZE(), a to především z výkonnostních důvodů.
Na druhou stranu, mezi měřítkem [Počet států (CROSSFILTER)] a měřítkem [Počet států (SUMMARIZE)], respektive [Počet států (Bridge table)], je významově rozdíl. Nejde ale říct, zda je jedna nebo druhá varianta výpočtu lepší nebo horší. Při výběru jedné nebo druhé varianty musíme zvážit, zda chceme z dimenzní tabulky vždy pouze záznamy navázané na faktovou tabulku, nebo zda chceme pracovat se všemi záznamy, tedy i s těmi, pro které zatím nemáme odpovídající řádky ve faktové tabulce.
Přirozeně bude existovat celá řada modelů, kde významový rozdíl mezi jedním nebo druhým výpočtem nebude mít vliv na výsledné hodnoty, protože ve faktové tabulce budou existovat záznamy pro každý řádek tabulky dimenzní. V této situaci bude naší prvotní volbou funkce CROSSFILTER(), a to kvůli jednoduchosti použití a z důvodu lepší výkonnosti.
Shrnutí
Nastavení obousměrné propagace filtrů v modelu není samo o sobě chybné. Musí se ale používat správně a pouze v situacích, kdy je to bezpečné. Obecným pravidlem pak je, že pokud to není nezbytné, je lepší se obousměrným relacím v modelu vyhnout. V tomto příspěvku jsme si ukázali, jak se vyhnout obousměrným relacím pomocí DAX výpočtů. Častou chybou dále je, že se obousměrná relace v modelu nastavuje z důvodu synchronizace vizuálů, zejména průřezů v reportech. Jak synchronizovat průřezy bez nutnosti nastavovat obousměrnou propagaci filtrů v modelu na popsáno v samostatném příspěvku dostupném pod tímto odkazem.
Komentáře
Okomentovat