Po ukončení drobných úprav v PowerPivote zvyčajne nasleduje vytvorenie vypočítaných stĺpcov v modeli. PowerPivot umožňuje vytvoriť vypočítané stĺpce v každej tabuľke, ktorú naimportujete do dátového modelu. Používajú sa najmä na tieto veci:

1. ak chcete vytvoriť nové analytické kategórie z existujúcich dát,
2. ak potrebujete opraviť chyby v dátach pomocou vzorcov,
3. ak si potrebujete zjednodušiť komplexné výpočty,
4. ak si potrebujete dotiahnuť stĺpec z inej tabuľky do zvolenej tabuľky,
5. ak potrebujete obísť niektoré z obmedzení PowerPivotu.

Všetky tieto veci idú spraviť pomocou vypočítaných stĺpcov. A my sa budeme v tomto článku venovať prvému bodu.

Vypočítané stĺpce sa vytvárajú pomocou vzorcov, ktoré nápadne pripomínajú excelovské vzorce. Niektoré základné funkcie z Excelu dokonca fungujú aj tu. Rozdiel je ale hneď v niekoľkých veciach:

1. PowerPivot používa jazyk DAX (Data Analysis EXpressions), ktorý sa Excelu len veľmi podobá,
2. V PowerPivote neexistuje koncept bunky ako v Exceli. Vzorec píšete pre všetky “bunky” v danom stĺpci naraz. Preto v DAXe neexistuje možnosť odkazovať sa na konkrétne bunky súradnicami, čo však – ako uvidíte neskôr – vôbec nie je na škodu, práve naopak. Umožní Vám to vytvoriť tzv. portabilné vzorce, ktoré môžete skopírovať do iného PowerPivotu, a s drobnými úpravami budú fungovať ďalej.
3. Počet vypočítaných stĺpcov je obmedzený až na cca. 2 miliardy stĺpcov pre každú tabuľku. To by Vám mohlo nejaký čas stačiť 🙂

Vypočítané stĺpce sa teda vytvárajú v jazyku DAX. Je to špeciálny analytický jazyk vyvinutý pre PowerPivot, kde základnou požiadavkou bolo to, aby sa ho vedeli ľahko naučiť aj užívatelia Excelu. Po Exceli teda zdedil základné znaky, ako napr.:

1. vzorce začínajú symbolom “=”
2. syntax vzorcov je prebratá z Excelu – tzn. funkcie a ich parametre sa zapisujú rovnakým spôsobom ako v Exceli
3. operátory, podmienky a základné funkcie sú tiež rovnaké ako v Exceli

Rozdiel je však v myslení a spôsobe používania funkcií. Ideme si to vysvetliť na príklade. Otvorte si vzorový zošit Excelu z predchádzajúceho článku, a choďte do okna PowerPivotu. Tam prejdite na záložku tabuľky Čas.

V tejto tabuľke máme 1 riadok pre každý dátum od roku 2001 do roku 2004. Pomocou existujúcich stĺpcov už môžeme analyzovať dáta v kontingenčke, ako napr. predaje podľa rokov, mesiacov, dní, týždňov, a pod.. My by sme ale chceli analyzovať predaje podľa pracovných dní vs. víkendov. A na to si potrebujeme vytvoriť nový vypočítaný stĺpec, ktorý pre každý riadok s dátumom bude obsahovať buď “pracovný deň” alebo “víkend”, podľa toho, o aký typ dňa sa jedná. Prejdeme teda do posledného stĺpca v tabuľke Čas, ktorý sa vždy volá Pridať stĺpec:

Kliknite do hociktorej bunky v tomto stĺpci, a napíšte tam tento vzorec:

=IF([DayNumberOfWeek] = 7 || [DayNumberOfWeek] = 1; “víkend”; “pracovný deň”)

Na konci vzorca stlačte Enter, a výsledok by mal vyzerať takto:

Vytvoril sa nám nový vypočítaný stĺpec, a v ňom sa pravidelne strieda 5x hodnota “pracovný deň” a 2x hodnota “víkend”, tak ako by sme to čakali v normálnom týždni. PowerPivot tento stĺpec automaticky nazval CalculatedColumn1. Prejemenujeme si ho teda na názov Víkend, aby mal v kontingenčke (a prípadne aj do ďalších vzorcov) zmysluplný názov. Kliknite teda pravým tlačidlom myši na názov stĺpca, vyberte položku Premenovať stĺpec, a zadajte nový názov stĺpca ako Víkend:

A teraz si ideme rozobrať dopodrobna, ako to celé funguje.

Všimli ste si, že je jedno, do ktorej bunky zadávame vzorec pre vypočítaný stĺpec. Je to preto, že tento vzorec je spoločný pre všetky bunky v rámci daného stĺpca. Keď zadáte vzorec a stlačíte Enter, PowerPivot vyhodnotí tento vzorec pre každý riadok zvlášť, a výsledok zapíše do aktuálne počítanej “bunky”. Kvôli tomu musí náš vzorec vždy vracať práve 1 hodnotu pre aktuálne počítaný riadok (a DAXové vzorce vedia vracať aj tabuľky, stĺpce a kadečo iné, takže treba si dať na to neskôr pozor).

Syntax vzorca je podobná ako pre bežný vzorec v Exceli. Použili sme funkciu IF, ktorá sa zapisuje v tvare:

IF(podmienka; co_vratit_ak_je_splnena_podmienka; co_vratit_ak_nie_je_splnena)

