Mendelova univerzita v Brně Provozně ekonomická fakulta. bankovního API. Diplomová práce. Vedoucí práce: Ing. Jan Přichystal, Ph.D

Mendelova univerzita v Brně Provozně ekonomická fakulta

Analýza finančních dat pomocí bankovního API Diplomová práce

Vedoucí práce: Ing. Jan Přichystal, Ph.D.

Bc. Tomáš Pavlacký

Brno 2014

Na tomto místě bych chtěl poděkovat vedoucímu práce Ing. Janu Přichystalovi, Ph.D., za cenné rady a čas věnovaný konzultacím a také odborníkům ze společnosti Intelligent Technologies s. r. o., za odborné vedení práce a poskytnutí prostředků pro tvorbu práce.

Čestné prohlášení Prohlašuji, že jsem tuto práci: Analýza finančních dat pomocí bankovního API vypracoval samostatně a veškeré použité prameny a informace jsou uvedeny v seznamu použité literatury. Souhlasím, aby moje práce byla zveřejněna v souladu s § 47b zákona č. 111/1998 Sb., o vysokých školách ve znění pozdějších předpisů, a v souladu s platnou Směrnicí o zveřejňování vysokoškolských závěrečných prací. Jsem si vědom, že se na moji práci vztahuje zákon č. 121/2000 Sb., autorský zákon, a že Mendelova univerzita v Brně má právo na uzavření licenční smlouvy a užití této práce jako školního díla podle § 60 odst. 1 Autorského zákona. Dále se zavazuji, že před sepsáním licenční smlouvy o využití díla jinou osobou (subjektem) si vyžádám písemné stanovisko univerzity o tom, že předmětná licenční smlouva není v rozporu s oprávněnými zájmy univerzity, a zavazuji se uhradit případný příspěvek na úhradu nákladů spojených se vznikem díla, a to až do jejich skutečné výše.

V Brně dne 19. května 2014

....................................................

4

Abstract Pavlacký, T. Financial data analysis using bank API. Diploma thesis. Brno, 2014. This diploma thesis is focused on exploring possibilities of financial data analysis using bank API. Theoretical section of this diploma thesis is focused on Business Intelligence and analysis of tools which were used for achieve established goal of this diploma thesis. Based on these facts was created Power Pivot sheet, which allows users of the bank to create more detailed analysis of their financial transactions. Keywords: bank API, Business Intelligence, financial data, Power Pivot

Abstrakt Pavlacký, T. Analýza finančních dat pomocí bankovního API. Diplomová práce. Brno, 2014. Diplomová práce je zaměřena na prozkoumání možností analýzy finančních dat pomocí bankovního API. Teoretická část této diplomové práce je zaměřena na problematiku Business Intelligence a dále na analýzu nástrojů, které byly využity pro dosažení cíle této práce. Na základě těchto poznatků byl v praktické části práce vytvořen sešit v programu Power Pivot, který uživatelům banky umožňuje detailněji analyzovat své finanční transakce. Klíčová slova: bankovní API, Business Intelligence, finanční data, Power Pivot

5

OBSAH

Obsah 1 Úvod a cíl práce 1.1 Úvod práce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 Cíl práce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

8 8 9

2 Business Intelligence 2.1 Pojem Business Intelligence . . . . . . . . . . . . . . . 2.2 Základní principy řešení BI . . . . . . . . . . . . . . . . 2.2.1 Multidimenzionální databáze . . . . . . . . . . . 2.2.2 Porovnání analytických a OLTP systémů . . . . 2.3 Nástroje, aplikace a vrstvy BI . . . . . . . . . . . . . . 2.3.1 Produkční systémy . . . . . . . . . . . . . . . . 2.3.2 Extraction, Transformation and Loading – ETL 2.3.3 Enterprise Application Integration – EAI . . . . 2.3.4 Dočasné úložiště dat – DSA . . . . . . . . . . . 2.3.5 Operativní úložiště dat – ODS . . . . . . . . . . 2.3.6 Datový sklad (Data Warehouse) . . . . . . . . . 2.3.7 Datové tržiště (Data Mart) . . . . . . . . . . . . 2.3.8 OLAP databáze . . . . . . . . . . . . . . . . . . 2.3.9 Reporting . . . . . . . . . . . . . . . . . . . . . 2.3.10 Data Mining . . . . . . . . . . . . . . . . . . . . 2.4 Trendy Business Intelligence . . . . . . . . . . . . . . . 2.4.1 Mobilní BI . . . . . . . . . . . . . . . . . . . . . 2.4.2 Cloud BI . . . . . . . . . . . . . . . . . . . . . . 2.4.3 Samoobslužné BI . . . . . . . . . . . . . . . . . 2.4.4 Open source BI . . . . . . . . . . . . . . . . . . 2.4.5 Big Data . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . .

10 10 10 10 13 14 16 17 17 17 18 18 19 19 20 20 21 21 21 22 22 22

3 API společnosti Fio banka, a. s. 3.1 Funkční popis . . . . . . . . . . . . . . . . . . 3.2 Získání tokenu . . . . . . . . . . . . . . . . . . 3.3 URL struktura dotazu pro získání dat . . . . . 3.3.1 Pohyby na účtu za určené období . . . 3.3.2 Oficiální výpisy pohybů z účtu . . . . . 3.3.3 Pohyby na účtu od posledního stažení 3.4 Podporované formáty seznamu pohybů . . . . 3.4.1 XML . . . . . . . . . . . . . . . . . . . 3.4.2 OFX . . . . . . . . . . . . . . . . . . . 3.4.3 GPC . . . . . . . . . . . . . . . . . . . 3.4.4 CSV . . . . . . . . . . . . . . . . . . . 3.4.5 HTML . . . . . . . . . . . . . . . . . . 3.4.6 JSON . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

24 24 24 25 25 25 26 26 26 26 27 27 27 28

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

6

OBSAH

3.4.7

STA (MT940) . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

4 Metodická východiska práce 4.1 Specifikace aplikace . . . . . . 4.2 Použitá metodologie . . . . . 4.3 Použité nástroje a technologie 4.3.1 Power Pivot . . . . . . 4.3.2 Power Query . . . . .

. . . . .

. . . . .

. . . . .

. . . . .

5 Tvorba datového modelu 5.1 Analýza vstupních dat . . . . . . . . 5.1.1 Výběr formátu dat . . . . . . 5.2 Dimenzionální modelování . . . . . . 5.2.1 Časová dimenze . . . . . . . . 5.2.2 Dimenze Protiúčet . . . . . . 5.2.3 Dimenze Typ platby . . . . . 5.2.4 Dimenze Měna . . . . . . . . 5.2.5 Dimenze Příkaz . . . . . . . . 5.2.6 Dimenze Uživatel . . . . . . . 5.2.7 Dimenze Kategorie . . . . . . 5.2.8 Dimenze Plán . . . . . . . . . 5.2.9 Faktová tabulka . . . . . . . . 5.3 ETL procesy . . . . . . . . . . . . . . 5.3.1 Příprava zdrojových dat . . . 5.3.2 Tvorba dimenzí . . . . . . . . 5.3.3 Tvorba faktové tabulky . . . . 5.3.4 Kompletace datového modelu

. . . . .

. . . . . . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

6 Prezentační vrstva aplikace 6.1 Vkládání kontingenčních tabulek a grafů 6.2 Úvod aplikace . . . . . . . . . . . . . . . 6.3 Hlavní přehled . . . . . . . . . . . . . . . 6.3.1 Aktuální přehled . . . . . . . . . 6.3.2 Přehled veškerých transakcí . . . 6.4 Zařazení uživatele do kategorie . . . . . 6.5 Plán výdajů po měsících . . . . . . . . . 6.5.1 Sestavení plánu . . . . . . . . . . 6.5.2 Kontrola plnění plánu . . . . . . 6.6 Přehled výdajů dle uživatelů . . . . . . . 6.7 Přehled výdajů dle kategorií . . . . . . . 6.8 Správa kategorií . . . . . . . . . . . . . . 6.9 Vlastní analýzy . . . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

. . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . .

29 29 29 30 30 32

. . . . . . . . . . . . . . . . .

33 33 33 34 35 35 35 35 36 36 36 36 36 37 38 39 43 44

. . . . . . . . . . . . .

48 48 49 50 50 51 52 53 53 53 55 56 57 58

7

OBSAH

7 Výsledky a diskuse 7.1 Zhodnocení a přínosy . . . . . . . 7.2 Možnosti rozšíření . . . . . . . . . 7.3 Srovnání s existujícími řešeními . 7.4 Ekonomické zhodnocení projektu 7.5 Diskuse . . . . . . . . . . . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

60 60 60 60 61 62

8 Závěr

63

9 Literatura

64

Přílohy

66

A Vybraná makra pro práci s výslednou aplikací

67

B DVD médium

70

1

ÚVOD A CíL PRÁCE

1 1.1

8

Úvod a cíl práce Úvod práce

Důležitost oblasti Business Intelligence si v dnešní době začíná uvědomovat stále více firem. Tomuto trendu nepodléhají již pouze velké firmy, které si mohou dovolit vynaložit vetší množství finančních prostředků pro dotování činností spojených právě s Business Intelligence. V dnešní době tato oblast proniká stále více ke středním a malým firmám, které si její důležitost začínají stále více uvědomovat a hledat tak konkurenční výhody, které by jim pomohly s vydobytím lepší konkurenceschopnosti na trhu. Není tedy divu, že developerské společnosti v oblasti Business Intelligence se předhání v nabízení stále nových produktů, které slibují stále propracovanější řešení uživatelských projektů pro společnosti všech velikostí. Využitelnost oblasti Business Intelligence ovšem nespadá pouze do firemního sektoru, jak by se mohlo na první pohled dle názvu zdát, ale také do oblasti domácností. I domácnost se dá považovat za velmi specifickou firmu, jejíž hlavním zájmem je tvorba zisku. V případě domácností může být tímto ziskem například výše úspor na bankovním účtu, množství investovaných finančních prostředků do nemovitostí, atd. Aby mohla domácnost lépe hospodařit a dosáhnout tak svého cíle, je zapotřebí detailně analyzovat veškeré výdaje, které tato domácnost vynakládá. Společnosti mohou například sledovat ziskovost jednotlivých firemních útvarů, vynaložené náklady na jednotlivé oddělení v průběhu roku či návratnost investic, které společnost v rámci daného časového období provedla. Ekvivalentem u domácností může být například sledování výdajů v jednotlivých oblastech zájmu dané domácnosti – například výše nájmu, zálohy za energie, výdaje za sport a zábavu, jídlo, atd. Dále mohou domácnosti sledovat příjmy, které jim plynou z různých činností či investic – výplata od členů domácnosti, dividenda za investované finanční prostředky či nejrůznější příspěvky na bydlení, stravování, atd. Existující řešení v oblasti Business Intelligence ovšem stále ještě více cílí na sektor firem. Tato řešení tedy nejsou pro domácnosti velmi přínosné a jejich přeměna z firemních řešení na řešení pro domácnosti je velice náročná, nebo není možná vůbec. Je tedy velmi aktuální a bezesporu přínosné, pokusit se zaměřit Na sektor domácností a najít vhodné řešení, které by vzhledem k náročnosti na přizpůsobení se potřebám domácnosti nebylo finančně ani časově náročné. Jestliže chce domácnost sledovat veškeré své transakce, je vhodné, aby měla veden účet u nějaké bankovní instituce, která umožňuje svým klientům přístup k jejich transakcím prostřednictvím Internetu. Bude tak snazší sledovat veškeré transakce a následně s nimi pracovat, než by tomu bylo v případě pouze měsíčních výpisů z účtu či pouhému sledování transakcí na základě složenek či účtenek. Aby bylo možné tato data získat v nějaké ucelené podobě, je také vhodné, aby daná bankovní instituce poskytovala nástroje pro stažení těchto dat. V dnešní době tuto možnost stále ještě nenabízí všechny bankovní instituce, a tak je zapotřebí zvolit takovou, která tuto

1.2

Cíl práce

9

možnosti nabízí. V rámci této práce byla zvolena společnost Fio banka, a. s, která tuto možnost stažení dat o uživatelských transakcích nabízí od roku 2013. Přístupů a nástrojů k tvorbě aplikace tohoto typu může být velké množství. Téma této práce bylo vytvořeno společností Intelligent Technologies, která si k tvorbě této aplikace stanovila jasné nástroje. Požadavek byl, aby aplikace byla vytvořena v programu Microsoft Excel 2013 s doplňkem Power Pivot. Tento nástroj je jedním z nejznámějších programů svého typu u většiny obyvatel České republiky, jehož základy se vyučují již na základních školách. Při tvorbě této aplikace musí být kladen důraz na přehlednost, jednoduchost ovládání a přehlednost při prezentování dosažených výsledků analýz.

1.2

Cíl práce

Tato práce si klade za cíl navržení a následnou implementaci samoobslužného Business Intelligence řešení pro správu vlastních transakcí v prostředí programu Microsoft Excel 2013 s doplňkem Power Pivot. Tyto transakce budou načítány prostřednictvím API od společnosti Fio banka, a. s. Tato data budou následně přeměněna do formy datového modelu v prostředí programu Microsoft Excel 2013 a následně bude na základě tohoto datového modelu vytvořen Power Pivot sešit, ve kterém si uživatelé budou moci vytvářet libovolné analýzy nad svými daty. V tomto Power Pivot sešitě budou již nějaké příklady možných využitelných analýz vytvořeny, aby měli uživatelé jasnou představu o tom, jaké výsledky analýz lze od této aplikace očekávat. Bude zde také nápověda ve formě popisů u jednotlivých listů, které uživatele provedou při práci s touto aplikací.

2

BUSINESS INTELLIGENCE

2 2.1

10

Business Intelligence Pojem Business Intelligence

Termín Business Intelligence zavedl v roce 1989 Howard J. Dresner, analytik společnosti Gartner Group, který jej popsal jako sadu konceptů a metod určených pro ” zkvalitnění rozhodnutí firmy“. Každý autor, který píše publikaci o Business Intelligence, se snaží o to, aby právě jeho definice co nejvýstižněji vyjadřovala pravou podstatu pojmu Business Intelligence. Tohoto ovšem nelze zcela úplně docílit, a tak jsou i tyto definice jednotlivých autorů mírně odlišné. Pro tuto práci jsem vybral definici z knihy Business Intelligence: Jak využít bohatství ve vašich datech: Business ” Intelligence (BI) představuje komplex přístupů a aplikací IS/ICT, které téměř výlučně podporují analytické a plánovací činnosti podniků a organizací a jsou postaveny na principu multidimenzionality, kterým zde rozumíme možnost pohlížet na realitu z několika možných úhlů“ (Novotný, Pour, Slánský, 2005).

2.2 2.2.1

Základní principy řešení BI Multidimenzionální databáze

Informační systémy mohou pracovat se dvěma základními typy informací – operativními (operačními) a analytickými. Operativní informace slouží pro realizaci obchodních a dalších transakcí v podniku. Většinou jsou uloženy v podobě relačního databázového modelu, který sdružuje data do tzv. relací (tabulek), které obsahují n-tice (řádky). Data v těchto databázích jsou většinou normalizována (převedena do 3NF) a vysoce strukturována. Mezi výhody tohoto způsobu uložení dat patří především to, že je tento způsob běžně používaným ve firmách, je mu přizpůsobena velká část softwarových a vývojových nástrojů a je použitelný v transakčních databázích a datových skladech. Mezi nevýhody relačních modelů patří absence analytických nástrojů a objemy dat, se kterými je možné pracovat v reálném čase. Tyto databáze tedy zobrazují aktuální stav podniku a mohou se i několikrát měnit v průběhu jednoho dne. Transakční systémy realizují jejich zpracování v reálném čase a označují se jako OLTP (On Line Transaction Processing) systémy. Příkladem může být vytvoření objednávky, rezervace letu, přijetí platby, apod (Lacko, 2003). Systémy, které pracují s analytickými informacemi využívají primární data vytvořená v OLTP systémech. Díky svému uložení a operacím s daty se pro tyto systémy vžilo v osmdesátých letech označení OLAP (On Line Analytical Processing). S rozvojem nástrojů a technologií pro podporu analytických činností v organizaci se však výraz OLAP poněkud zúžil. Tento užší význam je v literatuře definována čistě technologicky, tedy jako: informační technologii založenou především na kon” cepci multidimenzionálních databází. Jejím hlavním principem je několikadimenzionální tabulka umožňující rychle a pružně měnit jednotlivé dimenze, a měnit tak

2.2

Základní principy řešení BI

11

