Každá tabulka v tabulárním modelu může mít za určitých okolností svou rozšířenou verzi tabulky. Rozšířená tabulka je virtuální tabulka, která vždy obsahuje všechny nativní sloupce ze základní tabulky a dále může obsahovat další sloupce ze souvisejících tabulek. Znalost konceptu rozšířených tabulek je velmi důležitá při psaní DAX výpočtů, protože vždy když v DAX výpočtu použijeme celou tabulku, pracujeme ve skutečnosti s její rozšířenou verzí.
V tomto příspěvku si proto na cvičném modelu popíšeme, jakým způsobem identifikovat které sloupce patří do rozšířené verze tabulky, a na jednoduchých příkladech si ukážeme jaký dopad mohou mít rozšířené tabulky na DAX výpočty.
Co je to rozšířená tabulka v jazyku DAX
Rozšířená verze tabulky není fyzická tabulka, ale určitá abstrakce v podobě virtuální tabulky. Rozšířená verze tabulky může vzniknout vždy až po vytvoření relací mezi tabulkami v modelu. Jakmile dojde k vytvoření relací, některé tabulky mohou ve své rozšířené verzi obsahovat pouze sloupce patřící do základní tabulky (nativní sloupce), ale jiné tabulky mohou obsahovat ve své rozšířené verzi i sloupce z některých souvisejících tabulek, které jsou se základní tabulkou propojené pomocí relací. Jestli základní tabulka obsahuje ve své rozšířené verzi také sloupce ze souvisejících tabulek záleží na typu relace.
První podmínkou pro to, aby základní tabulka obsahovala ve své rozšířené verzi i sloupce z jiných tabulek je, aby každému řádku v základní tabulce odpovídal vždy pouze jeden nebo žádný řádek v tabulce se kterou je základní tabulka propojená pomocí relace. Do rozšířené verze tabulky tak mohou patřit pouze sloupce, které pocházejí z tabulek propojených se základní tabulkou pomocí relací M:1 nebo 1:1, a současně je související tabulka vždy na straně 1. U relací M:1 se rozšíření tabulky provádí pro tabulku na straně M a tato tabulka ve své rozšířené verzi obsahuje také sloupce z tabulky na straně 1. U relace 1:1 obsahují obě tabulky ve své rozšířené verzi stejné sloupce, protože u tohoto typu relace dojde k rozšíření obou tabulek o sloupce ze související tabulky.
Druhou podmínkou je, že relace mezi tabulkami nesmí být relace omezená (limited relationship nebo weak relationship). Omezená relace je například relace M:N, což je relace která nesplňuje ani první výše popsanou podmínku pro definici rozšířených tabulek. Omezená relace je ale například také relace, která je vytvořená mezi tabulkami kde je jedna tabulka nahraná v modelu v režimu Import, a druhá tabulka je v režimu Direct Query. U relací, které jsou omezené dochází ke spojení tabulek až v době dotazu a nevzniká pro tyto relace stejná interní struktura, jako v případě relací běžných. Proto u omezených relací, i když jsou M:1 nebo 1:1, nedochází k vytvoření rozšířených verzí tabulek a také u nich není možné automaticky ověřovat referenční integritu. Omezené relace také neumožňují použití některých DAX funkcí. Například funkce RELATED(), která úzce souvisí právě s konceptem rozšířených tabulek, nemůže být u omezených relací použita k načtení hodnot ze sloupce na straně 1.
Rozšíření tabulky nijak nesouvisí se směrem filtrace, ale pouze s kardinalitou relace. Pokud například u relace M:1 nastavíme obousměrnou propagaci filtrů, na konceptu rozšířených tabulek se nic nemění, a k rozšíření dojde pouze u tabulky která je na straně M, a to o sloupce z tabulky která je na straně 1.
K rozšíření dochází také u tabulek, které jsou propojené pomocí řetězce relací. Zde ale platí, že všechny relace v daném řetězci relací musí mít stejnou kardinalitu nebo kardinalitu 1:1. Stále tedy musí platit, že do rozšířené verze základní tabulky patří pouze sloupce z těch tabulek, u kterým máme jistotu, že pro jeden záznam v základní tabulce existuje v související tabulce maximálně jeden navázaný záznam.
Jak zjistit které sloupce patří do rozšířené verze tabulky si ukážeme na několika příkladech.
Jak zjistit které sloupce patří do rozšířené verze tabulky
Jak už jsme si popsali výše, do rozšířené verze tabulky patří všechny sloupce ze souvisejících tabulek, které jsou spojeny se základní tabulkou pomocí relací M:1 nebo 1:1 a související tabulka je vždy na straně 1. Nesmí se ale jednat o omezenou relaci.
Na následujícím obrázku je zachycen model, který obsahuje pouze dvě tabulky, tabulku 'Product' a tabulku 'Category'.
Mezi tabulkou 'Product' a tabulkou 'Category' je vytvořená relace M:1. Obě tabulky jsou nahrané v modelu v režimu Import a jedná se proto o běžnou relaci.
Protože je tabulka 'Product' na straně M a tabulka 'Category' na straně 1, pro každý jeden záznam v tabulce 'Product' existuje v tabulce 'Category' maximálně jeden odpovídající záznam. V takto vytvořeném modelu obsahuje tabulka 'Product' ve své rozšířené verzi všechny sloupce z tabulky 'Product' a dále všechny sloupce z tabulky 'Category'. Na druhou stranu, tabulka 'Category' je s tabulkou 'Product' ve vztahu 1:M na straně 1. Pro jeden záznam v tabulce 'Category' může existovat více záznamů v tabulce 'Product', a proto tabulka 'Category' ve své rozšířené verzi obsahuje pouze sloupce z tabulky 'Category'.
Na následujícím obrázku je zachycen jiný model, ve kterém jsou dvě tabulky propojeny pomocí relace 1:1. Obě tabulky jsou opět v režimu Import.
Na následujícím obrázku je zachycen model s pěti tabulkami. Všechny tabulky jsou nahrané v modelu v režimu Import.
- Tabulka 'Category' obsahuje ve své rozšířené verzi pouze sloupce z této tabulky.
- Tabulka 'Product' obsahuje ve své rozšířené verzi sloupce z tabulky 'Product' a z tabulky 'Category'.
- Tabulka 'Date' obsahuje ve své rozšířené verzi pouze sloupce z této tabulky.
- Tabulka 'Sales' obsahuje ve své rozšířené verzi všechny sloupce ze všech tabulek nahraných v modelu.
- Tabulka 'Sales Order' obsahuje ve své rozšířené verzi všechny sloupce ze všech tabulek nahraných v modelu.
U tabulek 'Category', 'Product' a 'Date' by již měla být definice rozšířených verzí těchto tabulek zřejmá. Zastavit se ale můžeme u tabulky 'Sales' a u tabulky 'Sales Order'.
Tabulka 'Sales' obsahuje ve své rozšířené verzi celý model. Pokud bychom následovali kteroukoliv relaci směřující od tabulky 'Sales' k jakékoliv jiné tabulce, dojdeme vždy k závěru, že pro jeden záznam v tabulce 'Sales' existuje ve všech ostatních souvisejících tabulkách také maximálně jeden navázaný záznam. Vztah mezi tabulkou 'Sales' a tabulkou 'Date' je jednoznažný, tabulka 'Date' je ve vztahu M:1 na straně 1 a proto do rozšířené verze tabulky 'Sales' patří všechny sloupce z tabulky 'Date'. Stejná situace je u tabulky 'Product', která je také ve vztahu s tabulkou 'Sales' na straně 1. Mezi tabulkou 'Sales' a tabulkou 'Sales Order' je relace 1:1. I zde tedy platí, že pro jeden záznam v tabulce 'Sales' existuje v tabulce 'Sales Order' maximálně jeden odpovídající záznam, a proto také tabulka 'Sales Order' patří do rozšířené verze tabulky 'Sales'. Tabulka 'Category' sice nemá s tabulkou 'Sales' přímou relaci, ale také patří do rozšířené verze tabulky 'Sales'.
Do rozšířené verze základní tabulky totiž mohou patřit také tabulky které jsou se základní tabulkou propojeny pomocí řetězce relací. V řetězci relací ale musí mít všechny relace stejnou kardinalitu M:1 nebo kardinalitu 1:1. V našem příkladu směřuje z tabulky 'Sales' k tabulce 'Product' relace M:1, a stejně tak z tabulky 'Product' směřuje k tabulce 'Category' relace M:1. Obě relace mají stejnou kardinalitu, a proto také tabulka 'Category' patří do rozšířené verze tabulky 'Sales'. Pokud by ale relace vycházející z tabulky 'Sales' byla typu M:1, a relace z tabulky 'Product' k tabulce 'Category' typu 1:M (ve směru od tabulky 'Sales') nebo M:N, došlo by k přerušení řetězce a tabulka 'Category' již by nepatřila do rozšířené verze tabulky 'Sales'. To ale není případ našeho modelu, ve kterém platí, že pro každý záznam v tabulce 'Sales' můžeme prostřednictvím tabulky 'Product' načíst z tabulky 'Category' pouze jednu konkrétní kategorii produktů.
U tabulek propojených pomocí relace typu 1:1 můžeme za určitých okolností uvažovat jako o jedné tabulce. To je příklad tabulek 'Sales Order' a 'Sales'. Obě tabulky ve své rozšířené verzi obsahují sloupce ze všech tabulek nahraných v modelu. V tabulce 'Sales Order' můžeme pro každý jeden záznam najít maximálně jeden odpovídající záznam v tabulce 'Sales'. Pokud budeme z tabulky 'Sales' pokračovat k ostatním tabulkám nahraným v modelu, opět dojdeme k závěru, že pro každý záznam v tabulce 'Sales Order' existuje ve všech ostatních tabulkách maximálně jeden odpovídající záznam. Zaměřit se můžeme například na vztah mezi tabulkou 'Sales Order' a tabulkou 'Category'. Mezi těmito tabulkami existuje řetězec tří relací: 1:1, M:1, M:1. Každá související tabulka je s tabulkou 'Sales Order' na straně 1, řetězec relací proto není přerušen a tabulka 'Category', stejně jako kterákoliv jiná tabulka nahraná v modelu patří do rozšířené verze tabulky 'Sales Order'.
Podívat se můžeme ještě na příklad Smíšeného modelu (Composite model). Na následujícím obrázku je zachycen model, ve kterém jsou tabulky 'Date', 'Product' a 'Sales' v režimu Import a tabulky 'Currency' a 'Customer' v režimu Direct Query.
Všechny relace směřující od tabulky 'Sales' k ostatním tabulkám jsou typu M:1. Do rozšířené verze tabulky 'Sales' ale v tomto konkrétním modelu patří pouze sloupce z tabulky 'Date' a sloupce z tabulky 'Product'. Relace mezi tabulkou 'Sales' a tabulkami 'Currency' a 'Customer' jsou totiž omezené relace (v diagramu modelu jsou omezené relace přerušené závorkami). U omezených relací není možné zaručit, zda pro jeden záznam v základní tabulce ('Sales') existuje v související tabulce (tabulky 'Currency' a 'Customer') maximálně jeden odpovídající záznam. Tabulky 'Currency' a 'Customer' proto nepatří do rozšířené verze tabulky 'Sales'.
Tip: Pokud pracujeme se složitým modelem a nejsme si jistí, zda některá z tabulek patří do rozšířené verze základní tabulky, můžeme pro ověření použít funkci RELATED(). Pokud v základní tabulce vytvoříme nový počítaný sloupec, tak pomocí funkce RELATED() můžeme načítat hodnoty ze všech sloupců z tabulek které patří do rozšířené verze základní tabulky, ve které vytváříme nový počítaný sloupec. Pokud pomocí funkce RELATED() nelze načíst hodnoty ze sloupců z některé z propojených tabulek, tato tabulka nepatří do rozšířené verze základní tabulky.
V následující části příspěvku si na jednoduchých příkladech ukážeme, jak důležité je při psaní DAX výpočtů znát koncept rozšířených tabulek. Pokud totiž v jazyku DAX pracujeme ve vzorcích s celou tabulkou, odkazujeme se ve skutečnosti na rozšířenou verzi této tabulky.
Rozšířené tabulky a DAX výpočty
Všechny příklady v této části příspěvku mají zejména edukativní charakter. Některé ze zobrazených výpočtů mohou být dokonce považované z pohledu výkonnosti za špatné. Důvodem je že dobrá praxe při tvorbě měřítek v jazyku DAX spočívá v používání co nejmenšího počtu sloupců, které jsou potřebné pro dosažení požadovaného výsledku. Abychom si ale mohli ukázat efekt rozšířených tabule, musíme pracovat s celými tabulkami, protože k rozšíření dochází pouze u tabulek, nikoliv u jednotlivých sloupců. Všechny výpočty zobrazené v následující části příspěvku jsou součástí přiloženého souboru.
Model nahraný v použitém cvičném souboru vypadá následovně.
Začneme jednoduchým reportem s vizuály Průřez a Matice. Ve vizuálu Průřez jsou barvy produktů z tabulky 'Product'. Ve vizuálu Matice jsou v řádcích roky z tabulky 'Date' a v hodnotách měřítko [Prodeje], které sčítá hodnoty ze sloupce 'Sales'[Sales Amount] z faktové tabulky 'Sales'.
Uvažujme nyní situaci, kdy bychom chtěli porovnat prodeje ve vybraných barvách s prodeji za všechny produkty, bez ohledu na filtry nastavené na jakékoliv sloupce z tabulky 'Product'. Měřítko, které bude vracet sumu za prodeje všech produktů bez ohledu na filtry nastavené na sloupce v tabulce 'Product' může vypadat například následovně.
Měřítko:
V měřítku [Prodeje (všechny produkty)] odstraňujeme před vyhodnocením měřítka [Prodeje] všechny filtry z tabulky 'Product'. Všechny ostatní filtry ze všech ostatních tabulek zůstanou zachovány. Pokud tedy vložíme nové měřítko do původního vizuálu Matice, měřítko [Prodeje] bude vracet sumu za prodeje produktů ve vybraných barvách a letech, a nové měřítko [Prodeje (všechny produkty)] bude vracet hodnotu prodejů za aktuální rok v aktuálním řádku vizuálu, ale za všechny produkty, bez ohledu na to, jaké barvy jsou vybrané v Průřezu.
Tento efekt může být ještě zřetelnější při použití tabulky 'Sales', do jejíž rozšířené verze patří všechny tabulky z celého modelu.
Měřítko:
V novém měřítku odstraňujeme filtry z rozšířené verze tabulky 'Sales'. Do rozšířené verze tabulky 'Sales' patří v použitém modelu všechny sloupce ze všech tabulek. Pokud tedy vložíme měřítko [Prodeje (celkem)] do původního vizuálu, výsledné hodnoty by již nyní neměly být překvapivé.
O tabulkách bychom v jazyku DAX měli vždy přemýšlet jako o tabulkách v jejich rozšířených verzích, a nemusí jít pouze o výpočty ve kterých odstraňujeme filtry. Představme si například report, ve kterém bychom chtěli vidět počet prodávaných produktů v jednotlivých letech a počet prodaných kusů těchto produktů.
Měřítko které bude vracet počet prodaných kusů vytvoříme jednoduše, stačí sečíst hodnoty ze sloupce 'Sales'[Order Quantity] například následujícím způsobem.
Měřítko:
Zjistit počet produktů které se v daném období prodávali ale už tak jednoduché nebude. V tabulce 'Sales' máme k dispozici sloupec 'Sales'[ProductKey], nicméně tento sloupec obsahuje jedinečné identifikátory aktuální verze produktu. V tabulce 'Product' ale může mít jeden produkt více verzí. Pokud se například u některého z produktů změnila cena nebo jakýkoliv jiný atribut, má tento produkt v tabulce 'Product' novou verzi s novým jednoznačným identifikátorem, nicméně stále se jedná o ten samí produkt. Pokud bychom tedy chtěli zjistit jedinečný počet prodávaných produktů, musíme pracovat se sloupcem 'Product'[Product] z tabulky 'Product'.
Měřítko:
Pokud obě nová měřítka vložíme do původního vizuálu Matice, výsledek bude vypadat následovně.
Měřítko [Počet prodaných kusů] vrací správné hodnoty. Výsledky měřítka [Počet produktů] jsou ale v každém řádku vizuálu stejné, a jde o počet všech produktů v kategorii "Bikes". Důvodem proč měřítko [Počet produktů] vrací pro každý rok stejnou hodnotu je, že filtry které jsou nastaveny na sloupce z tabulky 'Date' jsou propagovány do tabulky 'Sales', ale směr filtrace mezi tabulkou 'Sales' a 'Product' už neumožňuje propagaci filtrů do tabulky 'Product'.
Požadovaného výsledku bychom mohli dosáhnout například aktivací obousměrné propagace filtrů u relace mezi tabulkami 'Sales' a 'Product', nicméně v článku věnovanému rozšířeným tabulkám si ukážeme alternativní řešení. Jak už jsme si několikrát řekli, tabulka 'Sales' obsahuje ve své rozšířené verzi všechny tabulky z celého modelu, a tedy i tabulku 'Product'. Pokud tedy zafiltrujeme celou tabulku 'Sales', budeme filtrovat všechny tabulky v modelu, včetně tabulky 'Product'.
Měřítko:
Pokud nové měřítko vložíme do původního vizuálu, výsledek bude vypadat následovně.
V měřítku [Počet produktů (2)] je nyní ve filtru funkce CALCULATE() použita celá tabulka 'Sales'. Filtry ve funkci CALCULATE() jsou vyhodnoceny v originálním kontextu, to znamená že v našem reportu v kontextu vybrané kategorie "Bikes" a v aktuálním roce v aktuálním řádku vizuálu. Tabulka 'Sales' je proto před použitím ve filtru funkce CALCULATE() zafiltrována pouze na vybranou kategorii produktů v Průřezu a pouze na aktuální rok v aktuálním řádku vizuálu. Následně je takto zafiltrovaná tabulka 'Sales' použita jako filtr před vyhodnocením funkce DISTINCTCOUNT() v prvním argumentu funkce CALCULATE(). Protože používáme ve filtru funkce CALCULATE() celou tabulku a tato tabulka obsahuje ve své rozšířené verzi všechny tabulky z celého modelu, včetně tabulky 'Product', filtrujeme ve skutečnosti také právě tabulku 'Product', a to pouze na produkty z kategorie "Bikes" a na produkty které se prodávaly v aktuálním roce v aktuálním řádku vizuálu. Funkce DISTINCTCOUT() proto vrací v každém řádku vizuálu jedinečný počet produktů prodávaných v daném období a ve vybrané kategorii.
Ačkoliv měřítko [Počet produktů (2)] vrací správné výsledky, používání celých tabulek ve filtrech funkce CALCULATE() není dobrá praxe. Takovéto filtry jsou velmi komplexní jak na pochopení, tak z pohledu výkonnosti. Stejného výsledku můžeme dosáhnout i následujícím způsobem.
Měřítko:
V měřítku [Počet produkůt (3)] je nyní ve filtru funkce CALCULATE() pouze jeden sloupec, a to sloupec s jedinečnými identifikátory produktů, který je použitý pro vytvoření relace mezi tabulkou 'Sales' a tabulkou 'Product'. Funkce SUMMARIZE() pracuje také s principem rozšířených tabulek, to znamená že opět spoléháme na to, že tabulka 'Sales' je před vyhodnocením prvního argumentu funkce DISTINCTCOUNT() zafiltrovaná pouze na vybrané kategorie a roky, nicméně v samotném filtru již nepoužijeme celou tabulku v její rozšířené verzi, ale pouze sloupec 'Product'[ProductKey]. To je jediný sloupec, který potřebujeme pro vytvoření filtru pro dosažení požadovaného výsledku.
Shrnutí
Kdykoliv se chystáme použít v DAX výpočtu celou tabulku, měli bychom se zastavit a uvědomit si, že používáme tabulku v její rozšířené verzi. Pokud totiž zjistíme, že použitá tabulka ve své rozšířené verzi obsahuje i sloupce z jiných tabulek, může to mít dopad jak na výsledek výpočtu, tak na rychlost vyhodnocení výpočtu. Využívání rozšířených tabulek v DAX výpočtech vyžaduje určitou praxi, nicméně i pro zkušené DAX vývojáře platí jednoduché pravidlo, díky kterému se můžeme vyhnout zbytečné složitosti. V jakémkoliv výpočtu bychom vždy měli používat pouze ty sloupce, které jsou nezbytně nutné k dosažení požadovaného výsledku. Tím se můžeme vyhnout jak zbytečné komplexitě výpočtů, tak potencionálním problémům s výkonem.
Komentáře
Okomentovat