Ako sa stať analytikom, časť 4: PowerPivot – čo ste o Exceli ani len netušili

Viackrát som sa na svojich kurzoch stretol s účastníkmi, ktorý si v oblasti spracovania a analýzy údajov vybrali zlý kurz. Bolo to spravidla kvôli nedostatočnému prehľadu o možnostiach, ktoré majú k dispozícií. Táto séria článkov má urobiť práve takýto ucelený prehľad o analyzovaní údajov od základných konceptov ako je BI, cez nástroje použiteľné pri spracovaní importu až po SQL a iné pokročilé nástroje.

Štvrtá časť série sa zaoberá doplnkom Power Pivot, čo je nástroj na prepájanie a modelovanie údajov. Jednoducho povedané, pripravenie údajov pre kontingenčnú tabuľku do takej podoby, aby obyčajný užívateľ nemusel „maturovať“ v kontingenčnej tabuľke nad tým, kde v zbierke 15 tabuliek získa údaje pre výpočet tržieb v čase. Znamená to zadefinovanie výpočtov (tržby), a nastavenie parametrov ku výpočtom (napr. časové parametre ako sú kalendárny a fiškálny rok).

Spoločnosť Microsoft si niekedy koncom minulej dekády uvedomila, že veľa firiem analyzuje svoje dáta v Exceli. Deje sa tak napriek dostupnosti analytických nástrojov SQL serverov (v prípade servera MS SQL sú to nástroje OLAP kocky a tvorba Reporting services).
Dôvodov môže byť veľa – dva hlavné sú financie a spájanie údajov z rôznych systémov.
O probléme informačných systémov sme hovorili v druhej časti tohto seriálu. Problém financií je problém najmä malých a stredne veľkých spoločností, ktoré môžu mať ťažkosti s veľkou finančnou investíciou. Nejde pritom len o vybavenie SQL servera, ale aj o investície do ľudských zdrojov, ktoré sú v tejto oblasti spravidla jedna z najväčších položiek. Ak by sa spoločnosť rozhodla, že si problém nechá vyriešiť externe, tak sa problém len presunul na externého dodávateľa IS, ktorý pravdepodobne siahne po podobnom riešení. Práve pre túto oblasť vznikol doplnok PowerPivot. Priniesol nástroj na spájanie a modelovanie údajov (jazyk DAX) do užívateľského softvéru Excel. Teda takmer pre každého.

Prečo „takmer pre každého“? Doplnok Power Pivot nie je dostupný vo všetkých edíciách balíka MS Office. Prvá verzia vznikla pre Excel 2010, pre ktorú bola (a ešte stále aj je) zdarma stiahnuteľná z webovej stránky spoločnosti Microsoft. Vo verzií 2013 už síce tento doplnok netreba sťahovať, ale je k dispozícií iba v edíciách MS OFFICE Profesional Plus a v niektorých plánoch MS OFFICE 365. Predtým ako sa spustí vlna kritiky na adresu Microsoftu, skúsme sa zamyslieť.
Nástroj Power Pivot prináša naozaj nemalé úspery firemných financií v podobe ušetrených človeko-hodín. Okrem toho ide o technológiu, ktorá nemá v oblasti kancelárskych Office balíkov obdobu. Je to preto, lebo ju vytváral tím zaoberajúci sa vývojom SQL server technológií. V konečnom dôsledku jazyk DAX je rovnakým spôsobom používaný aj databázovým MS SQL serverom na modelovanie údajov. V neposlednom rade treba oddeliť dátový model a nástroj Power Pivot, ktorý je editor dátového modelu. Z toho vyplýva, že Power Pivot potrebuje v skutočnosti iba osoba, ktorá dátový model vytvára. Obyčajným užívateľom na prezeranie výsledkov postačuje aj najnižšia verzia pre študentov a domácnosti, v ktorej môžu používať už hotový dátový model. V tabuľke je prehľad verzií balíka Office obsahujúcich doplnok Power Pivot.