pohledy uživatele na modelovanou ekonomickou realitu“ (Novotný, Pour, Slánský, 2005). Na rozdíl od OLTP jsou analytické systémy charakteristické především těmito vlastnostmi: • informace poskytují na základě vstupů získaných z primárních dat • data jsou v nich uložena multidimenzionálně • obsahují různé úrovně agregace • zachycují faktor času a umožňují realizovat časová srovnání, časové řady, predikci budoucího vývoje, apod. • umožňují provádění analýz nad uloženými daty (například zjištění, které produkty se nejlépe prodávají, ve kterém regionu jsou nejnižší tržby, apod.) U analytických dat již není výhodné jejich uložení do relačních databází jako tomu bylo u dat operativních. Abychom mohli na tato analytická data nahlížet z více možných úhlů, je zapotřebí tato data ukládat takovým způsobem, abychom mohli vytvářet multidimenzionální pohledy. Což je u relačních databází v 3NF nesmírně náročné. Za tímto účelem byly vytvořeny tzv. multidimenzionální databáze. Tyto databáze jsou již optimalizované pro uložení a interaktivní využívání multidimenzionálních dat. Výhodou tohoto řešení je rychlost zpracování a efektivní analýzy multidimenzionálních dat. Základním principem je několikadimenzionální tabulka, která umožňuje uživateli pružně měnit jednotlivé dimenze tak, aby dosáhl požadovaných přehledů nad daty. Tato tabulka je zobrazena na obrázku 1. Z obrázku lze vyčíst to, že standardními dvěma dimenzemi jsou ukazatele (ekonomické proměnné) a čas. Ostatní dimenze se specifikují na základě potřeby individuálně. Těmito dimenzemi může být například organizační jednotka, zákazník, region, dodavatel, atd. Obsahem těchto dimenzí jsou prvky dimenzí. Tyto prvky jsou většinou uspořádány do hierarchické struktury a jedná se již o konkrétní závody, zákazník, dodavatel, komodita, atd. Tato hierarchická struktura umožňuje uskutečňovat agregace na základě jednotlivých úrovní této struktury. Využívá se jí především díky urychlení odezvy systému na analytické požadavky. Spojíme-li všechny dimenze do jednoho bodu této multidimenzionální tabulky, dostaneme prvek multidimenzionální databáze. Multidimenzionalita dat může být implementována na dvou úrovních (Novotný, Pour, Slánský, 2005): • na úrovni relační databáze (schéma sněhové vločky, schéma hvězdy) • na úrovni speciální binární databáze Schéma sněhové vločky i schéma hvězdy vychází z jedné tabulky dimenzí pro každou dimenzi a jedné centrální tabulky faktů (fact table). Faktová tabulka obsahuje sledované ekonomické a další ukazatele, které jsou identifikované složeným klíčem z klíčů dimenzionálních tabulek, a bývá zpravidla největší tabulkou

2.2

12

Základní principy řešení BI

or

závod 1

ga

ni

za čn

je

dn

ot ky

další dimenze tabulky

ukazatele (např. prodej určité komodity, stav zaměstnanců, dosažené tržby)

např.

30.9.

31.10. 30.11. 31.12.

prvek tabulky vyjadřuje hodnotu stav zaměstnanců k 31.12. v závodě 1

čas

Obrázek 1: Princip multidimenzionální databáze (Novotný, Pour, Slánský, 2005).

v databázi. Dimenzionální tabulky obsahují textový popis informací o hodnotách, které obsahuje faktová tabulka. Jestliže je těžké rozhodnout, zda daná veličina patří do dimenzionální či faktové tabulky, je vhodné zjistit, zda daná veličina je proměnná v čase. Jestliže se s časem mění, je vhodné ji zařadit do faktové tabulky. Jestliže se s časem nemění, zařadíme ji do tabulky dané dimenze. Schéma hvězdy dostalo svůj název podle způsobu grafického znázornění, které určitým způsobem připomíná hvězdu. Výhodou oproti schématu sněhové vločky je vysoký výkon a tedy rychlá odezva na dotazy. Jako nevýhoda by se mohla jevit nutnost ukládat některé údaje redundantně, což by vedlo k vyšší náročnosti na úložný prostor. Ovšem tabulky dimenzí většinou potřebují pouze 1–5 % z celkového potřebného prostoru, a tak redundance není zase až takový problém. Příklad databáze, která je vytvořena na základě schématu hvězdy je znázorněno na obrázku 2 (Jensen, Pedersen, Thomsen, 2010). Znázorníme-li graficky schéma sněhové vločky dostaneme strukturu, která připomíná sněhovou vločku. Výhodou tohoto schématu je rychlé zavedení dat do normalizovaných tabulek, jejichž využití využívá méně prostoru na disku a snižuje tak náročnost na požadavky diskového prostoru. Ovšem jak je uvedeno výše, dimenzionální tabulky obsahují pouze 1–5 % veškerého potřebného prostoru, a tak tato výhoda není zase až tak razantní. Nevýhodou je nutnost propojovat dimenzionální

2.2

13

Základní principy řešení BI

BookID 7493 9436

Book

Genre

Tropical Food Winnie the Pooh

Cooking Children´s books

Book (dimension table)

BookID

CityID

DayID

9436 7493 7493 9436 9436

854 854 876 876 876

2475 2475 3456 3456 2475

Sale 20 5 2 11 18

CityID 876 854

City Arlington

State Virginia

Boston

Massachisetts

Location (dimension table)

Sales (fact table)

DayID 2475 3456

Day March 1, 2009 March 13, 2009

Month March 2009 March 2009

Year 2009 2009

Time (dimension table)

Obrázek 2: Schéma hvězdy (Jensen, Pedersen, Thomsen, 2010).

tabulky pro získání odpovědí na dotazy, což vede k vyšší odezvě systému. Příklad databáze, která je vytvořena na základě schématu sněhové vločky je znázorněno na obrázku 3. 2.2.2

Porovnání analytických a OLTP systémů

Požadujeme-li pohled na data z více hledisek (dimenzí), musíme také tato data optimalizovaně ukládat. Takto ukládaná data jsou převážně historická, agregovaná a průběžně rozšiřovaná. Většina těchto dat je svým zaměřením uzpůsobena pro potřeby managementu a jsou ukládána v jednoduché struktuře, která je vhodná pro analýzu. Mezi OLTP a analytickými systémy lze tedy nalézt následující rozdíly (Novotný, Pour, Slánský, 2005): • OLTP systémy jsou primárně určeny pro získávání dat – velký počet tabulek a spojení mezi nimi ve 3NF, snaha o nulovou redundanci. • Data do OLTP systémů jsou pořizována v reálném čase a za běžného provozu probíhají desítky až statisíce transakcí za minutu. • na rozdíl od OLTP jsou analytické systémy primárně určeny pro podporu rozhodování – několikavrstvé databáze, databázové modely s menším počtem denormalizovaných tabulek, duplicita uložení dat, atd.

2.3

14

Nástroje, aplikace a vrstvy BI

YearID

Year

88

2009

Year (Time dimension) MonthID

Month

YearID

45

March 2009

88

Month (Time dimension) DayID

Day

MonthID

2475

March 1, 2009

45

March 13, 2009

45

3456

Day (Time dimension) BookID

CityID

DayID

Sale

9436

854

2475

20

9436

854

2475

5

9436

876

3456

2

BookID

Book

9436

876

3456

11

7493

Tropical Food

12

9436

876

2475

18

9436

Winnie the Pooh

12

Book (Book dimension)

Sales (fact table) CityID

City

StateID

GenreID

Genre

876

Arlington

783

23

Cooking

Boston

147

12

Children´s books

854

City (Location dimension) StateID

GenreID

Genre (Book dimension)

State

147

Massachusetts

783

Virginia

State (Location dimension)

Obrázek 3: Schéma sněhové vločky (Jensen, Pedersen, Thomsen, 2010).

• Analytické systémy aktualizují svá data periodicky (denně či měsíčně) – zatížení databází není kontinuální jako tomu bylo u OLTP, ale je nárazové. Nejvíce je tato nárazová náročnost zřejmá ve fázi nahrávání dat nebo také při plnění analytických úloh. • OLTP systémy ukládají data na maximální úrovni detailu, zatímco analytické systémy ukládají data pouze relevantní pro analýzy (agregovaná na vyšší úroveň, nebo zahrnující pouze některé atributy).

2.3

Nástroje, aplikace a vrstvy BI

Obecná koncepce architektury Business Intelligence je znázorněna na obrázku 4. Na základě tohoto zobrazení můžeme tuto koncepci rozdělit do jednotlivých vrstev,

2.3

Nástroje, aplikace a vrstvy BI

15

které se od sebe liší svým obsahem. Jak je z obrázku patrné, na nejnižší úrovni jsou zdrojové (produkční) systémy. Tyto systémy slouží k pořízení a sběru vstupních dat pro BI analýzy. Příkladem těchto systému mohou být ERP (Enterprise Resource Planning), CRM (Customer Relationship Management), SCM (Supply Chain Management), atd. Tyto systémy ovšem do skupiny BI aplikací ještě nepatří. Nad vrstvou zdrojových systémů je vrstva pro extrakci, transformaci, čištění a nahrávání dat. Úkolem této vrstvy je přenést a upravit data z vrstvy zdrojových systémů do vrstvy pro ukládání dat. Součástí této vrstvy jsou tzv. ETL (Extraction, Transformation and Loading) systémy, neboli systémy pro extrakci, transformaci a nahrání dat, a EAI (Enterprise Application Integration) systémy, které slouží k integraci aplikací. Jakmile jsou takto data upravena a načtena, je zapotřebí je někde ukládat. Za tímto účelem je zde další vrstva, která se nazývá vrstva pro ukládání dat. V této vrstvě také probíhá aktualizace a správa dat. Pro uskladnění dat se využívá několika základních komponent (Kimball, Ross, 2002): • Datové sklady (Data Warehouse) – strukturované úložiště údajů sloužící k podpoře rozhodování, které je uloženo odděleně od operační databáze (základní databázová komponenta řešení BI) • Datová tržiště (Data Marts) – součást nebo nadstavba datové skladu, která je specifičtěji zaměřena na požadavky uživatele • Operativní datová úložiště (Operational Data Store) – podpůrné analytické databáze • Dočasná úložiště dat (Data Staging Areas) – databáze, do které jsou ukládána data před jejich vlastním zpracováním Vrstva analytických komponent slouží pro vlastní zpřístupnění zatím uložených dat a jejich analýzu. Na obrázku 4 jsou v této vrstvě uvedeny tři základní činnosti: • Reporting – činnost, pomocí které jsou prováděny dotazovací procesy na databázové komponenty řešení BI • Systémy OLAP (On Line Analytical Processing) – pokročilé a dynamické analytické úlohy • Data Mining – systémy, které jsou zaměřeny na analýzu velkého množství dat Aby byly výsledky dosažené v rámci analýzy užitečné, musí být prezentovány vedení podniku jasným a přehledným způsobem. Za tímto účelem je zde nejvyšší vrstva komponent BI – vrstva prezentační. V rámci této vrstvy komunikuje zadavatel požadavků se systémem a zadává požadavky na analýzy. Výsledkem jsou mu relevantní údaje, které od systému vyžadoval. Pro prezentaci výsledků mohou být použity například portálové aplikace založené na technologiích World Wide Web,

2.3

Nástroje, aplikace a vrstvy BI

16

systémy EIS (Executive Information Systems) či nejrůznější analytické nástroje a aplikace inmon. Na nejvyšší úrovni této koncepce architektury BI se nachází koncoví uživatelé, kteří s tímto systémem pracují a pro které jsou výsledky získané v rámci této analýzy důležité a pomáhají jim v rozhodovacích procesech o budoucí strategii ve vedení podniku. Patří sem tedy například management společnosti, analytické oddělení, oddělení péče o zákazníky, atd.

Obrázek 4: Obecná koncepce architektury BI (Novotný, Pour, Slánský, 2005).

2.3.1

Produkční systémy

Jako produkční systémy mohou být také někdy označovány systémy OLTP, primární čí transakční systémy. Všechny tyto pojmy jsou takové systémy, ze kterých aplikaci Business Intelligence získávají potřebná data. Vlastností všech těchto systémů je jejich architektura podporující ukládání a modifikaci dat v reálném čase. Zdroji pro BI nemusí být ovšem pouze vnitřní systémy podniku, mohou jimi být i externí systémy, jako například telefonní seznamy, data získána ze statistických úřadů či vládních institucí, atd. Příkladem těchto produkčních systémů mohou být systémy ERP, CRM, systémy pro podporu personálních oddělení, finančních oddělení, atd.

2.3

Nástroje, aplikace a vrstvy BI

17

Produkční systémy jsou hlavním (a často také jediným) vstupem do BI. V praxi je většinou spektrum produkčních systémů pro BI velmi různorodé a heterogenní jak obsahově, tak technologicky. Úkolem řešení BI je pak zajistit analýzu těchto zdrojů z pohledu potřeb řízení firmy, výběr relevantních dat pro řízení, a následně pro jejich vzájemnou integraci. Právě tato část projektů BI je pracovně, časově i finančně nejnáročnější, ale představuje zcela nezbytný předpoklad úspěšných aplikací BI (Novotný, Pour, Slánský, 2005). 2.3.2

Extraction, Transformation and Loading – ETL

Za systém extrakce, transformace a následného načtení dat se dá považovat seskupení procesů, které zajišťují načtení dat z produkčních (zdrojových) systémů, jejich transformaci a čištění a následné načtení do datového skladu. Nástroje ETL pracují v dávkovém režimu, což znamená, že data jsou přenášena v určitých časových intervalech. Ve fázi extrakce jsou tedy tato data načtena z produkčních systémů a jsou předána k provedení dalších kroků. Tato fáze může znít triviálně, ovšem pravda je taková, že tato fáze se může stát hlavní překážkou v procesu ETL. Dalším krokem je transformace dat. Tato fáze se nachází mezi načtením a uložením dat do datové struktury. V této fázi se můžeme setkat například s operacemi, jakými jsou: přesun dat, validace dat, modifikace obsahu nebo struktury dat, integrace dat z různých datových zdrojů, kalkulace derivovaných či agregovaných dat na základě zpracovaných dat, atd. Třetím krokem je načtení dat do specifické datové struktury. Tato fáze není pouhým naplněním získaných a upravených dat do cílové tabulky. Součástí toho načítání mohou být například management tabulek dimenzí, tvorba cizích klíčů, atd. Výsledkem fáze ETL jsou tedy data, která jsou připravena pro analýzu a je možné je uložit do datového skladu (Kimball, Ross, 2002). 2.3.3

Enterprise Application Integration – EAI

Cílem nástrojů EAI je integrovat primární podnikové systémy a razantně redukovat počet jejich vzájemných rozhraní. Tyto nástroje pracují na dvou úrovních: • na úrovni datové integrace – EAI využity pro integraci a distribuci dat • na úrovni aplikační integrace – EAI využity nejen pro integraci a distribuci dat, ale především pro sdílení vybraných funkcí informačních systémů Na rozdíl od ETL systémů nepracují EAI systémy dávkově, ale v reálném čase. EAI tak doplňuje dávkový přenos a umožňuje vznik nové generace datových skladů, tzv. Real-Time Data Warehouse (Inmon, 2005).

2.3

Nástroje, aplikace a vrstvy BI

2.3.4

18

Dočasné úložiště dat – DSA

DSA (Data Staging Area) slouží k prvotnímu ukládání netransformovaných dat z produkčních systémů. Nachází své uplatnění zejména u neustále zatížených produkčních systémů, kde je potřeba vybírat a přenášet jejich data s minimálním dopadem na výkonnost těchto systémů. Dočasné úložiště dat tak obsahuje data s následujícími charakteristikami (Gála L., Pour J., Prokop T., 2006): • data jsou detailní a nekonzistentní • na rozdíl od DWH a DMA neobsahují historii dat – přenášejí pouze aktuální data • po zpracování dat v DSA a přenosu do dalších komponent BI se tato data mažou • jsou uložena ve stejné struktuře, v jaké jsou ve zdrojových systémech 2.3.5

Operativní úložiště dat – ODS

ODS (Operational Data Store) je další komponentou, kterou nemusíme nalézt ve všech řešeních BI. Stejně jako DSA obsahuje i ODS pouze aktuální snímky, které se mění po každém nahrání. Oproti DSA obsahuje ODS však data konzistentní a v určitých případech i agregovaná. Nejvíce je rozdíl ODS a DSA vidět v jejich použití. Zatímco DSA slouží jako dočasné uložení dat před jejich vlastním zpracováním v datovém skladu, ODS slouží jako databáze podporující analytický proces. Do DSA tedy nemají přístup koncoví uživatelé, zatímco do ODS tento přístup mají a umožňuje jim tak přístup k datům pro analýzy či dotazy s minimálním zpožděním. Existují dva základní přístupy k definici ODS (Kimball, Ross, 2002): • První přístup definuje ODS jako jednotné místo datové integrace aktuálních dat z produkčních systémů. Jedná se o zdroj pro sledování konsolidovaných dat téměř v reálném čase (s minimální dobou odezvy). V mnoha případech takové ODS slouží jako centrální databáze základních číselníků (zákaznický, produktový) nebo pro podporu interaktivní komunikace se zákazníkem (např. podpora pracovníků call center – ODS dodává data o zákazníkovi, jeho profilu). Takto definované databáze podporují vkládání a modifikaci dat v reálném čase a jsou typicky napojeny na integrační (EAI) platformy. • Druhý přístup definuje ODS jako databázi navrženou s cílem podporovat relativně jednoduché dotazy nad malým množstvím aktuálních analytických dat. Na rozdíl od prvního přístupu podle tohoto vymezení vzniká ODS jako derivace již existujícího datového skladu a obsahuje pouze aktuální záznamy vybraného množství dat.

2.3

Nástroje, aplikace a vrstvy BI

2.3.6

19

Datový sklad (Data Warehouse)

