Petra Havlínová: Automatické zpracování podkladů pro vyhodnocení vybraných KPI

Název mé práce zní dost obecně, protože v době zadání ještě nebylo jisté, jaká data budu zpracovávat. Jasné bylo pouze to, že využiji firemní data, kterým rozumím a vytvořím dashboardy, které se budou automaticky aktualizovat.

Cíle projektu

Cíle krátce

• Nastavit automatické načtení a zpracování dat do reportů/dashboardů
• Vytvořit propojení mezi externími daty a daty z informačního systému
• Vytvořit a sdílet smysluplný dashboard v Power BI
• Napsat „kuchařku“ jak na to, abychom byli schopni podobné řešení realizovat u našich zákazníků

Cíle dlouze

Cílem mého projektu není zpracovat velké množství dat, ale „prošlapat“ cestu k automatickému zpracování dashboardů z dat, která jsou převážně uložena v informačním systému QI. To je systém, který naše firma implementuje k zákazníkům a používáme ho i k řízení naší firmy. (Kovářova kobyla je tentokrát obutá).

Dá se říct, že zájem zákazníků o dashboardy je přímo úměrný tomu, kolik informací do systému zapisují. Předpokládám, že grafická interpretace dat zvýší oblibu systému a tahle „fičura“ se bude dobře prodávat. Celý postup zpracuji do návodu, na jehož základě bude možné vytvářet nové dashboardy a reporty a poskytovat je zákazníkům.

Dashboardy by měly být pochopitelné, smysluplné, ale především by se měly vytvářet automaticky. To znamená, že zákazník nemusí rozumět žádnému programovacímu jazyku, manažer nemusí myslet na to, že má vytvořit nějaké grafy, ani tím pověřovat asistentku a doufat v její pevné zdraví. Dashboardy budou interaktivní a pokud zákazník provede „neuvážené“ změny, aktualizací si nahraje původní verzi zpět. Tím zajistíme poskytování dashboardů bez větších nároků na údržbu, abychom se z toho v budoucnu nezbláznili.

Dalším předpokladem je, že zákazník bude využívat nástroje, které už si v souvislosti s informačním systémem pořídil (třeba SQL Server) nebo které jsou finančně dostupné, ideálně zdarma (Power BI). Později, při větším počtu zpracovaných dat a při potřebě nastavit práva uživatelů, přejdeme na placené nástroje.

Minislovník pro datové začátečníky 
aneb ať víte, o čem píšu

KPI (Key Performance Indicators) - klíčové ukazatele, kterými se měří úspěšnost aktivity firmy. KPI se dají různě škatulkovat, ve svém projektu se zaměřím na ukazatele efektivity, resp. produktivity.

SQL Server Analysis Services (SSAS) -  je analytický datový nástroj používaný v oblasti podpory rozhodování a business analýzy. Poskytuje data pro další aplikace, ve kterých se vytváří reporty. Například Power BI, Excel, Reporting Services a další nástroje pro vizualizaci dat. V tomto projektu budu SSAS používat k uložení datových kostek z informačního systému.

Datové řezy – jsou pohledy na data v databázi

Postup práce

Následující schéma zobrazuje jednotlivé kroky projektu v souvislosti s mojí náladou, která oscilovala mezi nadšením a zoufalstvím. Jak je vidět, konec dobrý, všechno dobré.

1. Definování ukazatelů

Výběr KPI
Pro pilotní zpracovaní jsem si vybrala ukazatele, kterými budu měřit efektivitu práce oddělení supportu. Rovnou jsem vynechala typické ukazatele, jako obrat, počet prodaných produktů apod. Ty už samozřejmě sledujeme a není úplně nutné k nim vyrábět další grafy. Chtěla jsem zpracovat něco nového, v čem zároveň nemáme dobře nastavené procesy. Tím jsou právě pracovní výkazy v návaznosti na fakturaci zákazníkům.

Současné hypotézy: 

• Vedení firmy se domnívá, že členové supportu nejsou dostatečně vytíženi, neboť se to neprojevuje na finančních výsledcích.
• Zaměstnanci supportu mají pocit, že pracují na hraně svých možností a jsou naopak přetíženi. 

V ukazatelích tedy budu měřit:

• Kolik práce vykáží zaměstnanci v daném měsíci

Pro stanovení tohoto ukazatele budu potřebovat měsíční kapacitu zaměstnance se zohledněním různých druhů nepřítomnosti. Zároveň předpokládám, že ne všechen čas strávený v práci se dá vykázat. Cíl tedy bude stanoven na 70% možného odpracovaného času. To je poměrně přátelská hodnota, která se může časem upravit.

