V Tabulárním modelu, který běží na pozadí Power BI a dalších analytických nástrojů, můžeme mezi tabulkami vytvořit různé typy relací. Následné propagování filtrů mezi tabulkami pomocí těchto relací je přirozené a intuitivní a velmi zjednodušuje práci při tvorbě DAX výpočtů. Existují ale situace, kdy vytvoření relace mezi dvěma tabulkami není možné nebo alespoň není úplně jednoduché. V těchto případech můžeme buď upravit model nebo vytvořit takzvanou virtuální relaci. V tomto příspěvku si ukážeme příklad vytvoření virtuální relace.
K tomuto tématu je k dispozici také video:
Virtuální relace je relace, která je vytvořena pouze v měřítku. Tato relace tedy není vidět v náhledu modelu ani v metadatech modelu. Ačkoliv je virtuální relace dostupná pouze v daném měřítku, pro uživatele reportu se nic nemění a virtuální relace se chová stejně jako by se jednalo a relaci standardní.
Uvažujme například následující model, kde jsou tabulky 'Sales', 'Date' a 'Product'.
Tabulka 'Sales' obsahuje záznamy o prodejích produktů. Tabulky 'Date' a 'Product' jsou filtrovací (dimenzní) tabulky a jsou propojeny s tabulkou 'Sales' pomocí standardních relací ONE – TO – MANY, kdy každý jeden produkt mohl být prodaný vícekrát, a stejně tak v každém dnu mohlo dojít k více objednávkám.
Představme si nyní situaci, kdy některé z prodaných produktů byly vadné a bude je proto nutné stáhnout zpět od zákazníků a těmto zákazníkům vrátit jejich peníze. Produkty určené ke stažení jsou uloženy v samostatné tabulce nazvané 'Vadná série', která má následující strukturu.
Tabulka 'Vadná série' obsahuje čtyři sloupce. V prvním sloupci jsou jedinečné identifikátory produktů, ve druhém sloupci datumy ve kterých byly tyto produkty prodány a dále sloupec kategorie produktů a sloupec rok.
Naším úkolem je nyní zjistit, kolik peněz nás bude stát zpětný odkup produktů od zákazníků, to znamená zjistit kolik jsme inkasovali za prodeje produktů uvedených v tabulce 'Vadná série'. Tabulka 'Vadná série' ale nemá společnou relaci s tabulkou 'Sales', kde jsou uloženy potřebné informace o prodejích včetně celkové částky inkasované za prodeje produktů.
Pokud vložíme sloupec 'Vadná série'[Rok] a sloupec 'Vadná série'[Category] do řádků vizuálu Matice, a do hodnot vložíme měřítko [Prodeje], výsledek bude vypadat následovně.
Měřítko [Prodeje] nyní vrací v každém řádku vizuálu stejnou hodnotu, a to sumu za prodeje všech produktů ze všech kategorií a za všechny roky, protože filtry nastavené na sloupce z tabulky 'Vadná série' nejsou propagovány do tabulky 'Sales', protože nemáme mezi těmito tabulkami vytvořenou relaci. Vytvoření relace mezi tabulkou 'Sales' a tabulkou 'Vadná série' nyní ani není možné bez předešlé úpravy modelu, protože ani v tabulce 'Sales', ani v tabulce 'Vadná série' nemáme k dispozici sloupec pro vytvoření potřebné relace. V takovéto situaci může být řešením právě vytvoření virtuální relace. Než se ale dostaneme k samotnému řešení, podívejme se ještě jednou na strukturu tabulky 'Vadná série'.
Klíčové pro pochopení celého problému je uvědomit si vztah mezi prvními dvěma sloupci v tabulce uvedené na obrázku výše. Vadný produkt určený ke stažení totiž není automaticky každý prodaný produkt, který je uvedený ve sloupci 'Vadná série'[ProductKey]. Současně nemůžeme jednoduše říci, že všechny produkty prodané v určitý den ze sloupce 'Vadná série'[Order Date] jsou produkty určené ke stažení. Vadné produkty sice byly ty, které mají záznam v tabulce 'Vadná série', ale současně muselo k jejich prodeji dojít v konkrétní den. Jinak řečeno, firma v určitém období prodává produkty, které jsou v pořádku. V následujícím období dojde k prodejům některých vadných produktů. Protože víme, ve který den byl konkrétní vadný produkt prodán, budeme stahovat z prodeje pouze tento produkt, ale stejný produkt prodaný před nebo po uskutečnění prodejů vadných produktů byl v pořádku a takovýto produkt proto nebudeme od zákazníků stahovat. Mezi sloupcem 'Vadná série'[ProductKey] a sloupcem 'Vadná série'[Order Date] tedy existuje vztah, který nesmíme porušit.
Jako první varianta řešení problému se může nabízet vytvoření dvou relací. První relace by propojila tabulku 'Vadná série' s tabulkou 'Product' na základě sloupců s jedinečnými identifikátory produktů v každé tabulce. Druhá relace by propojila tabulku 'Vadná série' s tabulkou 'Date' na základě sloupců 'Vadná série'[Order Date] a 'Date'[Date]. Na následujícím obrázku je znázorněn takto popsaný model.
Problémem výše uvedeného schématu je že takto vytvořený model by byl nejednoznačný. Nejednoznačnost spočívá v tom, že filtry aplikované na sloupce z tabulky 'Vadná série' by mohly být propagovány do tabulky 'Sales' více cestami. Jednou přes tabulku 'Product', jednou přes tabulku 'Date' a potřetí přes obě tabulky současně. Tato nejednoznačnost je při tvorbě modelu nepřípustná, protože bychom jednoduše mohli dostat různé výsledky podle toho jakým způsobem by došlo k propagování filtrů pomocí relací. Z tohoto důvodu nám Power BI ani neumožní vytvořit model takovýmto způsobem.
Pokud budeme nadále vycházet z předpokladu že nechceme nebo nemůžeme upravovat model, řešením může být vytvoření virtuální relace, jak si ukážeme v následující části příspěvku.
Virtuální relace a funkce TREATAS
Logika vytvoření virtuální relace je relativně jednoduchá. V měřítku budeme potřebovat načíst hodnoty ze sloupců určených pro vytvoření relace z jedné tabulky v aktuálním kontextu vyhodnocení, a použít tyto hodnoty pro filtrování sloupců z tabulky nebo z více tabulek, se kterými chceme virtuální relaci vytvořit. V našem příkladu budeme chtít použít hodnoty ze sloupce 'Vadná série'[ProductKey] pro filtrování sloupce 'Product'[ProductKey], a hodnoty ze sloupce 'Vadná série'[Order Date] pro filtrování sloupce 'Date'[Date]. Tuto logiku můžeme v jazyku DAX vytvořit více různými způsoby. Nejjednodušším a ve většině případů nejefektivnějším řešením je použití funkce TREATAS() následujícím způsobem.
Měřítko:
Pokud nyní vložíme nové měřítko do původního vizuálu Matice s roky a kategoriemi produktů z tabulky 'Vadná série' v řádcích, výsledek bude vypadat následovně.
Měřítko [Prodeje (Vadná série)] nyní vrací v sumu za prodeje produktů určených ke stažení, přesně podle našich požadavků.
Výhodou vytvoření virtuální relace tímto způsobem je fakt, že měřítko [Prodeje (Vadná série)] můžeme používat přímo v kontextu atributů (sloupců) z tabulek 'Date' a 'Product'. Tabulku 'Vadná série' tak můžeme pro uživatele reportů úplně skrýt, a nechat uživatele používat měřítko [Prodeje (Vadná série)] libovolným způsobem. Pokud například odebereme z řádků vizuálu roky a kategorie pocházející z tabulky 'Vadná série', a místo těchto sloupců vložíme do řádků vizuálu sloupce 'Date'[Rok] a 'Product'[Category], na výsledném vizuálu se nic nezmění.
Protože je virtuální relace vytvořena na základě sloupců, které určují granularitu tabulek 'Product' a 'Date' (sloupce s jedinečnými hodnotami určené pro vytvoření relací), můžeme měřítko [Prodeje (Vadná série)] použít také v kontextu jiných atributů než v kontextu kategorií a let. Do řádků vizuálu tak můžeme přidat například čtvrtletí z tabulky 'Date' a barvy produktů z tabulky 'Product'.
Jak je možné vidět na obrázku výše, všechny vadné produkty byly prodány v černé barvě, a to v období od čtvrtého čtvrtletí roku 2018 do třetího čtvrtletí roku 2018.
Virtuální relace vytvořená výše popsaným způsobem má také své limity. Stejný postup bychom museli aplikovat na každý typ agregace, protože virtuální relace je dostupná pouze v rámci vyhodnocení měřítka. Z pohledu výkonnosti je pak vždy lepší řešením vytvoření standardní relace. Na výkonnostní limity virtuální relace bychom ale narazily až v případě práce s velkým množstvím záznamů v jednotlivých tabulkách, což není případ použitého modelu. Rychlost vyhodnocení jednotlivých variant výpočtů je však vždy velmi individuální a pro spravedlivé porovnání je třeba vždy provést měření.
Funkce TREATAS() je funkce představená v roce 2017, a jako taková nemusí být dostupná ve starších verzích Tabulárního modelu. Pokud pracujeme s Power BI a máme aktuální verzi tohoto nástroje, použití funkce TREATAS() pro vytvoření virtuální relace by měla být první volba. Funkce TREATAS() ale není k dispozici například ve starších verzích Power Pivot v Excelu, respektive je dostupná pouze ve verzi Excelu Office 365. Stejně tak tuto funkci nenajdeme SSAS Tabular ve verzích do roku 2016 včetně. Proto si ještě můžeme ukázat alternativní způsob vytvoření virtuální relace s využitím funkce INTERSECT().
Virtuální relace a funkce INTERSECT
Funkce INTERSECT() má dva povinné argumenty, a to tabulky se stejným počtem sloupců. Předpokladem pro použití funkce INTERSECT() je že první sloupec v první tabulce musí být stejného datového typu jako první sloupec v druhé tabulce. Druhý sloupec v první tabulce musím mít stejný datový typ jako druhý sloupec v druhé tabulce, a tak dále.
Výsledkem funkce INTERSECT() je pak tabulka, která obsahuje hodnoty vyskytující se v obou tabulkách současně. Nejdůležitější informací pro náš příklad s vytvořením virtuální relace je fakt, že funkce INTERSECT() drží Lineage Tag tabulky zadané v prvním argumentu. Proto v našem příkladu použijeme v prvním argumentu funkce INTERSECT() tabulku se sloupci 'Product'[ProductKey] a 'Date'[Date], a ve druhém argumentu tabulku se sloupci 'Vadná série'[ProductKey] a 'Vadná série'[Order Date]. Celý výpočet s použitím funkce INTERSECT() může vypadat například následovně.
Měřítko:
Pokud nové měřítko vložíme do původního vizuálu Matice, můžeme si porovnat že výsledky měřítka [Prodeje (Vadná série)] a měřítka [Prodeje (vadná série) – alternativní] vrací obě stejné výsledky.
Chování obou měřítek je významově totožné, nicméně ve většině situací bude varianta výpočtu s funkcí TREATAS() vyhodnocena rychleji. Opět i zde platí že každý model je jiný a vždy při porovnávání výkonu více různých variant výpočtů musíme použít měření.
Další příklady můžete najít na stránce DAX - Příklady nebo na mém Youtubovém kanále pod tímto odkazem.
Komentáře
Okomentovat