Stejně jako u pojmu Business Intelligence, existuje i pro pojem datový sklad velké množství definic od různých autorů. Jako nejlepší se mi zde jeví zvolit definici od Billa Inmona, který je jedním ze zakladatelů Data Warehousingu: Datový sklad ” je integrovaný, subjektově orientovaný, stálý a časově rozlišený souhrn dat, uspořádaný pro podporu potřeb managementu“ (Inmon, 2005). Tyto pojmy lze pak interpretovat následovně (Novotný, Pour, Slánský, 2005): • Subjektově orientovaný – data jsou rozdělena podle jejich typu, ne podle aplikací jejich vzniku. Data jsou tedy uložena pouze jednou, a to v jedné databázi datového skladu. V produkčním systému bývají rozptýlena podle toho, kde mají být použita. • Integrovaný – data jsou ukládána v rámci celého podniku, ne pouze v rámci jednoho oddělení. • Stálý – žádná data nevznikají ručním pořízením a nelze je ani měnit žádnými uživatelskými nástroji. Data jsou do datových skladů načítána z externích zdrojů a existují zde po celou dobu existence datového skladu. • Časově rozlišený – načítaná data obsahují také dimenzi času. Tato dimenze je důležitá z toho důvodu, aby bylo možno s daty provádět časové analýzy. 2.3.7

Datové tržiště (Data Mart)

Datové tržiště plní podobně jako datové sklady roli zprostředkovatele informací, které jsou získávány z podnikových dat, nicméně datové tržiště je zacíleno pouze na konkrétní skupinu uživatelů (jednotlivá oddělení, pobočky, atd.) a jejich požadavky. Tyto skupiny se od sebe liší svými předměty zájmu. Datové tržiště tedy obsahuje přesně zacílenou podmnožinu dat, která vyhovuje analytickým požadavkům dané skupiny, která vyžaduje analýzy z dané oblasti. Podstatou jsou tak decentralizované datové sklady, které se postupně integrují do celopodnikového řešení. Ralph Kimball, zakladatel datových skladů, definuje datové tržiště jako: Da” tový sklad není nic jiného než sjednocení datových tržišť “. z tohoto tvrzení vyplývá, že logickým sjednocením datových tržišť bychom měli dostat datový sklad (Kimball, Ross, 2002). 2.3.8

OLAP databáze

OLAP databáze se skládají z OLAP kostek. Tato kostka může být jedna, nebo jich v databázi může být více. Tyto OLAP kostky již většinou obsahují předzpracované agregace dat podle hierarchických struktur dimenzí. V zásadě existují čtyři způsoby uložení kostek do databáze (Inmon, 2005):

2.3

Nástroje, aplikace a vrstvy BI

20

• MOLAP (Multidimensional OLAP) – data jsou uloženy do formy multidimenzionálních kostek. Výhodou tohoto způsobu uložení je rychlost dotazování a kostky jsou vytvářeny k rychlému získání požadovaných dat. Všechny potřebné výpočty jsou provedeny při vytvoření kostky a mohou být jednoduše aplikovány právě při dotazování. Nevýhodou je to, že tento způsob uložení je schopen pojmout pouze určité množství dat. • ROLAP (Relational OLAP) – data v tomto modelu jsou uloženy do relačních databází. Výhodou tohoto modelu je to, že může zpracovat velké množství dat a mohou být využity veškeré funkce relačních databází. Nevýhodou je rychlost zpracování dotazů, jelikož každý ROLAP dotaz je uskutečňován pomocí SQL dotazů. • HOLAP (Hybrid OLAP) – je kombinací předchozích dvou přístupů. Detailní data jsou uložena v relační databázi a agregované hodnoty jsou uloženy v binárních OLAP kostkách. • DOLAP (Desktop OLAP) – umožňuje připojit se k centrálnímu úložišti OLAP dat a stáhnout si požadovanou podmnožinu kostky do lokálního počítače. Veškerá takto získaná data jsou tedy uložena v uživatelském počítači a uživatel tak pro zpracování dotazů nemusí být připojen k serveru, na kterém se data nachází. 2.3.9

Reporting

Reporting představuje činnosti spojené s dotazováním se do databází pomocí standardních rozhraní těchto databází (například pro relační databáze se jedná o SQL dotazy). V rámci reportingu lze identifikovat (Novotný, Pour, Slánský, 2005): • standardní reporting – v určitých časových periodách jsou spuštěny předpřipravené reporty • ad hoc reporting – na databáze jsou explicitně formulovány specifické dotazy, které formuluje uživatel Důležitou součástí reportingu jsou i stanovená kritéria řízení výkonnosti. Tato kritéria se označují jako KPI, což je zkratka pro anglické sousloví Key Performance Indicators. Tato KPI se využívají pro hodnocení skutečného vývoje v porovnání se stanovenými cíli a úkoly. Prostřednictvím reportingu získá oprávněná osoba potřebné informace ve formě, která je mu graficky příjemná, funkcionálně dostatečná a snadno se obsluhuje tak, aby nebyl uživatel zdržován a zahlcován úkoly, které nejsou bezpodmínečně nutné. Nejčastějším výstupem reportingu jsou tedy přehledné grafy a tabulky, které dávají ucelený a jasný přehled o tom, jaká je situace v oblasti zájmu žadatele o tento report. Jedná se tedy o finální fázi celého BI řešení, ke které je směřováno veškeré úsilí a je to hlavní důvod toho, proč je BI v podniku realizováno.

2.4

Trendy Business Intelligence

2.3.10

21

Data Mining

Data mining (neboli dolování z dat) je proces hledání informací a znalostí ve velkém objemu dat. Usama Fayyad, přední odborník na oblast data miningu, definuje data mining následovně: Data Mining je proces výběru, prohledávání a modelování ” ve velkých objemech dat, sloužící k odhalení dříve neznámých vztahů mezi daty za účelem získání obchodní výhody“. Při analýze pomocí dolování dat, nejsou předem známy žádné otázky, na které nám tato analýza dá odpovědi. Neví se ani, zda bude dosaženo nějakých využitelných výsledků. Mezi metody, které se v dnešní době používají pro dolování dat, patří například (Rud, 2001): • regresní metody – lineární a nelineární regresní analýza, neuronové sítě • klasifikace – rozhodovací stromy, diskriminační analýza • segmentace (shlukování) – genetické algoritmy, shluková analýza, Kohonenovy mapy • predikce v časových řadách – Boxova-Jenkinsonova metoda, autoregresní modely Oblast dolování dat také využívá dvou základních modelů. Prvním z nich je tzv. deskriptivní model. Tento model popisuje nalezené vzory a vztahy v datech, které mohou ovlivnit rozhodování . Druhým modelem je model prediktivní. Tento prediktivní model umožňuje předvídat budoucí hodnoty atributů na základě nalezených vzorů v datech (Rud, 2001).

2.4

Trendy Business Intelligence

Oblast Business Intelligence je neustále se rozvíjející oblastí. Vzhledem k této skutečnosti můžeme sledovat určité trendy, které se v této oblasti vyskytují. Cílem této kapitoly bude tyto trendy stručně popsat a přiblížit tak čtenáři pohled na předpokládaný rozvoj oblasti Business Intelligence. 2.4.1

Mobilní BI

Hlavní výhodou mobilních BI řešení je to, že uživatel má své informace neustále k dispozici a nemusí tak na získání těchto informací čekat do okamžiku, kdy dorazí do kanceláře a pomocí počítače získá požadované informace. Mezi zařízení podporující mobilní BI patří mobilní telefony, tablety, komunikátory, čtečky elektronického obsahu. Značný pokrok ve vývoji aplikací pro mobilní zařízení nastal v posledních letech především díky rozvoji výpočetního výkonu mobilních zařízení, rozvoji vývojových prostředí pro mobilní platformy, zabezpečení mobilních zařízení nebo také díky lepšímu pokrytí 3G sítí.

2.4

Trendy Business Intelligence

2.4.2

22

Cloud BI

Cloud computing je velmi oblíbeným řešením v téměř všech softwarových segmentech. Ovšem v oblasti Business Intelligence toto neplatí. Salesforce.com a NetSuite díky svým cloud řešením překazily dominantní postavení firem SAP a Oracle na trhu business aplikací. To stejné se nedá říci o oblasti Business Intelligence, kde společnosti nabízející cloud řešení (Birst, GoodData, Indicee) stále zabírají minoritní pozici na trhu. Toto by se ovšem mělo změnit v roce 2014 a cloud řešení by se konečně měly rozrůst i do oblasti Business Intelligence. Hlavními znaky, které tomuto rozvoji nasvědčují, jsou (Howson, 2014): • Společnosti Birst a GoodData obdržely dotace na financování rizikového kapitálu • Společnost Jaspersoft spustila Cloud Analytics jako pay-as-you-go službu v Amazon Web Services a tento krok zaznamenal výrazný úspěch • Společnost Microsoft spustila aplikaci PowerBI • Společnost Oracle má v úmyslu spustit BI aplikaci na bázi SaaS 2.4.3

Samoobslužné BI

Samoobslužné BI umožňuje společnostem vytvářet vlastní analýzy bez nutnosti znalosti například SQL či MDX. Výhodou samoobslužného BI řešení je úspora nákladů a času IT oddělení společnosti a možnost zaměření tohoto oddělení na řešení jiných úkolů. Tímto krokem ovšem roste důraz na jednoduchost a intuitivnost nástrojů pro samoobslužné BI z toho důvodu, aby i lidé, kteří jsou méně technicky gramotní zvládli práci s těmito nástroji. Nevýhodou tohoto řešení může být tvorba neadekvátních reportů a následné špatné rozhodnutí vedení společnosti. 2.4.4

Open source BI

Open source řešení BI jsou takové produkty, které jsou volně šiřitelné, každý si může přizpůsobit obsah a funkcionalitu svým požadavkům a mají daleko nižší pořizovací náklady než produkty od klasických poskytovatelů BI produktů. Vzhledem k nízké pořizovací ceně jsou tyto nástroje využívány především malými a středně velkými společnostmi, které si mohou detailněji specifikovat vlastní požadavky a získat tak pouze požadovanou funkcionalitu. V dnešní době jsou již open source BI nástroje na velmi dobré úrovni a z pohledu funkcionality jsou srovnatelné s pokročilým proprietárním řešením od společností Microsoft, Oracle, IBM či SAP. Další výhoda open source řešení je podpora široké komunity, která dokáže pohotově reagovat na dotazy, podněty nebo požadavky na změny, úpravy či vylepšení. Mezi nejznámější poskytovatele open source BI řešení patří například JasperSoft, Pentaho, SpagoBI nebo OpenI (Howson, 2014).

2.4

Trendy Business Intelligence

2.4.5

23

Big Data

Jako Big Data jsou označována taková data, která svým charakterem převyšují schopnosti zpracování dat u konvenčních databázových systémů v rozumném čase. Tato data jsou velmi objemná, často se mění jejich umístění nebo nezapadají do databázové struktury. Vstupní data pro Big Data systémy mohou být získány například ze sociálních sítí, logů webových serverů, senzorů sledujících provoz na silnicích, meteorologických radarů nebo bankovních transakcí. Pro popis charakteristik těchto velkých objemů dat se využívá pojmu 3V, který označuje tři počáteční písmena z anglických slov (Dumbill, 2012): • Volume (Objem) – množství relevantních dat pro daný subjekt narůstá exponenciálně každým rokem. Pro takovéto množství dat již nejsou běžné databázové systémy dostatečné, a tak je zapotřebí využít nových prostředků pro zpracování těchto dat. Jedním z nástrojů, které umí pracovat s Big Data, je open source framework Hadoop, který zvládne zpracování petabytů a exabytů dat současně z více uzlů. • Velocity (Rychlost) – rychlost vzniku dat a potřeb jejich okamžitého zpracování či analyzování stoupá s neustále pokračující digitalizací velkého množství lidských činností. Příkladem mohou být internetové obchody, které ukládají veškeré kliknutí všech uživatelů a ze všech zařízení v jeden okamžik. Rychlost vyhodnocení těchto kliknutí a případné nabídnutí dalšího produktu k nákupu může být pro společnost nesmírnou konkurenční výhodou. • Variety (Různorodost) – data mohou být získávána ve velkém množství různorodých formátů a struktur. Mohou to být například nestrukturované textové soubory ze sociálních sítí, data přímo ze senzorů, multimediální data, atd. Předpokládá se, že během následujících dvou až tří let se bude objem trhu ročně zvětšovat o 31,7 %, a to až na 23,8 miliardy amerických dolarů v roce 2016. Podle analytiků je jedním z hlavních problémů nedostatek zaměstnanců s potřebnými znalostmi a zkušenostmi, který podle nich firmy přiměje k využívání cloudových řešení pro Big Data, případně jednoúčelová zařízení. Důraz bude kladem na vysokou míru automatizace nasazení i provozu a řada firem bude provoz příslušných technologií outsourcovat (Mandik, 2013).

3

API SPOLEČNOSTI FIO BANKA, A. S.

3 3.1

24

API společnosti Fio banka, a. s. Funkční popis

Aplikační programové rozhraní, které vyvinula společnost Fio banka, a. s., slouží k propojení uživatelů s Fio bankovním systémem a umožňuje těmto uživatelům získávání dat z účtů vedených právě u této společnosti. Toto rozhraní může být použito pro napojení účetních programů nebo pro automatické strojové zpracování pohybů či výpisů z bankovního systému. Komunikace mezi klientem a bankovním systémem probíhá pomocí SSL protokolu s minimálně 128bitovým šifrováním. Prvotním předpokladem ke správné komunikaci mezi bankovním systémem a klientem je oprávnění k přístupu do tohoto bankovního systému, tedy znalost přístupových údajů k danému bankovnímu účtu. Po připojení do internetového bankovnictví k danému účtu je zapotřebí vygenerovat token, který je nutným předpokladem k přístupu k datům pomocí bankovního API. Tento token se skládá ze 64 znaků a data mohou být stahována pět minut po vygenerování tohoto tokenu. Tento token je unikátní pro každý účet, a tak chce–li uživatel stahovat data z více účtů, musí si vygenerovat unikátní token pro každý z těchto účtů. Pro stažení dat z bankovního účtu není zapotřebí být přihlášen do internetového bankovnictví, odpovědi na žádosti o data z bankovního systému se získávají prostřednictvím definovaného rozhraní, které je přístupné přes URL. Rozhraní má různé metody pro získávání strukturovaných dat nebo jejich nastavení (Fio banka, 2012).

3.2

Získání tokenu

Pro získání tokenu je zapotřebí, aby se uživatel přihlásil do svého internetového bankovnictví ke zvolenému účtu, ze kterého chce stahovat data, a následně se v menu s označením Přehledy“ dostal do kategorie Informace o účtu“. V této kategorii ” ” má možnost podívat se na základní informace o účtu (zůstatek, datum zřízení, číslo účtu, atd.), spravovat oprávnění k přístupu k účtu a také vygenerovat tento token pro přístup k informacím na účtu pomocí API. Jakmile uživatel klikne na možnost Přidat token“, je mu zobrazeno okno s názvem Aktivace tokenu k účtu“. ” ” První položkou, která je v tomto okně uvedena, je 64 znakový řetězec označující tento token. Dále má možnost zvolit, zda se pomocí tohoto tokenu má účet pouze sledovat, nebo zda má být také povoleno zadávání platebních příkazů a inkasa. Dále je zapotřebí vyplnit pojmenování tokenu. Tato možnost slouží pouze k lepší orientaci mezi tokeny, pakliže jich uživatel má na svém účtu hned několik. Poslední položkou, kterou uživatel musí vyplnit, je pole s označením Platnost do “. Do tohoto pole ” uživatel zadá datum, do kdy má být tento token aktivní. Datum se zde zadává ve tvaru dd.mm.rrrr. Vyplněné údaje se odešlou ke zpracování po kliknutí uživatele na tlačítko Ode” slat“. Pro aktivaci tokenu je nezbytné, aby byl ověřen pomocí SMS podpisu. Uživatel tedy musí vyčkat na SMS, ve které je autorizační kód k tomuto tokenu, který zadá

3.3

URL struktura dotazu pro získání dat

25

do patřičného pole. Po zadání tohoto autorizačního kódu je zapotřebí vyčkat alespoň 5 minut k aktivaci tohoto tokenu. Po této době je již token plně aktivní a uživatel jej může začít plně využívat v rámci API.

3.3

URL struktura dotazu pro získání dat

Pro získání dat z Fio API se využívá URL. Doporučený nejmenší interval dotazu na stejný token je 30 sekund bez ohledu na typ formátu. Tato data lze získat více způsoby (Fio banka, 2012): • Pohyby na účtu za určené období • Oficiální výpisy pohybů z účtu • Pohyby na účtu od posledního stažení 3.3.1

Pohyby na účtu za určené období

Data získaná pomocí tohoto dotazu vrátí jako výsledek transakce, které byly provedeny v definovaném období. Struktura dotazu je následující: https://www.fio.cz/ib api/rest/periods/{token}/{datum od}/{datum do}/ transactions.{format} Token zde označuje unikátní 64 znakový řetězec, který byl vygenerovaný v rámci internetového bankovnictví pro daný účet. Datum od a datum do jsou časové milníky, které vymezují časové období, pro které má být výpis transakcí generován. Tato data se zadávají ve formátu rok–měsíc–den. Poslední proměnnou je zde formát, který může nabývat jedné z hodnot, které jsou popsány v kapitole Podporované formáty seznamu pohybů. 3.3.2

