Práce s Parent/Child hierarchií v Power BI a DAX

Tento příspěvek obsahuje ukázku, jakým způsobem můžeme pomocí jazyka DAX transformovat Parent-Child hierarchii (hierarchii rodič-potomek) do podoby klasické hierarchie, kdy je každá úroveň v hierarchii uložená v samostatném sloupci.

K tomuto tématu je k dispozici také video:

Parent-Child hierarchie je hierarchie uložená typicky ve dvou sloupcích, kdy každý potomek má ve stejném řádku odkaz pouze na svého nejbližšího rodiče.

Na obrázku výše můžete vidět ukázku takovéto hierarchie. Zvýrazněné buňky pak znázorňují všechny nadřízené jednoho konkrétního zaměstnance se jménem Jae Pak. Tento zaměstnanec s hodnotou 291 ve sloupci 'Employee'[EmployeeKey], stejně jako kterýkoliv jiný, má ve svém řádku odkaz pouze na svého nejbližšího přímého nadřízeného, kterým je zaměstnanec s číslem 290. Pokud bychom chtěli zjistit jméno tohoto zaměstnance s číslem 290, musíme se přesunout do jeho řádku kde zjistíme, že zaměstnanec číslo 290 se jmenuje Amy Alberts a tento zaměstnanec má také svého nadřízeného, tentokrát s číslem 277. Takto bychom mohli pokračovat dále až k zaměstnanci na nejvyšší úrovni, kterým je Ken Sánchez.

Tímto způsobem jsou často vytvořeny hierarchie uložené v databázových serverech, například na SQL Serveru. Vztah mezi jednotlivými úrovněmi hierarchie je v takto vytvořené tabulce vyjádřen takzvanou Self-Reference relací, tedy relací, kdy jeden sloupec z dané tabulky odkazuje na druhý sloupec ze stejné tabulky. Tento způsob uložení dat v databázových serverech má své opodstatněné důvody, kterými se ale v tomto příspěvku nemusíme zabývat. 

Power BI, nebo obecně Tabulární model, který běží na pozadí stále více analytických nástrojů, ale nepodporuje Self-Reference relace. Na druhou stranu, v jazyku DAX jsou k dispozici funkce, které umožňují práci s Parent-Child hierarchií a pomocí kterých můžeme Parent-Child hierarchii upravit do podoby klasické hierarchie.

V následující části příspěvku si ukážeme postup, jak Parent-Child hierarchii takzvaně zploštit do podoby klasické hierarchie, zachycené na obrázku výše. S takto upravenou strukturou již můžeme pohodlně pracovat v Power BI nebo dalších analytických nástrojích.

Přeměna Parent-Child hierarchie na klasickou hierarchii v Power BI

V použitém cvičném souboru, který si můžete stáhnout pod odkazem uvedeným níže pod tímto příspěvkem, jsou pro zjednodušení pouze dvě tabulky. Tabulka prodejů 'Sales' a tabulka zaměstnanců 'Employee'. Tabulka zaměstnanců 'Employee' obsahuje tři sloupce - jedinečný identifikátor každého zaměstnance, jedinečný identifikátor nadřízeného zaměstnance a jméno aktuálního zaměstnance.

Pokud bychom v takto vytvořené tabulce použili sloupec se jmény zaměstnanců v reportu ve vizuálu Tabulka, spolu s částkou za prodeje produktů přes tyto zaměstnance, výsledný vizuál by mohl vypadat například následovně.

Na obrázku výše můžeme vidět zaměstnance z tabulky 'Employee' a jejich prodeje. V použitém vizuálu se ale vůbec nezobrazují nadřízení těchto zaměstnanců, protože žádný z nadřízených nemá žádné prodeje. Dále v takto vytvořené tabulce chybí informace o tom, jak je daný zaměstnanec zařazený v organizační struktuře. Cílem příkladu v tomto příspěvku bude vytvořit následující pohled na prodeje jednotlivých zaměstnanců.

Na obrázku výše již můžeme vidět, jakého má každý zaměstnanec nadřízeného a nově také sumu za prodeje produktů u každého vedoucího. Tato celková částka za prodeje u vedoucích zaměstnanců se skládá z jednotlivých dílčích částek vyprodukovaných zaměstnanci, kteří jsou v hierarchii pod aktuálním nadřízeným. Pro zaměstnance Ken Sánchez, který je v hierarchii na nejvyšším místě a je tedy nadřízeným pro každého dalšího zaměstnance, ať už přímím nebo nepřímím, vrací měřítko [Prodeje] celkovou sumu prodejů za všechny zaměstnance.

