Importy dat z relační databáze do OLAP datových kostek

Masarykova univerzita Fakulta informatiky

Importy dat z relační databáze do OLAP datových kostek

Bakalářská práce

Brno 2012

Zdeněk Laštůvka

Prohlášení Prohlašuji, že tato práce je mým původním autorským dílem, které jsem vypracoval samostatně. Všechny zdroje, prameny a literaturu, které jsem při vypracování používal nebo z nich čerpal, v práci řádně cituji s uvedením úplného odkazu na příslušný zdroj. V Brně dne 16. května 2012

______________________

Poděkování Děkuji vedoucímu práce, RNDr. Tomáši Ludíkovi, za odborné vedení a připomínky při zpracování této práce. Dále zaměstnancům GEMMA Systems za jejich ochotu a poskytnuté materiály, zejména panu Ing. Jakubu Urbáškovi.

Shrnutí Cílem práce je popsat proces a možnosti převodu dat z podnikového informačního systému do OLAP datové kostky. Konkrétně je práce zaměřena na nástroje Microsoft SQL Server a Infor PM OLAP. Na základě získaných poznatků byla vytvořena praktická aplikace v jazyce Visual Basic, která umožní převod dat z Microsoft SQL Serveru do analytického nástroje Infor PM OLAP. Výsledný program je užitečný pro koncovou firmu, která díky němu může lépe analyzovat svá data a vytvářet strategie. Klíčová slova OLAP, datová kostka, Infor PM, datová pumpa, ETL proces, transformace dat, analýza dat.

Obsah

Obsah 1

Úvod

1

2

Obecný princip importu dat z OLTP systémů do OLAP kostek

2

3

4

2.1

Provozní systémy............................................................................................. 3

2.2

Datová pumpa.................................................................................................. 5

2.3

Datový sklad..................................................................................................... 6

2.4

OLAP ................................................................................................................. 7

2.5

Presenční vrstva ............................................................................................... 9

Importy dat z Microsoft SQL Serveru do Infor PM OLAP

10

3.1

Microsoft SQL Server 2000 ........................................................................... 11

3.2

Infor Performance Management (Infor PM).............................................. 11

3.3

Možnosti převodu dat z MS SQL Serveru do Infor PM OLAP............... 13

3.4

Analýza dat z Infor PM OLAP v programu MS Excel ............................. 17

Tvorba programu na převod dat

19

4.1

Požadavky ...................................................................................................... 19

4.2

Analýza a návrh............................................................................................. 20

4.3

Implementace ................................................................................................. 27

4.4

Prezentace dat z Infor PM OLAP ................................................................ 33

5

Závěr

35

6

Literatura

36

7

Přílohy

39

Úvod

1

1 Úvod V dnešním světě se neobejdeme bez informací v elektronické podobě, je potřeba s nimi jednoduše pracovat a umět je správně analyzovat. Většina firem používá informační systémy, které pracují s velkým množstvím dat. Ta jsou uložena ve složitých a propracovaných transakčních databázích, označovaných také jako databáze OLTP (Online Transaction Processing), které mohou být reprezentovány objektově orientovanými, objektově-relačními a nejčastěji relačními databázemi. Obecně jsou OLTP databáze určeny pro provozní systémy, kde jsou důležité jednoduché dotazy, vyloučení redundance a bezpečná úprava dat ve víceuživatelském prostředí [15]. Z těchto důvodů by analýza dat OLTP databází byla neefektivní a do firem se zavádí různé analytické nástroje. Jedním z nich je technologie OLAP (Online Analytical Processing), která je primárně určena pro snadnou analýzu dat. Při řešení své bakalářské práce jsem se zabýval získáním dat z relační databáze, jejich úpravou a následným vložením do OLAP databáze, aby je bylo možné analyzovat. Tento proces se obecně označuje zkratkou ETL (z anglických slov Extraction, Transformation a Load). Cílem práce bylo vytvořit program ve spolupráci s firmou GEMMA Systems, která dodává informační systémy a související služby pro výrobní podniky [5]. Tento program bude provádět přenos dat, poskytnutých od zmíněné firmy, z relační databáze reprezentovanou Microsoft SQL Serverem 2000 (dále jen MS SQL Server 2000) do OLAP datové kostky, v databázi Infor Performance Management OLAP (dále jen Infor PM OLAP). Bakalářská práce se dělí na dvě části. V první teoretické se věnuji obecně principu přenosu dat z OLTP do OLAP databází, dále pak konkrétně převodu dat z MS SQL Serveru 2000 do Infor PM OLAP. Jsou také nastíněny možnosti analýzy dat z OLAP kostky v programu Microsoft Excel. Ve druhé části jsou aplikovány poznatky z teoretické části na tvorbu vlastního programu, ke kterému byla podkladem data od společnosti GEMMA Systems.


2

2 Obecný princip importu dat z OLTP systémů do OLAP kostek Existuje velké množství OLTP systémů i nástrojů OLAP [7]. Tím vzniká velké množství různých kombinací a je na každé organizaci, jaké softwarové řešení si vybere a bude provozovat. Obecně existuje několik základních možností, pokud se organizace rozhodne analyzovat svá data z provozních systémů pomocí technologie OLAP. Tyto možnosti zachycuje obr. 1. Jeho jednotlivé komponenty jsou vysvětleny podrobněji níže v samostatných podkapitolách.

Obr. 1

Různé způsoby analýzy dat


3

Z technického hlediska nic nebrání tomu, aby organizace analyzovala data přímo z provozních systémů. Je to však velmi neefektivní a použitelné pouze u velmi malých organizací. Navíc data z provozních systémů je pro analýzu potřeba

vyexportovat

pomocí

jazyka

SQL,

což

vyžaduje

pracovníka

s jeho znalostí. Další možností je analyzovat data pomocí technologie OLAP, která je určena pro manažery a analytiky. Tudíž není potřeba větší odborné znalosti. Tato technologie může být přímo nad provozními daty. To je ovšem stejně neefektivní jako analyzovat přímo provozní data. Další variantou je data z provozních systémů nahrát do databáze OLAP a pracovat nad takto vyexportovanými daty. U největších organizací, které mají pobočky v různých geografických částech světa a na nejrůznějších platformách, je potřeba nejdříve data integrovat na jedno místo. Proto se využívá datový sklad, kam jsou nahrána data ze všech provozních systémů. A nad datovým skladem se provádí technologie OLAP.

2.1

Provozní systémy

Provozní systémy obsahují základní data organizace, které slouží k jejímu běhu. Nejčastěji jsou to transakční databáze, také označované jako systémy OLTP (Online Transaction Processing), ale může jít o veškerá data která jsou k provozu organizace nutná, například textové soubory, tabulky v programu Microsoft Excel. OLTP systémy jsou určeny pro okamžité zpracování transakcí, kde transakci chápeme jako posloupnost úkonů (příkazů) nad danými daty, která je atomická, konzistentní, stálá a izolovaná od ostatních právě probíhajících transakcí v systému. Tyto požadavky se souhrnně označují akronymem ACID [20]. Jsou to tedy databáze, které ukládají a spravují záznamy o jednotlivých provedených transakcích a zajišťují k nim bezpečný přístup i pro více uživatelů současně. OLTP systémy mohou být reprezentovány objektově orientovanými, objektově-relačními a nejčastěji relačními databázemi.


2.1.1

4

Relační databáze

Tento typ databáze je založen na entitně relačním modelu (E-R model) [19]. V tomto modelu jsou dva základní pojmy entita a relace. Pod pojmem entita v databázích rozumíme nezávislý unikátní objekt, který je charakteristický svými vlastnostmi. Těmto vlastnostem se říká atributy, které mají určitý datový typ. Celkovému výčtu možností, které může nabývat daný atribut, se říká doména [18]. Relace je stejně jako v matematice podmnožina kartézského součinu neprázdných množin. V případě relačních databází jsou neprázdné množiny domény jednotlivých atributů. A relace je jejich podmnožina, která vyjadřuje nějaké vztahy entity s jejími vlastnostmi. V relačních databázích tedy můžeme brát relaci jako synonymum k tabulce, kde jednotlivé řádky, také často označované jako záznamy, představují entity neboli n-tice relace, kde n je počet atributů relace (sloupců tabulky). Takže počet sloupců tabulky nám vyjadřuje aritu relace. Na pořadí řádků v tabulce nezáleží, stejně tak na pořadí sloupců, ale všechny entity v dané relaci mají pořadí sloupců stejné [18]. Pro konkrétní adresaci dané entity v tabulce je zaveden pojem primární klíč, který je unikátní v celé relaci a může být tvořen jedním nebo více atributy, avšak tato množina atributů musí být minimální pro unikátní určení řádku tabulky. Relační databáze je tedy tvořena množinou relací (tabulek). K manipulaci s těmito tabulkami slouží operace selekce, projekce a spojení. Selekce vybírá záznam (řádek) tabulky, projekce vybírá atributy (sloupce) tabulky a spojení slouží k propojení tabulek většinou na bázi klíče [1]. Tyto operace jsou realizované nejčastěji pomocí jazyka SQL. Jako zástupce relační databáze je například MS SQL Server, Oracle, MySQL. 2.1.2

