Moderný reporting – prvý report (2. časť) + Inštalácia Jaspersoft Studio

 

Dostávame sa do situácie, keď máme všetko potrebné pre prípravu reportu. Nakoľko sa táto fáza zdá byť kozmetická záležitosť, opak je pravdou a čo je ešte viac smutné, 99 % firiem neinvestuje do vývoja obsahovej zložky ani potrebné minimum.

Ako to funguje:

80 % analytikov v novej práci zdedí už hotové reporty. Ich úlohou je pochopenie a pravidelná aktualizácia, prípadne tvorba dodatočných čísiel v závislosti od požiadaviek managementu. Čo musí analytik / controller zvládnuť je pochopiť biznis tak, aby naň mal relevantný pohľad a vedel, “na ktoré čísla dávať zvýšenú pozornosť”. Z technického hľadiska je výhodné automatizovať samotnú prácu ako napríklad čistenie dát, tvorba summary apod..

V prípade, že zvládnete už spomenuté požiadavky, nastáva čas pre “show time”. Vzniká  priestor, v ktorom sa začnete zamýšľať a inovovať existujúce reporty, resp. metriky. Inými slovami konzultovať a prezentovať nápady vedeniu spoločnosti.

V prípade, že sa táto snaha vrámci spoločnosti ocení – máte ideálne podmienky na “varenie si  polievočky seniornej resp. managerskej pozície”.

Rozhodol som sa preto zdieľať niekoľko rád, odpovedí na otázku: Ako vytvoriť kvalitný report?

1) Definujte si, čo je cieľom reportu – aké info má poskytovať? V našom prípade to bude odpoveď na otázku: Koľko jednotlivý predajca predal áut v danom regióne, pričom platí, že predajcovia kolujú medzi predajňami.

2) Stanovte si osnovu reportu. Obrázok ilustruje nasledujúcu logiku:

Zvolím si zamestnanca, obdobie, metriku (vysvetlím neskôr) a ostatné parametre. Prvá vizualizácia je celkový predaj v období, kde okrem absolútnych hodnôt zobrazujem porovnanie s minulým obdobím (ako si polepšil / pohoršil oproti minulému mesiacu alebo kvartálu), s benchmarkom (istá očakávaná hodnota, pod ktorú by nemal spadnúť).

Tento celkový počet chcem rozdeliť podľa modelov – vzhľadom k množstvu verzií automobilov Škoda a skutočnosti, že sa nejedná o časovú súslednosť medzi hodnotami (mám jedno číslo, rozdelené do skupín – modelov aut, ktoré sa predali vo vybranom čase) považujem za prehľadnejší spôsob tabuľku v porovnaní s grafom.

Nakoniec ma zaujíma, kde sa predajcovi darilo najviac a kde najmenej (aby som v budúcnosti vedel optimalizovať alokáciu dealerov). Túto problematiku nám zobrazí HeatMap s hodnotami.

Čo môžem zistiť? – Skutočnosť či zamestnanec nadhodnocuje alebo podhodnocuje plán, ak áno je to sezónna záležitosť (tým pádom by sa nemohol odlišovať od benchmarku) alebo individuálna (rozdiel oproti predchádzajúcemu obdobiu a súčasne benchmarku)? Taktiež, chcem vidieť ktoré autá dobre predával a ktoré nie (zistím, či daný dealer ľahšie predáva rodinám (Yeti, Combi verzie Škodovky), alebo je B2B orientovaný (obnovuje vozový park managerskými Octaviami alebo Superbami). Nakoniec chcem vidieť región, v ktorom mal najlepší a najhorší predaj. Tým pádom ak vezmem v úvahu čas v ktorom regióne ako dlho predával, dokážem s ním efektívne konzultovať výkonnosť a doladiť podmienky tak, aby predával čo najlepšie!

3) Stanovím si metriky – Inak povedané, je rozdiel konštruovať benchmark priemerom, váženým priemerom, mediánom apod. Používajte aspoň vážený priemer, ja osobne preferujem medián, čo je 50% kvantil – v praxi to znamená, že 50% dealerov predalo nad mediánovou hodnotou. Otatní sa budú musieť snažiť viac . Detail o  danej problematike nájdete kliknutím tu.

4) Nepoužívam divoké grafy. V našom prípade použijeme:

Prvý bude kombináciou Line a Area Chartov. Druhý je kombináciou mapy   (Bing / Google) a pre mnohých veľmi obľúbeného, avšak irelevantne používaného Pie chartu. O problematike používania grafov budem písať v samostatnom článku.

Teraz si ukážeme ako si nainštalovať Jaspersoft Studio. Pripomeniem, že pomocou Studia budeme navrhovať daný report.

Kliknite na odkaz: https://community.jaspersoft.com/download Kliknite na

