Typickým příkladem, který zachycuje vztah mezi dvěma tabulkami na základě rozsahu je zařazení položek z jedné tabulky do kategorií, kdy kategorie jsou uložené v jiné tabulce, a každá kategorie má spodní a horní hranici, mezi které musí hodnota aktuální položky patřit. V tomto příspěvku si ukážeme dva možné přístupy, jak pomocí jazyka DAX vyjádřit vztah mezi dvěma tabulkami na základě rozsahu v případě, kdy v tabulkách nemáme k dispozici potřebné klíče pro vytvoření standardní relace.
K tomuto tématu je k dispozici také video:
V Tabulárním modelu (Power BI, SSAS Tabular, Power Pivot atd.) můžeme vytvořit relace s různou kardinalitou (1:M, M:N, 1:1) a s různým nastavením směru propagace filtrů (jednosměrné, obousměrné). Existují ale situace, kdy vytvořit relaci mezi tabulkami jednoduše není možné, protože zkrátka nemáme k dispozici potřebné klíče (sloupce určené pro vytvoření relací).
Uvažujme například situaci, kdy máme model, který zachycuje mimo jiné prodeje produktů přes různé obchodní kanály. Tento model obsahuje data nadnárodní firmy, která má celou řadu možností, jak prodávat své produkty. Prodeje se tak uskutečňují v různých zemích přes kamenné prodejny, přes obchodníky, přes katalogové prodeje nebo přes internet. Informace o obchodech jsou uloženy ve standardní dimenzní tabulce, kdy každý obchod má v této tabulce jeden záznam s informacemi o daném obchodě.
Po určité době existence daného modelu vznikne požadavek ohodnotit obchody podle jejich výkonnosti v posledním roce. Do modelu tak bude přidána nová tabulka, která bude obsahovat kategorie obchodů podle jejich výkonnosti. Kategorie výkonnosti jsou čtyři (Výborná, Dobrá, Špatná, Velmi špatná) a daný obchod bude do vybrané kategorie zařazen podle objemu prodejů, který bude přepočítán na počet zaměstnanců.
Problémem je že mezi tabulkou s obchody a tabulku s kategoriemi neexistuje relace, a nemáme k dispozici ani potřebné klíče pro vytvoření odpovídající relace. V tabulce s hodnocením totiž není kategorie pro každý objem prodejů, ale jsou zde kategorie pro určitý rozsah prodejů. Každá kategorie má spodní a horní hranici, a aby byl obchod zařazen do kategorie, musí být přepočítaný objem prodejů na jednoho zaměstnance v rozsahu který je dán právě minimální a maximální hodnotou pro danou kategorii.
V následující části si ukážeme dva možné přístupy pro vyřešení výše popsaného problému. V prvním přístupu nebudeme tvořit relaci, ale v tabulce s obchody si vytvoříme nový počítaný sloupec, do kterého si pomocí DAX výrazu uložíme název kategorie, do které příslušný obchod spadá. Toto řešení je ideální pokud nám stačí do tabulky s obchody přidat jeden sloupec. V případě, kdy by tabulka s kategoriemi obsahovala celou řadu sloupců, které jsou užitečné pro tvorbu reportů, je obvykle lepší vytvořit mezi tabulkami relaci. Proto si ukážeme i druhý přístup, ve kterém si pomocí jazyka DAX vytvoříme potřebné klíče pro vytvoření standardní relace.
Vyjádření vztahu mezi tabulkami na základě rozsahu v jazyku DAX
Celý příklad je vytvořen ve cvičném Power BI souboru Contoso Sales Sample for Power BI Desktop.pbix. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.
V použitém modelu je mimo jiné dimenzní tabulka s názvem 'Stores', která obsahuje jeden záznam pro každý obchod. Přes tyto obchody se pak prodávají produkty fiktivní společnosti "Contoso". Záznamy o prodaných produktech jsou v tabulce s názvem 'Sales'.
V tabulce 'Stores' jsou čtyři typy obchodů – Catalog, Online, Reseller a Store. V příkladech uvedených v tomto příspěvku budeme chtít vyhodnotit, zda obchody v dané kategorii dosahovali určité výkonnosti v konkrétním roce. Výkonnost bude vyhodnocena na základě objemu prodejů uskutečněných přes každý obchod. Objem prodejů přes konkrétní obchod ještě přepočítáme na počet zaměstnanců v daném obchodě. Dále budeme chtít při zařazení obchodu do výkonnostní kategorie rozlišovat, o jaký typ obchodu se jedná, protože nemá smysl porovnávat výkonnost přepočtenou na zaměstnance například internetových obchodů (Online) s kamennými prodejnami (Store). Obchody budeme řadit do čtyř kategorií podle výkonnosti - Výborná, Dobrá, Špatná, Velmi špatná.
Konfigurační tabulka pro hodnocení obchodů má následující strukturu.
Sloupec 'Hodnocení'[Kategorie] obsahuje hodnoty jenž potřebujeme přiřadit ke každému obchodu. Kategorii pak určíme na základě typu obchodu v prvním sloupci, a objemu ročních prodejů přepočítaných na počet zaměstnanců v daném obchodě, kdy tato hodnota musí spadat do rozsahu který je dán hodnotami ve sloupcích 'Hodnocení'[Spodní hranice kategorie] a 'Hodnocení'[Horní hranice kategorie].
Stejnou tabulku, kterou vidíme na obrázku výše, si můžeme v Power BI vytvořit jednoduše kliknutím na tlačítko "Nová tabulka", které najdeme na kartě "Modelování". Do okna editoru vzorců pak stačí vložit následující DAX výraz.
Počítaná tabulka:
Nová tabulka se po dokončení stane součástí modelu, nicméně mezi tabulkou 'Hodnocení' a tabulkou 'Stores' nyní nemůžeme vytvořit relaci.
V tabulce 'Stores' neexistuje klíč, který bychom mohli použít pro vytvoření relace s tabulkou 'Hodnocení'. Abychom mohli přiřadit hodnocení ke konkrétnímu obchodu, máme dvě možnosti. Vytvořit klíče v každé tabulce potřebné pro vytvoření relace, nebo vytvořit nový počítaný sloupec v tabulce 'Store', který bude obsahovat pro každý obchod příslušnou kategorii z tabulky 'Hodnocení'. Ukážeme si oba dva přístupy s tím, že začneme druhou možností, kdy budeme tvořit v tabulce 'Stores' nový počítaný sloupec který bude obsahovat kategorii z tabulky 'Hodnocení'.
Poznámka: Přiřazením výkonnostní kategorie přímo do tabulky s obchody mluvíme z pohledu tvorby modelu o takzvané denormalizaci. V případě vytvoření relace mluvíme o takzvané normalizaci modelu. Oba přístupy mají své výhody i nevýhody a výběr jednoho nebo druhého přístupu záleží na mnoha faktorech, jejichž popis je nad rámec tohoto příspěvku.
Počítaný sloupec v DAX s kategoriemi na základě rozsahu hodnot
Abychom mohli přiřadit ke každému obchodu výkonnostní kategorii z tabulky 'Hodnocení', budeme muset v novém počítaném sloupci postupně:
- vypočítat prodeje přes daný obchod v konkrétním roce,
- přepočítat prodeje přes daný obchod na jednoho zaměstnance,
- zjistit typ daného obchodu (Catalog, Online, Store, Reseller),
- na základě prodejů na zaměstnance a typu obchodu přiřadit výkonnost obchodu z tabulky 'Hodnocení'.
Nový počítaný sloupec si vytvoříme v jednom kroku, kdy si jednotlivé mezivýpočty uložíme do proměnných. Nejdříve určíme rok, za který budeme obchody hodnotit. Jako rozhodující rok pro vyhodnocení výkonnosti můžeme použít rok, který bude předcházet poslednímu roku, ve kterém došlo k alespoň nějakým prodejům, abychom měli jistotu, že budeme pracovat s uzavřeným obdobím. Následně tento rok přidáme do filtru při vytvoření sumy prodejů pro jednotlivé obchody. Počet zaměstnanců v daném obchodě najdeme ve sloupci 'Stores'[EmployeeCount]. Protože tvoříme výpočet v počítaném sloupci, kde je automatický kontext řádku, můžeme k aktuálnímu počtu zaměstnanců přistupovat jednoduše pomocí odkazu na tento sloupec. Následně vydělíme sumu prodejů ve vybraném roce počtem zaměstnanců v daném obchodě. Poslední informaci, kterou potřebujeme zjistit, je typ obchodu, který je uložený ve sloupci 'Stores'[StoreType].
Jakmile budeme mít v proměnných k dispozici všechny hodnoty potřebné pro sestavení filtrovacích podmínek pro tabulku 'Hodnocení', můžeme načíst tuto tabulku zafiltrovanou pomocí dříve uložených kritérií pro aktuální obchod. Tato tabulka by měla v každém řádku tabulky 'Stores' obsahovat pouze jeden řádek, což si před vyhodnocením výpočtu ještě ověříme jednoduchým logickým výrazem. Celý výpočet může vypadat například následovně.
Počítaný sloupec v tabulce 'Stores':
Výsledkem je nový počítaný sloupec v tabulce 'Stores'. Tento sloupec pak můžeme použít v Power BI vizuálech a podívat se tak například, kolik obchodů patří do každé kategorie.
Měřítko:
Na obrázku níže můžeme například vidět, že nejvíce obchodů patří do kategorie "Dobrá". Naopak hodnocení "Velmi špatná" má pouze 17 obchodů z celkového množství 306 obchodů.
Nový sloupec 'Stores'[Výkonnost] můžeme používat v reportech jako kterýkoliv jiný sloupec. Můžeme si například vytvořit následující vizuál, kde jsou v řádcích vizuálu hodnoty ze sloupce 'Stores'[Výkonnost], 'Stores'[StoreType] a 'Stores'[StoreName], a v hodnotách vizuálu vybraná měřítka.
Prostřednictvím výše zobrazeného vizuálu tak již můžeme mimo jiné vidět, které konkrétní obchody patří do konkrétních kategorií.
Přístup s přiřazením kategorie ke každému obchodu do tabulky 'Stores' zobrazený v této části příspěvku je obvykle dostačující v případě, kdy potřebujeme do cílové tabulky přidat ze zdrojové tabulky (v našem příkladu z tabulky 'Hodnocení') jeden nebo malé jednotky sloupců. Pokud by ale ve zdrojové tabulce bylo větší množství sloupců potřebných pro tvorbu reportů, může být z pohledu komprese dat a výkonnosti výhodnější vytvořit mezi tabulkami relaci. Jak vytvořit relaci mezi tabulkou 'Stores' a tabulkou 'Hodnocení' si ukážeme v následující části příspěvku.
Vytvoření chybějících klíčů pro relaci pomocí DAX výpočtů
Před tím, než přistoupíme k samotnému řešení, můžeme si připomenout strukturu tabulky 'Hodnocení' kterou potřebujeme připojit k modelu pomocí relace.
Tabulka 'Hodnocení', tak jak je nyní vytvořená v modelu, neobsahuje žádný klíč, pomocí kterého bychom mohli tuto tabulku propojit pomocí relace s tabulkou 'Stores'. Vytvořit si klíč pro relaci ale v tabulce 'Hodnocení' nebude nijak složité. Při detailnějším pohledu na celou tabulku zobrazenou na obrázku výše je totiž zřejmé, že každý řádek v této tabulce je jedinečný. Potřebný klíč pro vytvoření relace tak můžeme vytvořit například zřetězením hodnot z více existujících sloupců. Nový, takto vytvořený sloupec by pak měl v každém řádku obsahovat jedinečný identifikátor aktuálního řádku.
K vytvoření klíče by v našem příkladu stačilo zřetězit hodnoty z prvních dvou sloupců tabulky 'Hodnocení', tedy sloupců s typem obchodu a kategorií, protože kombinace těchto dvou hodnot je pro každý řádek jedinečná.
Další možností, kterou si také ukážeme, je vytvořit si v tabulce 'Hodnocení' jedinečný identifikátor pomocí některé z DAX funkcí určených pro vytvoření pořadí. K tomuto účelu se nabízí například funkce ROWNUMBER(), která v případě kdy není zaručeno jedinečné pořadí, vrací chybu.
Vytvořit si v tabulce 'Hodnocení' jedinečný identifikátor pomocí DAX funkce ROWNUMER() můžeme například následujícím způsobem.
Počítaný sloupec v tabulce 'Hodnocení':
K funkci ROWNUMBER() je důležité poznamenat, že tato funkce negeneruje jedinečný identifikátor každého řádku, ale vrací v podstatě pořadí jednotlivých řádků z tabulky zadané v prvním argumentu. Aby tato funkce správně fungovala, musí být každý řádek v použité tabulce jedinečný. V opačném případě vrací funkce ROWNUMBER() chybu.
Dále, funkce ROWNUMBER() patří do kategorie WINDOW funkcí. Tyto funkce, pokud jsou použity v počítaných sloupcích, mohou generovat cyklickou závislost. K tomu dochází zejména v situaci, kdy v argumentu [Relation] - tedy v argumentu kde uvádíme tabulku, použijeme odkaz na celou tabulku ve které současně tvoříme počítaný sloupec. Při určování aktuálního řádku WINDOW funkcemi totiž dochází k použití všech sloupců z této tabulky. Do této tabulky, pokud je uvedena celá, ale patří také nový počítaný sloupec, který aktuálně tvoříme, a tím výsledek nového počítaného sloupce závisí sám na sobě a vzniká cyklická závislost.
Způsobů, jak se v tomto případě vyhnout cyklické závislosti je celá řada. Zřejmě nejjednodušší je pak použít funkci MATCHBY(), stejně jako v případě výše uvedeného výpočtu. Pokud totiž použijeme v rámci WINDOW funkcí funkci MATCHBY(), pro určení aktuálního řádku jsou použity pouze sloupce uvedené v této funkci. Opět i zde by v našem příkladu stačilo vložit do funkce MATCHBY() první dva sloupce z tabulky 'Hodnocení', které nám jednoznačně identifikují každý řádek tabulky. Použít ve funkci MATCHBY() všechny sloupce z tabulky, přirozeně vyjma sloupce který aktuálně tvoříme, je ale více obecný postup.
Po dokončení nového počítaného sloupce vypadá tabulka 'Hodnocení' následovně.
Nyní tedy máme k dispozici sloupec s jedinečným identifikátorem každého řádku. Následně stačí přidat tento sloupec také do tabulky 'Stores'. To můžeme udělat velmi podobným způsobem jakým jsme přiřazovali kategorii ke každému obchodu v první části tohoto příspěvku. Jedinou změnou bude že nebudeme do nového počítaného sloupce v tabulce 'Stores' vkládat přímo kategorii, ale sloupec 'Hodnocení'[ID] s jedinečným identifikátorem tabulky 'Hodnocení'.
Počítaný sloupec v tabulce 'Stores':
Jediná změna oproti původnímu výpočtu v první části tohoto příspěvku je v proměnné HodnoceniObchoduID, kde nyní namísto kategorie vybíráme z tabulky 'Hodnocení' sloupec 'Hodnocení'[ID]. Na základě nových sloupců 'Hodnocení'[ID] a 'Stores'[ID hodnocení] již můžeme vytvořit relaci mezi tabulkami 'Hodnocení' a 'Stores'.
V takto vytvořeném modelu můžeme s tabulkou 'Hodnocení' pracovat jako s kteroukoliv jinou dimenzní tabulkou.
Shrnutí
V Tabulárním modelu se můžeme setkat se situací, kdy mezi dvěma tabulkami existuje vztah, který je vyjádřen určitým rozsahem. V takovém případě můžeme hodnoty ze zdrojové tabulky denormalizovat, to znamená pomocí počítaných sloupců je přidat do cílové tabulky. Druhou možností je vytvořit relaci mezi tabulkami. Pokud chceme tabulky se vztahem na základě rozsahu propojit pomocí relace, musíme nejdříve v dotčených tabulkách vytvořit vhodné sloupce s klíči potřebnými pro vytvoření relace.
Komentáře
Okomentovat