Dnes sa pozrieme na jednu peknú techniku, ktorou si viete uľahčiť a výpočtovo výrazne urýchliť jeden konkrétny typ výpočtov. Ide o problémy ako sledovanie rozpracovanosti objednávok či vyťaženia hotelových izieb voči jednotlivým dňom v kalendári. Táto technika sa nazýva kombinačné tabuľky, a častokrát sa využíva práve na zjednodušenie a zároveň masívne zrýchlenie výpočtov v jazyku DAX.

Povedzme, že máme takúto tabuľku s objednávkami:

Na každom riadku je objednávka nejakej hotelovej izby. Každá má zadaný dátum od (čo môžete vo všeobecnosti brať ako dátum zadania/začiatku objednávky) a dátum do (čo môžete brať vo všeobecnosti ako dátum ukončenia/vybavenia objednávky). V tomto prípade tie dátumy hovoria o tom, odkedy dokedy bola daná izba obsadená.

A teraz by sme chceli zobraziť maticu, resp. kontingenčku, kde do stĺpcov dáme interval dátumov deň po dni. V nej potom chceme zobraziť nasledovné:

1. Ktoré izby boli v daný deň obsadené? Pri tých, ktoré boli, by sme chceli toto indikovať X-kom.
2. Koľko izieb bolo v daný deň obsadených? Resp. vo všeobecnosti, koľko bolo v daný deň rozpracovaných objednávok?

Najprv teda prvý problém. Chceli by sme zobraziť maticu, kde na riadkoch bude zobrazený zoznam izieb, a v stĺpcoch jednotlivé dni. A na priesečníku danej izby a daného dňa chceme zobraziť X, ak bola táto izba v daný deň obsadená.

Keby ste to chceli riešiť štandardnými technikami, tak by ste potrebovali tabuľku so všetkým dátumami z histórie, tak isto ako napr. časovú tabuľku pri funkciách Time Intelligence. Nesmela by byť však prepojená na tabuľku objednávok, a keď už, tak len za cenu relatívne komplikovaného riešenia. Potom by ste spravili merítko, ktoré by ste dali do matice na priesečníky riadkov a stĺpcov, a kde by ste otestovali, či pre danú izbu a daný dátum existuje nejaká objednávka, kde aktuálne testovaný dátum spadá do intervalu dátumov od-do zadaných v objednávke. Nie že by to bolo ťažké spraviť, ale bolo by to zbytočne prácne, a pri desiatkach až státisícoch objednávok veľmi pomalé.

Existuje však oveľa ľahšie riešenie cez tzv. kombinačné tabuľky. Sú to dopredu predpočítané tabuľky, ktoré obsahujú všetky kombinácie sledovaných entít. V našom prípade to budú kombinácie všetkých sledovaných dátumov so všetkými sledovanými izbami. A pre každú kombináciu si predpočítame, či v daný deň bola daná izba obsadená. Zdá sa vám to veľa dát? Nezabúdajte, že PowerPivot je na to stavaný. A keby sme sledovali povedzme 3 roky deň po dni, a zároveň 1000 izieb, tak by výsledná tabuľka mala iba niečo cez milión riadkov. Excelistom sa už oči prevracajú, že to zas bude čakania, ale pre PowerPivot je to hračka. Štatistiku z takejto tabuľky zošrotí za stotinu sekundy. A aj svokra bude prekvapená, ako ste to zvládla.

Ak teda chcete používať kombinačné tabuľky, tak je potrebné vytvoriť si novú vypočítanú tabuľku v dátovom modeli (mimo Power BI a SSAS Tabularu ju musíte vytvoriť inde, napr. v Power Query). Začneme dátumovým stĺpcom, ktorý bude obsahovať všetky použité dátumy z objednávok:

Matica = CALENDAR(MIN(‘Objednávky'[Dátum od]); MAX(‘Objednávky'[Dátum do]))