• Kolik z této práce se fakturuje zákazníkům

Každý pracovní výkaz, který slouží jako podklad pro fakturaci by měl být napojen na fakturu vydanou nebo na dodací listy vydané. Dle počtu vykázaných hodin lze vypočítat poměrnou částku pro jednotlivé zaměstnance.

Minimální částka, kterou musí zaměstnanec supportu vyfakturovat se pro začátek rovná interním nákladům na zaměstnance. Zjednodušeně řečeno, každý si musí zaplatit svůj pobyt v práci.

2. Zjištění dostupnosti dat

Myslela jsem si, že tento bod bude poměrně jednoduchý, neboť vím, jak data vypadají a kde je najdu. Většinu jsem chtěla čerpat z našeho informačního systému a k tomu připojit pár excelových tabulek. Realita byla mnohem horší, takže počáteční nadšení vystřídalo zoufalství. Napojení výkazů na fakturaci v systému totiž existovalo jen na papíře. Ve skutečnosti si každý manažer projektu našel svůj vlastní způsob, jak hlídat náklady a výnosy. Bohužel všechny způsoby ukazovaly na ad hoc pdf soubory nebo tabulky, případně jen maily. Tedy nic, co by se dalo použít pro zpracování.

Vypadalo to, že v dalších kapitolách už nebude o čem psát. Naštěstí mi kolegové hodně pomohli a fakturace za poslední čtyři měsíce zpětně provázali s výkazy. Ještě, že nejsme eshop nebo korporát a vytváříme rozumné množství dokladů měsíčně.

3. Instalace a nastavení SQL Server Analysis Services

Tak, první překážka je úspěšně za mnou. Plyne z toho poučení, že je potřeba se lépe připravit na krizové situace a nejlépe je rovnou předvídat. Proto jsem si v dalším kroku nejprve vyzkoušela instalaci a nastavení SQL Server Analysis Services Developer Edition, který je zdarma. Ten jsem napojila na testovací databázi QI na mém notebooku. Zvládla jsem to pomocí návodu a pár dotazů (zní to jednoduše, ale nešlo to úplně samo).

Mnohem větší inferno začalo při instalaci Analysis Services k ostrým datům na našem serveru, což už musel udělat náš správce IT a já jsem mu musela vysvětlit, co vlastně chci. Nejtěžší bylo sladit společný jazyk. Můj - plný ukazovacích zájmen a jeho – plný neznámých termínů a potenciálních sprostých slov. Pak jsme museli vyřešit přístupová práva tak, abych se dostala k datovým kostkám, které vytvořím v informačním systému. Nakonec jsme se shodli, že se mi bude věřit a mám „admin“ přístup ke všemu, co potřebuji.

4. Získání dat

Jak už jsem psala, hlavním zdrojem dat pro moje dashboardy je informační systém QI. Ten v sobě spojuje relační a objektový databázový model.

Relační model
Data jsou v QI organizována v tabulkách, které se skládají z řádků (záznamů) a sloupců (atributů). Tabulky jsou mezi sebou propojené vazebními tabulkami.

Objektový model
Jednotlivé tabulky (třídy), mohou mít mezi sebou definovány nejen relační vztahy, ale i vztahy objektové neboli dědičnosti. Celý informační systém lze na objektové úrovni zjednodušit na vztahy mezi čtyřmi vrcholovými tabulkami, kterými jsou akce, doklady, statky a subjekty. Každá vrcholová tabulka (třída) udržuje objektové vazby do svých hierarchicky podřízených tříd, ale tabulka na každém stupni hierarchie disponuje jinými atributy. Platí, že tabulky v hierarchii výše, obsahují atributy obecnější.

Ukázka datového modelu informačního systému QI. Zkuste se v něm vyznat.

Data se ze systému dají dostat v různých formátech a různým způsobem. Pro otestování jsem nejprve vyzkoušela ctrl + C a ctrl + V do Excelu. Je to nejrychlejší způsob, jak získat data (nebo třeba text do diplomky), ale nedává moc smysl. Vyzkoušela jsem tedy export do formátu .csv nebo .txt. V obou případech se spolu s daty přenášely i nějaké informace na pozadí, které se v souborech špatně čistily.

Zhruba před rokem výrobce systému uvolnil na trh nový modul, který umožňuje tvorbu OLAP kostek. Tento způsob je náročnější na nastavení, ale zároveň je nadčasový. Navíc, tvorba kostek se dá spustit automaticky pomocí naplánované úlohy. Což je přesně to, co potřebuju.