Oficiální výpisy pohybů z účtu

Druhým typem výpisu, který lze z Fio API získat, je oficiální výpis pohybů z účtu. Jedná se o jednotlivé výpisy za daný měsíc a rok. Struktura dotazu je následující: https://www.fio.cz/ib api/rest/by-id/{token}/{year}/{id}/ transactions.{format} Token zde opět označuje unikátní 64 znakový řetězec vygenerovaný v rámci internetového bankovnictví. Proměnná year značí rok, za který má být daný výpis získán. Id zde představuje číslo výpisu. Jestliže uživatel například požaduje výpis za leden pro rok 2014, bude proměnná id nabývat hodnoty 1 a proměnná year hodnoty 2014. Ovšem v případě, že uživatel založil účet například v červenci roku 2013 a zadal by do proměnné year hodnotu 2013 a do proměnné id hodnotu 1,

3.4

Podporované formáty seznamu pohybů

26

získá tak výpis za měsíc červenec pro rok 2013. Proměnná formát opět nabývá jedné z hodnot, které jsou popsány v kapitole Podporované formáty seznamu pohybů. 3.3.3

Pohyby na účtu od posledního stažení

Třetím typem dat, jaká lze z tohoto API získat, jsou nové transakce, které byly provedeny od posledního stažení. Struktura URL dotazu je opět velmi podobná a vypadá následovně: https://www.fio.cz/ib api/rest/last/{token}/transactions.{format} Token zde opět reprezentuje 64 znakový unikátní řetězec, formát je opět jeden z formátů, které jsou popsány v kapitole Podporované formáty seznamu pohybů.

3.4 3.4.1

Podporované formáty seznamu pohybů XML

Xml seznam pohybů se skládá ze dvou hlavních částí – Info a TransactionList. V části Info jsou získány informace o účtu, počátečních a koncových stavech na tomto účtu a období, za které jsou dané transakce zobrazeny, identifikace výpisu, posledního stažení pohybů. V části TransactionList jsou získány pohyby na účtu za zvolené období. O jednotlivých pohybech na účtu lze získat následující informace: ID pohybu, datum pohybu, velikost a měna přijaté/odeslané částky, číslo a název protiúčtu, číslo a název banky protiúčtu, konstantní, variabilní a specifický symbol, zpráva pro příjemce, typ operace, jméno osoby, která zadala příkaz, upřesňující informace, bankovní identifikační kód a ID pohybu. 3.4.2

OFX

Výpis typu Ofx se skládá z následujících částí (Fio banka, 2012): • STATUS – vrací chybová hlášení. Toto hlášení obsahuje chybový kód, závažnost chyby a textový popis chyby. • BANKACCTFROM – poskytuje data o účtu. Mezi tato data patří číslo banky, číslo účtu a typ účtu (CHECKING – běžný účet, SAVINGS – Fio konto, termínovaný vklad, atd., CREDITLINE – úvěr, kontokorent, hypotéka). • BANKTRANLIST – obsahuje jednotlivé pohyby na účtu ve zvoleném období. • STMTTRN – vztahuje se již ke konkrétnímu pohybu. Obsahuje typ transakce, částku pohybu, ID pohybu, typ operace a komentář. • BANKACCTTO – obsahuje informace o protiúčtu (číslo banky a účtu protiúčtu, typ protiúčtu).

3.4

Podporované formáty seznamu pohybů

3.4.3

27

GPC

GPC seznam pohybů se skládá ze dvou částí. První část je označena jako Data – vý” pis v Kč“ a obsahuje informace o účtu – struktura této části je zobrazena na obrázku 5. Druhá část se nazývá Data – obratová položka“ a zachycuje jednotlivé pohyby ” na účtu – struktura této části je znázorněna na obrázku 6. Obě tyto části mají pevnou délku 130 znaků a mezi jednotlivými údaji v záznamu není žádný oddělovač. Každý údaj má vymezen předem definovaný počet míst, které zabírá. Jestliže hodnota nedosahuje tohoto počtu míst, je tato hodnota doplněna na požadovanou délku pomocí znaku nula, který se vkládá zleva (Fio banka, 2012). Byty 1–3 4–19 20–39 40–45 46–59 60 61–74 75 76–89 90 91–104 105 106–108 109–114 115–128 129–130

Popis "074" = označení typu záznamu "Data - výpis v Kč" přidělené č. účtu s vodícími nulami 20 alfanumerických znaků zkráceného názvu účtu, doplněných mezerami zprava datum starého zůstatku ve formátu DDMMRR starý zůstatek v haléřích 14 numerických znaků s vodícími nulami znaménko starého zůstatku, 1 znak "+" nebo "-" nový zůstatek v haléřích 14 numerických znaků s vodícími nulami znaménko nového zůstatku, 1 znak "+" nebo "-" obraty debet (MD) v haléřích 14 numerických znaků s vodícími nulami znaménko obratů deber (MD), 1 znak "0" nebo "-" obraty kredit (D) v haléřích 14 numerických znaků s vodícími nulami znaménko obratů kredit (D), 1 znak "0" nebo "-" pořadové číslo výpisu datum účtování ve formátu DDMMRR (vyplněno 14 znaky mezera z důvodu sjednocení délky záznamů) ukončovací znaky CR a LF

Obrázek 5: Struktura Data – výpis v Kč“. ”

3.4.4

CSV

CSV je jednoduchý souborový formát, který se používá pro výměnu tabulkových dat. Skládá se z řádků, ve kterých jsou jednotlivé údaje odděleny středníkem. Výpis ve formátu CSV je také rozdělen do dvou částí – hlavičky a jednotlivých pohybů na účtu. Jednotlivé položky v každé této části jsou totožné s položkami ve formátu XML. 3.4.5

HTML

Údaje, které lze získat z výpisu ve formátu HTML, jsou totožné s údaji ve formátech XML či CSV. Je zde opět rozložení do dvou částí, kdy v první jsou informace o účtu a ve druhé opět informace o jednotlivých pohybech na účtu. Každá z těchto částí je reprezentována pomocí samostatné tabulky, kde záhlaví obsahuje názvy jednotlivých

3.4

Podporované formáty seznamu pohybů

Byty 1–3 4–19 20–35 36–48 49–60 61 62–71 72–81 82–91 92–97 98–117 118 119–122 123–128 129–130

28

Popis "075" = označení typu záznamu "Data - obratová položka" přidělené číslo účtu 16 numerických znaků s vodícími nulami číslo účtu 16 numerických znaků s vodícími nulami (případně v pořadí předčíslí + číslo účtu) číslo dokladu 13 numerických znaků částka v haléřích 12 numerických znaků s vodícími nulami kód účtování vzta žený k čí slu účtu: 1 = položka debet, 2 = položka kredit , 4 = storno položky debet, 5 = storno položky kredit variabilní symbol 10 numerických znaků s vodícími nulami konstantní symbol 10 numerických znaků s vodícími nulami ve formátu BBBBKSYM, kde: BBBB - kód banky, KSYM - konstantní symbol specifický symbol 10 numerických znaků s vodícími nulami "000000" = valuta, platba v ČR - datum splatnosti ve formátu DDMMRR 20 alfanumerických znaků zkráceného názvu klienta, doplněno mezerami zprava "0 " "0203" = kó dměny pro Kč datum splatnosti ve formátu DDMMRR ukončovací znaky CR a LF

Obrázek 6: Struktura Data – obratová položka v Kč“. ”

položek (accountID, bankID, atd.). Tabulka, která obsahuje informace o účtu, obsahuje pouze jeden řádek. Tabulka zachycující pohyby na účtu obsahuje tolik řádků, kolik bylo na účtu provedeno transakcí za dané období. 3.4.6

JSON

Datový formát JSON je určen k přenosu dat, která mohou být organizována v polích nebo agregována v objektech a je nezávislý na počítačové platformě. JSON je založen na podmnožině programovacího jazyka JavaScript. Vstupem může být libovolná datová struktura, ale výstupem je vždy řetězec. Data se zde nachází ve dvou hlavních strukturách, ve dvojicích název:hodnota a v tříděných seznamech hodnot. Seznam pohybů se skládá opět ze dvou částí – Info a TransactionList. Jednotlivé položky těchto částí jsou shodné s formáty XML, CSV či HTML. 3.4.7

STA (MT940)

Formát souboru výpisů z účtu je založen na mezinárodním SWIFT formátu ve tvaru MT940. Tento formát je pouze pro výpisy a nelze jej použít pro výpis pohybů na účtu. Jeden logický výpis z účtu může mít jednu nebo více stránek a každá tato stránka se skládá ze záhlaví, textového bloku a ukončovacích znaků. Struktura výpisů obsažených v souboru vypadá tak, že každý výpis je uvozen pomocí speciálních kódů uzavřených ve složených závorkách {1:} {2:}. Hlavní tělo výpisu se nachází v poli {4:}, uzavřeném rovněž do složených závorek. Maximální délka zprávy je 2000 znaků (Fio banka, 2012).

4

METODICKÁ VÝCHODISKA PRÁCE

4

29

Metodická východiska práce

V předešlých kapitolách se práce věnovala představení nejdůležitějších teoretických poznatků v oblasti Business Intelligence a také popisu API společnosti Fio banka, a. s. Tato část diplomové práce bude věnována popisu postupů, nástrojů a metodik, kterých bylo využito při vývoji aplikace, která uživatelům umožní analýzu vlastních finančních dat získaných prostřednictvím API společnosti Fio banka, a. s.

4.1

Specifikace aplikace

Hlavním cílem práce je navrhnout a implementovat aplikaci, která uživateli umožní provádět individuální analýzy vlastních finančních dat. Uživatel bude moci transakce zařadit do vlastních kategorií, sestavit si individuální plán finančních aktivit a bude mít možnost detailnějšího popisu konkrétních transakcí. Hlavní důraz bude kladen na to, aby si uživatelé mohli aplikaci co nejvíce přizpůsobit vlastním požadavkům a nebyli omezeni pouze na konkrétní implementaci. Aplikace nebude přizpůsobena pro odesílání transakcí, a tak bezpečnost jejich dat nebude touto aplikací žádným způsobem ohrožena. Výstupem bude sešit aplikace Power Pivot, který uživatelům bude umožňovat nejrůznější analýzy a přehledy nad jejich daty. Bude zde také prostor pro tvorbu vlastních analýz dle konkrétních požadavků uživatelů.

4.2

Použitá metodologie

Při vývoji této aplikace je potřeba brát v úvahu, že zde budou dvě na sebe navazující části. V první části je zapotřebí analyzovat data, která lze prostřednictvím Fio API získat a následně na základě této analýzy vytvořit datový model. Tato část aplikace je pro uživatele skryta. Druhá část, která je pro uživatele nejdůležitější, slouží k prezentaci finálních analýz a přehledů. Při tvorbě datového modelu bylo využito standardní metodologie vývoje datového skladu od Ralpha Kimballa. Tato metodologie se ovšem nemohla dodržet naprosto přesně, jelikož v tomto případě nebyl vytvářen plnohodnotný datový sklad, ale pouze datový model. Některé postupy byly ovšem použitelné i v tomto případě, a tak tvorba datového modelu nepřímo vychází z této metodologie. Prvním krokem byla již zmíněná analýza zdrojových dat, v mém případě dat získaných prostřednictvím Fio API, a také definice uživatelských požadavků. Na základě provedené analýzy dat byl navržen dimenzionální model, který byl následně převeden na fyzickou úroveň. Následovala fáze naplnění tohoto datového modelu – tedy fáze ETL procesů. Jakmile byla tato data upravena a načtena do datového modelu, dala se první fáze považovat za ukončenou a následovala práce na prezentační vrstvě aplikace. V prezentační vrstvě aplikace bylo zapotřebí prostředí aplikace přizpůsobit tak, aby uživatel měl jasnou představu o tom, kde se zrovna nachází a jaké typy analýz a přehledů zde může vytvářet. Aplikace byla tvořena pro společnost Intelligent

4.3

Použité nástroje a technologie

30

Technologies, která má ve svém portfoliu podobnou aplikaci iDoklad, a tak byl vzhled aplikace volen na základě aplikace iDoklad.

4.3

Použité nástroje a technologie

Jedním z hlavních požadavků společnosti Intelligent Technologies bylo, aby uživatelé mohli provádět analýzy v prostředí aplikace Power Pivot. Nebylo zde tedy zapotřebí analyzovat konkurenční nástroje a následně vybrat jeden z nich. 4.3.1

Power Pivot

Power Pivot je doplněk do aplikace Microsoft Excel, který dává uživatelům možnost seznámit se s prostředím Self-Business Intelligence. Tento doplněk je podporován od verze Microsoft Excel 2010 a pro tuto verzi je nutné si jej dodatečně stáhnout ze stránek výrobce. Verze Microsoft Excel 2013 má již tento doplněk v sobě integrován. Jedná se o aplikaci, která uživatelům dává možnost jednoduše proniknout do světa tvorby datových skladů bez nutnosti studování dlouhých návodů k obsluze daného programu. I přesto, že je to doplněk do aplikace Microsoft Excel, nejedná se o tabulkový procesor. Jeho alternativou je tedy spíše Analysis Services než samotný Excel. Projekty, které jsou vytvořeny prostřednictvím aplikace Power Pivot, jsou ukládány do souborů s koncovkou xlsx. Tato data ovšem nejsou ukládána do listů v aplikaci Microsoft Excel, nýbrž do samostatné databáze doplňku Power Pivot. Maximální možný počet záznamů v tabulkách Power Pivot je v řádech milionů, tedy daleko za hranicemi možností samotného Microsoft Excel. Power Pivot umožňuje import dat z více zdrojů a následné jejich propojení pomocí relací. Díky tomuto propojení je následně možné tvořit nejrůznější analýzy nad velkým množstvím dat z různých zdrojů a také je přehledně znázornit pomocí klasických nástrojů programu Microsoft Excel – kontingenční tabulka, graf či jiné objekty. Možnosti pro import dat do aplikace Power Pivot lze rozdělit do následujících skupin (Collie, 2012): • Z již existující tabulky v programu Microsoft Excel – jedná se o nejrychlejší způsob, jakým lze data importovat z prostředí programu Microsoft Excel do prostředí doplňku Power Pivot. Jestliže jsou v této tabulce provedeny jakékoli úpravy, Power Pivot je automaticky rozezná a aktualizuje tuto tabulku i ve svém prostředí. Možnými nevýhodami tohoto řešení může být nemožnost odkazování se na tabulky v jiných sešitech a dále při zpracování velkého množství dat (desetitisíce záznamů) může dojít k přerušení práce programu a uživatel může přijít o nezálohovanou práci. Při tomto způsobu načítání dat Power Pivot defaultně automaticky aktualizuje kopii této tabulky pokaždé, když je otevřeno okno aplikace Power Pivot, což vede ke zpomalení práce na projektu. Možnost této aktualizace se dá ovšem v nastavení potlačit a tím tento nedostatek odstranit.

4.3

Použité nástroje a technologie

31

• Z databáze – pomocí této nabídky lze importovat takřka neomezené objemy dat například z aplikace Access, ze systému SQL Server či služby Analysis Services. Další výhodou je tvorba kalkulovaných sloupců na úrovni databáze a následně jejich import do aplikace Power Pivot a nikoli jejich tvorba až v tomto doplňku – obzvláště, pokud importovaná data jsou velmi objemná. Nevýhodou této možnosti může být fakt, že ne vždy jsou požadovaná data k dispozici ve formě databáze či na databázovém serveru a nelze data převádět z jednoho typu databáze na jiný. Nelze tedy například získat databázi prostřednictvím Microsoft Access a následně ji nahrát na SQL Server. • Z datové služby – zde může uživatel zvolit jednu ze tří možností, jaká data chce importovat. První možností je import dat z webu Windows Azure Marketplace. Zde jsou uživatelům k dispozici nejrůznější data, která mohou uživatelé ve svých analýzách vyžadovat. Může se jednat například o historický vývoj daní v dané zemi, stav počasí v jednotlivých dnech, vývoj tržních cen dané komodity, atd. Druhou možností je import dat pomocí možnosti Návrh dat v relaci. Tato volba způsobí to, že Windows Azure Marketplace automaticky navrhne data, která nějak souvisí s již dříve importovanými daty na základě kategorií, které Power Pivot daným datům přiřadil. Třetí možností je import dat z datového kanálu OData pomocí URL daného webového serveru. • Z jiných zdrojů – tato možnost v sobě skrývá veškeré předchozí možnosti a přidává také některé nové zdroje. Možnosti importu dat jsou zde rozděleny na čtyři kategorie. První kategorií jsou Relační databáze, kde má uživatel na výběr z mnoha databázových systémů. Jedná se o všechny systémy, které jsou uvedeny v kategorii Z databáze ve druhém bodě tohoto výčtu a dále například o připojení k databázi Oracle, Teradata, Sybase či Infomix. Druhou kategorií jsou Multidimenzionální zdroje, kde je k dispozici pouze Microsoft Analysis Services, což umožňuje připojit se k datové krychli služby SQL Server Analysis Services a následně importovat data vrácená z dotazu MDX. Třetí kategorií je kategorie datové kanály, která obsahuje shodné možnosti importu, jako jsou ty, které jsou uvedeny v kategorii Z datové služby ve třetím bodě tohoto výčtu. Poslední kategorií je kategorie Textové soubory. Tato kategorie obsahuje možnosti importu dat ze souboru aplikace Excel nebo z textového souboru. • Existující připojení – tato možnost umožňuje importovat data z externího zdroje dat, který je vybrán ze seznamu dříve použitých zdrojů. K vytvoření nového sloupec nebo nové míry se v aplikaci Power Pivot využívá jazyka DAX (Data Analysis Expressions). Tento jazyk se velice podobá vzorcům, které jsou běžně využívány v programu Microsoft Excel, ovšem existuje zde pár odlišností. Jednou z těchto odlišností je to, že výstupem funkce může být i tabulka. Při vytváření nového sloupce ze dvou již existujících se vzorec pro vytvoření nového sloupce zapisuje do prvního prázdného sloupce. Všechny řádky takto vytvořeného sloupce obsahují stejný vzorec a nelze zde tedy měnit vzorce v jednotlivých

