V predchádzajúcom článku sme si ukázali, ako využiť vypočítané stĺpce na vytvorenie vlastných analytických kategórií, a elegantnejšie analyzovať dáta. Týmto sa však ich využitie nekončí. Pomocou nich môžeme napr. opravovať chyby v dátach. Čo z času na čas budete musieť robiť kvôli tomu, že v každej väčšej databáze sa nachádzajú chyby, napriek sebeväčšej snahe programátorov a administrátorov databáz. A ak ich neopravíte, nebudete mať správne výsledky, respektíve bude Vám to zbytočne komplikovať vzorce a kopec ďalších vecí v PowerPivote. Preto si to pozrieme teraz, ako na také chyby prísť, a ako ich opraviť hneď na začiatku.

V predchádzajúcich článkoch ste si možno všimli, že sme vytvárali nejaký počet analytických kategórií, ale kontingenčná tabuľka zobrazila o jednu kategóriu viac, a tá sa volala „(prázdne)“, tak ako na tomto obrázku v predchádzajúcom článku, kde sme skončili naposledy:

Toto sa bežne stáva vtedy, keď v hlavnej tabuľke (v našom prípade Objednávky) je uvedené neexistujúce číslo v prepájacom stĺpci na číselník (v našom prípade tabuľka Produkty). A opravuje sa to nasledovne:

1. zistíme najprv vypočítaným stĺpcom, na ktorých riadkoch sa nachádza chyba,
2. vo vzorci vypočítaného stĺpca nahradíme nesprávne hodnoty správnymi hodnotami,
3. zrušíme pôvodné prepojenie tabuliek, a prepojíme ich cez tento nový, opravený vypočítaný stĺpec.

Keby sme to však chceli spraviť priamo, tak nám to nepôjde, pretože PowerPivot nám pri prepájaní nového stĺpca ohlási cyklickú závislosť vo výpočtoch, a tá je, podobne ako v Exceli, zakázaná vo všetkých vzorcoch. Preto si pomôžeme tým, že náš číselník produktov (tabuľku Produkty) naimportujeme do nášho dátového modelu ešte raz. Následne prepojíme tabuľku Objednávky s týmto číselníkom, pomocou neho opravíme dáta v tabuľke Objednávky, a potom zmeníme pôvodné prepojenie medzi touto tabuľkou a pôvodným číselníkom produktov na prepojenie cez tento nový, opravený vypočítaný stĺpec.

Ideme teda na to. Stiahnite si tento vzorový excelovský súbor, alebo použite ten z predchádzajúceho článku. Prejdite do PowerPivotu, a naimportujte tabuľku produktov znova – kliknite v hornej lište PowerPivotu na tlačidlo Z databázy a potom na Z programu Access:

Otvorí sa Sprievodca importom tabuľky. V tomto okne vyberte v poli Názov databázy svoj accessovský súbor (alebo použite náš vzorový accessovský súbor z tohto odkazu):

Potom kliknite vpravo dole na tlačítko Ďalej a ešte raz Ďalej, a zobrazí sa zoznam tabuliek, ktoré sa nachádzajú vo vybratom súbore/databáze. Tu zaškrtnite tabuľku „Product“, do stĺpca Neformálny názov zadajte „ProduktyPomocne“, a kliknite na tlačítko Dokončiť:

Následne sa nám naimportuje do nášho modelu číselník produktov ako nová tabuľka ProduktyPomocne. Po zatvorení importovacieho okna prejdite do diagramového zobrazenia, a prepojte stĺpec ProductKey v tabuľke Objednávky na stĺpec ProductKey v tabuľke ProduktyPomocne:

Teraz prejdeme naspäť do tabuľkového zobrazenia, do tabuľky Objednávky, a ideme si vytvoriť nový vypočítaný stĺpec. Týmto stĺpcom budeme zisťovať, ktorá referencia na číselník produktov je v tejto tabuľke neplatná. Zneužijeme na to funkciu RELATED, ktorá slúži na doťahovanie stĺpcov z inej tabuľky do aktuálnej tabuľky. A využijeme fakt, že keď pre daný riadok neexistuje príslušný riadok v tabuľke, odkiaľ chceme hodnotu toho stĺpca dotiahnuť, tak táto funkcia vracia prázdnu hodnotu. Túto hodnotu následne nahradíme platnou hodnotou.

Vytvorte teda nový vypočítaný stĺpec, ktorý nazveme ProduktIDTest, s nasledovným vzorcom:

=RELATED(ProduktyPomocne[ProductKey])

Výsledok bude vyzerať takto:

V tomto stĺpci máme teraz iba platné čísla produktov z pomocného číselníka produktov, a prázdne hodnoty pre neplatné čísla produktov. Aby sme si veľmi rýchlo pozreli, ktoré záznamy obsahujú neplatný odkaz, využijeme automatický filter tohto vypočítaného stĺpca, ktorý funguje rovnako ako v Exceli. V ňom si dáme vyfiltrovať prázdne hodnoty (keďže vieme, že riadky s prázdnymi hodnotami v tomto stĺpci obsahujú neplatné odkazy):

…a zobrazí nám to ten riadok, v ktorom je chyba:

Keď sa presunieme do stĺpca ProductKey (prvý stĺpec), zistíme, že je v ňom hodnota 1000, pre ktorú neexistuje žiaden produkt s týmto identifikátorom v tabuľke produktov. A takto sme tú chybu práve lokalizovali.