Prvním krokem ke zploštění Parent-Child hierarchie bude vytvoření pomocného sloupce v tabulce 'Employee', který bude obsahovat celou cestu aktuálního zaměstnance v hierarchii. Takovýto sloupec můžeme vytvořit pomocí funkce PATH(). Funkce PATH() má dva povinné argumenty. Prvním argumentem je jedinečný identifikátor tabulky, kterým je v našem příkladu sloupec 'Employee'[EmployeeKey] s jedinečným identifikátorem každého zaměstnance. Druhým argumentem funkce PATH() je sloupec s identifikátorem rodiče pro aktuální záznam v tabulce. V našem příkladu se bude jednat o sloupec s identifikátory nadřízených, tedy o sloupec 'Employee'[ParentEmployeeKey].

Počítaný sloupec:

Hierarchie celá cesta = PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

Funkce PATH() projde rekurzivně celou tabulku a sestaví pro každého zaměstnance celou jeho cestu v hierarchii následujícím způsobem.

Na základě nového pomocného sloupce 'Employee'[Hierarchie celá cesta] již můžeme vytvořit jednotlivé úrovně hierarchie. Každá úroveň bude v samostatném sloupci. Pro první úroveň můžeme použít následující výpočet.

Počítaný sloupec:

Úroveň 1 =

VAR AktualniUroven = 1

VAR ZamestnanecKey = PATHITEM( Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)

VAR ZamestnanecJmeno = LOOKUPVALUE( Employee[Name], Employee[EmployeeKey], ZamestnanecKey)

RETURN

    ZamestnanecJmeno

Ve funkci PATHITEM() v proměnné ZamestnanecKey je v prvním argumentu odkaz na pomocný sloupec 'Employee'[Hierarchie celá cesta], který obsahuje celou cestu v hierarchii každého zaměstnance. Z této cesty pak budeme chtít v první úrovni vybrat zaměstnance na prvním místě. Výsledkem proměnné ZamestnanecKey tedy bude identifikátor zaměstnance na prvním místě v hierarchii. Tato hodnota pak bude použita v proměnné ZamestnanecJmeno, kde pomocí funkce LOOKUPVALUE() načteme jméno zaměstnance s tímto jedinečným identifikátorem.

Podobným způsobem vytvoříme také další sloupce s dalšími úrovněmi. Jedinou změnou bude první proměnná, ve které budeme měnit číslo aktuální úrovně podle toho, pro jakou úroveň je aktuální sloupec vytvořen.

Počítané sloupce:

Úroveň 2 =

VAR AktualniUroven = 2

VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)

VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)

RETURN

    ZamestnanecJmeno

Úroveň 3 =

VAR AktualniUroven = 3

VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)

VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)

RETURN

    ZamestnanecJmeno

Úroveň 4 =

VAR AktualniUroven = 4

VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)

VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)

RETURN

    ZamestnanecJmeno

V tomto příkladu je maximální hloubka hierarchie 4. To znamená, že pro každou úroveň jsme vytvořili jeden sloupec, celkem čtyři sloupce. V reálném modelu by pak bylo vhodné doplnit do rezervy další sloupce, protože hloubka hierarchie se může v čase měnit.

Nyní zbývá vložit jednotlivé sloupce postupně od úrovně jedna po úroveň čtyři do vizuálu Matice. Pokud do vizuálu vložíme také měřítko [Prodeje], výsledek bude vypadat následovně.

Nyní se již ve vizuálu zobrazují také nadřízení a v řádku každého nadřízeného můžeme vidět sumu za prodeje produktů, které byly prodány přes jeho podřízené zaměstnance. Zbývá dořešit jeden problém, který se vyskytne v případě, kdy je hloubka hierarchie aktuálního zaměstnance menší než je maximální hloubka hierarchie. Tento problém můžeme vidět například u zaměstnance jménem Pamela Ansman-Wolfe.

Pamela Ansman-Wolfe je zaměstnanec přímo podřízený nejvyššímu zaměstnanci v hierarchii a současně pod sebou nemá žádné podřízené. Pamela Ansman-Wolfe proto končí v hierarchii na úrovni číslo dvě. Ostatní úrovně jsou pro tohoto zaměstnance prázdné, to znamená mají hodnotu BLANK. 