4.3

Použité nástroje a technologie

32

řádcích tak, jak tomu při práci se vzorci v programu Microsoft Excel. Jestliže chceme odlišit chování jednotlivých buněk v tomto sloupci, musíme využít funkce IF(). Reference na jiné buňky v prostředí Power Pivot vždy vypadají jako [Název sloupce], čímž se zvyšuje přehlednost těchto formulí. Na sloupce se lze také odkazovat pomocí ’NázevTabulky’[Název sloupce], což najde uplatnění v případech, kdy uživatel potřebuje vytvořit nový sloupec z více tabulek. Míry jsou využívány především v případech, kdy uživatel požaduje vytvoření například součtu, průměru či dalších funkcí pro daný sloupec. Tyto míry se zapisují do speciální části okna Power Pivot, která se nachází pod samotnými daty. Tímto způsobem jsou vytvářeny explicitní míry, které mohou být následně využity při práci s KPI. 4.3.2

Power Query

Dalším doplňkem aplikace Microsoft Excel, který byl při vytváření Power Pivot sešitu využit, je nástroj Power Query. Tento doplněk poskytuje komplexní funkce pro získávání dat a jejich transformaci. Tento doplněk umožňuje kombinovat data z různých zdrojů a reorganizovat je k přípravě na další analýzy v analytických nástrojích – například výše zmíněný doplněk Power Pivot, Power View nebo Power Map.

5

TVORBA DATOVÉHO MODELU

5

33

Tvorba datového modelu

V této kapitole budou detailně popsány veškeré postupy, které vedly k naplnění cíle této diplomové práce. Základním prvkem aplikace, bez kterého by nebylo možné provádět žádné analýzy nad vlastními daty, je datový model. Proto je velmi důležité správně navrhnou a implementovat všechny podstatné části, ze kterých se datový model skládá. Jednotlivé kroky od návrhu samotného datového modelu až po jeho naplnění daty budou popsány v následujících kapitolách. Bude zde tedy názorně představen postup tvorby datového modelu aplikace pro podporu Self-Business Intelligence v prostředí programu Microsoft Excel s doplňky Power Pivot a Power Query.

5.1

Analýza vstupních dat

Jestliže chceme vytvářet aplikaci, která bude sloužit jako nástroj pro podporu SelfBusiness Intelligence, je nutné se nejdříve podrobně seznámit s daty, se kterými budeme pracovat. V tomto případě tedy s daty, která lze získat prostřednictvím Fio API. Této problematice je věnována kapitola 3 této práce. 5.1.1

Výběr formátu dat

Jak je již zmíněno v kapitole 3 této práce, Fio API nabízí velké množství různých formátů, ve kterých lze data o finančních transakcích uživatele získat. Na základě požadavku společnosti Intelligent Technologies, tedy nutnosti využít nástroj Microsoft Excel s doplňkem Power Pivot, bylo zapotřebí analyzovat možnosti importu dat do tohoto nástroje. Power Pivot je velmi užitečný nástroj a umožňuje uživateli import dat nejrůznějších formátů. Ovšem žádný z formátů, které jsou produkovány Fio API, nebyl vhodný pro přímou práci s datovým modelem, a tak musel být do procesu importu a úpravy dat přidán nový mezičlánek. Všechny formáty, které Fio API produkuje, jsou prosté textové formáty bez žádných vnitřních struktur. Vše, co po importu tohoto typu dat do prostředí Power Pivot bylo získáno, byla jedna tabulka s redundantními daty. Bylo tedy zapotřebí využít doplňku Power Query, který umožňuje uživateli lépe spravovat importovaná data. Velkou výhodou tohoto doplňku je možnost importovat data z více různých zdrojů a s těmito daty následně pracovat. Analýza doplňku Power Query ukázala, že podporuje import většiny formátů, které produkuje Fio API. Po detailnější analýze jednotlivých formátů bylo ovšem zjištěno, že některé formáty mají při importu tendenci ztrácet data. V některých případech nebylo ani možné rozeznat, zda se opravdu jedná o data z Fio API, nebo zda byla tato data pouze náhodně vygenerována. Jedinými dvěma formáty, se kterými nebyl při importu žádný problém, byly formáty XML a CSV. Zbývalo tedy rozhodnout, který z těchto datových formátů bude v práci využit. Výhodou formátů XML je přehledná stromová struktura dat, jasně definované

5.2

Dimenzionální modelování

34

hodnoty atributů a snadná editace údajů. Formát CSV v porovnání s formátem XML nemá stromovou strukturu (je tedy o poznání kratší) a veškeré hodnoty jsou odděleny čárkami, což vede k horší orientaci v souborech tohoto typu. Na první pohled by se mohlo zdát, že zisk atributů a k nim přiřazených hodnot bude tedy snadnější v případě formátu CSV. Ovšem nástroj Power Query je pro práci s XML soubory velmi dobře přizpůsoben, a tak je zde práce s těmito formáty naprosto srovnatelná. Bylo ovšem zapotřebí rozhodnout se pouze pro jeden formát. z důvodu lepší přehlednosti zdrojových dat byl vybrán formát XML, ve kterém lze snadněji dohledat atributy a jejich hodnoty pro kontrolu správnosti načtených dat. Ukázka syntaxe výpisu konkrétní transakce ve formátu XML je zobrazena na obrázku 7.

Obrázek 7: Syntaxe transakce ve formátu XML.

5.2

Dimenzionální modelování

Správný návrh datového modelu je základním předpokladem tvorby relevantních analýz nad těmito daty. Po analýze vstupních dat z Fio API začala fáze návrhu tohoto datového modelu. Při návrhu bylo použito schéma sněhové vločky, jelikož

5.2

Dimenzionální modelování

35

u některých dimenzí bylo zapotřebí jejich vyšší granularity. Návrh datového modelu se skládá z jedné faktové tabulky a devíti dimenzí. 5.2.1

Časová dimenze

První dimenzí, která nevychází ze vstupních dat, ale tato data jsou na ní nesmírně závislá a měla by se vyskytovat prakticky v každém datovém skladu, je dimenze časová. Díky této dimenzi bude mít uživatel možnost analyzovat svá data v čase a mít tak přehled o příjmech či výdajích v různých časových horizontech. Tato dimenze obsahuje celkem osm atributů a jednu hierarchii. Těmito atributy jsou DateKey, Datum, Měsíc, Den, Rok, Čtvrtletí, Název měsíce a poté také cizí klíč, kterým je propojena tato dimenze s dimenzí, která slouží pro správu plánu finančních aktivit. Obsahuje také časovou hierarchii, která umožňuje analyzovat finanční transakce na základě hierarchie Rok – Čtvrtletí – Měsíc. 5.2.2

Dimenze Protiúčet

Dále je zde dimenze Protiúčet, která zajišťuje správu protiúčtů, mezi kterými došlo mezi uživatelem a druhou stranou dané finanční transakce ke komunikaci a převodu finančních prostředků. Tato dimenze obsahuje atributy ID protiúčtu, Název protiúčtu, Číslo protiúčtu, Kód banky a Název banky, u které je tento protiúčet zřízen. Zde by bylo možno uvážit, zda není vhodné vytvořit další dimenzi, která by se starala o údaje spojené s bankami protiúčtů. Navrhované řešení se ovšem zdálo být plně dostačující, a tak jsem se rozhodl tuto dimenzi nevytvářet. 5.2.3

Dimenze Typ platby

Další dimenzí, která vychází přímo ze vstupních dat, je dimenze Typ platby. Tato dimenze se stará o správu údajů o typu konkrétní finanční transakce. Obsahuje atributy ID typ a také Název typu platby. Těmito typy plateb mohou být například bezhotovostní příjem, platba kartou, poplatek platební karta, vklad pokladnou, převod PayU, atd. 5.2.4

Dimenze Měna

Jelikož mohou být účty vedeny ve více měnách a transakce mohou taktéž probíhat v různých měnách, je zde také dimenze Měna. Tato dimenze obsahuje atributy ID měny, Celý název měny a Zkratka měny, která je uváděna u transakcí. Fio API poskytuje ovšem pouze tuto zkratku, a tak zde bylo nutno přiřadit tabulku, která obsahovala zkratky všech měn a jejich celé názvy.

5.2

Dimenzionální modelování

5.2.5

36

Dimenze Příkaz

Dimenze Příkaz se stará o evidenci údajů o tom, kdo danou transakci provedl a jaký komentář byl u transakce uveden. Atributy této dimenze tedy jsou ID pohybu, Jméno uživatele, který transakci provedl a Komentář. 5.2.6

Dimenze Uživatel

Dimenze, která má na starosti správu údajů o druhém účastníkovi platební transakce, se nazývá dimenze Uživatel. Tato dimenze svým typem potvrzuje volbu schématu sněhové vločky, jelikož je na ni napojena dimenze Adresa. V dimenzi uživatel se nachází atributy ID uživatele, Název uživatele a klíč, pomocí kterého je tato dimenze propojena s dimenzí Adresa. Dimenze Adresa se stará o správu adresy uživatele a obsahuje atributy ID adresy, Město, Stát a také hierarchii, které umožňuje vytvářet přehledy na základě hierarchie Stát – Město. 5.2.7

Dimenze Kategorie

Aby si uživatelé mohli své transakce přiřazovat do vlastních kategorií, bylo zapotřebí vytvořit také dimenzi Kategorie. Tato dimenze již nepřebírá žádná data z Fio API, ale uživatelé její obsah vytváří samostatně na základě vlastních požadavků. Mohou si zde tedy například vytvořit kategorii Zábava, do které si následně přiřadí transakce, které svým charakterem spadají do dané kategorie a následně mohou analyzovat své výdaje či příjmy v těchto kategoriích. Tato dimenze obsahuje pouze dva atributy, kterými jsou ID kategorie a Název kategorie. 5.2.8

Dimenze Plán

Jedním z požadavků společnosti Intelligent Technologies na tuto aplikaci bylo to, aby si uživatelé mohli vytvářet vlastní plán svých finančních aktivit. Byla tedy vytvořena dimenze Plán, která tuto funkcionalitu zajišťuje. Vytváření tohoto plánu by mělo probíhat být na základě určení sumy výdajů či příjmu v rámci jednotlivých let, měsíců a kategorií. Uživatel tedy bude moci zvolit, že například v červenci roku 2014 plánuje utratit 20 000 Kč v rámci kategorie dovolená. Zpětně pak bude moci posoudit, zda tento svůj plán dodržel, popřípadě o kolik překročil výdaje v této kategorii v daném měsíci daného roku. Aby bylo možné takto analyzovat tato data, bylo zapotřebí propojit dimenzi Plán s dimenzemi Datum a Kategorie na základě cizích klíčů. Dimenze Plán tedy obsahuje atributy Název kategorie, Plán datum key, Plánované výdaje, Rok a Měsíc těchto plánovaných výdajů. 5.2.9

Faktová tabulka

Centrálním bodem návrhu datového modelu je faktová tabulka, která obsahuje základní údaje o měřitelných hodnotách, v tomto případě se jedná především o finanční

5.3

37

ETL procesy

částku jednotlivých transakcí, a také cizí klíče, se kterými je tato faktová tabulka propojená s dimenzemi. Finální návrh je zobrazen na obrázku 8.

dimDatum ID_datum Datum_plán_key Datum Rok Čtvrtletí Měsíc Den Název_měsíce Datum_hierarchie Rok Čtvrtletí Název_měsíce

dimProtiúčet ID_protiúčtu Název_protiúčtu Kód_banky Název_banky Protiúčet_ID

dimPlán Plán_datum_ID Rok Měsíc Kategorie Plánované výdaje

dimKategorie ID_kategorie Název_kategorie

dimMěna Objem Pohyb_key Datum_key Měna_key TypPlatby_key Uživatel_key Protiúčet_key Kategorie_key

ID_měna Celý_název Zkratka

dimTypPlatby ID_typ_platby Typ_platby

dimAdresa dimPříkaz ID_pohybu Povedl Komentář

dimUživatel ID_uživatel Uživatel_název Adresa_key

ID_adresa Město Stát Adresa_hierarchie Stát Město

Obrázek 8: Schéma datového skladu.

5.3

ETL procesy

Po dokončení návrhu datového modelu již mohla být zahájena další fáze projektu. Dle metodologie od Ralpha Kimballa by měla následovat fáze realizace návrhu datového skladu na fyzickou úroveň. V mém případě jsem tento krok sloučil s krokem následujícím, tedy s ETL procesy. Při práci s daty v prostředí Power Query jsou automaticky generovány tabulky, které jsou následně importovány do datového modelu aplikace Microsoft Excel (tuto funkcionalitu nabízí Microsoft Excel až od verze 2013). Spojení těchto tabulek (tabulek faktových a dimenzí) do datového modelu pomocí vazeb proběhne až v prostředí doplňku Power Pivot.

5.3

ETL procesy

5.3.1

38

Příprava zdrojových dat

Při vytváření datového modelu došlo ovšem záhy k problému. Jak je popsáno v kapitole 3, nelze aktualizovat data získaná prostřednictvím Fio API častěji než každých 30 sekund. Nebyla zde tedy možnost načítat data pro každou dimenzi zvlášť pomocí tohoto API a muselo být nalezeno alternativní řešení. Jako nejschůdnější řešení se nakonec ukázalo načtení dat z Fio API do listu aplikace Microsoft Excel, který sloužil jako zdrojová data pro tvorbu datového modelu. Tímto tedy byl vyřešen problém, který se týkal možnosti aktualizovat data pomocí Fio API pouze jednou za 30 sekund. Zdrojová data se dále dala rozdělit na dvě hlavní části – část Informace o účtu a část Transakce. Jelikož nebylo potřeba načítat stále dokola obě části, byl pro každou část výpisu vytvořen dotaz v programu Power Query, který sloužil jako vstupní data pro další dotazy, prostřednictvím kterých se získávala data pro jednotlivé dimenze a faktovou tabulku. Pro načtení zdrojových dat to prostředí Power Query se využívá příkaz, který je zobrazen na obrázku 9. CurrentWorkbook() označuje, že Power Query má pracovat se sešitem programu Excel, ze kterého byl spuštěn doplněk Power Query. [Name=”Table all”] označuje název tabulky, ze které se mají načítat data.

Obrázek 9: Syntaxe příkazu pro načítání dat do programu Power Query.

Při vytváření těchto dvou nových tabulek byly zároveň přejmenovány atributy tak, aby jejich identifikace byla v dalších krocích jednodušší. Doposud se pracovalo s atributy například Column 22 name a Column 22 id, nyní byly tyto atributy přejmenovány dle jejich pravého významu, tedy Column 22 name nahradil název Identifikátor pohybu. K přejmenování atributů v prostředí programu Power Query slouží příkaz Table.RenameColumns, jehož syntaxe je zobrazena na obrázku 10.

Obrázek 10: Syntaxe příkazu Rename v programu Power Query.

Bylo také zapotřebí odstranit nevyžadované sloupce tabulky. K tomuto účelu se používá příkaz Table.RemoveColumns, jehož syntaxe je zobrazena na obrázku 11. Zdroj v této syntaxi označuje tabulku zdrojových dat. V tomto případě se jedná o příkaz, který byl zobrazen na obrázku 9 a jedná se tedy o tabulku Table all z aktuálního sešitu aplikace Microsoft Excel. Atributy, které jsou vyčteny ve složených závorkách a vzájemně jsou odděleny čárkou, označují ty sloupce tabulky, které mají být odebrány. Dále bylo také vhodné odebrat časové údaje o tom, v kolik byla daná transakce zanesena do systému. Jelikož tato hodnota neodpovídala skutečnosti, tedy nezobrazovala přesně daný okamžik kdy uživatel danou transakci provedl, ale nabývala

5.3

ETL procesy

39

Obrázek 11: Syntaxe příkazu Remove v programu Power Query.

hodnot pouze 02:00 a 01:00, nebyla tato hodnota důležitá pro další zpracování. Byla ovšem ve společném sloupci tabulky, ve kterém bylo uvedeno datum provedení transakce, a tak bylo zapotřebí tento sloupec rozdělit na dva pomocí oddělovače a následně sloupec s nevyžadovanými údaji odstranit. K tomuto účelu slouží příkaz Table.SplitColumn. Jeho syntaxe je zobrazena na obrázku 12. RemovedColumns12 zde označuje poslední příkaz, který byl proveden před zavoláním příkazu Table.SplitColumn, ”Datum” odkazuje na sloupec tabulky, se kterým chceme pracovat, Splitter.SplitTextByEachDelimiter(”+”) udává, na základě jakého znaku má dojít k rozdělení sloupce ”Datum”, v tomto případě to bude na základě znaku +, jelikož vstupní hodnota ve sloupci Datum je ve formátu YYYY–MM–DD + HH:MM.