Riešenie zvyčajne spočíva v tom, že nahradíte takéto nesprávne odkazy správnymi odkazmi. Zvyčajne použijete jeden z týchto troch spôsobov:

1. nahradíte nesprávny odkaz správnym odkazom,
2. doplníte do číselníka nový riadok pre neznámu položku, a nazvete ju ako neznámu položku (v našom prípade pridáte riadok s produktom, ktorý nazvete povedzme „neznámy produkt“). Tomuto riadku dáte ako identifikátor riadku hodnotu -1. Potom v hlavnej tabuľke (v našom prípade Objednávky) touto hodnotou identifikátora nahradíte nesprávnu hodnotu odkazu,
3. nahradíte nesprávny odkaz odkazom na niektorú existujúcu položku v číselníku (čo je síce diskutabilné, ale veľakrát priechodné riešenie).

My použijeme kvôli jednoduchosti tretie riešenie.

Najprv zrušte automatický filter v tabuľke Objednávky nad stĺpcom ProduktIDTest, aby sme videli znova všetky riadky. Potom vytvorte v tabuľke Objednávky ďalší vypočítaný stĺpec, ktorý nazvite ProduktIDOpravene, a zadajte doňho nasledovný vzorec:

=IF(ISBLANK([ProduktIDTest]); 100; [ProduktIDTest])

Výsledok bude vyzerať takto:

V našom vzorci sme použili 2 triky:

1. funkciou ISBLANK sme otestovali, či zadaný stĺpec (ProduktIDTest) obsahuje v aktuálnom riadku prázdnu hodnotu alebo nie
2. ak funkcia z bodu 1 vrátila hodnotu áno (v PowerPivote jej zodpovedá hodnota True), tak vrátime hodnotu 100 (pretože ide o neplatný odkaz na produkt, a ten chceme premapovať na produkt č. 100), a v opačnom prípade vrátime hodnotu nášho testovacieho stĺpca (pretože ide o platný odkaz na tabuľku produktov).

Ako posledný krok nám už zostáva len zmeniť prepojenie medzi tabuľkou Objednávky a tabuľkou Produkty pomocou tohto opraveného vypočítaného stĺpca. Prejdite teda do diagramového zobrazenia, a nájdite si prepojenie medzi tabuľkami Objednávky a  Produkty. Kliknite 2x na čiaru symbolizujúcu prepojenie medzi týmto tabuľkami, a otvorí sa Vám okno Upraviť vzťah:

Tam v políčku napravo, nazvanom ako Stĺpec, zmeňte ProductKey na ProduktIDOpravene, a stlačte tlačítko OK:

Takto zmeníme prepojenie medzi týmito dvoma tabuľkami, aby boli prepojené pomocou opraveného stĺpca so správnymi dátami. Teraz ostáva už len upratať po sebe – poskývať tabuľku ProduktyPomocne a stĺpce ProduktIDTest a ProduktIDOpravene – pretože tieto objekty sú len pomocné, a pre užívateľa kontingenčky nemajú žiadny význam. Keď to spravíme a presunieme sa naspäť do kontingenčky, Excel automaticky aplikuje naše zmeny, a obnoví dáta v kontingenčke. Výsledok bude vyzerať takto:

Všimnite si, že predtým kontingenčka zobrazovala 4 kategórie, a teraz zobrazuje len 3. S tým, že automaticky vytvorená kategória (prázdne) zmizla, pretože už neexistujú chybné dáta v prepojení tabuľky objednávok na tabuľku produktov. Zároveň sa nám tých 5 040 dolárov za danú objednávku pripočítalo do kategórie „slabo predávané“, pretože ten produkt (s číslom 100), na ktorý sme premapovali objednávku s chybným odkazom, nemal predtým žiadne predaje, a po novom má predaje 5 040 dolárov. A to ho zaraďuje do tejto kategórie.

Takto teda viete opravovať chyby v dátach, najmä neplatné odkazy na číselníky, ktoré sa Vám z času na čas určite vyskytnú vo Vašich dátach. Dajte si na tom vždy záležať, aby v dátach bolo vždy čo najmenej chýb, pretože čím viac chýb a nepresností neopravíte, tým väčšie problémy Vám to bude robiť pri následnej analýze a písaní vzorcov. A keďže život je príliš krátky na to, aby sme si ho zbytočne komplikovali, ošetrite si takéto prípady už na začiatku 😉 Budete potom vedieť využiť PowerPivot aj na veľmi pokročilé analýzy.

A aj to je jeden z dôvodov, prečo učíme rôzne techniky opravy dát v našom pokročilom kurze PowerPivotu – pretože zo skúsenosti vieme, že práve toto býva jeden z najväčších problémov, na ktorom zbytočne stroskotá riešenie Business Intelligence pomocou PowerPivotu. A potom sa musí zbytočne investovať oveľa viac peňazí do komplikovanejších systémov, ktoré ste namiesto toho mohli použiť na užitočnejšie veci. Preto je dobré mať pri sebe „profíka„, ktorý Vás hneď na začiatku naučí, ako takéto problémy riešiť 😉

Toľko teda k téme vypočítaných stĺpcov. V ďalšom článku sa pozrieme na to, čo sú to vypočítané merítka, ako ich používať, a na čo sú vlastne dobré. Pretože nimi práve začína tá najzaujímavejšia časť PowerPivotu 🙂