kde:

1. podmienka je podmienka, ktorá sa vyhodnotí pre daný riadok,
2. co_vratit_ak_je_splnena_podmienka je hodnota, ktorú má funkcia IF vrátiť, ak je podmienka v bode 1 splnená
3. co_vratit_ak_nie_je_splnena je hodnota, ktorú má funkcia IF vrátiť, ak podmienka v bode 1 NIE JE splnená

Parametre funkcie sa oddeľujú bodkočiarkou (ak máte v počítači nastavené regionálne nastavenia na slovenské alebo české), alebo čiarkou (ak máte nastavené napr. americké regionálne nastavenia). Výhodou funkcií v DAXe je to, že aj keď premiestnite excelovský súbor s PowerPivotom z jedného jazykového prostredia do druhého, vzorce sa nikdy nepreložia do iného jazyka. Vždy sa používa len ich anglická verzia, a neexistuje žiaden ich slovenský ani český ekvivalent (ako napr. funkcia KDYŽ v Exceli). Takže peklo so vzorcami v iných jazykoch nehrozí 🙂

Ďalej si všimnite, akú podmienku sme použili:

=IF([DayNumberOfWeek] = 7 || [DayNumberOfWeek] = 1 …

Keď sa potrebujete vo vzorci odkázať na iný stĺpec v rovnakej tabuľke, musí byť názov stĺpca vždy uvedený v hranatých zátvorkách. V našom prípade sme použili stĺpec DayNumberOfWeek, ktorý obsahuje poradové číslo dňa v týždni. A keďže je vypočítaný podľa amerického kalendára, tak 7. deň v týždni je sobota, a 1. deň v týždni nedeľa. To využijeme aj v našej podmienke a napíšeme ju ako “ak je poradové číslo dňa rovné 7 alebo je rovné 1, tak…” a za podmienkou už nasledujú návratové hodnoty funkcie IF. A to buď “víkend”, ak je podmienka splnená, alebo “pracovný deň”, ak podmienka nie je splnená.

Takisto si všimnite, ako sme do podmienky zadali logický operátor alebo. V PowerPivote môžete použiť 2 operátory:

1. 2x symbol pipe (nachádza sa na americkej klávesnici, keď stlačíte Shift + spätné lomítko, alebo na slovenskej klávesnici ako Alt + 47). Toto je z dlhodobého hľadiska prehľadnejšia a ľahšie čitateľná verzia,
2. funkciu OR, ktorá sa používa rovnako ako v Exceli, ale pri komplikovaných vzorcoch výrazne znižuje čitateľnosť vzorcov.

Takže ak by ste chceli použiť funkciu OR namiesto ||, tak namiesto podmienky:

[DayNumberOfWeek] = 7 || [DayNumberOfWeek] = 1

…by ste zadali:

OR([DayNumberOfWeek] = 7; [DayNumberOfWeek] = 1)

Celý vzorec z príkladu vyššie by potom vyzeral takto:

IF(OR([DayNumberOfWeek] = 7; [DayNumberOfWeek] = 1); “víkend”; “pracovný deň”)

Oba spôsoby sú ekvivalentné, rovnako rýchle aj podporované. Z dlhodobého hľadiska ale používajte radšej operátor || namiesto funkcie OR, pretože pre väčšinu ľudí je to prehľadnejšia forma zápisu, a neskôr pri kombinovaní viacerých podmienok oceníte túto radu o to viac 🙂

Ak by ste ešte chceli zadať logickú spojku A namiesto ALEBO, tak môžete použiť:

1. operátor && (na americkej klávesnici je to Shift + 7, na slovenskej klávesnici Alt + 38),
2. funkciu AND

Obe alternatívy sa používajú podobne ako v príklade vyššie.

Vytvorili sme teda stĺpec Víkend, a ideme si ho vyskúšať do kontingenčky. Prepnite sa naspäť do okna Excelu. Do oblasti hodnôt dajte stĺpec “Cena objednávky” z tabuľky Objednávky, a do oblasti riadkov dajte náš nový stĺpec – stĺpec Víkend z tabuľky Čas. Výsledok by mal zobraziť sumu objednávok rozdelenú na víkendy, vs. pracovné dni:

A takto podobne si môžete vytvoriť ďalšie vlastné analytické kategórie dát podľa toho, ako to budete potrebovať. Dobrá správa je to, že každý z vypočítaných stĺpcov môžete použiť ako vstup do ďalších vypočítaných stĺpcov, takže aj navonok komplikované výpočty si môžete zjednodušiť cez medzikroky vo viacerých vypočítavaných stĺpcoch, alebo vytvoriť ďalšie analytické kategórie nad už vypočítanými stĺpcami/kategóriami. A na rozdiel od bežných vypočítaných stĺpcoch v Exceli sú tieto výpočty extrémne rýchle, a samotnú kontingenčku to nespomalí ani o chlp, aj keby ste mali desiatky miliónov riadkov v modeli. Je to dané tým, že PowerPivot je už optimalizovaný na prácu s veľkým množstvom dát, a takéto výpočty sú pre neho hračka. Čo však neznamená, že sa nedá napísať vzorec, ktorý potrápi PowerPivot 😉 O tom však niekedy nabudúce.

V ďalšom článku sa budeme venovať tejto téme ešte viac, pretože si zasluhuje oveľa väčšiu pozornosť, ako sa zmestí do 1 článku. O tom ale už viac v nasledujúcom článku 😉