Jazyk SQL

Zkratka SQL je z anglického „Structured Query Language“. Je to standardní jazyk pro definování dat v databázi a pro práci s nimi. Je podobný normálnímu anglickému jazyku. Avšak k jeho používání je potřeba znát jeho syntaxi a strukturu databáze. Proto není vhodný pro manažery a analytiky organizace, kteří pokud mají určitý dotaz, musí jej sepsat v přirozeném jazyce. Předat jej programátorovi, který jej přepíše do jazyka SQL a následně výsledek vrátí zpět


5

dotazovateli. Takový postup je značně pomalý a neflexibilní, proto vznikly analytické

technologie

(OLAP)

[1],

které

jsou

popsány

v samostatné

kapitole 2.4.

2.2

Datová pumpa

Cílem datové pumpy je převést data z provozního systému, který je většinou reprezentován relační databází, do analytického nástroje, například OLAP databáze, datový sklad [4]. Obecně jsou procesy, kde jsou vytažena data z jednoho systému, upravena a vložena do druhého, označované jako ETL procesy. 2.2.1

ETL proces

Zkratka ETL (z anglických slov Extraction, Transformation a Load) označuje procesy, kde jsou vytažena data ze systému, upravena a následně vložena do systému druhého. ETL proces může sloužit pouze k jednomu převodu dat, ale většinou je používán na periodicky se opakující akci. ETL procesy jsou využívány pro převod dat z provozních systémů do analytických nástrojů. Pro tento účel je velice důležitá kvalita ETL procesu, aby byl převod proveden ve stanovený čas a analytik měl svá data k dispozici. Prvním krokem je extrakce, která zajišťuje načtení dat z primárního systému, tj. provozních databází. Zde se klade důraz na to, aby nebyla zatížena provozní databáze a proces proběhl co nejrychleji. Typicky se provádí v noci, případně v takový čas, kdy je nejméně zatížený provozní systém. Data nejsou nějak upravována, jde pouze o jejich vytažení z provozní databáze. Dalším krokem je transformace, která se dá považovat za jádro procesu ETL. Zde dochází ke kontrole integrity. Data jsou upravována do vhodné podoby. Probíhá mapování zdrojových dat na cílovou databázi. Jsou agregována podle požadavků analytika. Dopočítávají se různé hodnoty a ukazatele, jelikož některá data jsou ve zdrojových datech uložena pouze implicitně. Posledním krokem je plnění cílové databáze. V této fázi jsou data po transformaci nahrávána do cílového systému. Ze všech různých zdrojů se vytvoří data stejné struktury, centrálně uložená v analytickém nástroji.


2.3

6

Datový sklad

Datový sklad je v dnešní době nezbytnou součástí nejen velkých firemních informačních systémů, ale i menších, které chtějí kvalitně analyzovat svá data ze všech různých zdrojů a cílit tak svoji produkci lépe na zákazníka. Datový sklad můžeme považovat za určitý typ databáze, kde jsou data dané organizace uložena do struktury, tak aby je bylo možné snadno a efektivně analyzovat. Pojem datový sklad se objevil na začátku 90. let a za jeho zakladatele je považován William H. Inmon, který jej definoval jako „podnikově strukturovaný depozitář subjektově orientovaných, integrovaných, časově proměnlivých, historických dat použitých na získávání informací a podporu rozhodování. V datovém skladu jsou uložena atomická a sumární data“ [14]. Protože je definice složitá, jednotlivé pojmy z ní jsou vysvětleny níže podle [7][14]. • Subjektově orientovaná data – V provozních systémech jsou data shromažďována podle aplikace, ve které jsou vytvořena. Například faktury jsou na jednom místě a na jiném jsou informace o zákaznících. V provozních systémech jde o to, aby data měla co nejpodobnější strukturu a nebyla náročná jejich správa. Zatímco u datového skladu je kladen důraz na předmět zájmu, kterým je například zákazník, produkt, prodejna. • Integrovaná data – Datový sklad čerpá data z různých zdrojů, může být plněn i externími zdroji. Je potřeba tyto různé zdroje integrovat, tzn. sjednotit. O to se stará proces ETL, který je popsaný v samostatné kapitole 2.2.1. • Časově proměnlivá data – V analýze je velmi důležitý údaj čas. Většinou se provádí analýza určitého období. Například analytik chce zjistit, o kolik organizaci vzroste tržba o Vánocích nebo za určitý měsíc. Aby to bylo možné, jsou data do skladu ukládána s časem. Díky tomu je vidět vývoj organizace v čase. Na rozdíl od provozních systémů, kde jsou data stále aktuální a tudíž čas nehraje tak důležitou roli. Protože plnění skladu je periodická činnost, čas je možné doplnit, i když není obsažen v provozních databázích.


7

• Historická data – V provozním systému se uchovávají většinou pouze aktuální data. Pro analytiky je však důležité dlouhé období, proto jsou do datového skladu data pouze periodicky zaváděna. A dále se již pouze čtou a nejsou měněna nebo mazána. • Atomická a sumární data – Do datového skladu jsou zaváděna data o různé úrovni podrobnosti. Ta nejméně podrobná nazýváme atomická, dále nedělitelná data. Atomická data slouží jako podklad k vytvoření sumárních dat, což jsou data o menší úrovni podrobností nebo též jemnosti.

2.4

OLAP

Zkratka OLAP (Online Analytical Processing) označuje analytickou technologii, která byla vytvořena pro snadnou práci s daty. Pro ukládání dat se v analytických databázích nevyužívají relace jako u OLTP systémů, ale multidimenzionální struktura. Typicky se pomocí OLAP technologie analyzuje velké množství dat v téměř reálném čase. Výsledky analýz slouží manažerům k rozhodování, ať už v oblasti řízení firmy, řízení ekonomických nebo technologických procesů [15][21]. Technologii OLAP definoval v roce 1993 E. F. Codd se svými spolupracovníky dvanácti pravidly (multidimenzionální konceptuální

pohled,

transparentnost,

dostupnost,

konzistentní

výkon,

architektura klient-server, generická dimenzionalita, dynamické ošetření řídkých matic, podpora pro více uživatelů, neomezené křížové dimenzionální operace, intuitivní manipulace s údaji, flexibilní vykazování, neomezený počet dimenzí a úrovní agregace) [15][2]. 2.4.1

Multidimenzionální model

V provozních

systémech

jsou

data

ukládána

v relačních

databázích

v dvojrozměrných relačních tabulkách. Po agregaci a sumarizaci relačních dat získáme multidimenzionální strukturu – kostka. Můžeme tedy říct, že tabulka v relačním modelu je ekvivalentem pro kostku v multidimenzionálním modelu.


8

Kostka je tvořena tabulkou faktů (hodnoty kostky) a dimenzí (strany kostky). Přestože mluvíme o kostce, nemusí jít pouze o tři dimenze (strany kostky), ale je to volitelné podle požadavků analytika [15][2].

Obr. 2

OLAP kostka s třemi dimenzemi [9]

Na obr. 2 je vidět multidimenzionální kostka, která má tři dimenze (produkt, prodejna a čas). Jednotlivé údaje (hodnoty) jsou v průnicích těchto dimenzí. 2.4.2

Logická realizace

Existují dvě základní logické realizace, které jsou pojmenované podle tvaru schématu. Prvním je hvězdicové schéma, ve kterém je kostka reprezentovaná tabulkou faktů, která obsahuje cizí klíče, tj. primární klíče dimenzí. Toto schéma nemá normalizované dimenze, ale poskytuje vysoký „dotazovací výkon“, protože není potřeba nic za běhu dopočítávat a spojovat [12][15]. Druhou možností je schéma sněhové vločky, kde se dimenze může skládat z relačně svázaných tabulek. Tabulka faktů tedy obsahuje cizí klíče pouze na některé dimenze a tyto dimenze mohou obsahovat cizí klíče na další dimenze. Vzniká tak schéma podobné sněhové vločce. Je tedy jednodušší plnění, ale dotazovací výkon je menší [12][15]. 2.4.3