Základom nástroja Power Pivot je dátový model. Je to miesto, kde sa budú stretávať všetky naše údaje z viacerých rôznych zdrojov cez vytvorené prepojenia. Prepojenia môžu byť vytvorené priamo nástrojom Power Pivot, alebo v nástroji Power Query (viď predošlá časť seriálu). Power Query tu bude hrať rolu čistiaceho nástroja a filtra nežiadúcich údajov. Power Pivot použijeme ako editor dátového modelu na vytvorenie relačných vzťahov medzi jednotlivými zdrojmi, ale aj na dopočítanie všetkých potrebných informácií, ktoré sú potrebné na analýzu v kontingenčnej tabuľke.

Pre jazykových puristov dodávam, že správny názov nástroja je „dátový model“, nie „údajový model“. Takto sa totižto volá daná funkcionalita v slovenskej verzii Excelu. A keďže slovo vývojára má rovnakú váhu pre užívateľa ako slovo božie pre veriaceho, budem túto terminológiu rešpektovať aj ja.

Povedzme, že potrebujeme vytvoriť analýzu tržieb, ktorú bude požívať vedúci alebo majiteľ spoločnosti. Tržby bude potrebné analyzovať podľa časových údajov (fiškálne, kalendárne obdobie) podľa štruktúry spoločnosti (oddelenie, vedúci, zamestnanec …), produktov (kategórie, modelové rady …). Okrem toho bude potrebné sledovať plnenie plánovaných tržieb v čase alebo podľa oddelení. Väčšinu údajov máme v databázovom systéme, ktorý uchováva objednávkové, skladové a fakturačné údaje. Tieto údaje budeme priamo napájať z nástroja Power Pivot. Personálne údaje sú v samostatnom informačnom systéme, v ktorom nemáme prístup do databázy, máme však k dispozícií export v podobe textového súboru. Tretia časť úlohy, teda plánované tržby, nie je v žiadnom systéme a je evidovaná excelovskými tabuľkami. Ukážky údajov vstupujúcich do analýzy sú na obrázku.

Začneme tým, že si vytvoríme pripojenia na všetky zdroje (databázu, CSV súbor a Excel). Power Pivot síce disponuje možnosťou načítavať údaje z databáz ako aj s textových zdrojov, avšak pre potreby modelovania odporúčam použiť MS Power Query. Treba si uvedomiť, že Power Pivot vstupné údaje do dátového modelu ukladá. Aby nebol model príliš veľký, je vhodné pri výbere v Power Query odstrániť všetky nadbytočné riadky a stĺpce, prípadne pospájať údaje z niekoľkých tabuliek do spoločných logických blokov (napr. údaje k produktom, objednávkam a pod.). Oblasť, kde sa Power Query nevyhneme, je transformácia atypických tabuliek do databázovej podoby. V našom prípade ide o tabuľku kvót, ktorá je v súbore z excel-u. Výsledky všetkých Power Query dotazov pridáme do dátového modelu. Po vytvorení si môžeme údaje prezrieť v okne Power Pivotu v režime mriežka, v režime diagram ich vzťahy (viď obrázok dolu). Ako je vidno, musíme vytvoriť vzťahy medzi jednotlivými časťami. V prípade, že by sme importovali údaje priamo z databázy, mali by sme výhodu načítaných vzťahov, avšak za cenu veľkého množstva údajov vo veľkom množstve tabuliek.

Modelovanie začneme vytvorením všetkých potrebných relačných vzťahov. Vzťahy vytvoríme jednoduchým systémom „drag & drop“ medzi primárnym a cudzím kľúčom. Ďalší dôležitý krok je poskrývanie stĺpcov, ktoré sú síce dôležité pre dátový model, ale pre kontingenčnú tabuľku sú zbytočné (napr. primárne a cudzie kľúče). Okrem toho chceme sprehľadniť informácie, ktoré môžu spôsobiť chaos pri tvorení kontingenčnej tabuľky, ak sa nedajú do súvisu. Ako príklad môžu slúžiť informácie o delení produktov medzi rôzne kategórie, modelové rady a podobne. Popisy, ktoré na seba nadväzujú, umiestnime do hierarchickej štruktúry, čo spôsobí, že pri použití v kontingenčnej tabuľke sa budú umiestňovať súčasne a vytvoria v popise štruktúru. Aktuálny stav modelu je vidieť na obrázku.