Nejprve si zjistím, jaké údaje budu potřebovat k výpočtům a k propojení (primární klíče). V praxi to znamená, že projdu vybrané formuláře, vyberu sloupce a zjistím, z jakého datového řezu je formulář vytvořen.

Do datových kostek pak vybírám data z datových řezů, mohu si i vytvářet vlastní řezy/pohledy. Nicméně, v QI je dnes téměř 10.000 datových řezů, takže není třeba počty zvyšovat. Pro začátek je určitě z čeho vybírat.

Datový řez může být:

• Normální - datový řez určený ke zpřístupnění dat za účelem jejich vkládání, mazání a editace
• Počítaný - datový řez obsahující vypočítaná pole nebo agregáty 
• Kombinovaný - datový řez obsahující normální datový řez plus počítaná agregovaná pole, která se vrací zároveň s daným normálním datovým řezem. 
• Ostatní (méně používané) - hierarchický, metodický 

Do kostek je nejlepší vybírat jen atributy z datových řezů normálních a kombinovaných. Je velká pravděpodobnost že budou (s mírnými úpravami) fungovat.

Jelikož potřebuji ke kostkám připojit externí tabulky, importuji si všechno do Power BI. Na úrovni QI provedu jen základní filtrování pomocí SQL příkazů. A jdeme na další krok.

5. Propojení a čištění dat

V Power BI (desktop) importuji všechna data z vytvořených kostek, k nim přidám dva excelové soubory. Pak nastavím propojení. Problém nastává tehdy, pokud jsou datové řezy z kostek propojeny vazební tabulkou, jejíž údaje jsou neviditelné a zároveň v tabulkách není žádný viditelný údaj, pomocí kterého by šly propojit. Tato situace nastala v případě propojení Výkazů a Dodacích listů.

Problém se musel vyřešit přímo v informačním systému. Do tabulky Výkazy se vložil nový sloupec (atribut), který obsahoval číslo dokladu, ke němuž se výkaz vztahoval. Vznikla potom vazba N:1 mezi Výkazy[Evidenční číslo dodacího listu] a Dodací listy[Evidenční číslo pohledávky].

Čištění dat v Power BI je zábavná a veselá práce, mnohem lepší (a trvalejší) než předvánoční úklid. Je to hlavně proto, že mám pěkná data. QI poměrně dobře předchází tomu, aby v datech vznikaly duplicity nebo chyby. Kde je to jen trochu možné, vkládají uživatelé údaje pomocí číselníků. Případně se nové záznamy automaticky označují unikátním ID, které je vytvořeno podle předem domluvené metodiky. ID je možné použít jako klíč.

Obecně se při čištění dat nejprve zbavím hodnot „Unknown“ a nahradím je „Null“. Identifikuji chybějící údaje a doplním je. Přeformátuji některé sloupce, ve kterých se špatně přenesl datový typ. Pravděpodobně to bylo způsobeno různými jazykovými mutacemi systému, serveru a Power BI. Dále je potřeba přidat některé nové sloupce, především typu Datum.

Vytváření metrik

Postupně při tvorbě grafů potřebuji dopočítat chybějící údaje. V některých případech musím použít jinou vazbu než tu, která byla nastavena jako hlavní/aktivní.

Funkce, které nejčastěji používám:

• CALCULATE - tato funkce typicky sdružuje funkci FILTER a jinou agregační funkci - např. SUM, MAX, FIRSTDATE atd.
• SUM – sčítá všechny hodnoty ve sloupci
• USERELATIONSHIP – použití neaktivní vazby k výpočtu
• ALL – vrací všechny údaje ve sloupci nebo v řádku, bez ohledu na filtrování 

Nejvíc práce mi zabralo vytvoření kalkulací pro mobilní aplikaci. V mobilu se zobrazují jen přehledy k aktuálnímu měsíci. Nechtěla jsem, aby uživatelé pořád dokola nastavovali filtr na daný měsíc. Cílem přehledů je zjistit stav vykázaných hodin bez zbytečného klikání (na mobilu se klikání asi říká jinak).

Postup vypadá zhruba takto:

6. a 7. Vytvoření a sdílení dashboardů

Zpracovala jsem dva dashboardy pro manažery, které jsem publikovala v online verzi Power BI. První dashboa
rd zobrazuje měřené údaje z pohledu jednotlivce. Druhý zobrazuje celkové údaje za oddělení supportu.

Zprovoznila jsem také verzi pro mobilní telefony, ke které mají přístup všichni zaměstnanci supportu. V mobilu každý zaměstnanec zjistí, zda má vykázáno dostatek hodin v daném měsíci, případně kolik mu ještě chybí.