A vyberte si nasledujúci inštalátor podľa toho,koľko bitový máte OS:

 

alebo stiahnite si Studio pre Win 64bit kliknutím tu. Potom kliknite na uložený súbor:

A spusťte inštaláciu (I agree –> niekoľkokrát Next –> Finish a je to ):

Po inštalácii sa Vám progam spusí sám. Ak nie kliknite na ikonku:

Pri otvorení nadefinujte miesto, kde sa majú ukladať reporty, adaptéry a rôzne iné nastavenia vrámci Japsersoft:

Potom by sa Vám malo zobraziť Welcome okno:

Ak ho zavriete alebo prejdete na: Window –> New Window dostanete sa na Jaspersoft Workbench:

Gratulujem, nachádzate sa v prostredí, z ktorého budeme tvoriť webové reporty.

Podrobný popis Studia a prvé začiatky si ukážeme v nasledujúcom článku. V prípade dotazov alebo iných informácii / služieb neváhajte kliknúť na Facebook, alebo si prejsť môj web.

Moderný reporting–prvý report (1. časť)

 

Dostávame sa k prvej biznisovej otázke:

Koľko jednotlivý predajca predal áut v danom regióne, pričom platí, že predajcovia kolujú medzi predajňami.

Na nasledujúcom obrázku máme vyznačné tabuľky, vrámci ktorých budeme pripravovať query:

Ak použijeme jednoduchú query na počet záznamov (predajov) dostaneme nasledujúci výsledok:

select count(*) as 'Počet' from sales; výsledok: 17472

Čo je na Škoda auto celkom málo, avšak relevantné data ešte len prídu. Ak si rozšírime query podľa rokov a mesiacov, dostaneme nasledujúci výsledok:

select Mesiac, Rok, count(*) as 'Počet' from sales group by Mesiac, Rok;

Teraz nasleduje pripojenie Predajcov, takže použijeme Join klauzulu. Všimnite si logiku pochodu, kde celkový počet rozdeľujem do detailov, pričom Grand Total mi slúži ako kontrola selektu (Predchádzajúcu tabuľku som pripravil v Exceli samozrejme):

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID group by sales.Mesiac, sales.Rok, Dealer.Predajca;

Výsledok, vzhľadom k tomu, že sa už v tomto prípade jedná o celkom veľkú Pivotku je obmedzený na rok 2014 vo filtri, kde si môžeme prepínaním overiť predajnosť dealerov vzhľadom k totálu (728 vozidiel pre všetky mesiace, 672 ročný predaj každého dílera):

Vezmime si teraz napríklad Romana, ktorého celkový predaj za Január v roku 2014 má byť 56 vozidiel. Rozšírime tento skript o hodnoty z tabuľky City:

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', City.Mesto as 'Mesto', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID  left join City on sales.Mesto = City.ID group by sales.Mesiac, sales.Rok, Dealer.Predajca, City.Mesto;

Celkovo si všimnite, že sa nám mesačná predajnosť zhoduje (56 áut) spolu s celkovou predajnosťou dílera za rok 2014 (672 áut).

Ako posledný skript budeme uvažovať doplnenie o typ auta, t.z. pridanie tabuľky Car:

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', City.Mesto as 'Mesto', Car.Auto as 'Auto', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID  left join City on sales.Mesto = City.ID left join Car on sales.Auto = car.ID  group by sales.Mesiac, sales.Rok, Dealer.Predajca, City.Mesto, Car.Auto;

Platí, že pre každý mesiac predá presne po jednom kuse špecifického modelu.

Ako už iste tušíte, tieto data nepatria medzi najvhodnejšie vrámci budovania reportov. No vzhľadom k tomu, že chcem aby sa Vám SQL-ko dostalo čo najskôr pod kožu, zvolil som túto predbežnú cvičnú databázku (17472 = 728 x 12 x 2 – Tabuľka 1 alebo 56 x 12 x 13 x [2 – Filter] – Tabuľka2 alebo 8 x 7 x 12 x [13 x 2 – Filter] alebo 7 x 1 x 12 x [13 x 2 x 8 – Filter] – Tabuľka3).

Nabudúce si povieme niečo k obsahu reportu, resp. ako má report vyzerať a budeme kresliť

Moderný reporting pracujeme s MySQL (spájanie pomocou JOIN)

 

Väčšina z PC friendly čitateľov určite vrámci databáz počula pojem SELECT-FROM-WHERE-JOIN – GROUP BY spojenie. Vzhľadom k predchádzajúcim článkom si v podstate všetko okrem klauzuly JOIN a GROUP BY dokážete dať do súvislostí.

Tak teda čo znamená pojem JOIN v SQL jazyku:

Podľa Wiki je to klauzula, založená na vymedzení referenčných polí, ktorá kombinuje záznamy medzi tabuľkami. Ľudskou rečou povedané, ak mám napríklad dve tabuľky tak pomocou JOIN spolu s vymedzaním relevantných stĺpcov (Foerign key stĺpec v primárnej tabuľke =  Primary key stĺpec v referenčnej)  program vie, ako má dané záznamy spojiť.

Ak si spomeniete na predchádzajúci článok, kde som spájal dve tabuľky takto:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tak v novom príkaze vypustíme klauzulu WHERE a pridáme JOIN (typ spojenia). Tu sa dostávame pre mnohých do komplikovaného rozhodnutia typu: Aký typ JOIN klauzuly použiť?

V tomto bode si dovolím inšpirovať sa knihou Mistrovství v MySQL od M. Koflera, kde na str. 237 má prehľad typov JOIN klauzúl:

Nepodmienené typy vrátia všetky možné kombinácie vrámci záznamov, odlišný je však STRAIGHT_JOIN, ktorý neoptimalizuje poradie výberu dat. Zameriame sa preto na použitie podmienených typov:

V podmienených typoch záleží na poradí tabuliek. Pre pochopenie som vytvoril nasledujúce tabuľky:

Tabuľka Dealer:

Tabuľka Dovolenka:

Všimnite si, že Tabuľky Dovolenka sa odkazuje na zoznam pracovníkov z tabuľky Dealer a zároveň fakt, že Helena zatiaľ nečerpala žiadnu dovolenku a v Dovolenke máme dva neplatné záznamy s ID zamestnanca 5 a 7.

Ak vezmeme v úvahu LEFT JOIN (a zároveň NATURAL JOIN) vrámci tabuliek dealer a dovolenka, nasledujúci skript:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti všetky záznamy z tabuľky Dovolenka, ku ktorým doplní relevantné mená z tabuľky Dealer. V prípade, ak by sme v evidencii Dovolenka mali ID > 4, znamenalo by to, že tam máme zle vloženú evidenciu alebo ID zamestnanca, ktorý ešte nie je evidovaný v systéme (tabuľke Dealer).

To znamená, ak by kontent tabuľky Dovolenka vyzeral takto:

Predchádzajúci skript by vyhodil nasledujúci prehľad:

To znamená, že vrátil všetky dovolenkové záznamy, vrátane tých, ku ktorým v tabuľke Dealer nenašiel Meno (súčet hodín s prívlastkom NULL).

Čo by sa stalo, ak by sme zamenili LEFT za RIGHT JOIN:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka right join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Tento skript by zamenil tabuľky. To znamená, ku každému Menu v tabuľke Dealer by priradil súčty hodín vrámci dovolenky:

Všimnite si, že nám vrátil naviac prehľad o Helene, ktorá ešte dovolenku nečerpala a zároveň sa zbavil neidentifikovaných 13 hodín voľna. Čo sa stane ak použijem pôvodný select typ z predcházajúceho článku alebo INNER JOIN? INNER JOIN v podstate definuje niečo ako skalárny súčin údajov, medzi dvoma tabuľkami.

Znamená to, že ak niektorá z tabuliek obsahuje vrámci referencií hodnoty NULL (Helena v tabuľke Dealer a záznam 4 a 5 z tabuľky Dovolenka), tak tieto hodnoty budú ignorované pri výstupe:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka inner join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

alebo:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka, dealer where dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti nasledujúci prehľad:

Ak by sme tam chceli zanechať tých 13 hodín, museli by sme im priradiť správne ID alebo definotať ich ako nových zamestnancov. Helena sa tam objaví v prípade, že niekedy načerpá dovolenku.

Všimnite si časť GROUP BY: Tá je potrebná, aby aplikácia vedela, podľa ktorých parametrov má urobiť prehľad. Inak povedané, predchádzajúce kódy vypočítali súčet hodín z dovoleniek a rozdelili ich podľa hodnôt zo stĺpca predajca v tabuľke dealer.

Použijeme prvý skript ale bez group by:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID;

Sami vidíme, že výsledok čo sa týka počtu hodín je rovnaký. Akurát problém je v tom, že tu aplikácia zobrala prvé ID = 1, ktoré má hodnotu Peter a k nemu priradila celkový súčet hodín. Príkaz GROUP BY vytvorí prehľad všetkých Mien a k nim nasledne dohodí súčty, ktoré celkovo dajú hodnotu 82 hodin.

Poučenie: snažte sa čo najlepšie poznať svoju databázu a taktiež si vždy presne vymedzte, čo je cieľom selektu.

Tým pádom môžem konštatovať, že sme pripravení si v ďalších článkoch vytvárať selekty podľa daných požiadaviek. Taktiež sa začneme hrať (kresliť si) so schémami dashboardov.

V prípade dotazov sa neváhajte pýtať – alebo kliknite na FB odkaz dole.