Zatiaľ to bola klikačka, „ííííízy píííízy džapanííízy“. Ak chceme získať niektoré kľúčové informácie, budeme potrebovať výpočty. Chýba nám napríklad prepojenie k tabuľke kvóty, ktoré nevieme vytvoriť vzhľadom na to, že máme iba 2 stĺpce – meno a priezvisko. V tabuľke Zamestnanci si preto vytvoríme nový stĺpec Celé_meno so vzorcom FirstName & „ „ & „ „ LastName. Teraz už vieme vytvoriť vzťah do tabuľky kvót a tiež nové hierarchické delenie zamestnancov podľa oddelení. Na výpočet tržieb budeme okrem toho potrebovať vypočítať celkové ceny ku každej predanej položke. Vytvoríme si nový stĺpec Cena_celkom v tabuľke Predaje a nastavíme ho ako OrderQty*Uniteprice*(1-UnitePriceDiscount). Podobným spôsobom môžeme dopočítať dodatočné informácie ku časovým údajom (kvartály, mesiace a pod). Výsledok nášho snaženia do tohto bodu si môžeme pozrieť v kontingenčnej tabuľke, Cena_celkom umiestnime do hodnôt ako súčet a do popisu umiestnime Celé_meno, do stĺpca môžeme umiestniť Orderdate z tabuľky Objednávky .

Na vypočítanie celkových tržieb podľa parametrov by nám tento výsledok stačil. Ak ich však chceme porovnávať oproti plánovaným, budeme musieť použiť vypočítané mierky v jazyku DAX. Vypočítané mierky tvoria jadro Power Pivotu, čo je základný dôvod, prečo ho chceme využiť. Jednoduché mierky sa nebudú svojim správaním líšiť od výpočtu v kontingenčnej tabuľke. Napríklad na výpočet celkových tržieb a ich plnenie vytvoríme tri nasledovné mierky:

Na prvý pohľad sa naše mierky vôbec nelíšia od obyčajného výpočtu v kontingenčnej tabuľke. Výhod je na tomto mieste niekoľko.
• Pre výsledné údaje je možné prednastaviť formátovanie. V našom prípade sme napríklad nastavili pre celkové tržby euro a pre plnenie plánu percento
• Výsledky majú priamy BI popis, ktorý priamo hovorí, o aký údaj ide. V našom prípade Celkové tržby, plánované tržby a plnenie plánu
• Umožňujú výpočty, ktoré sa v kontingenčnej tabuľke robiť nedajú. V našom prípade to bol pomer celkových a plánovaných tržieb.
Práve posledná menovaná oblasť je asi najsilnejším dôvodom pre použitie PowerPivotu. Rôzne oblasti použitia nemusia byť len plnenia plánov, ale aj definovanie výpočtov s konkrétnym predvoleným filtrom – napr. celkové online tržby, počet online objednávok, a podobne. Výsledok nášho snaženia si môžeme pozrieť na obrázku.

Veľmi častým začiatočníckym omylom je zaradenie PowerPivotu ako nástroja na čistenie údajov. Čistenie a filtrovanie údajov robíme pri vstupe v Exceli cez nástroj PowerQuery, o ktorom som písal v predošlej časti, alebo pomocou jazyka SQL, o ktorom si niečo povieme v niektorej z nasledujúcich častí. PowerPivot je nástroj na tvorbu dátového modelu, ktorý nám poskytne v kontingenčnej tabuľke všetky potrebné vypočítané údaje s akýmkoľvek rozmerom.

Pevne verím, že sa vám táto malá ochutnávka nástroja PowerPivot zapáčila. PowerPivot vie naozaj výrazne uľahčiť prácu – zjednoduší a zníži počet krokov vedúcich k cieľu, čím citeľne šetrí čas. Ak trávite veľa času nad prípravou kontingečných tabuliek a takáto skratka vám pomôže, určite vás radi privítame na našom kurze Excel databázy 2: PowerPivot a naučíme ako na to.

V nasledujúcej časti seriálu sa dočítate o možnostiach vizualizácie v Exceli pomocou nástrojov Power View a Power Map.