8. Nastavení aktualizace

Téměř v závěru práce jsem se zaměřila na nastavení aktualizace dat.

Aktualizace datových kostek v informačním systému

Nejprve bylo potřeba nastavit aktualizaci v informačním systému QI. Ta se nastavuje pomocí naplánované úlohy, ke které se vytvoří makro nezávislé na aplikačním serveru. Makro bylo nakonec velmi jednoduché, ale trvalo několik hodin, než jsem se dostala k jeho finální podobě. Při vytváření jsem například zjistila, že chybová hláška „Příliš mnoho skutečných parametrů“ ve skutečnosti může znamenat „Příliš málo skutečných parametrů“. Dá se to vyřešit vložením parametru „Nothing“.

Nastavení aktualizace v Power BI

Naplánovaní aktualizace datových sad v Power BI (online verzi), funguje téměř na lusknutí prstu. Nesmíte být ale dřevorubec po patnácti letech nočního kácení (já), to si jen tak nezalouskáte.
Aby vás Power BI pustil k jednoduchému formuláři na plán aktualizace, je potřeba nainstalovat a nastavit bránu (data gateway).

Předpoklady pro úspěšné fungování:

• Data i brána musí být online dostupná v době naplánované aktualizace
• Data i brána musí být na jednom počítači/serveru

Předešlé dva body jsem objevovala v podstatě jeden celý den a vytvořila jsem k nim několik testovacích souborů s názvy „zoufalství1 až 3“. Bylo potřeba uložit všechny zdrojové soubory na server.  Pak nastavit novou cestu k souborům v absolutní podobě a zkontrolovat všechny metriky a vazby v Power BI.

Úplná cesta k souborům:

Nakonec se podařilo všechno vyladit. Aktualizaci jsem zatím nastavila tak, že se každý den v 1:00 vytvoří nové datové kostky. V 2:00 je potom načtu pomocí PBI Gateway (spolu s excelovými soubory) do PBI Reportů.


Celý tok dat se dá shrnout do následujícího schématu

Co mě ještě čeká aneb co se nepodařilo

Původně jsem chtěla zpracovávat data v datovém skladu na SQL Serveru a pak je teprve exportovat do Power BI, kde už by se jen vytvořily grafy. Slibovala jsem si od toho, že se víc naučím programovat v SQL, protože v Digitální akademii jsme mu věnovali poměrně málo času. Nakonec jsem ráda, že jsem tento krok z časových důvodů přeskočila. Hodně jsem se zdržela instalací a zprovozněním SQL Serveru (jeho části Analysis Services) a slovo SQL jsem pár dní nemohla ani slyšet.

Co jsem se naučila a jaký to mělo přínos

Podařilo se mi zvládnout vše podstatné, co jsem si naplánovala. Jen jsem musela některé kroky zjednodušit (datový sklad), případně ještě čekají na dokončení (návody).

Nicméně aktualizace dashboardů probíhají podle plánu, grafy fungují a přinášejí jednoznačné závěry managementu, jak to s tím vykazováním a fakturováním vlastně je.

Nyní mám rozpracované další reporty pro zákazníky, na kterých si vyzkoušíme celý postup znovu. Určitě narazíme na spoustu nových problémů, ale základ máme, myslím, velmi dobrý.

Během projektu jsem se naučila trochu programovat, hodně se ptát a seznámila jsem se se spoustou zajímavých lidí.

Na závěr jsem vybrala několik mouder pro příští účastnice DA:

1. Nebojte se ptát úplně cizích lidí na řešení problému, který máte.
2. Nečekejte, že vám odpoví.
3. Když zkopírujete jednoduchý kód, většinou nefunguje. Když zkopírujete složitý kód, nějakým zázrakem funguje. Hlavně do něho nevrtejte.
4. Váš časový odhad na zpracování projektu vynásobte alespoň třemi.
5. Všechno dobře dopadne.

Zdroje, ze kterých jsem čerpala:

• Diplomová práce: Datový sklad pro analýzu finanční a účetní oblasti v rámci informačního systému QI, Bc. Ondřej Plaček 2016
• Bakalářská práce: Importy dat z relační databáze do OLAP datových kostek, Zdeněk Laštůvka 2012
• http://www.w3schools.com - dobré na trénink SQL příkazů
• http://www.daxpatterns.com – příkazy pro Power BI
• http://www.qiakademie.cz – znalosti a prezentace k modulu BI v QI
• http://www.codeschool.com/ - procvičení programování krok za krokem

… a samozřejmě nejlepší kamarád datového analytika - google