Obrázek 12: Syntaxe příkazu SplitColumn v programu Power Query.

Po vytvoření dvou nových sloupců, kdy první z nich obsahoval datum a druhý čas zanesení transakce do systému, byl změněn datový typ prvního jmenovaného sloupce na datový typ Datum. Tato změna se provede pomocí příkazu Table.TransformColumnTypes. Syntaxe příkazu je zobrazena na obrázku 13. V tomto případě je zde změněn datový typ sloupce Datum.1 na datový typ datum a datový typ sloupce Datum.2 na datový typ čas. Změna datového typu u sloupce Datum2. se následně ukázal být jako zbytečný, jelikož byl tento sloupec smazán.

Obrázek 13: Syntaxe příkazu pro změnu datového typu sloupce tabulky.

Jak vypadala konečná syntaxe příkazu pro vytvoření tabulky Transactions, která sloužila jako zdrojová data pro některé dimenzionální tabulky, je zobrazeno na obrázku 14. Druhá tabulka, která byla z dat získaných prostřednictvím Fio API vytvořena, byla tabulka Info. Při tvorbě této tabulky byly využívány veškeré výše zmíněné příkazy. Výsledná tabulka obsahuje data, která svým charakterem nespadají do datového skladu a potenciál této tabulky byl využit až v prezentační vrstvě této aplikace. 5.3.2

Tvorba dimenzí

Dimenze, které byly následně přiřazeny do datového skladu, by se daly rozdělit na dva typy. Prvním typem dimenzí byly dimenze, jejichž data jsou získána pomocí

5.3

ETL procesy

40

Obrázek 14: Sekvence syntaxe příkazů pro vytvoření tabulky Transactions.

Fio API. Přesněji tedy z tabulky Transactions, jejíž implementace byla popsána v předchozí kapitole. Druhým typem dimenzí v této aplikaci jsou takové dimenze, které jsou naplněny daty, které jsou definovány dle požadavků uživatele. Při tvorbě dimenzí již bylo použito více nástrojů programu Power Query než tomu bylo při zpracování dat z Fio API do tabulky Info a tabulky Transactions. Dimenze Měna První dimenzí, která byla vytvořena, byla dimenze Měna. Jelikož vstupní data poskytovala pouze třímístnou zkratku měny, bylo zapotřebí tuto zkratku určitým způsobem propojit s celým názvem této měny. Tento krok nebyl pro aplikaci kritickým a jeho vynechání by nemělo na funkčnost aplikace žádné fatální následky, ale pro uživatele je příjemnější pracovat s celým názvem měny než pouze s její zkratkou. Seznam měn i s jejich zkratkami, které jsou totožné s těmi získanými ze zdrojových dat, je ke stažení na mnoha webových serverech. Jakmile byl tento seznam importován do tabulky programu Microsoft Excel a byl k jednotlivým měnám přiřazen jejich unikátní identifikátor, mohla být zahájena tvorba dimenze Měna. Prvním krokem bylo načtení dat z tabulky Transactions do prostředí programu Power Query a následné odstranění nevyžadovaných atributů tabulky. Odstraněny byly všechny atributy kromě atributu Měna. Dalším krokem bylo odstranění duplicitních hodnot ve sloupci pomocí příkazu Table.Distinct(RemovedColumns). V tomto kroku tedy tabulka obsahovala jeden atribut Měna, který nabýval unikátních zkratek měn, ve kterých na účtu

5.3

ETL procesy

41

probíhaly transakce. Nyní bylo zapotřebí k těmto zkratkám přiřadit správné celé názvy měn a jejich unikátní identifikátory. Ke sloučení dvou tabulek slouží příkaz Table.NestedJoin(), jehož syntaxe je zobrazena na obrázku 15.

Obrázek 15: Syntaxe příkazu pro sloučení tabulek.

Na tomto obrázku je vidět, že došlo ke sloučení aktuální tabulky s tabulkou s názvem Měna PQ na základě atributů Měna (v aktuální tabulce) a Zkratka (v tabulce Měna PQ, jež obsahuje zkratku měny, celý název a unikátní identifikátor měny). NewColumn zde představuje nový atribut, do kterého bude vložena sloučená tabulka. Power Query nevkládá pro každý atribut spojované tabulky nový sloupec do již existující tabulky, ale vloží pouze jeden sloupec, který obsahuje odkaz na takto připojené atributy. Tento sloupec se dá expandovat pomocí příkazu Table.ExpandTableColumn(). Jeho syntaxe je zobrazena na obrázku 16.

Obrázek 16: Syntaxe příkazu pro sloučení tabulek.

Z obrázku je patrné, že dojde k expandování sloupce NewColumn (tento sloupec byl vytvořen na základě sloučení tabulek) a budou zde z tabulky, na kterou tento sloupec odkazuje, vloženy hodnoty Název a ID do sloupců NewColumn.Název a NewColumn.ID. Posledním krokem při vytváření dimenze Měna bylo pouhé přejmenování atributů na požadované názvy. Dimenze Protiúčet Protiúčet identifikuje subjekt, se kterým byla provedena daná transakce. Data pro tuto dimenzi pochází z tabulky Transactions, ze které jsou pomocí příkazu Table.RemoveColumns() odstraněny nepotřebné atributy. Unikátním identifikátorem je zde číslo protiúčtu, které ovšem nemusí být vždy uvedeno. Jestliže transakce nemá uvedeno číslo protiúčtu, je atribut Protiúčet nastaven na hodnotu Neznámý. Dimenze Adresa Zdrojová data pro tuto dimenzi pochází opět z tabulky Transactions. Tato tabulka ovšem neposkytuje přímo atributy Město a Stát, a tak jejich získání probíhá ze sloupce Uživatelská identifikace. Prvním krokem je odstranění nepotřebných sloupců z tabulky Transactions pomocí příkazu Table.RemoveColumns(). Sloupec Uživatelská identifikace má následující formát: Uživatel, Město, Stát, Datum, Částka. Hodnoty jsou od sebe odděleny čárkami, a tak pro získání požadovaných atributů Město a Stát byla využita opět funkce Table.SplitColumn() a jako oddělovač byl zvolen znak čárky. Výstupem bylo pět nových atributů, ze kterých byly pro tuto dimenzi relevantní pouze dva. Zbylé atributy byly odstraněny. Dalším krokem bylo odstranění duplicitních hodnot atributu Město pomocí příkazu Table.Distinct().

5.3

ETL procesy

42

K takto zpracovaným datům byly přidány automaticky generované indexy příkazem Table.AddIndexColumn(), které slouží jako cizí klíče k dimenzi Uživatel. Byl zde také vložen nový záznam, který slouží pro správu transakcí, které nemají specifikovány místo transakce. Dimenze Uživatel Při tvorbě této dimenze byly použity téměř identické postupy jako v případě tvorby dimenze Adresa. Po rozdělení sloupce Uživatelská identifikace na pět nových atributů, byly zachovány pouze atributy specifikující Název uživatele a Město, ve kterém byla provedena transakce. Dále byly odstraněny duplicitní záznamy a u transakcí, které neměly uvedeny město provedení transakce, byla do atributu Město vložena hodnota Neznámé. K takto zpracovaným datům byla následně pomocí příkazu Table.NestedJoin() připojena tabulka Adresa na základě shody hodnot v atributech Město. Po tomto připojení byly smazány veškeré nepotřebné atributy. Dimenze TypPlatby Tabulka Transactions, která poskytuje zdrojová data pro tuto dimenzi, obsahuje atribut specifikující typ dané platby, a tak získání dat pro dimenzi TypPlatby bylo velmi snadné. z tabulky Transactions byly odstraněny nevyžadované atributy, následně byly odstraněny duplicitní hodnoty v atributu Typ platby a do dimenze byl vložen automaticky generovaný index, který slouží jako primární klíč této dimenze. Dimenze Příkaz Tato dimenze obsahuje pouze tři atributy, z nichž všechny jsou obsaženy v tabulce Transactions. Při zisku těchto dat došlo tedy pouze k odstranění nevyžadovaných atributů ze zdrojové tabulky. Primárním klíčem této dimenze je atribut ID pohybu, který Fio API používá jako primární klíč pro identifikaci transakcí, a tak nebylo zapotřebí přidávat nový index, který by sloužil jako primární klíč této dimenze. U transakcí, které nemají uveden komentář, byla hodnota null v tomto atributu nahrazena hodnotou Bez komentáře. Dimenze Datum Aby uživatelé mohli sledovat své transakce v závislosti na čase, byla vytvořena časová dimenze Datum. Tato dimenze byla vytvořena v programu Microsoft Excel a následně přidána do datového modelu prostřednictvím programu Power Query. Vzhledem k tomu, že Microsoft Excel disponuje funkcemi pro práci s časovými údaji, bylo vytvoření této časové dimenze snadné. Jako primární klíč této tabulky byl zvolen atribut DateKey, který vznikl spojením atributů Rok, Měsíc a Den. Výsledkem je tedy klíč, který má formát YYYYMMDD. Dimenze Kategorie Poslední dimenzí, která byla v rámci této práce vytvořena, je dimenze Kategorie. Tato dimenze již nevychází z tabulky Transactions, jak tomu bylo u většiny předchozích dimenzí, ale uživatelé její obsah vytváří samostatně. Bylo tedy zapotřebí pouze

5.3

ETL procesy

43

definovat oblast v sešitu programu Microsoft Excel, ze kterého budou čerpány zdrojová data pro tuto dimenzi. Aktualizace dat v této dimenzi zajišťuje speciální makro, které se spustí v okamžiku přidání nové kategorie uživatelem. V programu Power Query je k těmto kategoriím přiřazen automaticky generovaný primární klíč. 5.3.3

Tvorba faktové tabulky

Po vytvoření všech požadovaných dimenzí byla vytvořena faktová tabulka. Tato tabulka opět vychází z tabulky Transactions, ale z této tabulky zachovává pouze atribut Objem. Všechny ostatní atributy této tabulky vznikly sloučením s danými dimenzemi, ze kterých byly zachovány pouze cizí klíče pro konkrétní hodnoty. Bylo zde tedy provedeno celkem sedm operací pro sloučení tabulky s dimenzemi a dále byly expandovány pouze sloupce s požadovanými klíči. Posloupnost příkazů, které vedly k vytvoření faktové tabulky je znázorněna na obrázku 17.

Obrázek 17: Vytvoření faktové tabulky.

5.3

ETL procesy

5.3.4

44

Kompletace datového modelu

Veškeré dimenze a následně i faktová tabulka byly vytvářeny v doplňku Power Query, který byl doinstalován do programu Microsoft Excel 2013. Tato verze již podporuje práci s datovým modelem, který je sdílen mezi Excel a Power Pivot. Při vytváření dimenzí a faktové tabulky byly tedy veškeré tyto tabulky nahrány do datového modelu, aby s nimi bylo následně možné pracovat v programu Power Pivot. Dřívější verze programu Microsoft Excel ještě neumožňovaly práci s datovým modelem, a tak bylo zapotřebí nejprve data získaná prostřednictvím doplňku Power Query nahrát do samostatných tabulek programu Microsoft Excel a tyto tabulky následně zpřístupnit v programu Power Pivot pomocí funkce Přidat do datového modelu na záložce Power Pivot. Pro aktivaci doplňku Power Pivot je zapotřebí přepnout se v programu Microsoft Excel na kartu Power Pivot. Na této kartě uživatel najde vybrané základní funkce, které nevyžadují otevření nového okna Power Pivot. Mezi tyto funkce se řadí například Počítaná pole, Klíčové ukazatele výkonu, Přidat do datového modelu nebo Aktualizovat vše. Pro vytvoření relací je zapotřebí přepnout se do okna programu Power Pivot pomocí tlačítka Spravovat na kartě Power Pivot. Vytváření relací v programu Power Pivot je možné vytvářet několika způsoby. Prvním způsobem je klasický drag and drop způsob, kdy se zvolí atribut z výchozí tabulky a jednoduše se tento atribut přemístí na atribut v cílové tabulce, na jehož základě má být vytvořena vazba. U tohoto způsobu však může dojít snadno k omylu při výběru atributů. Druhý způsob je volba možnosti Vytvořit relaci, která je přístupná z nabídky Návrh nebo také po přepnutí se do možnosti Zobrazení diagramu a následném kliknutí pravým tlačítkem myši na tabulku, u které má být relace vytvořena. Zde je od uživatele vyžadováno definování výchozí tabulky, sloupce tabulky, vyhledávací tabulky v relaci a vyhledávacího sloupce v relaci. Vytvoření vazby mezi dimenzionální tabulkou dimDatum a faktovou tabulkou factFinančníData je zobrazeno na obrázku 18.

Obrázek 18: Vytvoření relace mezi tabulkami dimDatum a factFinančníData.

5.3

ETL procesy

45

Tímto způsobem byly definovány veškeré vazby v datovém modelu v prostředí programu Power Pivot. Výsledný datový model se všemi vazbami je zobrazen na obrázku 19. Na tomto obrázku jsou již také znázorněny hierarchie, které byly navrženy v kapitole 5.2.

Obrázek 19: Výsledný datový model v prostředí programu Power Pivot.

Po vytvoření vazeb mezi tabulkami byly do faktové tabulky přidány dva nové sloupce. Prvním z nich byl sloupec Výdaje, který převádí veškeré záporné hodnoty ve sloupci Objem na kladné. Tento krok je důležitý především pro koncové uživatele, aby byly přehledněji prezentovatelné výsledky analýz. Vzorec, který byl při vytváření tohoto sloupce aplikován je znázorněn na obrázku 20. Funkce IF vrací inverzní hodnotu k hodnotě ve sloupci Objem pouze v případě, že je tato hodnota záporná. Jestliže se jedná o kladnou hodnotu, funkce vrací hodnotu BLANK().

Obrázek 20: Vzorec pro vytvoření sloupce Výdaje.

5.3

ETL procesy

46

Druhým sloupcem je sloupec Příjmy, který pouze přebírá veškeré kladné hodnoty ze sloupce Objem. Ukázka vzorce pro vytvoření sloupce Příjmy je zobrazena na obrázku 21. V tomto případě vrací funkce pouze kladné hodnoty ze sloupce Objem. Jestliže je hodnota ve sloupci Objem záporná, je opět vrácena hodnota BLANK().

Obrázek 21: Vzorec pro vytvoření sloupce Příjmy.

Vzhledem k tomu, že konečná aplikace měla umožňovat spravovat si vlastní plán výdajů a příjmů, musely být do modelu přidány i dvě počítaná pole. Počítaná pole se do datového modelu přidají z lišty nástroje Power Pivot pomocí nabídky Počítaná pole a následně tlačítka Nové počítané pole. Prvním z těchto polí je pole Rozdíl výdajů, které se stará o zobrazení rozdílu mezi plánovanými výdaji a skutečně vynaloženými výdaji v daném období a dané kategorii. Vzorec, kterým bylo dosaženo tohoto výpočtu je zobrazen na obrázku 22. z obrázku lze dále vyčíst, že výsledek bude ve formátu desetinného čísla, které bude zaokrouhleno na dvě desetinná místa.

Obrázek 22: Vytvoření počítaného pole Rozdíl výdajů.

Druhým z počítaných polí je pole % plnění. Toto počítané pole se stará o výpočet plnění plánu a jeho následný převod na procenta. Vzorec, kterým je do cíleno tohoto výpočtu je zobrazen na obrázku 23. z obrázku lze také vyčíst to, že číslo bude převedeno na procento a následně zaokrouhleno na dvě desetinná místa.

5.3

ETL procesy

47

Obrázek 23: Vytvoření počítaného pole % plnění.

Seznam počítaných polí je samozřejmě možné dále rozšiřovat, ovšem vzhledem k tomu, že ze vstupních dat je jedinou hodnotou suma transakce, není zde moc velký prostor pro počítání těchto polí z předem stanovených hodnot. Posledním krokem bylo vytvoření hierarchií, které byly definovány v kapitole 5.2. Pro vytvoření hierarchie je zapotřebí přepnout se do okna aplikace Power Pivot pomocí tlačítka Spravovat na kartě Power Pivot. Na tabulku, u které chceme vytvořit hierarchii, klikneme pravým tlačítkem myši a zvolíme možnost Vytvořit hierarchii. Alternativní způsob vytváření hierarchií je pomocí druhého tlačítka zprava v záhlaví dané tabulky. Poté se vloží nová hierarchie do dané tabulky. Tuto hierarchii je samozřejmě možné přejmenovat a následně do ní přiřadit požadované atributy, ze kterých se daná hierarchie bude skládat. Tyto atributy se přidávají způsobem drag and drop.

6

PREZENTAČNí VRSTVA APLIKACE

6

48

Prezentační vrstva aplikace

