Každý lepší dátový model obsahuje údaje, ktoré majú hierarchickú štruktúru. Medzi bežné príklady patria údaje o čase (napr. rok – mesiac – deň), geografické údaje (napr. kontinent – krajina – kraj – okres – mesto) či rôzne kategórie zákazníkov, produktov a materiálu. A práve na pohodlnejšiu analýzu takýchto dát slúžia hierarchie.

Umožňujú užívateľom vybrať do kontingenčky viacero stĺpcov naraz jedným kliknutím, bez toho, aby tie stĺpce museli zdĺhavo hľadať a po jednom znova a znova pridávať do kontingenčky.

Druhým problémom, najmä pri analýze podľa časových údajov ako Rok, je to, že keď takýto stĺpec zakliknete, že ho chcete do analýzy, tak s ním kontingenčka spraví to čo s každým číselným stĺpcom – dá ho automaticky do oblasti hodnôt, a spočíta Vám z neho súčet všetkých rokov v modeli 🙂

A vy ho musíte potom vždy presúvať do oblasti riadkov alebo stĺpcov, čo je dosť otrava. Takýmto stĺpcom sa síce dá zmeniť dátový typ na Text, ale potom Vám to bude robiť zasa problém pri pokročilejších časových kalkuláciách pri vypočítavaných poliach.

Ďalší problém pri analýze takýchto údajov je ten, že keď pridávate do kontingenčky stĺpce po jednom, tak ich Excel za Vás automaticky “rozklikne”. Čo je úplne “super” v tom, že keď napríklad pridáte do kontingenčky stĺpce Rok, Mesiac a Deň, a máte údaje za 10 rokov, tak automaticky budete mať rozkliknutú kontingenčku na 20 strán:

Napriek tomu, že Vás zvyčajne nezaujímajú predaje za každý jeden deň v histórii, ale chcete ich vidieť najprv na úrovni rokov, potom pre vybraný rok na úrovni mesiacov, a možno potom na úrovni dní len pre vybraný mesiac. Takéto postupné rozklikávanie dát Vám umožnia práve hierarchie.

Hierarchie Vám pomôžu vyriešiť všetky tieto problémy naraz. Je to vďaka tomu, že:

1. Hierarchie sú usporiadanou sadou ľubovoľných stĺpcov v jednej tabuľke,
2. Dajú sa použiť iba na rezanie dát, takže aj Excel ich štandardne dáva do riadkov alebo stĺpcov,
3. Dáta rezané hierarchiami sa v kontingenčke zobrazujú zbalené vždy na najvyššiu úroveň hierarchie, tzn. štandardne sú rozkliknuté len podľa prvého stĺpca v hierarchii.

Ideme teda na to, a ideme si vytvoriť časovú hierarchiu. Otvorte si excelovský zošit z tohto odkazu, alebo môžete použiť hociktorý zošit z predchádzajúcich častí blogu.

Prepneme sa do okna PowerPivotu, a v dátovom zobrazení prejdeme na tabuľku Čas. V nej máme stĺpce CalendarYear, MonthName a DayNumberOfMonth, z ktorých vytvoríme hierarchiu. Hierarchie sa však vytvárajú v diagramovom zobrazení, preto sa najprv prepnite doňho (napr. kliknutím vpravo hore na Zobrazenie diagramu):

Tam nájdite tabuľku Čas, v ktorej ideme hierarchiu spraviť. A aby sa nám to robilo lepšie, nabehnite myšou na záhlavie tabuľky, a kliknite na ikonku v pravom hornom tabuľky, ktorá sa volá Maximalizovať:

Tabuľka sa zväčší, a my budeme mať viac priestoru pre našu tvorbu 🙂

Teraz si nájdeme v tabuľke všetky stĺpce, z ktorých chceme vytvoriť hierarchiu, a označíme ich tak, ako označujete viacero súborov vo Windowsoch – podržíte kláves Ctrl a postupne kliknete myšou na tieto stĺpce: CalendarYear, MonthName a DayNumberOfMonth (na poradí klikania nezáleží).

Potom kliknite pravým tlačidlom myši na jeden z označených riadkov, a vyberte z ponuky Vytvoriť hierachiu.

Poznámka: ak v menu nevidíte položku Vytvoriť hierarchiu, ide o dlhodobo známu chybu v PowerPivote. Obídete ju tak, že odznačíte všetky stĺpce, potom ich označíte znova, a keď kliknete pravým tlačidlom myši znova na niektorý z označených stĺpcov, tak tam už položka Vytvoriť hierarchiu bude.

Po kliknutí na položku Vytvoriť hierachiu PowerPivot teraz zanalyzuje štatistický výskyt hodnôt a ich závislosti od seba, a podľa toho vyrobí novú hierarchiu s názvom Hierarchia1:

Hierarchiu Hierarchia1 premenujeme na “Rok-Mesiac-Deň”, aby užívateľ vedel, čo je v nej:

Takisto, poradie stĺpcov v hierarchii určuje, v akom poradí sa budú rozklikávať v kontingenčke. Ak Vám aktuálne poradie nevyhovuje, môžete ho zmeniť – stačí myšou zobrať a presunúť stĺpec hore alebo dole na to správne miesto, kde má byť. A aby sme boli terminologicky korektní – stĺpcom v hierarchiách sa hovorí úrovne hierarchie.

Všimnite si ešte jednu vec – každý stĺpec má za sebou momentálne zopakovaný svoj názov v zátvorkách ešte raz. Je to kvôli tomu, že stĺpce, keď už sú v hierarchiách, sa v každej hierarchii môžu volať inak. Čiže napr. stĺpec CalendarYear sa môže v tejto hierarchii volať “Rok”, a v ďalšej sa môže volať napr. “Fiškálny rok”. A ten názov v zátvorkách je vlastne pôvodným názvom stĺpca, aby ste vedeli, z čoho je tá úroveň hierarchie vytvorená. Takže aj my si premenujeme tieto stĺpce, resp. úrovne hierarchie na Rok, Mesiac a Deň. Spravíme to jednoducho – klikneme pravým tlačidlom myši na daný riadok v hierarchii, vyberieme položku Premenovať, a zadáme nový názov stĺpca, resp. úrovne:

Takto postupne premenujeme všetky 3 úrovne hierarchie na Rok, Mesiac a Deň. Výsledok bude vyzerať takto:

A teraz už konečne môžeme hierarchiu odskúšať 🙂 Aby sme však dali po sebe veci do poriadku, zmenšíme okno tabuľky Čas na pôvodnú veľkosť – kliknutím na ikonku v pravom hornom rohu tabuľky, ktorá sa nazýva Obnoviť:

To nám tabuľku znova zmenší na pôvodnú veľkosť.

Teraz sa prepneme naspäť do Excelu do kontingenčky. Všimnite si najprv ponuku kontingenčky pre tabuľku Čas:

Ak máte vytvorenú v danej tabuľke aspoň 1 hierarchiu, tak kontingenčka začne v ponuke štandardne zobrazovať iba hierarchie, pričom pôvodné stĺpce tabuľky zoskupí a skryje pod položkou Ďalšie polia:

Takže nebojte sa, stĺpce z ponuky nikam nezmizli 🙂

Keď teraz pridáte hierarchiu Rok-Mesiac-Deň do kontingenčky – jednoduchým zakliknutím ako hociktorý iný stĺpec – tak to bude vyzerať nasledovne:

Všimnite si, že hierarchia bola po zakliknutí automaticky pridaná do oblasti riadkov kontingenčky. Takisto, dáta zostali rozkliknuté len na úrovni rokov, a je na užívateľovi, či si ich chce rozkliknúť pluskom vedľa daného roku na úroveň mesiacov, prípadne dní, alebo nie:

Tým pádom si sám užívateľ rozklikáva kontingenčku len do tej úrovne, do ktorej ho to zaujíma, a nemusí hľadať dáta 3 hodiny po celej kontingenčke, a potom ju ešte 10 minút sklikávať tak, aby videl presne len to čo chce.

A práve preto by ste mali zadefinovať najčastejšie používané sady stĺpcov v tabuľkách ako hierarchie.

Za to pohodlie to jednoducho stojí. A opäť ide o jeden z mnohých odporúčaných postupov, ktoré učíme už na našom prvom kurze PowerPivotu, a na ktoré prijdete len veľmi dlhou praxou 😉

Hierarchie majú ale jedno obmedzenie – môžete ich vyrobiť len zo stĺpcov, ktoré sa nachádzajú naraz v jednej tabuľke. Toto obmedzenie sa dá ale veľmi ľahko obísť dotiahnutím stĺpcov z iných tabuliek do želanej tabuľky cez funkciu RELATED.

Dobrá správa je, že v každej tabuľke môžete vytvoriť hocikoľko hierarchií a s ľubovoľným počtom úrovní, aký len budete potrebovať. Takisto, medzi stĺpcami nemusí byť prirodzene hierarchický vzťah – ak chce niekto napr. analyzovať dáta najprv podľa farby produktu a potom podľa triedy produktu, tak mu vytvorte hierarchiu Farba-Trieda z týchto dvoch stĺpcov. PowerPivot to bez problémov zvládne.

Takže takto vytvoríte hierarchie.

Naša analýza má však ešte drobný problém. Keď v kontingenčke rozkliknete ľubovoľný rok, a pozriete sa na poradie mesiacov, tak uvidíte, že sú usporiadané abecedne podľa ich názvu, a nie podľa ich skutočného poradia v roku. To si dáme do poriadku v nasledujúcom článku 😉