Fyzická realizace

Pokud jsou data pro analýzu uložena v relační podobě, používá se označení ROLAP (Relational Online Analytical Processing). V této fyzické realizaci je


9

potřeba multidimenzionální dotazy automaticky překládat na SQL příkazy. Nevýhodou je pomalejší přístup k datům oproti způsobu MOLAP. Výhodou je možnost analýzy aktuálních dat z relační databáze a menší nároky na úložnou kapacitu [13][6]. MOLAP (Multidimensional Online Analytical Processing) je řešení s čistě multidimenzionálním uložením dat. Výsledky agregace a sumarizace dat jsou již vypočítány a uloženy v databázi, díky tomu je rychlý přístup k datům pro analytika, ale jsou velké nároky na úložnou kapacitu, protože dochází k redundanci dat [13][6]. Proto se často využívá fyzické realizace typu HOLAP (Hybrid Online Analytical Processing), která je kombinací mezi způsobem ROLAP a MOLAP. Data jsou uložena v relační databázi, ale pro zrychlení přístupu k datům, jsou některé sumarizace vypočítány a uloženy do multidimenzionální struktury [6].

2.5

Presenční vrstva

Presenční vrstva, nebo také klient, označuje aplikace, na kterých uživatel může sledovat výstupy. Typický přiklad je Microsoft Excel, který je velmi oblíbený, protože uživatele ho dobře znají. Podrobněji o analýze dat v programu MS Excel je napsáno v kapitole 3.4, kde je rozebrána analýza dat z databáze Infor PM OLAP.


10

3 Importy dat z Microsoft SQL Serveru do Infor PM OLAP Tato práce se věnuje převodu dat z MS SQL Serveru 2000 do databáze Infor PM OLAP. Na obr. 3 je červenou barvou vyznačen způsob analýzy pro tento konkrétní případ. Pro ilustraci jsou na obrázku i ostatní způsoby.

Obr. 3

Analýza dat v Infor PM OLAP z provozního MS SQL Serveru

MS SQL Server je použit jako provozní systém. Z něj jsou data importována do OLAP databáze reprezentované Infor PM OLAP, nad kterou zůstává presenční vrstva například v podobě produktu MS Excel.


3.1

11

Microsoft SQL Server 2000

Microsoft SQL Server 2000 je databázový server určený pouze pro operační systém Microsoft Windows. Pro ukládání dat používá relační model a pro manipulaci s daty jazyk SQL [17]. Je možné do něj doinstalovat i analytické služby, které však pro tuto práci nebudou potřeba, protože pro analýzu jsou využívány nástroje Infor PM. Základními nástroji jsou SQL Server Service Manager, který slouží ke startu, pozastavení nebo zastavení databázového serveru. Je možné nastavit automatický start serveru při startu operačního systému. Administraci umožňuje vykonávat SQL Server Enterprise Manager. Pomocí této aplikace lze například spravovat databáze, uživatelské účty, nastavovat strategii údržby a zálohování údajů v databázi. Dále je důležitá konzolová aplikace SQL Server Query Analyzer, ve které uživatel může zadávat SQL dotazy. Po dotazu uživatele se zobrazuje odezva databázového serveru, je to tedy vhodný nástroj pro ladění příkazů v etapě vývoje. Již zmíněné analytické služby MS SQL Serveru jsou spravovány pomocí aplikace Analysis Manager, která pracuje s multidimenzionální databází.

3.2

Infor Performance Management (Infor PM)

Infor PM od společnosti Infor Global Solutions je balík analytických nástrojů. Tento balík je určen pro společnosti, které chtějí lépe sledovat, měřit a řídit výkonnost podniku, čímž získají výhodu nad konkurencí. Infor PM umožňuje jednoduše prezentovat a analyzovat data, která mohou být z různých zdrojů, proto může organizace zjednodušit a zefektivnit finanční procesy a vytvářet lépe strategie. Infor PM obsahuje nástroje označované Business Process Applications (BPA) a Business Specific Analytics (BSA). BPA slouží pro finanční a podniková oddělení, zatímco BSA pro specifické výzvy postavené na odvětvích a rolích [11]. Mezi BPA nástroje patří Infor PM Forecasting, který umožňuje organizaci sbírat a analyzovat data o prodeji a potenciálních zakázkách a uživateli dělat nad těmito daty predikce o výkonnosti, trendech a sezónních modelech. Dalším nástrojem je Infor PM Planning and Budgeting, který umožňuje testovat různé obchodní scénáře, zjišťovat trendy ovlivňující


12

výkonnost a zkrátit čas potřebný na tvorbu rozpočtů. Pomocí Infor PM Financial Consolidation organizace může řídit kompletní proces konsolidace finančních dat z různých oddělení a globálních zastoupení velice efektivně. Nástroj Infor PM Strategic Management monitoruje aktivity v rámci obchodních jednotek a jednotlivců, jak přispívají k naplnění strategických cílů. Spojuje strategické záměry s operativními plány [3]. Mezi BSA nástroje patří aplikace Infor PM Administration, která slouží pro administraci OLAP databáze v Infor PM. Dají se v ní vytvářet uživatelské účty, dimenze, kostky, spravovat a modifikovat celé databáze. Nástroj Infor PM OLAP je optimalizovaná databáze, která slouží k ukládání dat v multidimenzionálním modelu. Infor PM OLAP je jádrem balíku Infor PM a jsou do něj ukládána data z provozních systémů. Ostatní nástroje využívají tato uložená data. Infor PM Office Plus a Infor PM Application Studio jsou prezenční vrstvou, která slouží pro prezentaci dat. Jejich společným znakem je intuitivní ovládání a jednoduché uživatelské prostředí [16]. 3.2.1

Pojmy v Infor PM OLAP

U každého analytického nástroje jsou různé konvence a různý způsob implementace. U Infor PM OLAP databáze je například v programu Infor PM Administration tabulka faktů spojena s datovou kostkou. Vytvořením datové kostky se tedy vytváří i tabulka faktů. Dále u dimenzí je v Infor PM OLAP zaveden primární klíč, jako hlavní prvek (element) dimenze a zbylé, doplňující, atributy v dimenzi jsou v samostatných atribučních tabulkách. 3.2.2

Funkce Infor PM OLAP pro Visual Basic

Infor PM OLAP API pro Visual Basic (VB) se skládá ze dvou DLL knihoven Mis.Alea.Excel.dll

a

Mis.Alea.VBAPI.dll.

Mis.Alea.Excel.dll

je

určen

pro

programování v programu MS Excel, Mis.Alea.VBAPI.dll pro zbylá vývojová prostředí (Visual Basic verze 5, 6; MS Access).


3.3

13

Možnosti převodu dat z MS SQL Serveru do Infor PM OLAP

Existuje několik způsobů převodu dat mezi MS SQL Serverem a Infor PM OLAP databází. Obecně však můžeme popsat tři základní způsoby, které se od sebe liší náročností z pohledu uživatele, financí a programátorských znalostí. 3.3.1

Ruční import

Nejvíce náročnou metodou na lidskou práci, ale nejlevnější formou je provést import ručně. To lze provést dvěma základními způsoby. Prvním je vyexportovat si v MS SQL Serveru data do textového souboru a v Infor PM OLAP provést import tohoto textového souboru. Druhou možností je připojit se k datům v MS SQL Serveru přes produkt MS Excel a z něj data importovat do Infor PM OLAP. Na obě tyto varianty není potřeba přílišná programátorská znalost, ale jsou velice náročné na lidskou práci a u periodicky se opakujících se plnění OLAP databáze je toto řešení nevhodné. 3.3.2

Vytvoření vlastní datové pumpy

Toto řešení je často používané, protože jde o rozumný kompromis mezi drahými ETL nástroji a manuálním nahráváním dat do OLAP databáze. Pro případ převodu dat z jednoho zdroje v našem případě MS SQL Serveru do Infor PM OLAP je toto řešení nejvhodnější. Je zapotřebí programátora, který vytvoří konkrétní ETL proces nad danými zdrojovými daty a strukturou. Analýza a návrh ETL procesu Prvním krokem je určení dat na zdrojovém systému, která budou vytažena. Tato informace je získána od koncového uživatele analytického nástroje, protože podle jeho požadavků, jaká data chce analyzovat, jsou data vybírána ze zdrojového systému. Dalším krokem je vytažení dat. U obecných ETL nástrojů je vytažení dat velice náročná činnost, protože tato data mohou být z nejrůznějších zdrojů a ETL nástroj se musí umět ke každému připojit a pracovat s ním. Následuje krok transformace dat do jednotné podoby. Vytažená data jsou z nejrůznějších zdrojů a je tedy potřeba, aby se provedla