Tato kapitola bude věnována popisu výsledné aplikace, která byla v rámci této práce vytvořena. Nejprve bude proveden stručný úvod k možnostem vkládání kontingenčních tabulek a grafů z doplňku Power Pivot do aplikace Microsoft Excel a následně budou popsány jednotlivé listy sešitu, které uživatelům umožňují detailněji analyzovat své finanční transakce od společnosti Fio banka, a. s.

6.1

Vkládání kontingenčních tabulek a grafů

Uživatel, který chce data z datového modelu vytvořeného v programu Microsoft Excel převést do podoby grafů či kontingenčních tabulek, se již nemusí k tomuto modelu žádným způsobem připojovat. K vytvoření grafu či kontingenční tabulky stačí se v prostředí programu Microsoft Excel přepnout na kartu Power Pivot a následně spustit okno doplňku Power Pivot pomocí tlačítka Spravovat. Zde se nachází tlačítko Kontingenční tabulka, které po aktivaci rozbalovací nabídky ve spodní části tohoto tlačítka uživateli nabídne další možnosti vložení přehledů. Uživatel má možnost zvolit jednu z následujících možností: • Kontingenční tabulka • Kontingenční graf • Graf a tabulka (vodorovně) • Graf a tabulka (svisle) • Dva grafy (vodorovně) • Dva grafy (svisle) • Čtyři grafy • Plochá kontingenční tabulka Po zvolení jedné z výše uvedených možností je uživatel dotázán, zda si přeje graf či tabulku vložit na nový či již existující list. Po volbě požadované možnosti je v pracovním prostředí programu Microsoft Excel zobrazena oblast, ve které bude uživateli vykreslen požadovaný graf či tabulka. V pravé části okna se současně otevře panel Seznam polí, ve kterém probíhají veškeré editace dat, nad kterými budou provedeny požadované analýzy. Jestliže toto okno není zobrazeno automaticky, musí si jej uživatel zobrazit ručně. Toto pole se zobrazí po kliknutí na tlačítko Seznam polí na kartě Analýza v programu Microsoft Excel. V postranním panelu Seznam polí uživatel vidí veškeré tabulky ze svého datového modelu, který byl dříve vytvořen pomocí doplňků Power Pivot a Power Query. Po rozbalení zvolených tabulek zde uživatel vidí atributy těchto tabulek, nad kterými jsou prováděny výsledné analýzy.

6.2

Úvod aplikace

49

Ve spodní části panelu Seznam polí jsou zobrazena čtyři okna, do kterých lze atributy tohoto datového modelu metodou drag and drop přetahovat. Jedná se o následující okna: • Filtry • Legendy (řady) • Osy (kategorie) • Hodnoty Jakmile uživatel vloží požadovaný atribut do příslušného okna panelu Seznam polí, je výsledná tabulka či graf automaticky okamžitě přepočítán. Tato funkce se dá zrušit zaškrtnutím položky Odložit aktualizaci rozložení, která se nachází ve spodní části tohoto panelu.

6.2

Úvod aplikace

Po spuštění aplikace je uživateli zobrazen úvodní list sešitu Microsoft Excel s názvem Úvod, který je zobrazen na obrázku 24. Hlavní funkcí tohoto listu je převzít od uživatele token, bez kterého by uživatel neměl přístup ke svým datům. Popis způsobu, jak získat daný token je popsán v kapitole 3.2.

Obrázek 24: Úvodní list aplikace.

Kromě pole, do kterého se zadává token, jsou zde také dvě tlačítka. Každé tlačítko obsahuje vlastní makro a má tedy svou specifickou funkci. První tlačítko se nazývá Načíst data z jiného účtu. Po aktivaci tohoto tlačítka dojde k celkovému vymazání veškerých zařazení transakcí do kategorií a dalších nastavení, které uživatel v minulosti nad svými daty provedl.

6.3

Hlavní přehled

50

Druhé tlačítko (Aktualizovat výpisy od posledního stažení) slouží k získání pouze nových transakcí, které byly na účtu provedeny od posledního stažení těchto dat pomocí daného tokenu. V tomto případě nedojde k vymazání žádných nastavení či přiřazení, které uživatel v minulosti provedl. Jestliže na účtu nejsou od posledního stažení žádné nové transakce, je tato zpráva uživateli oznámena pomocí vyskakovacího okna. Po aktivaci tlačítka je provedena požadovaná funkce a uživatel je přesměrován na list Hlavní přehled.

6.3

Hlavní přehled

Jak již název napovídá, slouží tento list pro zobrazení nejdůležitějších informací uživateli. Tento list se skládá ze dvou částí – Aktuální přehled a Přehled veškerých transakcí. 6.3.1

Aktuální přehled

Tato oblast listu Hlavní přehled slouží k zobrazení informací, které pochází ze zdrojového XML souboru z části Info, jehož obsah je zobrazen na obrázku 7. Ze všech informací, které tento XML soubor poskytuje, byly vybrány pouze informace o číslu účtu uživatele, měně, ve které je tento účet veden a aktuálním zůstatku na tomto účtu. Tato část listu Hlavní přehled je zobrazena na obrázku 25.

Obrázek 25: Část Aktuální přehled listu Hlavní přehled.

Druhým typem informací, které jsou v této části zobrazeny, jsou dva grafy. První z těchto grafů identifikuje měsíční příjmy a výdaje. Tyto příjmy a výdaje jsou zobrazeny v rozmezí jednotlivých měsíců a jako filtr sestavy jsou zde zvoleny jednotlivé roky, ve kterých byly na účtu zaznamenány nějaké transakce. Graf Měsíční příjmy a výdaje je zobrazen na obrázku 26. Druhý graf identifikuje výdaje dle uživatelů. V tomto grafu jsou tedy zobrazeni uživatelé, u kterých uživatel nejvíce utrácel v průběhu jednotlivých měsíců v daném roce. Tito uživatelé jsou seřazeni sestupně od toho, u kterého jsou na daném účtu vynaloženy největší finanční částky. Jako filtr sestavy je zde opět zvolen rok,

6.3

Hlavní přehled

51

Obrázek 26: Měsíční příjmy a výdaje.

ve kterém byly provedeny nějaké výdaje. Graf Výdaje dle uživatelů je zobrazen na obrázku 27.

Obrázek 27: Výdaje dle uživatelů.

6.3.2

Přehled veškerých transakcí

Druhá část listu Hlavní přehled je věnována zobrazení veškerých transakcí, které jsou na účtu zachyceny. Uživatel zde má možnost filtrovat zobrazení transakcí na základě přiřazení transakcí do kategorií. Po načtení nových transakcí jsou tyto transakce automaticky zařazeny do kategorie Nezařazeno. Jestliže nově načtená transakce pochází od uživatele, který již byl v minulosti zařazen do nějaké kategorie, je tato transakce zařazena do odpovídající kategorie. V hlavní tabulce, ve které jsou všechny tyto transakce zobrazeny, má uživatel možnost zařazovat transakce do kategorie, kterou si sám vytvořil. Tvorba těchto kategorií bude popsána v další části této práce. Jestliže chce uživatel zařadit transakci do této kategorie, stačí najet myší na zvolenou transakci a ve sloupci Kategorie rozevřít seznam kategorií a požadovanou kategorii zvolit. Takto může uživatel přiřazovat

6.4

Zařazení uživatele do kategorie

52

transakce jednotlivě – může tedy zařadit transakce od stejného uživatele do více kategorií. Jakmile jsou u všech požadovaných transakcí uživatelem zvoleny nové kategorie, musí uživatel tuto volbu potvrdit pomocí tlačítka Přiřadit platby, které se nachází pod tabulkou s transakcemi. Toto tlačítko má namapováno makro, s jehož pomocí dojde k aktualizaci potřebných tabulek datového modelu aplikace. Tato část sešitu Hlavní přehled je zobrazena na obrázku 28.

Obrázek 28: Přehled veškerých transakcí.

6.4

Zařazení uživatele do kategorie

Tento list byl vytvořen pro případy, kdy uživatel provádí opakovaně transakce se stejnými uživateli, které si přeje automaticky řadit do stále stejných kategorií. Ukázkovými příklady mohou být například převody finančních prostředků za nájem na účet pronajímatele, platba záloh za energie, příjem výplaty na účet, atd. V horní části tohoto listu je popis daného listu pro uživatele. Jedná se o malou nápovědu, která by měla přispět k uživatelské přívětivosti a uživatele navigovat při práci s řazením uživatelů do kategorií. Další část listu zabírá tabulka, která obsahuje všechny již dříve zařazené uživatele do kategorií. Obsahuje tedy sloupec Uživatel a Kategorie. Pro vložení nového záznamu do této tabulky musí uživatel kliknout na tlačítko Nový záznam, které do tabulky přidá nový řádek. V novém řádku ve sloupci Uživatel se pomocí rozbalovací nabídky v daném řádku a sloupci vybere nový uživatel a stejným způsobem se ve sloupci Kategorie vybere požadovaná kategorie. Tabulka pro přidělování uživatelů do kategorií je zobrazena na obrázku 29. Ve spodní části tohoto listu se nachází tabulka, ve které uživatel své zařazení může zkontrolovat. Tato tabulka dále obsahuje atributy Datum, Částka, Měna, Zpráva pro příjemce, Protiúčet, Banka, Kategorie a Uživatel. Poté, co uživatel ve výše zmíněné tabulce zařadí uživatele do požadované kategorie a zkontroluje

6.5

Plán výdajů po měsících

53

Obrázek 29: Tabulka pro přiřazení uživatele do kategorie.

tuto volbu v této tabulce, musí ještě tuto akci potvrdit. Toto potvrzení provede pomocí tlačítka Aktualizovat, které se nachází na úrovni prvního řádku tabulky s přehledem nezařazených transakcí a uživatelů na tomto listu.

6.5

Plán výdajů po měsících

Aby si uživatelé mohli plánovat své výdaje a následně plnění tohoto plánu kontrolovat, byl vytvořen list s názvem Plán výdajů po měsících. 6.5.1

Sestavení plánu

Jestliže chce uživatel kontrolovat své plnění plánu, musí si nejprve tento plán sestavit. Plán se sestavuje v tabulce, která se nachází v levém dolním rohu tohoto listu a je zobrazena na obrázku 30. Tato tabulka obsahuje atributy Rok, Měsíc, Kategorie a Plánované výdaje [Kč]. Po zadání roku, pro který si uživatel přeje plán vytvářet, je automaticky přidán nový řádek do tabulky. V takto vytvořeném řádku již uživatelé mohou volit atributy Měsíc a Kategorie z rozevíracího seznamu, který se u každé této buňky objeví. Hodnoty v atributu Měsíc jsou standardní názvy měsíců, hodnoty v atributu Kategorie vychází z kategorií, které si uživatel sám nadefinuje v listu Správa kategorií. Po vyplnění tabulky požadovanými daty je zapotřebí tato data uložit do datového modelu. Za tímto účelem je zde tlačítko Aktualizovat, které tuto aktualizaci dat zajistí. 6.5.2

Kontrola plnění plánu

Jakmile je plán výdajů zaveden do datového modelu, může již uživatel porovnat plnění svého plánu se skutečnými výdaji v daných kategoriích. Pro lepší prezentaci

6.5

Plán výdajů po měsících

54

Obrázek 30: Sestavení plánu výdajů.

výsledků byl zvolen nejen graf, ale i tabulka, ve které uživatel vidí rozdíl mezi plánovanými a skutečnými výdaji. Prvním způsobem prezentace výsledků plánovaných výdajů je graf, který je zobrazen na obrázku 31. Tento graf na svých osách obsahuje atributy Rok a Název měsíce, jako legendy (řady) jsou zde zvoleny hodnoty Výdajů (levá strana grafu) a % plnění (pravá strana grafu). Jako hodnoty v grafu jsou vybrány Plánované výdaje, Skutečné výdaje a počítané pole s názvem % plnění, které vyjadřuje rozdíl mezi skutečnými a plánovanými výdaji v procentech. Filtrem tohoto grafu jsou Rok a Kategorie, které jsou ke grafu přidány ve formě průřezu. Hodnoty pro skutečné a plánované výdaje jsou zachyceny ve formě sloupcového grafu, % plnění je v tomto grafu znázorněno pomocí grafu spojnicového.

Obrázek 31: Graf pro prezentaci plánu výdajů.

Pro zvýšení vypovídací hodnoty dat byla přidána ještě kontingenční tabulka zobrazena na obrázku 32, ve které je rozdíl mezi plánem a skutečností vyjádřen i v konkrétních číslech. Řádky této tabulky jsou opět atributy Rok a Název měsíce. Ve sloupcích jsou pak atributy Skutečné výdaje, Plánované výdaje, Rozdíl a % plnění. Sloupec Rozdíl je pouhým rozdílem mezi skutečnými a plánovanými výdaji. % plnění pak opět vyjadřuje poměr mezi skutečnými a plánovanými výdaji

6.6

Přehled výdajů dle uživatelů

55

v procentech. Pro zvýšení přehlednosti byly sloupce Rozdíl a % plnění zvýrazněny červenou, zelenou či žlutou barvou. Zelená barva vyjadřuje, že plán byl splněn a plánované výdaje nebyly vyšší než ty skutečné. Červená barva vyjadřuje pravý opak, tedy že skutečné výdaje byly vyšší než ty plánované. Žlutá barva poukazuje na to, že plán byl splněn na halíř přesně, tedy že skutečné výdaje jsou naprosto shodné s výdaji plánovanými.

Obrázek 32: Tabulka pro prezentaci plánu výdajů.

6.6

Přehled výdajů dle uživatelů

Dalším typem analýzy, která je uživateli poskytována, je analýza výdajů dle uživatelů. Pro prezentaci výsledků této analýzy byla opět zvolena forma tabulky i grafu. V tomto případě byl zvolen koláčový typ grafu, kdy je uživateli prezentována suma výdajů u jednotlivých uživatelů společně s procentuálním rozložením. Ke grafu byly připojeny i průřezy, s jejichž pomocí může uživatel filtrovat prezentovaná data. Filtrovat tato data může dle Roku, Měsíce a Kategorie, ve které se uživatelé nachází. Tento graf i s těmito průřezy je zobrazen na obrázku 33. Kontingenční tabulka vychází ze stejného rozložení atributů datového modelu jako v případě grafu zmíněného výše. Sloupci v této tabulce jsou tedy názvy kategorií, řádky jsou jména uživatelů a jako hodnoty byly zvoleny vynaložené výdaje. Tato kontingenční tabulka byla vytvořena tak, aby její obsah byl měněn na základě stejných průřezů, které jsou aplikovány na graf. Jestliže tedy uživatel požaduje například vidět pouze výdaje za rok 2013, jsou mu tato data prezentována ve formě grafu i kontingenční tabulky. Tato kontingenční tabulka je zobrazena na obrázku 34.

6.7

Přehled výdajů dle kategorií

56

Obrázek 33: Graf prezentující výdaje dle uživatelů.

Obrázek 34: Kontingenční tabulka prezentující výdaje dle uživatelů.

6.7

Přehled výdajů dle kategorií

V tomto listu uživatelé naleznou možnost provést analýzu, která poskytuje informace o tom, v jaké kategorii nejvíce utráceli a následně i u jakého uživatele z této kategorie nejvíce nakupovali. Rozložení tohoto listu je prakticky totožné s listem Přehled výdajů dle kategorií. Opět je zde zvolena prezentace výsledků pomocí grafu i kontingenční tabulky. Koláčový graf, který je zobrazen na obrázku 35, opět ukazuje procentuální i celkové rozložení veškerých nákladů v rámci jednotlivých kategorií. V levé části tohoto grafu jsou opět zobrazeny průřezy, s jejichž pomocí mohou uživatelé lépe specifikovat informace, které si přejí zobrazit. Jako průřezy jsou zde opět zvoleny atributy Rok a Název měsíce, ovšem třetím atributem zde již není atribut Kategorie, nýbrž atribut Uživatel. Je zde tedy možné vyfiltrovat si z grafu jen požadované uživatele a u nich sledovat kategorie, do kterých jsou zařazeni.

6.8

Správa kategorií

57

Obrázek 35: Graf prezentující výdaje dle kategorií.

Jak je uvedeno výše, nachází se zde také kontingenční tabulka, která data zanesená do grafu prezentuje i ve formě konkrétních hodnot. Tato tabulka je opět provázána s výše uvedenými průřezy, a tak automaticky reaguje na požadované filtrování zobrazovaných dat. V záhlaví sloupců této kontingenční tabulky se nachází jednotliví uživatelé, v jednotlivých řádcích jsou poté uvedeny jednotlivé kategorie, do kterých tito uživatelé spadají. Poslední řádek udává konečné součty výdajů u jednotlivých uživatelů, v posledním sloupci jsou poté uvedeny součty výdajů za požadované kategorie. Tato kontingenční tabulka je zobrazena na obrázku 36.

Obrázek 36: Kontingenční tabulka prezentující výdaje dle kategorií.

6.8

Správa kategorií

Tvorba kategorií, do kterých uživatel může přiřadit své platby, je z hlediska využitelnosti této aplikace její nejpodstatnější část, bez které by tato aplikace sloužila pouze pro zobrazení informací, které lze získat prostřednictvím Fio API. Díky těmto

6.9

Vlastní analýzy

58

