Hierarchie sú výborná funkcionalita pre zlepšenie reportingu. Doteraz sa využívali najmä v excelovskom PowerPivote, ale od minulého roka začínajú byť konečne použiteľnejšie aj v Power BI Desktope. Majú však jedno technické obmedzenie – dajú sa spraviť iba zo stĺpcov z tej istej tabuľky. Ako ale spraviť hierarchiu z viacerých tabuliek, keď to potrebujú užívatelia?

Hierarchie majú hneď viacero výhod. Zoskupujú najpoužívanejšie sady stĺpcov do jedného objektu, urýchľujú samotný reporting, a dajú sa dokonca použiť aj na zjednodušenie dátového modelu a aj optimalizáciu výkonu. Veľakrát však ľudia zastanú na tom, keď chcú združiť stĺpce z viacerých tabuliek do jednej hierarchie. Pretože hierarchia sa dá vytvoriť iba zo stĺpcov v jednej tabuľke. A vytvoriť hierarchiu z viacerých tabuliek je na prvý pohľad problém. Náhradné riešenia nie sú zložité, ale líšia sa mierne od toho, kde sa nachádzajú dané stĺpce. Vo všeobecnosti preto existujú 2 hlavné riešenia, ako na to. A teraz sa na ne pozrieme. Použijeme opäť náš vzorový súbor PowerPivotu, resp. Power BI.

Riešenie č. 1 – stĺpce z nadradených číselníkov

Majme takúto reportovaciu klasiku – máme hlavnú tabuľku Objednávky, na ktorú napojený číselník Produkty (obsahuje zoznam všetkých produktov), na ktorý je napojený nadčíselník Podkategórie (obsahuje zoznam všetkých podkategórií produktov), a naňho je napojený ďalší nadčíselník Kategórie (obsahuje zoznam všetkých kategórií produktov):

A my by sme teraz chceli spraviť hierarchiu Kategória-Podkategória-Produkt, kde:

1. úroveň Kategória pochádza z tabuľky Kategórie, zo stĺpca ProductCategoryName,
2. úroveň Podkategória pochádza z tabuľky Podkategórie, zo stĺpca ProductSubcategoryName,
3. úroveň Produkt pochádza z tabuľky Produkty, zo stĺpca ProductName.

V tomto prípade bude riešenie jednoduché. Stačí si dotiahnuť príslušné stĺpce do tej tabuľky, ktorá je na najspodnejšej úrovni budúcej hierarchie. Čiže v tomto prípade do tabuľky Produkty. Alternatívne môžete všetky stĺpce dotiahnuť aj do centrálnej tabuľky v dátovom modeli, v tomto prípade Objednávky. Rozdiel bude najmä v tom, kde budete mať umiestnenú tú hierarchiu. Pamäťové nároky budú vďaka automatickej kompresii viac-menej rovnaké.

Prejdete teda do tabuľky Produkty, a vytvoríme v nej tieto dva vypočítané stĺpce:

Podkategória = RELATED(‘Podkategórie'[ProductSubcategoryName])

Kategória = RELATED(‘Kategórie'[ProductCategoryName])

Toto sú klasické vypočítané stĺpce, ktoré sme už X-krát používali v predchádzajúcich článkoch. Na tomto prípade je zaujímavé možno akurát tak to, že stĺpec Kategória sme si dotiahli priamo cez 2 tabuľky cez jednu funkciu RELATED, pretože sme išli v rovnakom smere prepojení, a dá sa to potom takto spraviť aj naraz. Alternatívne si môžete tieto stĺpce dotiahnuť do tej istej tabuľky cez Power Query.

Potom už môžete spraviť hierarchiu. Vytvorte teda novú hierarchiu zo stĺpcov Kategória, Podkategória a ProductName, nazvite ju “Kategória-Podkategória-Produkt”, a prípadne aj premenujte jej úrovne na rovnaké názvy:

Po dosadení do kontingenčky spolu napr. s merítkom Obrat si môžete rozklikávať obraty podľa kategórií produktov, ich podkategórií až na úroveň jednotlivých produktov:

A takto ide ľahko vytvoriť hierarchiu z viacerých tabuliek 🙂

Riešenie č. 2 – stĺpce z rôznych číselníkov

V tomto prípade to bude trochu ťažšie, aj keď len na prvý pohľad. Povedzme, že si teraz vyberieme túto časť dátového modelu:

A v tomto prípade by sme tiež chceli spraviť hierarchiu z viacerých tabuliek, tentokrát hierarchiu Rok-Krajina, pričom:

1. úroveň Rok pochádza z tabuľky Čas zo stĺpca CalendarYear,
2. úroveň Krajina pochádza z tabuľky Regióny zo stĺpca Krajina.

Ak by sme išli podľa predchádzajúceho riešenia, tak by sme skúšali vytvoriť hierarchiu v tej tabuľke, ktorá obsahuje stĺpce z najnižšej úrovne budúcej hierarchie. Tu ale hneď narazíme na problém, že tie 2 tabuľky sú číselníky, ktoré nie sú medzi sebou prepojené priamo, a vlastne ani nemajú medzi sebou žiaden priamy súvis. Masochisti sa môžu pokúsiť o volanie správnej kombinácie funkcií RELATED a RELATEDTABLE, ale dá sa to aj ľahšie.

Stačí si uvedomiť dve veci. Prvá je to, že keď dáta v 2 tabuľkách nemajú medzi sebou žiaden súvis, tak sa zvyčajne robí kombinácia všetkých riadkov z jednej tabuľky so všetkými riadkami z druhej tabuľky. Čiže v našom prípade kombinácia všetkých rokov so všetkými krajinami. Z toho by nám ale vzišla zbytočne veľká hierarchia, pretože v databázach zvyčajne neexistujú predaje pre všetky kombinácie rokov a krajín. Čiže bol by to možno schodný prístup, ale zbytočne komplikovaný. Nehovoriac o tom, ako by ste potom prepájali takúto tabuľku na zvyšok dátového modelu.

Druhá vec je však to, že v dátovom modeli máme centrálnu tabuľku, konkrétne Objednávky, na ktorú sú napojené oba číselníky. A v tejto tabuľke sú nepriamo uložené aj všetky kombinácie rokov a krajín, pre ktoré existujú predaje. A to nám výrazne pomôže v zjednodušení riešenia – stačí dotiahnuť príslušné stĺpce z oboch číselníkov do tejto tabuľky, a v nej vytvoriť onú hierarchiu. Takto vytvoríte hierarchiu v rámci jednej tabuľky, a jednoducho nasimulujete hierarchiu z viacerých tabuliek. Takto získate to najlepšie z oboch svetov – riešenie je ultra jednoduché, a zároveň budú vo výslednej hierarchii iba existujúce kombinácie hodnôt zo všetkých jej úrovní.

Spravíme teda tieto 2 vypočítané stĺpce v tabuľke Objednávky:

Rok = RELATED(‘Čas'[CalendarYear])

Krajina = RELATED(‘Regióny'[Krajina])

A opäť, môžete to spraviť aj v Power Query, ak vám to vyhovuje viac.

Potom už stačí vytvoriť z týchto stĺpcov hierarchiu s názvom “Rok-Krajina” v tabuľke Objednávky:

…a dosadiť spolu s merítkom Obrat do kontingenčky:

Takto teda spravíte hierarchiu z viacerých tabuliek. Respektíve, lepšie povedané, z ľubovoľnej kombinácie tabuliek.

Ak by ste chceli mať takúto hierarchiu z viacerých tabuliek v osobitnej tabuľke, tak stačí toto riešenie iba mierne upraviť. Spravte si osobitnú tabuľku, v ktorej budú identifikátory riadkov z centrálnej tabuľky. Tú prepojte s touto tabuľkou. A do nej cez RELATED dotiahnite príslušné stĺpce z číselníkov. Prípadne sa to dá spraviť aj tak, že spravíte najprv to pôvodné riešenie č. 2, a potom spravíte kópiu tej tabuľky iba s ID riadka a tými stĺpcami pre hierarchiu, a prepojíte tú tabuľku na tú pôvodnú. Cez DAX pravdepodobne v niektorých prípadoch narazíte na cyklické závislosti vo výpočtoch. Takže aj v tomto prípade to ide potom spraviť alternatívne aj cez Power Query.

Čo ale v prípade, ak to chcete spraviť v jednej z tých 2 pôvodných tabuliek? Čiže ak chcete spraviť hore uvedenú hierarchiu napr. v tabuľke Regióny? Predtým, než sa o to pokúsite, sa skúste najprv zamyslieť. V tabuľke Regióny máme jednotlivé regióny (1 riadok = 1 región), a ku regiónom je priradená nejaká krajina. Ku každej krajine by ste v tomto prípade potrebovali priradiť každý jeden rok z tabuľky Čas. Čo by vám zduplikovalo riadky v tejto tabuľke regiónov. A museli by ste následne zrušiť prepojenie medzi tabuľkou Regióny a Objednávky, a prepojiť to cez medzitabuľku. A o ďalších komplikáciách, ktoré si tým vyrobíte v dátovom modeli, ani nehovoriac. Preto sa o to ani nepokúšajte. Nejak by ste to zbastlili, ale následné komplikácie vám za to určite nestoja. Takže povedzte radšej užívateľom, že to nejde. A spravte im to takto len vtedy, ak by veľmi, ale veľmi naozaj chceli.

Takto teda spravíte hierarchiu z ľubovoľnej kombinácie tabuliek. Obe riešenia sú principiálne rovnaké, len sa mení obsadenie rolí v hlavnej a vo vedľajších úlohách. Výsledok je však plne funkčný, a má tú výhodu, že vám nespôsobí absolútne žiadne ďalšie komplikácie v dátovom modeli. Jedine tak nové požiadavky od užívateľov, keď uvidia, že aj toto išlo spraviť. To však už nie je problém 🙂