14

jejich úprava do jednotné podoby, byly doplněny chybějící hodnoty a další problémy, které řeší transformace, jsou popsány níže podle [15][6]. • Nejednoznačnost dat (Konvence názvů) – Tento problém nastává, pokud je ve zdrojových datech ukládán jeden element pod různými názvy. Například pokud je vyžadována národnost, v databázi se nám může objevit „Česká“, „Czech“, „CZ“. Po transformaci by tato data měla být sjednocena například pod „CZ“. • Chybějící hodnoty – Pokud je v databázi místo očekávané hodnoty prázdná hodnota, v databázích označována jako NULL, pak je potřeba buď zkusit dohledat hodnotu z jiných zdrojů, nahradit nějakou námi vytvořenou hodnotou „neuvedeno“ nebo pokud jde o zanedbatelnou hodnotu pro analýzu, můžeme ji vypustit. • Různé peněžní měny a jednotky – V každém provozním systému mohou být

vedeny

informace

v různých

měnách

případně

v rozdílných

jednotkách. Jako příklad by mohl být server v Anglii, kde je jiný metrický systém, a druhý třeba v České republice. S tímto se proces transformace musí vyrovnat převodem na jednu jednotku nebo zavést pro každou jednotku (měnu) vlastní hodnotu a nový prvek pro univerzální hodnotu po přepočtu, ze všech jednotlivých jednotek. • Referenční integrita – Ve zdrojových datech nejsou pouze data, ale důležité jsou i vztahy mezi nimi. Je například potřeba vědět, který pracovník pracuje ve kterém oddělení, a jaká oddělení jsou na jednotlivých pracovištích. Problém nastane, pokud se zruší nějaká takováto vazba a v systému zůstanou pracovníci bez zařazení. Ti mohou potom v datovém skladu vytvářet špatné výsledky pro analytiky a tak i špatná rozhodnutí. • Chybějící datum – U většiny údajů je důležitý čas jejich uskutečnění. Analytik tak může zjišťovat, jak si v jakém období organizace vedla. Také je důležitý při pravidelném periodickém nahrávání do datového skladu, aby stačilo pouze nahrávat nová data, která doposud ve skladu nejsou.


15

Po vytažení a transformování dat do jednotné podoby následuje krok mapování dat na cílovou databázi. To znamená určit, který atribut z jaké zdrojové tabulky bude v jaké dimenzi, atribuční tabulce nebo jako hodnota v cílové databázi. Lze použít následující tabulku [8]. Tab. 1

Příklad specifikace mapování

Cílový umístnění

Zdrojová

Zdrojový

Může

Pravidlo pro

tabulka

sloupec

být

mapování

NULL Klíč dimenze

PRODUCT

ID

ne

1:1; Plus navíc prvky „NEZADANO“ a „CELKEM“

Atribuční tabulka

PRODUCT

CODE

ano

KOD_PRODUKTU

1:1; Pokud je NULL, tak nevkládat celý záznam

Atribuční tabulka

PRODUCT

NAZEV_PRODUKTU

T_ DESCRIPTION ne

1:1

(transformace)

V tab. 1 je vidět mapovaní tří atributů zdrojové tabulky PRODUCT na nespecifikovanou dimenzi. Sloupec „Může být NULL“ vyjadřuje, zda může zdrojový sloupec nabývat hodnoty NULL. Ve sloupci „Pravidla pro mapování“ se může zadat poměr mapovaných hodnot jedna ku jedné, tzn. každý prvek zdrojové tabulky bude i prvkem v dimenzi jako klíč nebo v atribuční tabulce. Dále se mohou objevit další pravidla, která by měla být pochopitelná ze slovního vyjádření. Data máme transformovaná, ale je potřeba ještě provést čištění. To znamená například vyřazení duplicitních řádků, které byly z různých zdrojů, ale obsahují stejné údaje a zkreslily by výsledky analýzy. Posledním krokem je vyčištěná a transformovaná data vložit podle mapovacích pravidel do cílové databáze. Dalším krokem v návrhu je vytvoření modelu datových toků v ETL procesu, ten obsahuje závislosti jednotlivých toků v ETL procesu. Jde tedy o diagram, na kterém jsou zobrazeny jednotlivé dílčí procesy ETL procesu, které se starají o transformaci, ošetření chyb, ověření dat, uložení dat do cílové databáze. Ukázka je na obr. 4.


Obr. 4

16

Ukázka modelu datových toků v ETL procesu

Tento průběh návrhu je platný obecně pro ETL nástroje, my však vytváříme ETL program, který pracuje nad konkrétními daty s nám známou strukturou, proto je návrh jednodušší a to zejména ve fázi vytažení a čištění dat. Samozřejmě i transformace a mapovaní je značně jednodušší než u obecných ETL nástrojů, které umí načítat data z nejrůznějších zdrojů. Implementace Infor PM OLAP je vhodné plnit pomocí programu, který je napsaný buď v programovacím jazyku Visual Basic (VB) nebo .NET, protože pro tyto dva jazyky jsou dodávány knihovny s funkcemi pro práci s Infor PM OLAP. Pro připojení k MS SQL Serveru je spousta možností. Jednou z nich je využít ActiveX Data Objects (ADO). Tato technologie umožňuje uživatelům VB snadno přistupovat k různým databázím, které podporují ODBC (Open Database Connectivity), tj. jednotný přístup k datům v databázích. Takže například k databázi Oracle nebo MS SQL. Další průběh implementace je závislý na návrhu ETL programu. Zaleží na zdrojových datech, jaké jsou potřeby transformace, mapování na cílová data. Vložení dat do cílové databáze Infor PM OLAP se provede za pomocí funkcí z knihovny od společnosti Infor pro programovací jazyk VB nebo .NET.


3.3.3

17

Využití existujícího ETL nástroje jako datové pumpy

Toto řešení je velice drahé, tudíž vhodné pro velké organizace, které svá data mají z mnoha různých zdrojů a využívají tak datový sklad. Pro tyto organizace se vyplatí pořídit si robustní ETL nástroj, který dokáže integrovat data z nejrůznějších zdrojů, transformovat je do jednotné podoby a následně vyexportovat do datového skladu, či OLAP databáze. Takovými nástroji jsou například od firmy Microsoft Data Transformation Services (DTS) ve MS SQL Serveru 2000. Od verze 2005 se jmenuje tento ETL nástroj SQL Server Integration Services (SSIS). Pro převod dat z MS SQL Serveru 2000 do Infor PM OLAP je doporučován produkt od firmy Infor, Import Master. Jedná se o robustní ETL nástroj, který je uživatelsky přívětiví a umožňuje nad zpracovanými daty provádět různé operace nebo připravit programovou logiku [10].

3.4

Analýza dat z Infor PM OLAP v programu MS Excel

Jak již bylo popsáno v kapitole 2.5 program MS Excel je klientem, nebo také prezenční vrstvou, pro analýzu. V našem případě tedy slouží pro zobrazení dat z OLAP databáze Infor PM OLAP. Připojit se k analytickým službám je možné ze všech počítačů, které mají přístup k analytickému serveru a ke konkrétním výsledkům analýzy. V Demu od společnosti Infor je dodáván MS Excel 2000, proto bude ukázán postup v tomto programu. Postup je však velmi podobný ve všech verzích programu MS Excel. Připojení k OLAP databázi je možné přes položku menu Data – Načíst externí data – Nový databázový dotaz. Ve zobrazeném dialogovém okně se vybere záložka Datové krychle OLAP. A po potvrzení tlačítkem OK se dostaneme k dialogovému oknu pro připojení k analytickému serveru. Je potřeba zadat název nového připojení a zprostředkovatele, v našem případě je zprostředkovatel Infor PM ODBO Provider. Po stisknutí tlačítka Připojit se dostaneme k samotnému připojeni do Infor PM OLAP. V něm se zadává typ připojení, uživatelské jméno, heslo, server a databáze, ke které se chceme připojit. Po dokončení se vytvoří zdroj údajů a můžeme zobrazovat


18

údaje z OLAP databáze. K tomu slouží kontingenční tabulky [22]. MS Excel nám pro snadnější práci nabízí Průvodce kontingenční tabulkou.

Obr. 5

Zobrazení dat v kontingenční tabulce

Na obr. 5 je ukázka analýzy, kolik organizace utržila v jaké měně za jednotlivé roky. Tato kontingenční tabulka je převedena v graf zobrazený na obr. 6.