Funkciou MIN si zistíme prvý dátum z objednávok, funkciou MAX posledný dátum z objednávok. Následne pomocou funkcie CALENDAR dáme vygenerovať tabuľku s jedným dátumovým stĺpcom s názvom Date, ktorý bude obsahovať súvislo všetky dátumy medzi týmito dvoma dátumami. V našom prípade bude tabuľka vyzerať takto:

Následne potrebujeme spraviť kartézsky súčin týchto dátumov so všetkými izbami. Resp. kombináciu všetkých týchto dátumov so všetkými izbami. Mimochodom, tento krok nutne nemusíte robiť, ak chcete riešiť iba 2. problém uvedený vyššie. Na tento účel máme v PowerPivote funkciu CROSSJOIN s touto syntaxou:

CROSSJOIN(tabuľka1; tabuľka2; ….; tabuľkaN)

Táto funkcia spraví kartézsky súčin riadkov medzi zadanými tabuľkami. Čiže kombináciu všetkých riadkov z prvej tabuľky so všetkými riadkami z druhej tabuľky, atď.. Výsledkom je samozrejme tabuľka.

V našom prípade zmeníme vzorec pre našu kombinačnú tabuľku tak, aby sme funkciou CROSSJOIN skombinovali všetky dátumy so všetkými izbami:

Matica = CROSSJOIN(CALENDAR(MIN(‘Objednávky'[Dátum od]); MAX(‘Objednávky'[Dátum do])); VALUES(‘Objednávky'[Izba]))

…alebo vypeknené cez DAX Formatter takto:

Do prvého parametra CROSSJOIN-u sme zadali tabuľku s dátumami vyrobenú v predchádzajúcom kroku s funkciou CALENDAR. A do druhého parametra CROSSJOIN-u sme dali funkciu VALUES, ktorá zo zadaného stĺpca vráti stĺpec s unikátnymi hodnotami (ktorý si DAX automaticky prekonvertuje na 1-stĺpcovú tabuľku, lebo na mieste jej použitia sa očakáva tabuľka). V tomto prípade je to stĺpec so zoznamom izieb. Výsledkom je takáto tabuľka (zobrazená iba časť):

A takto bežne vyzerajú čisté kombinačné tabuľky. Do takejto tabuľky už stačí iba pridať vypočítaný stĺpec, a v ňom si vypočítať, či bola daná izba v danom dátume obsadená. Ak áno, tak vrátime hodnotu “X”, inak nevrátime nič:

Obsadená = IF(COUNTROWS(FILTER(‘Objednávky’; [Date] >= [Dátum od] && [Date] <= [Dátum do] && ‘Objednávky'[Izba] = Matica[Izba])) > 0; “X”)

…alebo vypeknené cez DAX Formatter:

Výsledok bude vyzerať takto:

Vo vzorci sme využili fintu z techniky prepájania stĺpcov cez podmienku na dohľadávanie príslušných riadkov v druhej tabuľke. Funkciou FILTER si vyfiltrujeme najprv všetky riadky z tabuľky Objednávky, ktoré spadajú do daného dátumu a sú na danú izbu. Následne funkciou COUNTROWS spočítame počet týchto riadkov. Ak je nenulový, tak to znamená, že pre daný dátum a izbu bola nejaká objednávka, a teda vrátime hodnotu “X”. V opačnom prípade vrátime prázdnu hodnotu. To vo vzorci nevidíte, pretože vo funkcii IF sú zadané iba prvé 2 parametre – podmienka a hodnota v prípade splnenia podmienky. A ak nie je zadaný 3. parameter funkcie IF, a nie je splnená podmienka v prvom parametri, tak to štandardne vracia prázdnu hodnotu.

Kombinačné tabuľky sa zvyčajne potom používajú priamo v reportoch v základných prehľadoch, zvyčajne bez potreby písať ďalšie vzorce či robiť špeciálne merítka. Teraz už teda stačí iba vytvoriť kontingenčku/maticu v reporte, z našej kombinačnej tabuľky Matica, takto:

1. do oblasti riadkov dáme stĺpec Izba z tabuľky Matica,
2. do oblasti stĺpcov dáme stĺpec Date z tabuľky Matica,
3. do oblasti hodnôt dáme stĺpec Obsadená z tabuľky Matica (a prípadne mu nastavíme agregačnú funkciu MIN, resp. “Prvé/First”, ak by bolo treba).

Potom nezabudnite zapnúť v kontingenčke/matici, aby zobrazovalo aj položky bez hodnôt, lebo napr. v niektorých dátumoch nemusela byť rozpracovaná žiadna objednávka, resp. obsadená žiadna izba. A vypnite si zobrazovanie medzisúčtov, pretože v tomto prípade nemajú zmysel. Výsledná kontingenčka bude vyzerať takto:

Znak X neskôr môžete nahradiť ikonkami, heatmapami, a podobnými vizuálnymi hrátkami. Podľa toho, čo vám bude v danej situácii viac vyhovovať, a ako chcete takéto kombinačné tabuľky vizualizovať. Na tomto riešení je pekné aj to, že keď si do reportu dáte dátumový rozsahový slicer, tak ním môžete regulovať, aký rozsah dátumov sa zobrazí v kontingenčke:

A čo s druhým problémom? Tzn. ako zobraziť, koľko objednávok máme rozpracovaných v daný deň?

V tomto prípade si do našej kombinačnej tabuľky pridáme ďalší vypočítaný stĺpec, v ktorom pre daný dátum vypočítame počet rozpracovaných objednávok, zjednodušením vzorca vyššie:

Počet objednávok = COUNTROWS(FILTER(‘Objednávky’; [Date] >= [Dátum od] && [Date] <= [Dátum do] && ‘Objednávky'[Izba] = Matica[Izba]))

Tento  vzorec pre každý dátum jednoducho spočíta počet riadkov s objednávkami, ktoré spadajú do daného dátumu, pre danú izbu. Z toho predchádzajúceho vzorca sme len vyhodili funkciu IF. Výsledok bude vyzerať takto:

Teraz už stačí iba vytvoriť kontingenčku/maticu v reporte, opäť z našej kombinačnej tabuľky Matica, takto:

1. do oblasti riadkov dáme stĺpec Date z tabuľky Matica,
2. do oblasti hodnôt dáme stĺpec Počet objednávok z tabuľky Matica (a prípadne mu nastavíme agregačnú funkciu SUM, ak by bolo treba).

Výsledná kontingenčka je tiež ovládateľná dátumovým slicerom ako v predchádzajúcom prípade, a bude vyzerať napr. takto:

Takže takto jednoducho viete pracovať so štatistikou rozpracovaných objednávok, obsadenosťou izieb a podobne. A presne na toto sa používajú kombinačné tabuľky, aby ste si pomocou toho výrazne uľahčili reporting, a nemuseli toľko čarovať so vzorcami. Jedinou vnímanou akože nevýhodou je relatívne veľký počet riadkov, ktorý sa nageneruje do kombinačnej tabuľky. Ale keď viete, ako funguje PowerPivot, tak viete, že to veľmi nevadí – milióny riadkov sú preňho hračka, a opakujúce sa hodnoty v tabuľkách poľahky skomprimuje na nepatrný zlomok ich pôvodnej veľkosti. Takže aj napriek relatívne veľkému počtu riadkov to až tak dátový model nezväčší, ako keby ste to robili v klasickej databáze.

A to je na tom pekné. Že viete takúto na prvý pohľad jednoduchú technológiu použiť naoko neefektívnym spôsobom, ktorý vám ale výrazne ušetrí prácu a čas, a aj napriek tomu to pôjde bleskurýchlo. A toto je jedna z techník, ktoré učím v pokročilom aj Jedi Master kurze PowerPivotu aj Power BI. Pretože aj naoko ťažké problémy idú vyriešiť veľmi ľahko, ak máte na to nástroje, a viete ako na to 🙂