kategoriím si uživatelé mohou své transakce libovolně přiřazovat a následně je analyzovat z několika pohledů. List Správa kategorií obsahuje pole, do kterého uživatel zadá požadovaný název kategorie, kterou si přeje přidat do datového modelu aplikace. Vedle tohoto pole je tlačítko s názvem Přidat kategorii, jehož aktivace je pro přidání nové kategorie bezpodmínečně nutná. Po stisknutí tohoto tlačítka je spuštěno makro, které automaticky kontroluje duplicitu záznamu se stejným názvem kategorie. Jestliže je zjištěno, že kategorie s tímto názvem již v datovém modelu existuje, je vyvolána výjimka, která zobrazí dialogové okno oznamující uživateli, že kategorie s tímto názvem již existuje. Dále tento list obsahuje tabulku označenou jako Přehled kategorií, ve které jsou zobrazeny veškeré dříve přidané kategorie. Konečný vzhled tohoto listu je zobrazen na obrázku 37. Veškeré kategorie, které jsou zde uvedeny, jsou totožné s těmi, se kterými uživatel pracuje na listech Hlavní přehled, Zařazení uživatele do kategorie a Plán výdajů po měsících. Veškeré analýzy jsou tedy prováděny čistě s těmi kategoriemi, které si zde uživatel sám vytvoří.

Obrázek 37: List Správa kategorií s ukázkou zadání duplicitní kategorie.

6.9

Vlastní analýzy

Posledním listem, který byl do této aplikace přidán, je list pro vlastní analýzy uživatele. Postup, jak si uživatel může vlastní analýzu vytvořit a z jakých grafů či tabulek

6.9

Vlastní analýzy

59

může vybírat, je popsán v kapitole 6.1. Do tohoto listu je vložena pouze ukázková kontingenční tabulka, která je zobrazena na obrázku 38.

Obrázek 38: List Vlastní analýzy

Aby uživatelé nemohli vytvářet analýzy například z primárních klíčů tabulek v datovém modelu, byly tyto klíče a další analyticky nepoužitelné položky skryty. Toto skrytí nevyžadovaných atributů se provádí v prostředí doplňku Power Pivot. Jestliže chce uživatel daný atribut skrýt, musí se přepnout tlačítkem Spravovat na kartě Power Pivot do doplňku Power Pivot a následně pravým tlačítkem myši kliknou na atribut, který si uživatel přeje skrýt. Po tomto kliknutí se uživateli otevře nabídka, ze které vybere možnost Skrýt v nástrojích klienta. Uživatelům tak k jejich analýzám zůstaly v postranním okně Seznam polí pouze ty atributy, které jsou vhodné k tvorbě analýz.

7

VÝSLEDKY A DISKUSE

7 7.1

60

Výsledky a diskuse Zhodnocení a přínosy

V teoretické části této práce se lze seznámit se základními pojmy z oblasti Business Intelligence a následně také s funkčností API od společnosti Fio banka, a. s. Přínosy praktické části jsou především v ukázce praktického využití tohoto API. V současné době je aplikace k dispozici pouze společnosti Intelligent Technologies, pro kterou byla tato aplikace vytvořena a v budoucnu se předpokládá její nabídnutí a nasazení u svých stávajících nebo nových klientů.

7.2

Možnosti rozšíření

Již v průběhu práce na tomto projektu bylo objeveno několik potenciálních možností pro rozšíření této aplikace. Jednou z možností, jak doplnit novou funkcionalitu do této aplikace, je využití dalšího doplňku do prostředí programu Microsoft Excel. Jedná se o doplněk Power View, jehož hlavní funkcí je interaktivní zkoumání, vizualizace a prezentace dat. S pomocí tohoto doplňku by mohla být do aplikace přidána například interaktivní mapa, která by uživatelům zobrazovala místa, ve kterých nejvíce utráceli, odkud jim chodí nejvíce plateb, lepší využití KPI při zkoumání dodržení plánu, atd. Další možností pro rozšíření vychází ze vstupních dat, které poskytuje API společnosti Fio banka, a. s. Kdyby se společnost v budoucnu rozhodla o poskytování většího množství informací prostřednictvím tohoto API, byla by zde další možnosti pro rozšíření funkcionality této aplikace. Aplikace by také mohla být přizpůsobena pro odesílání transakcí a ne jejich pouhé zobrazování. Vzhledem k obavám o bezpečnost svých bankovních dat by tato funkcionalita již ovšem nemusela všem vyhovovat, a tak její implementace nebyla v tomto projektu ani diskutována. Fio API ovšem tuto možnost nabízí, a tak by v budoucnu tato aplikace mohla být touto funkcionalitou doplněna.

7.3

Srovnání s existujícími řešeními

V současné době na trhu není aplikace, která by uživateli umožňovala naprosto stejné služby jako aplikace, která byla vytvořena v rámci této diplomové práce. Existuje ovšem podobná aplikace přímo od Fio banky, a. s., která ovšem umožňuje uživateli pouze sledovat své transakce pomocí Fio tokenu, tedy bez nutnosti připojení do internetového bankovnictví. Tato aplikace je zdarma ke stažení na webových stránkách Fio banky, a. s. Dále lze na webu nalézt návod na tvorbu podobné aplikace, která také pracuje přímo s Fio API, ovšem přizpůsobení této aplikace vlastním požadavkům se provádí pomocí skriptů, jejichž obsluhu nemusí zvládnout každý uživatel. S touto aplikací přišel Filip Hráček, který pro vytvoření této aplikace využil Google Apps Script.

7.4

Ekonomické zhodnocení projektu

61

Aktualizace probíhá na základě cron jobu, který je umístěn na serverech společnosti Google. Výhodou tohoto řešení je bezesporu to, že uživatel má k dispozici tyto údaje online a může k nim přistupovat například i pomocí tabletu či mobilního telefonu.

7.4

Ekonomické zhodnocení projektu

Při práci na tomto projektu byl využíván především nástroj Microsoft Excel ve verzi 2013 s využitím doplňků Power Pivot a Power Query. Doplněk Power Query je v této verzi již obsažen a doplněk Power Query je zdarma ke stažení z webu společnosti Microsoft. Jediné náklady tedy vznikají při pořízení aplikace Microsoft Excel ve verzi 2013. Tento produkt se vyskytuje jako součást balíku Microsoft Office, jejichž licence se na trhu dá sehnat ve třech verzích. Nejlevnější z těchto verzí je Office 2013 pro domácnosti, jejíž cena je dle webových stránek Microsoftu stanovena na 3 499 Kč vč. DPH. Tento balík navíc obsahuje produkty Microsoft Word, OneNote a PowerPoint. Druhým nejlevnějším řešením je Office 2013 pro podnikatele, jehož cena je stanovena na 6 799 Kč vč. DPH. Navíc od balíku pro domácnosti je zde obsažen program Microsoft Outlook. Nejdražším balíkem je dle stránek výrobce balík Office 2013 pro podnikatele, který stojí 13 999 Kč vč. DPH. Navíc od verze pro domácnosti jsou zde obsaženy produkty Microsoft Access a Publisher (Microsoft, 2014). Druhou možností je zakoupení některé ze sad Office 365. Na rozdíl od předchozích balíků jsou Office 365 poskytovány jako služba ve formě ročního či měsíčního předplatného. Nejvýhodnější sada Office 365 je verze pro studenty vysokých škol, která je poskytována ve formě čtyřletého předplatného a jejíž cena je stanovena na 1 999,Kč vč. DPH. Druhou nejlevnější variantou je Office 365 pro jednotlivce, která uživatele vyjde na 1 699 Kč vč. DPH za rok. Dále existuje verze Office 365 pro domácnosti, která je dostupná za 2 499 Kč vč. DPH za rok (Microsoft, 2014). Jestliže budeme předpokládat, že uživatel je v této oblasti naprostým začátečníkem a budeme uvažovat i náklady na kurz v oblasti Microsoft Excel a Power Pivot, je zapotřebí tyto náklady také započítat. V dnešní době již existuje velké množství online kurzů a podrobných návodů pro práci s Microsoft Excel a doplňkem Power Pivot. Existují také osobní kurzy, kde uživatel může získat osvědčení o zvládnutí práce s těmito nástroji. V rámci České republiky je nejnižší cena za kurz, jehož obsahem je také funkcionalita nástroje Power Pivot, 2 360 Kč vč. DPH (1 950 Kč bez DPH). Tento kurz nabízí společnost Edumatik, spol. s. r. o. Druhou nejlevnější nabídkou je kurz od společnosti DIGI Akademie, s. r. o., která tento kurz nabízí za 2 541 Kč vč. DPH (2 100 Kč bez DPH). Na druhou stranu nejdražší kurz poskytuje společnost VOX a. s., která si za dvoudenní kurz účtuje 7 490 Kč vč. DPH (6 190 Kč bez DPH) (Edumenu, 2014).

7.5

7.5

Diskuse

62

Diskuse

K aplikaci, která by uživateli umožňovala analyzovat data získaná prostřednictvím určitého API, by se dalo přistupovat mnoha způsoby. Tato práce byla omezena na API společnosti Fio banka, a. s. a na vývojové prostředí programu Microsoft Excel s doplňkem Power Pivot. Toto omezení vycházelo z požadavků společnosti Intelligent Technologies, pro kterou byla praktická část této práce vytvářena. Při tvorbě této diplomové práce, jejíž hlavní část je věnována API od společnosti Fio banka, a. s., bylo zapotřebí využívat vlastního bankovního účtu u této společnosti, jelikož demo účet, který nabízí na svém webu, nebyl pro tuto práci vhodným. k tomuto demo účtu nelze zřídit token, bez kterého není možné získat data z bankovního účtu. Ani po několika telefonátech a osobní návštěvě na dvou pobočkách této banky se mi nepovedlo získat žádný tréninkový účet, ke kterému by bylo možné vygenerovat token. Přistoupil jsem tedy k variantě, kdy veškeré testy aplikace budu provádět na svém soukromém účtu. Jakmile byla aplikace vytvořena, byla její správná funkčnost ověřena na více účtech u této banky, které se mi podařilo získat od rodinných příslušníků. Při tvorbě aplikace byl kladen důraz na uživatelskou přívětivost a jednoduchost obsluhy této aplikace. Možná nevýhoda tohoto návrhu je v nutnosti doinstalování doplňku Power Query do programu Microsoft Excel 2013. Microsoft ovšem vydal prohlášení, že v další verzi programu Microsoft Excel by již tento doplněk měl být jeho součástí, a tak nebude nutné jej zvlášť doinstalovávat. Dále se vyskytla nepatrná závada při časné aktualizaci datového modelu ve 32bitové verzi programu Microsoft Excel 2013. Problémem 32bitové verze je to, že maximální vytížení paměti RAM datovým modelem v počítači uživatele je přibližně 500 – 700 MB. U 64bitové verze programu Microsoft Excel 2013 již tento problém nenastává a uživatel je tak limitován pouze reálnou velikostí RAM paměti v počítači. Další nepatrnou nevýhodou této aplikace může být trvání aktualizace datového modelu při načítání nových dat do tohoto modelu. Tento problém byl částečně odstraněn pomocí speciálních maker, které jsou přizpůsobeny jednotlivým funkcím, které vykonávají. Jednodušším řešením mohlo být to, že by všechna makra pro aktualizaci byla stejná a při každé nepatrné změně by se aktualizoval celý datový model, tedy i tabulky, které nejsou zapotřebí aktualizovat. V současném stavu má aplikace tato makra přizpůsobena aktualizaci pouze potřebných tabulek datového modelu.

8

8

ZÁVĚR

63

Závěr

Cílem této diplomové práce bylo vytvořit aplikaci v prostředí programu Microsoft Excel s doplňkem Power Pivot, která uživatelům bude umožňovat samostatně analyzovat své transakce na účtech vedených u společnosti Fio banka, a. s. V teoretické části došlo k nastudování dané problematiky, která se týkala Business Intelligence a API společnosti Fio banka, a. s. Veškeré informace, které byly v rámci této práce získány, pochází z literatury, která je uvedena v přehledu literatury. Po důkladném nastudování této problematiky byl vytvořen metodický postup práce na vývoji aplikace, který popisuje nejdůležitější kroky a nástroje pro dosažení požadovaného cíle. Praktická část práce se věnovala vývoji aplikace pro samoobslužný přístup k tvorbě analýz dle uživatelských požadavků v prostředí programu Microsoft Excel 2013 s doplňky Power Pivot a Power Query. V této části práce byl také důkladně popsán postup, který vedl k vytvoření této aplikace a také detailně popsán princip, na jakém aplikace funguje a jakým stylem ji uživatelé mohou obsluhovat. Je zde také uveden postup, jak si uživatelé do této aplikace mohou vlastnoručně přidávat nové analýzy dle jejich specifických požadavků. V poslední části byly zhodnoceny výsledky, kterých bylo v rámci této diplomové práce dosaženo. Byly zde také uvedeny možnosti pro rozšíření stávající aplikace, její porovnání s již existujícími řešeními dané problematiky a také zde bylo poukázáno na známe nedostatky této aplikace. Při zpracování této diplomové práce bylo dosaženo požadovaného výsledku, kterým je funkční aplikace, která uživatelům umožňuje analyzovat své finanční transakce provedené na jejich účtech u společnosti Fio banka, a. s. V prostředí programu Microsoft Excel 2013 s doplňkem Power Pivot a Power Query. Při práci na této diplomové práci bylo získáno mnoho nových informací a poznatků, které budou v rámci práce na dalších projektech podobného charakteru bezesporu velmi užitečné.

9

9

64

LITERATURA

Literatura

Casters M., Bouman R., Dongen J. Pentaho Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration. Wiley Publishing, Inc., 720 s., 2010. ISBN 978-0-470-63517-9. Collie R. Dax formulas for powerpivot: the excel pro’s guide to mastering dax. 1. vyd. Uniontown : Holy Macro! Books, 239 s., 2012. ISBN 978-1-615-47015-0. Dumbill E. What is big data? [online]. 2012 [cit. 07-05-2014]. Dostupné z: http://strata.oreilly.com/2012/01/what-is-big-data.html . Edumenu Kurzy [online]. 2014 [cit. 07-05-2014]. http://kurzy.edumenu.cz/search?fulltext¯ powerpivot .

Dostupné

z:

Fio banka. FIO API Bankovnictví [online]. 2014 [cit. 10-04-2014]. Dostupné z: http://www.fio.cz/docs/cz/API Bankovnictvi.pdf . Gála L., Pour J., Toman P. Podniková informatika. 1. vyd. Praha: Grada Publishing, a. s., 484 s., 2006. ISBN 80-247-1278-4. Howson C. 5 Big Business Intelligence Trends For 2014 [online]. 2014 [cit. 0904-2014]. Dostupné z: http://www.informationweek.com/software/informationmanagement/5-big-business-intelligence-trends-for-2014/d/d-id/1113468 . Inmon W. Building the Data Warehouse. Wiley Publishing, Inc., 576 s., 2005. ISBN 978-07-645-9944-6. Jensen Ch., Pedersen T., Thomsen Ch. Multidimensional Databases and Data Warehousing. Morgan and Claypool Publishers, 112 s., 2010. ISBN 9781608455378. Kimball R., Ross M.The data warehouse toolkit : the complete guide to dimensional modeling. Wiley Publishing, Inc., 436 s., 2002. ISBN 978-0-471-20024-6. Lacko L. Datové sklady, analýza OLAP a dolování dat. 1. Vyd. Brno: Computer Press, 486 s., 2003. ISBN 80-722-6969-0. Microsoft. Sady Office [online]. 2014 [cit. 07-05-2014]. Dostupné z: http://www.microsoftstore.com/store/mseea/cs CZ/list/SadyOffice/categoryID.66226800 . Mandik P. Zájem o podnikový software roste, miláčky budou BI a Big Data [online]. 2013 [cit. 06-04-2014]. Dostupné z: http://www.businessit.cz/cz/zajem-opodnikovy-software-roste-milacky-budou-bi-a-big-data.php . Novotný O., Pour J., Slánský D. Business Intelligence: Jak využít bohatství ve vašich datech. 1. Vyd. Praha: Grada Publishing, a. s., 254 s., 2005. ISBN 80-7179-409-0.

9

LITERATURA

65

Olivia R. Data Mining 1. vyd., Praha: Computer Press, 329 s., 2001. ISBN 80722-6577-6. Sabherwal R., Becerra-Fernandez I. Business intelligence: practices, technologies, and management. Hoboken, NJ: Wiley, 2011. ISBN 04-704-6170-5.

Přílohy

A

A

VYBRANÁ MAKRA PRO PRÁCI S VÝSLEDNOU APLIKACí

Vybraná makra pro práci s výslednou aplikací

Obrázek 39: Makro pro načítání nových zdrojových dat

67

A

VYBRANÁ MAKRA PRO PRÁCI S VÝSLEDNOU APLIKACí

Obrázek 40: Makro pro aktualizaci dat na listu Hlavní přehled

68

A

VYBRANÁ MAKRA PRO PRÁCI S VÝSLEDNOU APLIKACí

Obrázek 41: Makro pro přiřazení transakcí od uživatele do vybrané kategorie

69

B

DVD MÉDIUM

B

70

DVD médium

Médium obsahuje: • Diplomovou práci v elektronické podobě ve formátu PDF • Výsledný Power Pivot sešit • Doplněk Power Query pro Microsoft Excel 2013 ve 32bitové a 64bitové verzi