Obr. 6

Zobrazení dat v podobě grafu


19

4 Tvorba programu na převod dat Tato práce je ve spolupráci s firmou GEMMA Systems, která se zabývá nasazováním informačních systémů a nástrojů pro analýzu dat do podniků. Jedním z produktů této firmy je Infor PM, jehož jádrem je analytická databáze Infor PM OLAP. Firma Infor nabízí pro své obchodní partnery ukázkové Demo systému s Infor PM a provozní databází MS SQL Server 2000. Toto Demo slouží jako ukázka pro potenciální zákazníky. Není tedy běžně dostupné, ale dobře simuluje způsob fungování Infor PM. Toto demo je ve formě virtuálního operačního systému, konkrétně Microsoft Windows XP Professional. Společnost GEMMA mi poskytla data databáze Visual (její strukturu popíšu níže), kterou jsem nahrál do MS SQL Serveru v Demu od společnosti Infor. Tato databáze Visual je naplněna daty zákazníka, takže jde o opravdovou provozní databázi. Nad takto připraveným systémem jsem vytvořil vlastní program, který importuje data z relační databáze (MS SQL 2000) do databáze Infor PM OLAP.

4.1

Požadavky

Požadavky na program byly zadány firmou GEMMA Systems. Program by měl být schopen se připojit ke vzdálenému provoznímu MS SQL Serveru a vyexportovat z něho předem určená data z databáze Visual. Tato data mohou být budoucí dimenze nebo hodnoty v OLAP databázi. U dimenzí má mít uživatel možnost výběru, které z nich chce importovat do OLAP databáze. U hodnot má mít možnost výběru, z jakého časového období se mají vzít z provozního serveru. Jednotlivé dimenze, které se mají vytvořit v Infor PM OLAP, jsou zákazník, skupina položek, středisko, měsíce, roky, měna a účet. Z těchto dimenzí bude složena datová kostka tržby, která bude obsahovat finanční částky pro dané dimenze. Program by se měl umět vypořádat s chybějícími údaji, vytvořit hierarchickou strukturu a přidat atribuční tabulky dimenzí.


4.2 4.2.1

20

Analýza a návrh Určení dat na zdrojovém systému, která budou vytažena

Společnost Infor produkuje také ERP systémy, jedním z nich je Infor ERP Visual. Jelikož je to celý ERP systém, který je velice rozsáhlý, ve svém programu se věnuji převodu pouze určité části z tohoto systému. Z databáze Visual budu používat pouze tabulky, které jsou zobrazeny na obr. 7. Jedná se o oblast z účetnictví.

Obr. 7

Zdrojová data (výřez z databáze Visual)

Každá hlavička faktury (tabulka RECEIVABLE) je vázána na zákazníka (tabulka CUSTOMER) a měnu (tabulka CURRENCY). Každá faktura může obsahovat více řádků (tabulka RECEIVABLE_LINE). Na každý řádek jsou pak navázány tabulky skupiny položek (tabulka PRODUCT), účetní osnovy (tabulka ACCOUNT) a střediska (tabulka L_DIM1_CENTRE). Konkrétně se budeme zajímat pouze o atributy, které jsou na obr. 7 v tabulkách zatrhnuté.


4.2.2

21

Vytažení dat

Ke komunikaci s MS SQL Serverem, ze kterého budou data vytažena, je zvolena technologie ActiveX Data Objects (ADO). V této technologii je spousta objektů pro práci s databází. My využíváme pouze tři z nich. Začneme připojením k databázi pomocí objektu Connection. Dotaz na databázi provedeme pomocí objektu Command a následné zpracování výsledku dotazu provedeme pomocí objektu RecordSet. Vytažená data dále zpracujeme programem. 4.2.3

Transformace dat do jednotné podoby

Jednotlivé atributy ze zdrojových tabulek upravíme, pokud to bude potřeba. Upravené atributy označíme pro následné mapování prefixem „T“, aby bylo zjevné, že se jedná o již transformovaná data, nikoliv data ze zdrojové databáze. Níže jsou popsány transformace pouze u tří tabulek. Ostatní transformace jsou popsány v příloze 1. Tabulka CUSTOMER V tabulce CUSTOMER je potřeba transformovat atributy týkající se země zákazníka. Ty jsou ve zdrojovém systému ukládány do dvou různých sloupců STATE a COUNTRY. Po transformaci budeme mít pouze jeden sloupec, který si pro účely mapování nazveme T_STATE. Proces transformace můžeme zapsat takto: Pokud atribut STATE <> NULL, vlož jeho hodnotu do T_STATE Jinak pokud atribut COUNTRY <> NULL, vlož jeho hodnotu do T_STATE Jinak vlož hodnotu „NEZNAMA“ do T_STATE Tabulka PRODUCT V tabulce PRODUCT je hierarchie vytvářena pomocí prvních dvou znaků atributu CODE. Proto si pro mapování vytvoříme pomocný atribut T_CODE, který bude obsahovat první dva znaky atributu CODE, pokud však atribut CODE bude obsahovat pouze dva znaky, pak do T_CODE vložíme hodnotu NULL.


22

Dále je v tabulce PRODUCT potřeba transformovat atribut DESCRIPTION. Pokud atribut DESCRIPTION <> NULL, vlož jeho hodnotu do T_ DESCRIPTION Jinak vlož hodnotu „NEUVEDENO“ do T_ DESCRIPTION Tabulka RECEIVABLE V tabulce RECEIVABLE se upraví datum v atributu INVOICE_DATE pro potřeby mapování, kde je potřeba rok a měsíc. Proto si atribut rozdělíme na pomocné atributy T_ROK a T_MESIC, které budeme využívat při mapování. 4.2.4

Mapování dat

Jednotlivé tabulky, které specifikují způsob mapování, jsou vysvětleny. V případě, že daná dimenze má hierarchickou strukturu, je také popsána. Níže je pouze pro ukázku popsáno mapování dimenzí ZAKAZNIK a STREDISKO a tabulky faktů. Všechny ostatní specifikace mapování jsou v příloze 2. Dimenze ZAKAZNIK Zákazníci jsou ve zdrojové databázi Visual v tabulce CUSTOMER. Budou nás z této tabulky zajímat atributy ID, NAME a T_STATE, který vznikne po transformaci atributu STATE a COUNTRY. Tab. 2

Specifikace mapování na dimenzi ZAKAZNIK

Cílový umístnění Klíč dimenze

Zdrojová

Zdrojový

Může být

Pravidlo pro

tabulka

sloupec

NULL

mapování

CUSTOMER

ID

ne

1:1; Plus navíc prvek „CELKEM“

Atribuční tabulka

CUSTOMER

NAME

ano

NAZEV_ZAKAZNIKA


Atribuční tabulka NAZEV_ZEME

CUSTOMER

T_STATE (transformace)

ne

1:1


23

V dimenzi ZAKAZNIK je pouze hierarchická struktura tvořena prvkem „CELKEM“, který sdružuje všechny zákazníky. Z tab. 2 je vidět, že atribut ID bude klíčem (tedy cizí klíč pro tabulku faktů), NAME a T_STATE atribučními tabulkami. Dimenze STREDISKO Střediska jsou uložena v provozní databázi MS SQL Serveru v tabulce L_DIM1_CENTRE. Pro vytvoření dimenze STREDISKO je potřeba atributy ID, DESCRIPTION, který je transformován na T_DESCRIPTION, a PARENT_ID. Hierarchická struktura je tvořena pomocí atributu PARENT_ID, kde nejvyšší prvek (jeho atribut PARENT_ID obsahuje hodnotu NULL) je zařazen pod prvkem „CELKEM“ (tab. 3). Tab. 3

Specifikace mapování na dimenzi STREDISKO


Zdrojová

Zdrojový

Může být

Pravidlo pro

tabulka

sloupec

NULL

mapování

L_DIM1_CENTRE ID

ne


Atribuční tabulka

L_DIM1_CENTRE T_DESCRIPTION

NAZEV_STREDISKA

ne

1:1

(transformace)

Tabulka faktů – kostka TRZBY Tabulka faktů je plněna ze dvou tabulek (RECEIVABLE_LINE a RECEIVABLE), které

jsou

spojeny

pomocí atributu

INVOICE_ID.

Pokud se

atribut

CURRENCY_ID nerovná hodnotě „CZK“, pak budeme stejný záznam vkládat dvakrát. U duplicitního záznamu nastavíme cizí klíč dimenze MENA na „DOMACI_MENA“ a hodnotu přepočteme pomocí argumentu SELL_RATE z tabulky RECEIVABLE. Přepočet znamená vynásobení kurzu (SELL_RATE) a hodnoty (AMOUNT) (tab. 4).