V některých analytických nástrojích, jako například v SSAS Tabularním modelu, je pro tento případ k dispozici vlastnost, pomocí které můžeme v hierarchii skrýt řádky, které mají prázdné hodnoty BLANK. Tato vlastnost se jmenuje "Hide Members" a řešením je zvolit nastavení "Hide blank members". V Power BI není v době psaní tohoto příspěvku toto nastavení k dispozici, a proto si ještě vytvoříme měřítko, pomocí kterého budeme skrývat řádky s prázdnými hodnotami BLANK.

Poznámka: Výše zmíněná vlastnost Hide Members se nastavuje pro objekt Hierarchie, a ne pro jednotlivé sloupce které tvoří tuto hierarchii.

Toto pomocné měřítko určené pouze pro použití v rámci konkrétní hierarchie bude obsahovat jednoduchou logiku. V měřítku budeme ověřovat, jestli jsme v aktuální úrovni hierarchie a jestli je hodnota v aktuální úrovni rovna hodnotě BLANK. Pokud ano, jednoduše nahradíme měřítko [Prodeje] prázdnou hodnotou BLANK, a takovýto řádek se následně vůbec nebude ve vizuálu zobrazovat. Procházet budeme postupně každou úroveň hierarchie, a proto ověření pro jednotlivé úrovně vložíme do funkce SWITCH().

Měřítko:

Prodeje (v hierarchii) =

SWITCH

(

    TRUE(),

    ISINSCOPE(Employee[Úroveň 5]) && SELECTEDVALUE(Employee[Úroveň 5]) = BLANK(), BLANK(),

    ISINSCOPE(Employee[Úroveň 4]) && SELECTEDVALUE(Employee[Úroveň 4]) = BLANK(), BLANK(),

    ISINSCOPE(Employee[Úroveň 3]) && SELECTEDVALUE(Employee[Úroveň 3]) = BLANK(), BLANK(),

    ISINSCOPE(Employee[Úroveň 2]) && SELECTEDVALUE(Employee[Úroveň 2]) = BLANK(), BLANK(),

    ISINSCOPE(Employee[Úroveň 1]) && SELECTEDVALUE(Employee[Úroveň 1]) = BLANK(), BLANK(),

    [Prodeje]

)

Pokud nové měřítko vložíme do původního vizuálu matice namísto měřítka [Prodeje], řádky obsahující prázdné hodnoty, tedy řádky se zaměstnanci jejichž hloubka hierarchie je menší než je maximální hloubka hierarchie, se již nebudou zobrazovat.

V reálném modelu by ještě zbývalo vytvořit z jednotlivých úrovní Hierarchii (objekt). Tu je možné v Power BI vytvořit například pomocí kontextové nabídky, která se zobrazí po kliknutí pravým tlačítkem myši na některý ze sloupců, který chceme vložit do hierarchie.

Následně bychom mohli přidat do nově vytvořené Hierarchie všechny sloupce pro každou úroveň, a ostatní pomocné sloupce v tabulce zaměstnanců skrýt, aby se nezobrazovali v uživatelských nástrojích.

Shrnutí

Přeměna Parent-Child hierarchie do podoby klasické hierarchie, kde je každá úroveň uložena v samostatném sloupci, není díky dostupnosti speciálních DAX funkcí vytvořených za tímto účelem příliš složitá. Míra složitosti při práci s Parent-Child hierarchiemi se ale může výrazně zvyšovat s každou malou odchylkou nebo s každým drobným požadavkem na změnu nebo výjimku, což může v reálném modelu nastat velmi rychle. V takovýchto případech je nutné buď upravit měřítka, která jsou použita v kontextu hodnot ze sloupců v hierarchii, nebo definici jednotlivých sloupců vytvořených za účelem zploštění Parent-Child hierarchie. Postup zobrazený v tomto příspěvku ale může být i v těchto situacích považován za základ, od kterého se můžeme následně posunout k řešení specifických situací, které mohou být jedinečné pouze pro konkrétní model.

Další praktické příklady můžete najít na stránce DAX příklady nebo na stránce Power BI. Základní teorii k jazyku DAX a popis vybraných DAX funkcí pak najdete na stránce Jazyk DAX. Video návody pak můžete najít na Youtubovém kanále pod tímto odkazem.

Stáhnout soubor s řešením.
č. 90