Tab. 4

24

Specifikace mapování na tabulku faktů – kostka TRZBY


Zdrojová

Zdrojový sloupec

Může

Pravidlo pro

být

mapování

tabulka

NULL Hodnota

RECEIVABLE_LINE AMOUNT

ne

1:1

Cizí klíč na dimenzi

RECEIVABLE

ne

1:1

ano

1:1; Pokud je

CUSTOMER_ID

ZAKAZNIK Cizí klíč na dimenzi

RECEIVABLE_LINE L_COST_CENTRE

STREDISKO

NULL, tak vložit cizí klíč „NEZADANO“


RECEIVABLE

CURRENCY_ID

ano

MENA



RECEIVABLE_LINE GL_ACCOUNT_ID

ne

1:1

RECEIVABLE

ne

1:1

ne

1:1

ano

1:1; Pokud je

UCET Cizí klíč na dimenzi ROKY

T_ROK (transformace)

Cizí klíč na dimenzi MESICE

RECEIVABLE

T_MESIC (transformace)

Cizí klíč na dimenzi SKUPINA_POLOZEK

RECEIVABLE_LINE L_PRODUCT_CODE

NULL, tak vložit cizí klíč „NEZADANO“

4.2.5

Čištění dat

Data pocházejí z jednoho zdroje a referenční integrita zdrojových dat je zajištěna v databázi MS SQL, proto tento krok návrhu je přeskočen. Při návrhu robustního ETL nástroje je však tento krok velmi důležitý, aby data pro analýzu byla korektní.


4.2.6

25

Vložení do OLAP databáze

Samotné vložení dat se provede za pomocí funkcí knihovny pro Visual Basic od společnosti Infor. Konkrétně použité funkce jsou popsány níže v kapitole implementace. 4.2.7

Model datových toků v ETL procesu

Níže jsou zobrazeny a popsány pouze dva procesy plnění dimenzí a proces plnění tabulky faktů Všechny ostatní procesy jsou v příloze 3.. Na následujícím obr. 8 je zobrazen proces plnění dimenze ZAKAZNIK. Na začátku je potřeba získat data z provozní databáze VISUAL, konkrétně z tabulky CUSTOMER. Jsou vybrány atributy ID, NAME, STATE a COUNTRY. Dále se proces větví. Pokud atribut NAME obsahuje hodnotu NULL, záznam je bez uložení zahozen. V případě, že neobsahuje hodnotu NULL, pokračuje proces transformací, ve které je potřeba sjednotit atributy STATE a COUNTRY. Tato transformace je více

popsána

v kapitole 4.2.3

o

transformacích.

Z důvodu

vytvoření

hierarchické struktury je přidán prvek „CELKEM“ do atributu ID a následně vytvořena samotná hierarchie, která je popsána v kapitole 4.2.4 o mapování. Na závěr jsou data vložena do dimenze ZAKAZNIK.

Obr. 8

Model datových toků v programu (plnění dimenze ZAKAZNIK)

U procesu plnění dimenze UCET, který je na obr. 9, nedochází k žádnému větvení. V procesu se odehrávají po sobě následující úkony. Na počátku jsou načteny data z tabulky ACCOUNT, konkrétně atributy ID, DESCRIPTION


26

a PARENT_ACCT_ID. Je upraven atribut DESCRIPTION ve fázi transformace. Přidán prvek „CELKEM“ do atributu ID. Vytvořena hierarchická struktura dimenze za pomoci atributu PARENT_ACCT_ID, kde nejvýše je nově přidaný prvek CELKEM. Posledním krokem je uložení dat do dimenze UCET.

Obr. 9

Model datových toků v programu (plnění dimenze UCET)

Složitější proces je vložení hodnot do tabulky faktů, který je zobrazen na obr. 10. Načtení dat je potřeba provést ze dvou tabulek MS SQL Serveru. Jedná se o tabulky RECEIVABLE a RECEIVABLE_LINE. Tudíž hlavičky faktur a jejich řádky, jejichž vztah je 1:N. Pro jeden záznam hlavičky může existovat více řádků. Spojeni těchto tabulek je zajištěno pomocí atributu INVOICE_ID a jsou vybrány atributy AMOUNT, SELL_RATE, CUSTOMER_ID, L_COST_CENTRE, CURRENCY_ID, GL_ACCOUNT_ID a L_PRODUCT_CODE. Po načtení dat následuje větvení procesu. Pokud atribut CURRENCY_ID neobsahuje NULL, pokračuje se transformací, kde jsou doplněny hodnoty „NEZADANO“ atributům L_COST_CENTRE a L_PRODUCT_CODE, když je jejich hodnota rovna NULL. Dále je další větvení podle hodnoty atributu CURRENCY_ID. Pokud neobsahuje hodnotu „CZK“ (údaj není v české měně), tak se přidá nový záznam, kde je přepočtena hodnota do české měny pomocí atributu SELL_RATE a hodnota atributu CURRENCY_ID je „DOMACI_MENA“. Následuje uložení dat do tabulky faktů. Pokud byl údaj v české měně uloží se pouze jeden záznam, pokud bylo potřeba přidat nový záznam, do databáze se uloží dva záznamy.


Obr. 10

4.3

27

Model datových toků v programu (plnění tabulky faktů)

Implementace

Programovací jazyk zvolený pro tvorbu programu je Visual Basic (VB) a vývojové prostředí Microsoft Visual Basic 6.0, které je dodávané v Demu od společnosti Infor. Infor poskytuje knihovnu pro VB se spoustou funkcí pro správu OLAP databáze. Použité funkce v programu z této knihovny jsou popsány níže v kapitolách věnujícím se jednotlivým možnostem programu. Implementace se provádí podle návrhu, kde důležitými kroky je transformace a mapování dat. 4.3.1

Vytvoření dimenzí a kostky v Infor PM OLAP

Podle požadavků firmy GEMMA Systems byla struktura dimenzí a kostky vytvořena v Infor PM OLAP. Byla k tomu využita aplikace Infor PM Administration, kde lze vytvářet nové dimenze a datové kotky. Byly vytvořeny dimenze ZAKAZNIK, SKUPINA_POLOZEK, STREDISKO, ROKY, MENA, UCET a MESICE. Všechny jsou plněny vlastním programem kromě dimenze MESICE. Ta je statickou dimenzí, ve které data nejsou upravována a měněna, proto mohla být naplněna názvy českých měsíců předem v nástroji Infor PM Administration. Dále byla vytvořena datová kostka tržeb (TRZBI), která se skládá z dimenzí ZAKAZNIK, SKUPINA_POLOZEK, STREDISKO, ROKY, MENA, UCET a MESICE. Celý datový model je zobrazen na obr. 11.


Obr. 11

4.3.2

28

Model dimenzí a faktů v Infor PM OLAP databázi

Příprava vývojového prostředí – Import a inicializace knihoven

Před tvorbou programu bylo potřeba importovat potřebné knihovny. Jedná se o knihovnu Mis.Alea.VBAPI.dll poskytovanou společnosti Infor pro VB a dále knihovnu pro připojení k MS SQL Serveru Microsoft ActiveX Data Objects 2.8 Library. Knihovnu od společnosti Infor je potřeba při startu programu inicializovat. K tomu slouží funkce MdsInit a na konci uzavřít pomocí funkce MdsClose. Knihovna pro práci s MS SQL Serverem není potřeba inicializovat a použití objektů této knihovny je blíže popsáno v kapitole 4.3.4. Ve vývojovém prostředí VB 6.0 se import knihoven provádí přes záložku Project - > References viz obr. 12. Knihovna Mis.Alea.VBAPI.dll je v tomto výčtu označena ALEA – VBA. Po přidání knihoven do programu je možné ve vývojovém prostředí VB 6.0 si otevřít katalog objektů pomocí klávesy F2, kde po vybrání knihovny MDS se zobrazí všechny funkce pro práci s databází Infor PM OLAP.


Obr. 12

4.3.3

29

Import knihoven do programu ve VB

Hlavní okno programu

Program po spuštění obsahuje pouze jedno hlavní okno a jednotlivé funkce používají ke sdělování informací uživateli pouze dialogová okna. Jedná se například o funkce připojení k MS SQL Serveru a Infor PM OLAP databázi. U MS SQL databáze je po přihlášení zobrazeno v dialogovém okně, zda se přihlášení podařilo nebo případná chyba. Zatímco u přihlášení k Infor PM OLAP je v dialogovém okně uživatel informován o úspěchu a případný neúspěch přihlášení vyvolává dialogové okno pro nové přihlášení. Dále je uživatel informován přes dialogové okno o výsledcích importu dimenzí a hodnot. Při ukončení aplikace je uživatel vyzván k odhlášení z databází (pokud je přihlášen). Na obr. 13 je zobrazeno hlavní okno programu. V levé části jsou dva oddělené formuláře. Vrchní pro připojení do MS SQL Serveru. Spodní pro připojení do Infor PM OLAP databáze. V pravé části jsou jednotlivé možnosti importu, které program umožňuje. Jejich funkce je popsána níže v samostatných kapitolách. Po spuštění aplikace nejsou možnosti v pravé části


30

funkční. Až po úspěšném přihlášení do obou databází lze používat možnosti v pravé části okna. Po odhlášení se opět změní na nefunkční.

Obr. 13

4.3.4

Hlavní okno vlastního programu na import dat

Připojení k MS SQL

Pro přihlášení je potřeba získat od uživatele hodnoty IP adresy serveru, kde běží MS SQL Server s provozními daty, na formuláři prvek server. Dále jméno uživatelského účtu (user id), heslo a databázi. Z těchto údajů se vytvoří řetězec, který se předá objektu Connection z ActiveX Data Objects. O výsledku připojení je uživatel informován pomocí dialogového hesla. 4.3.5

Připojení k Infor PM OLAP

Podobně jako u připojení k MS SQL serveru je potřeba uživatelské jméno (user id) a heslo. Položka server ovšem zde znamená konkrétní databáze v Infor PM OLAP. Tyto údaje jsou předány funkci ServerConnect z knihovny od firmy Infor pro VB. Uživatel je pomocí dialogového okna informován o výsledku.


4.3.6

31

Import vybraných dimenzí

Podle uživatelem vybraných dimenzí se provede jejich import do Infor PM OLAP. Import každé dimenze je samostatná funkce, z tohoto důvodu lze jednoduše přidat novou dimenzi do programu. Import dimenzí probíhá podle návrhu transformace a mapování, obecně jsou u každého importu využity funkce popsané níže. • Funkce z knihovny Mis.Alea.VBAPI.dll od společnosti Infor o DimensionEditBegin je funkce, která umožní editaci dané dimenzi v databázi, je tedy potřeba před každou změnou v jakékoliv dimenzi. Všechny provedené změny je potřeba potvrdit funkcí DimensionEditCommit. o DimensionEditAddElement je funkce na vkládání elementů do dimenze. Umožňuje vložení elementu pod rodiče (jiný element) a automaticky se tak tvoří hierarchie dimenze. o DimensionEditCommit slouží k potvrzení provedených změn v dimenzi. o AtableFieldPutValue slouží pro vkládání prvků do atribuční tabulky dané dimenze. • Metody objektu Recordset z ADO o Open je metoda, které je předáván SQL příkaz buď přes objekt Command z ADO nebo přímo jako řetězec. Tato metoda se tedy stará o provedení dotazu a získání záznamů z databáze. o MoveNext přesune ukazatel na další záznam z databáze. o MoveFirst přesune ukazatel na první záznam získaný z databáze. o EOF označuje konec souboru, neboli poslední záznam. o Close uzavře daný objekt Recordset. Pokud není proveden Commit před uzavřením, dojde k chybě.


4.3.7

32

Import všech hodnot

Import všech hodnot smaže celou dosavadní tabulku faktů a importuje všechna data z provozního systému do datové kostky v Infor PM OLAP. Ke smazání hodnot je potřeba zavést datový prostor, který je následně možné smazat. K jeho vytvoření slouží funkce DataareaDefine z knihovny od společnosti Infor. Tato funkce přijímá argument ve formě matice, podle které je určen výsek dat v dané datové kostce. Tato matice je dvourozměrná. První index určuje hodnotu v dimenzi a druhý dimenzi v kostce. Pokud na nultou pozici dimenze vložíme hvězdičku, vybereme všechny prvky dané dimenze. V našem případě, kde chceme smazat všechny hodnoty, vložíme do každé dimenze na nultou pozici hvězdičku. Smazání provedeme pomocí funkce DataareaSetValue, která nastaví všem prvkům datového prostoru hodnotu prvku v argumentu této funkce. Pro smazání je to hodnota NULL. Po smazání můžeme daný datový prostor smazat pomocí funkce DataareaDestroy. Pro vložení hodnot do datové kostky se využívá objekt Recordset stejně jako v kapitole 4.3.6. Z knihovny od společnosti Infor se pro vložení hodnot využívá funkce DataPutValue. Podle argumentů je vložena hodnota do určité datové kostky s danými cizími klíči na dimenze. 4.3.8

Import hodnot od – do

Většinou jsou data nahrávána do OLAP databáze periodicky za nějaké období. Tato možnost umožňuje importovat hodnoty za určité roky. Implementačně se jedná o omezenou variantu možnosti „Import všech hodnot“. Rozdíl je patrný především u smazání hodnot, kde je potřeba na rozdíl od importu všech hodnot, v matici definující datový prostor určit hodnoty v dimenzi ROKY. Abychom nemuseli v dimenzi ROKY postupně vyplnit všechny hodnoty roků, ve kterých chceme data smazat, vytvoříme si podmnožinu hodnot dimenze. K vytvoření podmnožiny slouží funkce SubsetDefine. Do matice pro definici datového prostoru tak nyní můžeme jednoduše vložit na nultou pozici místo hvězdičky tuto vytvořenou podmnožinu roků. Po smazání vybrané části dat podmnožinu smažeme funkcí SubsetDelete.


4.4

33

Prezentace dat z Infor PM OLAP

Data, která jsme z provozní databáze Visual v MS SQL Serveru importovaly do Infor PM OLAP databáze, je možné analyzovat v programu MS Excel, jak je popsáno v kapitole 3.4. V této kapitole jsou ukázky analýzy konkrétních dat z provozní databáze, která program převedl. Analytik si může vytvořit libovolné sestavy, podle toho, co ho aktuálně zajímá, jejich příklady jsou níže.

Obr. 14

Příklad analýzy středisek z databáze VISUAL

Na obr. 14 jsou vidět tržby za roky 2010 a 2011 v jednotlivých měsících. Tyto tržby jsou dále rozděleny podle střediska (ZN, ZP). Analytik tak může snadno zjistit, v jakém středisku byla větší tržba v konkrétním měsíci. Další sestavou může být pohled na jednotlivé skupiny produktů (neuvedeno, NV, PM, PV, US) v jednotlivé roky v domácí měně (obr. 15).


Obr. 15

34

Příklad analýzy skupiny produktů z databáze VISUAL

Analytik si může zobrazit různou úroveň podrobností, sestavovat si pořadí sloupců i řádků tak, aby data byla snadno analyzovatelná. Další možností je nechat si ke kontingenčním tabulkám vygenerovat graf.

Závěr

35

5 Závěr Cílem této bakalářské práce bylo popsat proces importu dat z relační databáze do OLAP datové kostky. Konkrétně se věnovat převodu z MS SQL Serveru do Infor PM OLAP databáze. Byly představeny různé možnosti převodu a následně realizován převod pomocí vlastního programu. Tento vlastní program je ETL procesem, který pracuje s předem známými daty a jejich strukturou. Je tak výhodnější pro firmu, než nákup robustního ETL nástroje, který umí integrovat data z nejrůznějších zdrojů, což většina, hlavně menších firem, nevyužije. Výstupem této práce je program na převod dat, ve kterém se uživatel může vzdáleně připojit k MS SQL Serveru. Zvolit si dimenze, které chce v OLAP databázi naplnit. Nahrát všechny hodnoty nebo pouze data za zvolené období. Dále tato práce ukazuje jak navrhnout a implementovat ETL proces pro převod dat z MS SQL Serveru do Infor PM OLAP databáze. Firmy, které využívají takového programu na převod dat z provozních systémů do OLAP databáze, dokáží svá data lépe analyzovat, zlepšit tak strategii firmy a zvýšit tak svůj zisk. Jak je popsáno v práci, data z databáze OLAP lze jednoduše analyzovat například pomocí programu MS Excel, s kterým umí pracovat většina pracovníků. Tato práce byla realizována ve spolupráci s firmou GEMMA Systems, které byl program předán, a ta jej schválila. Program tak může být dodáván firmám, které chtějí převést svá data z provozního systému MS SQL Serveru do databáze Infor PM OLAP, aby je mohly analyzovat a lépe tak zhodnotit firemní zdroje.

Literatura

36

6 Literatura [1] BERKA, Petr. Dobývání znalostí z databází. Vyd. 1. Praha: Academia, 2003, 366 s. ISBN 8020010629. [2] BERSON, Alex a Stephen J SMITH. Data warehousing, data mining, and OLAP. New York: McGraw-Hill, 1997, 612 s. ISBN 0070062722. [3] CIO BUSINESS WORLD. Infor uvádí PM 10. [online] 19. 11. 2007 [cit. 201204-10].

Dostupné

z www:

sluzby/infor-uvadi-pm-10-2579>. [4] DOHNAL, Jan; POUR, Jan. Architektury informačních systémů v průmyslových a obchodních podnicích. 1. Vyd. Praha: Ekopress, 1997, 301 s. ISBN 8086119025. [5] GEMMA Systems. Moderní továrna nepotřebuje komín, ale neobejde se bez informačního systému. [online] [cit. 2012-05-03]. Dostupné z www: . [6] HAN, Jiawei. Data mining concepts and techniques. 1st ed. San Francisco: Morgan Kaufmann, 2000, 549 s. ISBN 1558604898. [7] HUMPHRIES, Mark. Data warehousing - návrh a implementace Přel. M. Kocan. 1.vyd. Praha: Computer Press, 2001, 257 s. ISBN 8072265601. [8] HUSÁRIKOVÁ, Světlana. ETL procesy. [online] 2007 [cit. 2012-04-24]. Bakalářská práce. Vysoká škola ekonomická v Praze, Fakulta informatiky a statistiky. Vedoucí práce Daniel Rydzi. Dostupné z: . [9] IBM. DB2 Alphablox Cube Server Overview. [online] [cit. 2012-04-23]. Dostupné z www: .

Literatura

[10]

37

INFOR. Import Master. [online] [cit. 2012-04-12]. Dostupné z www: .

[11]

INFOR. Infor dodává integrované řešení Performance Management pro efektivnější rozhodovací procesy. [online] 16. 11. 2007 [cit. 2012-04-10]. Dostupné z www: .

[12]

INMON, William H. Building the data warehouse. 4th ed. Indianapolis, Ind.: Wiley, 2005, 543 s. ISBN 0764599445.

[13]

JARKE, Matthias. Fundamentals of data warehouses. New York: Springer, 2000, 195 s. ISBN 3540653651.

[14]

LACKO, Luboslav. Business Intelligence v SQL Serveru 2005. Vyd. 1. Brno: Computer Press, 2006, 391 s. ISBN 8025111105.

[15]

LACKO, Luboslav. Datové sklady, analýza OLAP a dolování dat. Vyd. 1. Brno: Computer Press, 2003, 486 s. ISBN 8072269690.

[16]

LLP

GROUP. Infor

PM

(Performance

Management).

[online]

[cit. 2012-04-10]. Dostupné z www: . [17]

QUACKIT. About SQL Server. [online] [cit. 2012-05-03]. Dostupné z www: .

[18]

RYBIČKA, Jiří a Martin SEDLÁK. Úvod do databázových systémů. 1. vyd. Brno: Konvoj, 2001, 35 s. ISBN 8073020130.

[19]

SCHEBER, Anton. Dátabázové systémy. Bratislava: Alfa, 1988, 321 s.

[20]

STRAKA, Miroslav. Vývoj databázových aplikací. Praha: Grada, 1992, 129 s. ISBN 8085424436.

[21]

TRUJILLO, Juan a Il-Yeol SONG. DOLAP '05: proceedings of the Eighth ACM International Workshop on Data Warehousing and OLAP :

Literatura

38

November 4-5, 2005, Bremen, Germany, co-located with CIKM 2005. New York: Association for Computing Machinery, 2005, 114 s. ISBN 1595931627. [22]

WALL.CZ. Kontingenční tabulka I.. [online] 27. 2. 2008 [cit. 2012-04-14]. Dostupné z www: .

Přílohy

39

7 Přílohy Příloha 1:

Transformace dat

Příloha 2: Specifikace mapování Příloha 3: Diagramy procesů v programu Příloha 4: Zdrojové kódy programu (Nahrány do informačního systému Masarykovy univerzity)

Přílohy

Příloha 1

40

Transformace dat

Tabulky CUSTOMER, PRODUCT a RECEIVABLE Jsou popsána v kapitole 4.2.3. Tabulka L_DIM1_CENTRE V tabulce L_DIM1_CENTRE je potřeba transformovat atribut DESCRIPTION. Pokud atribut DESCRIPTION <> NULL, vlož jeho hodnotu do T_DESCRIPTION Jinak vlož hodnotu „NEUVEDENO“ do T_ DESCRIPTION Tabulka CURRENCY V tabulce CURRENCY je potřeba transformovat atribut NAME. Pokud atribut NAME <> NULL, vlož jeho hodnotu do T_ NAME Jinak vlož hodnotu „NEZNAMA“ do T_ NAME Tabulka ACCOUNT V tabulce ACCOUNT je potřeba transformovat atribut DESCRIPTION. Pokud atribut DESCRIPTION <> NULL, vlož jeho hodnotu do T_ DESCRIPTION Jinak vlož hodnotu „NEUVEDENO“ do T_ DESCRIPTION

Přílohy

41

Příloha 2

Specifikace mapování

Dimenze ZAKAZNIK, STREDISKO a tabulka faktů – kostka TRZBY Jsou popsána v kapitole 4.2.4. Dimenze MENA V dimenzi MENA není hierarchická struktura. Tab. 5

Specifikace mapování na dimenzi MENA


Zdrojová

Zdrojový

Může

Pravidlo pro

tabulka

sloupec

být

mapování

NULL Klíč dimenze

CURRENCY

ID

ne

1:1; Plus navíc prvek „DOMACI_MENA“

Atribuční tabulka

CURRENCY

NAZEV_MENY

T_NAME

ne

1:1

(transformace)

Dimenze UCET V dimenzi UCET je hierarchická struktura tvořena pomocí atributu PARENT_ACCT_ID, kde nejvyšší prvek (jeho atribut PARENT_ACCT_ID obsahuje hodnotu NULL) je zařazen pod prvkem „CELKEM“. Tab. 6

Specifikace mapování na dimenzi UCET


Zdrojová

Zdrojový

Může být

Pravidlo pro

tabulka

sloupec

NULL

mapování

ACCOUNT

ID

ne

1:1; Plus navíc prvek „CELKEM“

Atribuční tabulka NAZEV_UCTU

ACCOUNT

T_ DESCRIPTION ne (transformace)

1:1

Přílohy

42

Dimenze ROKY V dimenzi ROKY je pouze hierarchická struktura tvořena prvkem „CELKEM“, který sdružuje všechny roky. Tab. 7

Specifikace mapování na dimenzi ROKY


Zdrojová

Zdrojový

Může být

Pravidlo pro

tabulka

sloupec

NULL

mapování

RECEIVABLE

T_ROK

ne

1:1; Plus navíc

(transformace)

prvek „CELKEM“

Dimenze SKUPINA_POLOZEK V dimenzi SKUPINA_POLOZEK je hierarchická struktura tvořena pomocí atributu T_CODE. Nejvyšší prvek (jeho atribut T_CODE obsahuje hodnotu NULL) je zařazen pod prvkem „CELKEM“. Tab. 8

Specifikace mapování na dimenzi SKUPINA_POLOZEK


Zdrojová

Zdrojový

Může být

Pravidlo pro

tabulka

sloupec

NULL

mapování

PRODUCT

CODE

ne


Atribuční tabulka NAZEV_PRODUKTU

PRODUCT

T_ DESCRIPTION ne (transformace)

1:1

Přílohy

43

Příloha 3

Diagramy procesů v programu

Diagram plnění dimenzí ZAKAZNIK, UCET a tabulky faktů Jsou popsána v kapitole 4.2.7. Diagram plnění dimenze MENA (obr. 16)

Obr. 16

Model datových toků v programu (plnění dimenze MENA)

Diagram plnění dimenze STREDISKO (obr. 17)

Obr. 17

Model datových toků v programu (plnění dimenze STREDISKO)

Přílohy

44

Diagram plnění dimenze ROKY (obr. 18)

Obr. 18

Model datových toků v programu (plnění dimenze ROKY)

Diagram plnění dimenze SKUPINA_POLOZEK (obr. 19)

Obr. 19

Model datových toků v programu (plnění dimenze SKUPINA_POLOZEK)

Importy dat z relační databáze do OLAP datových kostek

Recommend Documents