Bankovní institut vysoká škola Praha Katedra matematiky, statistiky a informačních technologií
Business Intelligence pro hodnocení bakalářských a diplomových prací Bakalářská práce
Autor:
Gabriela Peroutková Informační technologie, Správce informačních systémů
Vedoucí práce:
Praha
Doc. Ing. Bohumil Miniberger, CSc.
duben 2013
Prohlášení: Prohlašuji, ţe jsem bakalářskou práci zpracovala samostatně a v seznamu uvedla veškerou pouţitou literaturu. Svým podpisem stvrzuji, ţe odevzdaná elektronická podoba práce je identická s její tištěnou verzí, a jsem seznámena se skutečností, ţe se práce bude archivovat v knihovně BIVŠ a dále bude zpřístupněna třetím osobám prostřednictvím interní databáze elektronických vysokoškolských prací.
V Praze dne 30. 4. 2013
Gabriela Peroutková
Poděkování Ráda bych touto cestou vyjádřila poděkování vedoucímu bakalářské práce Doc. Ing. Bohumilu Minibergerovi, CSc. za poskytnutí odborných rad, věcné připomínky, ochotu a vstřícný přístup během zpracování této práce. Zároveň bych chtěla poděkovat celé své rodině za velikou podporu a trpělivost po celou dobu studia na této škole.
Anotace Bakalářská práce předkládá základní koncepce a přehledy problematiky Business Intelligence a s ním souvisejících datových skladů. V dalších částech jsou stručně prezentovány základní komponenty systémů Business Intelligence a principy datového modelování včetně příkladů modelů v jednotlivých vrstvách datového skladu. Poslední praktická část je věnována návrhu realizace datového skladu pro hodnocení bakalářských a diplomových prací v prostředí BIVŠ. Klíčová slova: Business Intelligence, datový sklad, datový trh, ETL, multidimenzionální databáze, OLAP.
Annotation The bachelor’s thesis offers basic principles and overview of the Business Intelligence and related data warehousing matters. In the following sections there are briefly basic components of the Business Intelligence systems presented and in addition data modeling principles including examples of models of single data warehouse layers. The last practical section deals with data warehouse concept of evaluating of bachelor’s and diploma thesis in BIVŠ environment. Key words: Business Intelligence, Data warehouse, Data mart, ETL, Multidimensional database, OLAP.
Obsah Úvod.................................................................................................................................... 7 1
2
Úloha Business Intelligence při řízení podnikových procesů ......................................... 8 1.1
Definice Business Intelligence ......................................................................................... 8
1.2
Hodnota a přínos Business Intelligence ........................................................................... 9
1.3
Podniková data ............................................................................................................. 10
Architektura systémů BI a jejich komponenty ............................................................ 11 2.1 2.1.1
Hierarchické a síťové databáze ...................................................................................................... 12
2.1.2
Relační databáze ............................................................................................................................ 13
2.1.3
Multidimenzionální databáze ........................................................................................................ 18
2.2
Zdrojové transakční systémy ......................................................................................... 19
2.3
Datové pumpy (ETL a ELT) ............................................................................................. 19
2.4
Datová kvalita ............................................................................................................... 22
2.4.1
Čištění dat ...................................................................................................................................... 24
2.4.2
Konsolidace dat ............................................................................................................................. 25
2.5
Datový sklad a jeho role ................................................................................................ 26
2.5.1
Dočasné úložiště dat ...................................................................................................................... 26
2.5.2
Úložiště datového skladu............................................................................................................... 27
2.5.3
ODS – Operational Data Store ....................................................................................................... 28
2.5.4
Datové trhy .................................................................................................................................... 28
2.5.5
OLAP – Online analytical processing .............................................................................................. 29
2.6
3
Základem jsou databáze ................................................................................................ 12
Prezentace dat .............................................................................................................. 30
Konceptuální modelování BI ...................................................................................... 32 3.1
Úloha modelování ......................................................................................................... 32
3.2
Modelovací jazyky......................................................................................................... 33
3.2.1
UML – Unified Modellling Language.............................................................................................. 33
3.2.2
ER modely ...................................................................................................................................... 33
5
3.3
Typy datových modelů .................................................................................................. 34
3.3.1
Konceptuální model....................................................................................................................... 34
3.3.2
Logický model ................................................................................................................................ 34
3.3.3
Fyzický model ................................................................................................................................ 35
3.4
Modelování úložiště datového skladu ........................................................................... 35
3.5
Modelování datových trhů ............................................................................................ 36
3.5.1
Tabulky faktů ................................................................................................................................. 36
3.5.1.1
4
Typy tabulek faktů [2] .......................................................................................................... 37
3.5.2
Tabulky dimenzí ............................................................................................................................. 37
3.5.3
Hvězdicové schéma - STAR ............................................................................................................ 38
3.5.4
Schéma sněhové vločky - SNOWFLAKE.......................................................................................... 38
Návrh implementace BI pro sledování studijních výsledků na BIVŠ ............................ 40 4.1
Zadání a detailní analýza potřeb.................................................................................... 40
4.2
Stručný přehled navrhovaného řešení ........................................................................... 41
4.3
Fáze zpracování ............................................................................................................. 41
4.3.1
Export dat ...................................................................................................................................... 42
4.3.2
Datový sklad .................................................................................................................................. 43
4.3.2.1
Kontroly a chybová hlášení .................................................................................................. 44
4.3.2.2
Transformace ....................................................................................................................... 45
4.3.3
Datový trh ...................................................................................................................................... 49
4.4
Historická data .............................................................................................................. 54
4.5
Zobrazení dat ................................................................................................................ 56
4.6
Technické řešení ........................................................................................................... 58
Závěr ................................................................................................................................. 60 Seznam obrázků: ............................................................................................................... 61 Seznam tabulek ................................................................................................................. 62 Seznam použité literatury:................................................................................................. 63 Příloha č. 1 ........................................................................................................................ 64 Příloha č. 2 ........................................................................................................................ 66 6
Úvod Cílem této bakalářské práce je seznámit čtenáře s termínem Business Intelligence, který se v poslední době často pouţívá v souvislosti se sofistikovaným způsobem zpracování podnikových dat. V teoretické části je nejprve znázorněn a stručně charakterizován komplexní pohled na tento systém. V další části jsou pak blíţe popsány jednotlivé jeho komponenty z hlediska účelu a vhodnosti vyuţití. V neposlední řadě bude čtenář seznámen s procesem datového modelování databází v návaznosti na tvorbu systému Business Intelligence. V praktické části práce je navrţeno konkrétní řešení zpracování dat v prostředí BIVŠ. Jedná se o záznamy hodnocení bakalářských a diplomových prací udělených absolventům Bankovního institutu v minulých letech. Popsané a navrţené řešení nepracuje s velkými objemy dat, ale obsahuje pouze vzorek záznamů. Bakalářská práce se na tomto příkladu snaţí ukázat, jak lze i z malého vzorku dat získat a vytěţit velmi mnoho informací, z nichţ se stanou záhy znalosti jako podpora pro další rozhodování.
7
1 Úloha
Business
Intelligence
při
řízení
podnikových procesů Podnikání je v dnešní době velmi dynamická činnost, která vyţaduje flexibilní manaţery, kteří dokáţí rychle reagovat na stále se měnící podmínky na trhu. Výsledky podnikání z minulých let a odvozené tendence vývoje z minulého období pomáhají managementu při rozhodování, kam bude směřovat podnikání v dalších měsících, případně letech. Data, která manaţerovi k takovému rozhodnutí pomohou, jsou uloţená většinou v jednotlivých transakčních systémech podniku. Aby mohly efektivně poslouţit pro strategické rozhodování, je nutné z nich získat jen některé informace. Proces získání a zpracování těchto dat a následná transformace na znalosti nutné k strategickému rozhodování, nazýváme Business Intelligence. Z výše uvedeného vyplývá, ţe vedení podniku si uvědomuje, jak jsou jeho data cenná. Existuje celá řada organizací, které uvaţují o nasazení takovýchto systémů, aktuálně je vyvíjejí nebo uţ je pouţívají. Vývoj i provoz těchto systémů je samozřejmě nákladný, ale při plném vyuţití jejich potenciálu je moţné z toho vytěţit několikanásobně více, neţ byly jejich náklady. [2] Pojďme se tedy podívat nato, jak takový systém Business Intelligence vypadá, jaké jsou jeho komponenty a jakou můţe mít podobu.
1.1 Definice Business Intelligence Podle autorů publikace [4] termín Business Intelligence (dále jen 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ů. Business Intelligence zahrnuje procesy, aplikace a technologie, jejichţ cílem je účinně a účelně podporovat rozhodovací procesy ve firmě. Pro bliţší představu je třeba proces zpracování podnikových dat rozdělit na několik fází, které jsou součástí BI. Obrázek 1 zobrazuje jednotlivé fáze procesu zpracování dat pomocí systému Business Intelligence. Podniková data pocházející z různých provozních systémů podniku 8
(ERP, CRM1, datový soubor) jsou pomocí ETL procesu přenesena do tzv. datového skladu, kde jsou vyčištěna, agregována a analyzována. Následně jsou z nich vytvořeny potřebné sestavy, výpočty, reporty a grafy pro prezentaci managementu.
Obrázek 1 Datový tok v systému Business Intelligence, zdroj vlastní úprava
Ve druhé kapitole budou jednotlivé komponenty procesu Business Intelligence blíţe rozebrány.
1.2 Hodnota a přínos Business Intelligence [2] Hlavním přínosem technologie BI je centralizace podnikových dat v integrované formě, a ve vhodných strukturách pro analýzy a reporting. Dalším významným prvkem je přítomnost agregovaných dat a různé moţnosti prezentace výsledků zpracovaných dat. V dřívější době musel řídící pracovník poţádat pracovníka IT týmu o sestavu, přičemţ musel přesně definovat poţadavky. Získání reportu často trvalo dlouho a vlivem komunikačního
1
ERP = Enterprise resource planning, jsou jím označovány komplexní informační systémy organizací, zastřešující činnosti související s výrobou, financemi, účetnictvím, dodavatelskými řetězci, řízení lidských zdrojů atd. CRM = Customer Relationship Management, pokrývá koncepty uţívané organizacemi k udrţení jejich vztahu se zákazníky, včetně sběru, ukládání, vyuţívání a analýzy dat.
9
šumu často ani nedostal to, co přesně poţadoval. Systém BI umoţňuje uţivateli dotazovat se data sám a přímo, bez mezičlánku IT pracovníka. Je nastaven přístup vyjmenovaným uţivatelům, kteří se na uloţená data pomocí rozhraní s databází datového skladu sami dívají, případně dotazují. Tím se značně urychluje proces získávání poţadovaných dat. Díky tomu také lze pomocí zpětné vazby ladit poţadavky na větší konzistenci či kvalitu dat. IT tým se tak můţe soustředit na vývoj, ladění a optimalizaci systému a neslouţí jiţ jako kontaktní bod pro tvorbu sestav a reportů z jednotlivých transakčních systémů. Díky pravidelnému přenosu dat do datového skladu dochází ke kumulaci historických údajů, coţ můţe slouţit ke sledování událostí v minulosti a tím k vyvozování prognózy vývoje či určit nový směr podnikání. Z toho co je uvedeno výše lze odvodit výčet funkcí systému BI: a) shromáţdit všechna dostupná data na jenom místě – centrální úloţiště dat, b) umoţnit uţivatelů, aby se na data sami dotazovali, c) zajistit co nejvyšší kvalitu uloţených dat, d) systém musí být dostatečně výkonný, aby při paralelním dotazování byla odezva maximálně v minutách, ne v hodinách, e) je vhodné, aby data byla předem dle poţadavků agregována, f) uchování historických dat pro moţnosti srovnávání a vyvozování vývoje vzhledem k minulosti.
1.3 Podniková data V dnešní době je jiţ běţné, ţe podnikové procesy se realizují pomocí podnikových informačních systémů, nazývaných také ERP systémy, zpracovávající data v elektronické podobě. Ty pak představují podniková aktiva. Aby mohla data slouţit jako spolehlivý prostředek pro budoucí strategické rozhodování, je nutné zajistit jejich vysokou kvalitu. Data musí být srozumitelná a spolehlivá, pak je lze povaţovat za kvalitní. Na základě kvalitních dat lze pak provádět správná rozhodnutí. I kdyby byl člověk sebevíce zkušený v oblasti strategického rozhodování, ale neměl kvalitní informace, je výsledek rozhodnutí přinejmenším zkreslený, jak se uvádí v [2] . 10
V různých transakčních systémech je kvalita dat různá. Systém BI většinou obsahuje nástroj na čištění dat, aby výsledky zpracování nebyly zatíţeny příliš velkými chybami. Příčinou špatné kvality je primárně člověk. Informace podaná ústně můţe být příjemcem jinak pochopena a při zavedení do systému zkreslena. Dále často dochází k překlepům v písemném záznamu nebo k opomenutí vyplnění některého pole formuláře.
2 Architektura systémů BI a jejich komponenty Jak uţ bylo uvedeno výše, systém Business Intelligence je souborem několika aktivit, které na sebe navzájem navazují a jsou propojeny datovými toky. Podívejme se ještě jednou na architekturu celého systému a jednotlivé komponenty, které jsou jejími stavebními kameny.
Obrázek 2 Architektura systému Business Intelligence, zdroj vlastní úprava
Obrázek 2 popisuje jednotlivé alternativy datových toků od zdrojových systémů aţ po prezentaci výsledků pro management podniku. Obrázek sestává z několika komponent, které jsou vzájemně propojeny jednotlivými procesy a vazbami. Komponenty jsou v dalších částech práce detailněji popsány.
11
2.1 Základem jsou databáze Základem systému BI je zpracování a uloţení dat v databázích. 2Databáze je uspořádaná mnoţina informací v určitém formátu uloţená zpravidla na disku. V širším smyslu jsou součástí databáze i softwarové prostředky, které umoţňují manipulaci s uloţenými daty. Říká se mu Systém řízení báze dat (z ang. DBMS - DataBase Management System), coţ je programová vrstva nad uloţenými daty, která řeší operace s nimi. Z hlediska způsobu ukládání dat a vazeb mezi nimi můţeme rozdělit databáze do několika základních typů.
2.1.1 Hierarchické a síťové databáze [7] Ještě v nedávné historii, v 70. letech 20. století, byly informace uloţeny v hierarchických anebo síťových databázích, zaloţených na bázi stromové struktury a vztah mezi záznamy byl typu rodič – potomek, jak je vidět na obrázku 3.
Obrázek 3 Hierarchický model databáze, zdroj [7]
2
Wikipedie. Databáze [online]. [cit. 2013-04-21]. Dostupné z: http://cs.wikipedia.org/wiki/Databáze
12
2.1.2 Relační databáze Začátkem 70. let byl poprvé definován doktorem E.F. Coddem3 relační model databáze. [1] „Relační model je zaloţen na matematickém konceptu relace, která je fyzicky reprezentována tabulkou. Codd pouţil terminologii převzatou z matematiky, včetně teorie mnoţin a predikátové logiky. Relační model dat má pět hlavních sloţek: Relace – tabulka se sloupci a řádky. Atribut – pojmenovaný sloupec relace. Datová n-tice – řádek relace. Doména – mnoţina přípustných hodnot pro jeden nebo více atributů. Relační databáze – kolekce normalizovaných tabulek. V relačním modelu pouţíváme relace k uloţení informací o objektech, které chceme v databázi reprezentovat. Reprezentujeme relaci jako tabulku, v níţ řádky tabulky odpovídají jednotlivým datovým n-ticím a sloupce tabulky odpovídají atributům. Atributy se mohou objevit v libovolném pořadí, a přesto půjde o stejnou relaci, a proto bude obsahovat stejný význam. Pokud jde o atributy, datové n-tice se mohou objevit v libovolném pořadí a přesto půjde o stejnou relaci, a proto bude obsahovat stejný význam. Domény jsou důleţitou vlastností relačního modelu. Kaţdý atribut v relační databázi je spojen s doménou. Doména můţe být odlišná pro kaţdý atribut nebo můţe být dvě či více domén spojeno se stejným atributem.“ Výše uvedená citace je podle mě velmi těţko uchopitelná, proto si dovolím relační databázi popsat vlastními slovy. Hlavním stavebním prvkem relační databáze jsou dvourozměrné databázové struktury, které si lze představit jako tabulky. Mezi tabulkami jsou definovány
3
E.F. Codd definoval relační model dat ve své průlomové práci „A Relational Model of Data for Large Shared
Data Banks“ (Relační model dat pro velké sdílené banky dat). Toto pojednání se dnes všeobecně povaţuje za milník ve vývoji databázových systémů.
13
vztahy. Tabulky se skládají z řádků a sloupců. Řádky představují jednotlivé záznamy (n-tice) a sloupce chápeme jako atributy daného záznamu. Kaţdý sloupec má vlastní datový typ (doménu), např. datum, text, číslo, a podle toho jsou do něj vkládány příslušné hodnoty. Dalšími databázovými prvky jsou klíče, pohledy, indexy, sekvence a jiné. Aby byly záznam v tabulce jasně identifikovatelný, je nutné na tabulce vytvořit tzv. primární klíč. Ten je definován jako pole nebo minimální podmnoţina kombinace polí, jejichţ hodnoty jsou v souhrnu přes celou tabulku unikátní. Ţádný atribut, který je součástí primárního klíče nesmí obsahovat hodnotu NULL, to znamená, ţe nesmí být prázdný. Typickým příkladem primárního klíče je katalogové číslo výrobku, číslo smlouvy, kreditní karty či bankovního účtu nebo celostátní označení kódu banky. Pokud u záznamu neexistuje ţádný přirozený primární klíč, nebo je takový primární klíč příliš sloţitý, pouţívá se obvykle číslo automaticky přidělené samotnou databází. Takové číslo můţe být pořadové číslo, většinou se pak nazývá ID. Přidělení pořadového čísla mají na starosti sekvence, které se vytvářejí přímo nad sloupcem. Na sloupce mohou být aplikovány i tzv. indexy slouţící ke zrychlení vyhledávacích a dotazovacích procesů v databázi, definování unikátní hodnoty sloupce tabulky nebo k optimalizaci fulltextového vyhledávání. Kromě tabulek existují v databázi tzv. pohledy, které si lze představit jako virtuální tabulku sloţenou z různých jiných tabulek. Nástrojem pro tvorbu a správu relační databáze a manipulaci s daty je dotazovací jazyk SQL = Structured query language. Řešené operace pomocí jazyka SQL můţeme rozdělit na tři skupiny příkazů. První z nich je skupina příkazů určených k práci se strukturou databázových objektů = Data Definition Language (DDL). Mezi ně patří příkazy: CREATE - vytvoření ALTER - změna DROP - odstranění RENAME - přejmenování TRUNCATE - smazání, aniţ by se data ukládala do koše COMMENT - přidání komentáře
14
Druhou skupinu tvoří příkazy určené k manipulaci s daty = Data Manipulation Language (DML). Řadí se mezi ně následující příkazy: SELECT - vybrání dat z databáze INSERT - vloţení UPDATE - úprava nebo také editace či změna DELETE - smazaní MERGE – sloučení Třetí skupinou jsou příkazy, které slouţí k přidání či odebrání oprávnění k databázi a objektů v ní = Data Control Language (DCL). Patří sem dva příkazy: GRANT - přiřazení REVOKE = odebrání Optimální databázová struktura pro ukládání záznamů, jejich vyhledávání a manipulaci s daty je definována tzv. normálními formami. Normalizace (odtud normální formy) je sada pravidel, jak by se mělo postupovat při tvorbě jednotlivých entit relační databáze, aby byla práce s daty efektivní. Hlavním znakem normalizace je nejprve odstranění opakujících se poloţek záznamu pak odstranění závislostí záznamu na části klíče relace a posléze tranzitivních závislostí mezi záznamy. Tím se zabrání aktualizačním anomáliím. Je definováno šest normálních forem, pro naše potřeby však postačí definovat a blíţe vysvětlit na příkladech první tři normální formy. Conolly [1] definuje normální formy následovně: „PRVNÍ NORMÁLNÍ FORMA (1NF) Tabulka, v níţ kaţdý průsečík sloupce a záznamu obsahuje jen jedinou hodnotu.“ Například v databázové tabulce Osoba, obsahující data o nějaké osobě, budeme chtít mít více telefonních čísel. Jméno Jan Petr Pavel
Příjmení Adresa Novák Havlíčkova 2 Praha 3 Kovář Svatoplukova 15 Brno Pavel Papalášova 25 Kocourkov
Telefony 125789654;601258987 369852147;357951456;963852741 546789123
Tabulka 1 Příklad databázové tabulky Osoba, zdroj vlastní úprava
15
V této tabulce by se dost špatně provádělo vyhledávání právě podle telefonního čísla. Aby tabulka byla v 1NF musíme oddělit telefonní čísla do samostatné tabulky Telefon, kde vznikne odkaz na ID osoby z tabulky Osoba. ID 1 2 3
Jméno Jan Petr Pavel
Příjmení Adresa Novák Havlíčkova 2 Praha 3 Kovář Svatoplukova 15 Brno Pavel Papalášova 25 Kocourkov
ID osoby 1 1 2 2 2 3
Číslo 125789654 601258987 369852147 357951456 963852741 546789123
Tabulka 2 Databázová tabulka Osoba v 1NF a databázová tabulka Telefon v 1NF, zdroj vlastní úprava
„DRUHÁ NORMÁLNÍ FORMA (2NF) Tabulka, která je v 1NF a ve které jsou hodnoty kaţdého sloupce, který není součástí primárního klíče, determinovány všemi hodnotami sloupců, které tvoří primární klíč.“ Druhá normální forma se týká jen tabulek se sloţenými primárními klíči, tedy tabulek, jejichţ primární klíč tvoří dva nebo více sloupců. Vezměme si příklad tabulky Sklad: Název Mléčná čokoláda Oříšková čokoláda Tyčinka milkyway Mléčná čokoláda Oříšková horalka
Výrobce Milka Milka Milka Orion Horalka
Telefon výrobce +420123456789 +420123456789 +420123456789 +420987654321 +420456123987
Cena Množství 30 Kč 2500 30 Kč 2800 10 Kč 7000 25 Kč 5800 7 Kč 4560
Tabulka 3 Příklad databázové tabulky Sklad, zdroj vlastní úprava
Klíčem je zde kombinace atributů Název a Výrobce. Atribut Telefon výrobce ovšem není závislý na celém klíči, ale pouze na atributu Výrobce. Problém by vznikl, kdyby se vymazaly veškeré výrobky od výrobce Milka, ztratilo by se telefonní číslo na výrobce Milka, coţ není zrovna ţádané. Řešením je opět rozpad na dvě tabulky s odkazy pomocí cizích klíčů. Název Mléčná čokoláda Oříšková čokoláda Tyčinka milkyway Mléčná čokoláda Oříšková horalka
Výrobce ID 1 1 1 2 3
Cena Množství 30 Kč 2500 30 Kč 2800 10 Kč 7000 25 Kč 5800 7 Kč 4560
Výrobce ID 1 2 3
Výrobce Milka Orion Horalka
Telefon +420123456789 +420987654321 +420456123987
Tabulka 4 Databázová tabulka Sklad ve 2NF a databázová tabulka Výrobce ve 2NF, zdroj vlastní úprava
16
„TŘETÍ NORMÁLNÍ FORMA (3NF) Tabulka, která jiţ je v 1NF a 2NF a ve které všechny hodnoty ve sloupcích, které nepatří k primárnímu klíči, jsou determinovány pouze sloupci primárního klíče a nejsou determinovány ţádnými jinými sloupci.“ Definice odpovídá tzv. tranzitivní závislosti, coţ je závislost mezi minimálně dvěma atributy a klíčem, kde jeden atribut je funkčně závislý na klíči a druhý atribut je funkčně závislý na prvním atributu. Příklad: Řekněme, ţe firma chce uchovávat informace o zaměstnancích, takţe vytvoříme tabulku Zaměstnanec s atributy RČ (primární klíč), Jméno, Příjmení, Město, PSČ, Funkce a Plat, zbytek adresy vynecháme, protoţe pro příklad není důleţitý. RČ 1 2 3 4 5 6
Jméno Jack Franta Pepa Pavel Petr Honza
Příjmení Smith Vomáčka František Novák Koukal Novák
Město Jihlava Praha10 Plzeň Kocourkov Praha10 Plzeň
PSČ 58601 10000 10000 99999 12345 12345
Funkce Plat CEO 150000 Senior Software Architect 80000 Senior Software Architect 80000 Junior Developer 30000 Database Designer 75000 Junior Developer 30000
Tabulka 5 Příklad databázové tabulky Zaměstnanec, zdroj vlastní úprava
Z této tabulky je vidět kromě závislosti všech atributů na klíči ještě závislost Města na atributu PSČ a závislost Platu na Funkci. Závislost RČ → Město → PSČ je tranzitivní závislost PSČ na klíči, stejně tak závislost RČ → Funkce → Plat. Řešením problému je opět rozpad na více tabulek, v tomto případě dokonce na tři, protoţe 3NF byla porušena rovnou dvakrát. RČ 1 2 3 4 5 6
Jméno Jack Franta Pepa Pavel Petr Honza
Příjmení Smith Vomáčka František Novák Koukal Novák
Město ID 1 2 4 3 2 4
Funkce ID 1 2 2 4 3 4
Tabulka 6 Databázová tabulka Zaměstnanec ve 3NF, zdroj vlastní úprava
17
Město ID 1 2 3 4
Město Jihlava Praha10 Kocourkov Plzeň
PSČ 58601 10000 99999 12345
Funkce ID 1 2 3 4
Funkce CEO Senior Software Architect Database Designer Junior Developer
Plat 150000 80000 75000 30000
Tabulka 7 Databázová tabulka Adresa ve 3NF a databázová tabulka Funkce ve 3NF, zdroj vlastní úprava
2.1.3 Multidimenzionální databáze Multidimenzionální databáze je v jistém smyslu zobecněním relací databáze pro „n“ proměnných. Základní stavební jednotkou zde je n-rozměrná krychle. Ta se skládá ze sady dimenzí a faktů, coţ jsou v podstatě také tabulky, ale jsou mezi nimi trochu jiné vazby neţ v relační databázi. Podrobněji bude vysvětleno v kapitole 3.5 této bakalářské práce. Takto organizované databáze slouţí jako podklad pro získávání sumarizovaných a agregovaných dat odvozených od jednotlivých dimenzí a na nich závislých faktů.[3]
Obrázek 4 Multidimenzionální model, zdroj [8]
Dimenze kostky jsou kategorie, vůči kterým chceme data analyzovat a agregovat. Typickými dimenzemi v multidimenzionálních databázích jsou čas, lokalita a výrobek. Dimenze se můţe skládat z řady úrovní, které dále zpřesňují údaje. Např. čas můţe být uveden v minutách, hodinách, dnech, týdnech, měsících a letech. Podle zvolené časové hodnoty jsou pak faktické hodnoty agregovány. Faktické hodnoty kostky jsou tedy kvantitativní údaje, které chceme analyzovat. Nejčasnějšími fakty jsou objemy prodeje, peněţní výdaje či příjmy, úroveň a vývoj cen a jiné. [2] Dimenze a faktové tabulky budou podrobněji rozebrány později v kapitolách 2.5.4 a 3.5.
18
2.2 Zdrojové transakční systémy Data, která chceme uloţit do datového skladu, se nacházejí v často velmi nehomogenních a různorodých transakčních systémech či jiných externích zdrojích. Jedná se o různé systémy, běţně pouţívané v podniku pro ukládání transakcí, událostí či finančních toků, jako jsou systémy pro nákup, prodej a skladové hospodářství, účetní systém, systém pro evidenci zaměstnanců. Řadí se mezi ně také ERP, CRM a SCM4 systémy. Zde se kumuluje velké mnoţství dat, které po zpracování a vyhodnocení poslouţí pro podnikové rozhodování. Databáze zdrojových systémů jsou často relační databáze a bývají modelovány ve třetí normální formě. Takovýto design nejvíce vyhovuje poţadavkům na tyto provozní systémy, kdy jsou data rychle a jednoduše uloţena, případně vyhledána a upravována. Nevyhovuje však potřebám datové analýzy a tvorbě reportů a sestav. Navíc obsahují pouze část údajů potřebných pro analýzy a následné rozhodování.[2]
2.3 Datové pumpy (ETL a ELT) Jednou z komponent systémů Business Intelligence je proces zvaný ETL, zkratka Extract, Transformation and Load. Sestává z těchto tří aktivit, uvedených v názvu procesu. V podstatě se jedná o načtení dat ze zdrojových systémů, jejich úprava a přeměna do nových struktur a přenesení do cílové databáze. [6] Druhou alternativou přenosu zdrojový dat do datového skladu je proces ELT, tedy Extract, Load and Transformation, kdy jsou surová a nijak neupravená data přenesena 1:1 do dočasné vrstvy datového skladu (viz kapitola 2.5.1) a k transformacím a úpravám do nových struktur dochází později.
4
SCM = Supply Chain Management, označuje strategii moderního managementu pro optimalizaci všech činností a informačních systémů pro zabezpečení dodávky produktů a sluţeb od dodavatelů surovin přes jejich výrobu nebo vývoj, přes distribuční kanály aţ ke koncovému spotřebiteli.
19
Obrázek 5 Porovnání procesu ETL a ELT v grafické podobě, zdroj [6]
[2] Extrakce představuje výběr a získání dat z provozních systémů podniku či jiných externích zdrojů. Softwarové nástroje k tomu vyuţívají utilitu (komponentu) pro připojení do téměř jakéhokoliv typu databáze, tedy do zdrojového systému s definicí přiděleného přihlašovacího jména a hesla. Extrakt je proveden pomocí SQL příkazu „Select“ s omezením na příslušnou sadu dat z daných databázových struktur. U softwarového nástroje ETL následuje poţadovaná transformace jako je čištění a konsolidace, realizují se různé výpočty, agregace, spojení a dále přeměna do nových struktur cílové databáze. V rámci transformací musí být vyřešena nejednotnost pojmů a názvů v jednotlivých zdrojových systémech. Můţe se jednat o konkrétní hodnoty např. peněţní měny, kdy v jednom systému je uvedeno CZK a ve druhém Kč. V datovém úloţišti musí být hodnoty sjednoceny a vytvořena jednotná konvence názvů. Pro identifikaci záznamu ve zdrojovém systému je ke kaţdému řádku připojena informace o zdrojovém systému a jeho původním ID. Proces load představuje naplnění datového skladu zpracovanými daty. Opět vyuţívá připojení do databáze, a pomocí SQL příkazu Insert vkládá upravená data do cílové databáze. Není nutností, aby došlo k extraktu dat ze všech zdrojových systémů paralelně. Extrakce často neprobíhá přímo z provozní databáze. ETL engine se většinou nemůţe připojovat přímo do provozní databáze systému, a pokud ano, tak pouze na velmi krátkou dobu. Proto je realizován extrakt tak, ţe správci jednotlivých zdrojových systémů poskytnou příslušnou kopii dat v podobě naplněných databázových tabulek na pracovní databázový server. ETL nástroj tak nezasahuje do struktur transakčního systému, ale na nezávislý server. Na stejné místo jsou poskytnuty kopie dat všech provozních systémů, které jsou určeny k přenosu do datového skladu. Kaţdá sada má určené jiné databázové schéma, aby mohly existovat vedle sebe, aniţ by se navzájem rušily. Extrakty dat se sem dostávají postupně 20
a ETL nástroj je načítá v době, kdy jsou všechny k dispozici. Poté dochází k paralelnímu načtení všech zdrojových dat, aby mohla být provedena následná konsolidace (viz kapitola 2.4.2 Konsolidace dat). Tento postup je ovšem téměř nerealizovatelný, jelikoţ data z jednotlivých zdrojů jsou vzájemně nekompatibilní a proces transformace a konsolidace by byl příliš zdlouhavý. Navíc, kdyby došlo ke zpracování velkého objemu dat, byl by systém ETL příliš zatíţen a na výsledky by se čekalo příliš dlouho. [6] Proto byl vyvinut alternativní způsob přenosu a transformace, a to procesem ELT, Extract – Load – Transformation. Tento proces umoţňuje připojení přímo do databází transakčních systémů a to navíc i paralelně. Data jsou extrahována a následně uloţena do dočasné vrstvy datového skladu bez jakýchkoliv změn a úprav. Jelikoţ jsou data načítána 1:1, postačí nám pouze omezená doba připojení. Načítání dat pak můţe, ale nemusí probíhat paralelně. Tato data pak slouţí jako zdroj pro další analýzy a transformace a uloţení do příslušných struktur úloţiště datového skladu. Správný název procesu by měl být ELTL, tedy po transformaci následuje další „load“ do cílových tabulek datového skladu. Proces ETL nebo ELT je realizován pravidelně, např. jednou denně. Kaţdý den totiţ dochází ke vzniku nových nebo úpravě stávajících záznamů ve zdrojových systémech. Tyto změny musí být pravidelně reflektovány do datového skladu, jelikoţ kaţdý den mění pohled na celkové dění v podniku a tím se můţe také měnit konečná rozhodnutí managementu. Na trhu existuje celá řada ETL nástrojů jakoţto podpůrný software pro systémy Business Intelligence, například nástroje DataStage, Informatica, MS SQL Server a další [2] . Říká se jim často datová pumpa. Načtení a přenos dat procesem ELT bývá často realizováno pomocí SQL skriptů bez speciální softwarové aplikace. Transformace pak probíhají jiţ na úrovni samotné databáze nebo pomocí programovacího jazyka PL/SQL. Kaţdý z uvedených procesů má několik výhod i nevýhod. Transformace pomocí ETL nástroje je výhodou, nechceme-li zatěţovat server s běţícím datovým skladem. Ovšem jak uţ bylo uvedeno výše, při zpracování velkého objemu dat je zpracování příliš zdlouhavé a dojde-li případně k pádu aplikace, přijdeme o veškerá data a dosavadní výpočty. Navíc ETL nástroj vyţaduje samostatný server a vlastní diskový prostor pro běh aplikace a dočasné ukládání transformovaných dat. Další vlastností zpracování 21
pomocí ETL nástroje je zpracování na úrovni jednotlivých řádků. Nemůţeme zde realizovat agregace a práci se sadou záznamů. Naopak výhodou ELT procesu je, ţe není nutné vlastnit server, na kterém poběţí proces transformace v rámci ETL. Ale o to více bude zatíţen server, na kterém běţí datový sklad. Tím ţe jsou data uloţena na jednom místě, odpadá komunikace mezi transformačním serverem a databázemi. Dále je výhodou, ţe transformace probíhají na úrovni sady záznamů nejen na úrovni jednotlivých řádků, čímţ se stává zpracování výkonnější. Úskalím je ovšem proces čištění a konsolidace dat. Databázovými skripty nedocílíme tak efektivních výsledků konsolidace, jako samostatným nástrojem, přímo k tomu určeným. Proto pro tuto část procesu se doporučuje zvolit specializovaný software, který je zaměřen na datovou kvalitu a dokáţe efektivně záznamy, vyčistit, identifikovat a konsolidovat. A tady nastupuje nutnost další takovýto aplikační server tak jako tak pořídit. V případě jeho pádu budou ovšem uţ zdrojová data uloţená v dočasné vrstvě a nehrozí zde úplná ztráta dat. Co je tedy lepší, ETL nebo ELT? Kaţdý z nich má své klady i zápory. Návrh architektury musí vycházet z poţadavku na včasné doručení dat business uţivatelům. ELT je vhodné za předpokladu, ţe existuje dostatečné časové okno pro zpracování transformací a zdroje dat dodrţují dohodnutá SLA5. Proces ETL zase minimalizuje zatíţení databázového systému a zpracování můţe běţet v době, kdy je datový sklad pouţíván uţivateli. Ale máme moţnost vyuţít obojí a zavést proces ELTL a tím rozdělit zátěţ mezi ETL a databázový systém. ETL engine bude provádět řádkové transformace a databáze naopak operace na úrovni sady dat, jako jsou agregace.
2.4 Datová kvalita Pro správné a smysluplné rozhodování je důleţité mít k dispozici kvalitní, úplná a spolehlivá data. Kvalita dat je dána několika faktory. Jednak je třeba, aby data byla uloţena v příslušném kontextu, aby na vkládaná data byly aplikovány kontroly, aby byla vyjasněná terminologie
5
SLA = Service level agreement, dohoda o úrovni dodávaných sluţeb, nejčastěji IT sluţeb.
22
a aby byla zajištěna správná správa databáze a ochrana dat. Jednotlivé aspekty kvality si nyní vysvětlíme. Transakční systémy často obsahují kontroly vstupních hodnot. Příkladem můţe být omezení na numerické znaky např. u PSČ nebo alfabetické znaky u polí se jménem a příjmením. Dále můţe být přesně daný formát např. u rodného čísla, nebo ověření délky u pole s rodným číslem. Dalším typem kontrol je moţnost zápisu pouze očekávané hodnoty způsobem „drop down menu“. Součástí koncepce kvality dat je vyjasněná terminologie pojmů pouţívaných v organizaci. Pojem zákazník můţe pro jednotlivá oddělení podniku znamenat něco jiného. Oddělení prodeje definuje zákazníka jako kaţdou osobu, která si něco objednala, potaţmo koupila. Marketingový pohled na zákazníka zahrnuje kromě stávajících zákazníků i ty potencionální, tedy oslovené pomocí marketingové kampaně eventuálně byla-li jim zaslána nabídka produktu. Při budování datového skladu je tedy nutné zavést jednotnou terminologii, aby nedocházelo později k rozporům ve významech jednotlivých pojmů. Aby se z dat staly informace slouţící jako podklad pro další zpracování a následné rozhodování, je důleţité je mít v určitém kontextu. Řekněme, ţe máme částku „358,- Kč“. To nám samo o sobě nic neřekne. Ale víme-li, ţe se tato částka váţe k určité faktuře, uţ to dává jistý smysl, ale stále je to nedostačující. Nevíme, zda je to faktura pro našeho zákazníka a čekáme platbu, nebo jsme fakturu obdrţeli a máme ji zaplatit. Proto potřebujeme další podrobnosti, jako zda se jedná o fakturu přijatou či vydanou, tedy zjišťujeme druh faktury. Víme-li ale, ţe fakturu vystavila naše kolegyně z ekonomického oddělení, tzn., ţe zjistíme zodpovědnou osobu, více se nám konkretizuje představa o tomto dokumentu. Dále jsme schopni zjistit, na kterého zákazníka je faktura vystavena a kdy je splatná, zda teprve bude zaplacena nebo je-li uţ po splatnosti atd. [2] Těmto kontextovým informacím říkáme metadata. Jsou to v podstatě informace, které nám dávají ucelený přehled o datech uloţených v databázi. Metadata jsou pro datový sklad klíčová. V datovém skladu jsou data uloţena ve strukturách, které jsou právě popsány metadaty, která odpovídají podnikovému kontextu. Soubor metadat pak tvoří metasystém, který je podle [5] definován takto:
23
Metasystém označuje systém, který popisuje, resp. modeluje jiný systém, v našem případě systém informační. Metainformační systém je jednotou meta databáze (metadat) a operací, které umoţňují uchování a zpracování metadat. Metadata popisují IS/IT podniku a jejich významné vazby. Metasystém je neodmyslitelným nástrojem systémové integrace IS. Pro zajištění vysoké kvality dat je důleţitá i jejich správa. Je-li moţné záznam v databázi kdykoliv a kýmkoliv smazat bez evidence této skutečnosti, není zajištěná dostatečná ochrana dat. A i kdyby systém evidoval kdo a kdy záznam smazal, není nám to moc platné, kdyţ nevíme který záznam a s jakými hodnotami byl smazán. To znamená, ţe vhodný způsob správy systému, obsahující podniková aktiva, je velmi důleţitý.
2.4.1 Čištění dat Systém Business Intelligence nabízí nástroje na další zvýšení datové kvality. Proces čištění dat si klade za cíl celkově zvýšit kvalitu jednotlivých atributů záznamu tak, aby byly připraveny pro proces konsolidace. Dalším faktorem kvality dat je jejich spolehlivost a důvěryhodnost. V rámci čištění dat v systému Business Intelligence dochází k dalším a ještě hlubším kontrolám především osobních údajů. Rodné číslo je kontrolováno z hlediska formátu, platnosti, souladu s datem narození a pohlavím. Dále je provedena validace z hlediska dělitelnosti jedenácti, tzv. modulo 11. Hodnota v poli datum narození je zhodnocena např. podle limitních hodnot, tedy minimální a maximální moţná.
Dále se kontroluje platnost, tedy zda není uvedeno
neexistující datum a také je hodnota konfrontována s jiţ validovaným rodným číslem. Údaj z pole pohlaví, pokud je uveden, se validuje opět proti zkontrolovanému rodnému číslu. Dále je moţné validovat pohlaví i se jménem, tedy zda se jedná o ţenské či muţské křestní jméno. Validace jmen je realizována na základě dostupného číselníku známých ţenských a muţských jmen a příjmení. Ani vícečetná jména, jako např. Anna Marie Růţičková či Jan Pavel Tůma, nejsou problém ověřit. Systém si poradí i se zahraničními jmény osob a umí zpracovat i jméno a příjmení opačně zapsané v kolonkách, tedy v poli pro jméno je uvedeno příjmení a naopak. Ani drobné překlepy nečiní problém a lze je opravit a nahradit za správné. 24
Akademické tituly uvedené u jména či příjmení, jsou taktéţ rozpoznány a zapsány do odděleného pole pro titul. Systém provádí také čištění dat právnických osob. Nejprve se prověří formát a platnost identifikačního čísla organizace (IČO). Následuje standardizace právní formy jako je akciová společnost, spol. s.r.o. atd. Poté dochází k ověření existence daného IČA s dostupným číselníkem právnických subjektů a porovná se daný název subjektu s názvem z číselníku. Adresní údaje se porovnávají s dostupným celorepublikovým etalonem adresních bodů. Systém umí také dle pravděpodobnosti doplnit či opravit čísla popisná či orientační, doplnit obec nebo část obce pokud je to jednoznačné a téţ opravit překlepy v názvech měst či ulic. Výsledkem čištění jsou vyčištěná a standardizovaná data ve vysoce strukturované podobě. To znamená, ţe jednotlivá datová pole obsahují převáţně očekávané hodnoty.
2.4.2 Konsolidace dat Vyčištěná data dále slouţí ke konsolidaci. Konsolidace dat je proces, kdy jsou vzájemně porovnávány všechny záznamy osob a hledají se duplicity. Jestliţe data pocházejí z více primárních systémů, je nutné záznamy spojit, aby reprezentovaly jednu osobu. Spojení se provádí na základě úplné nebo částečné shody v rodném čísle, jméně, příjmení a datu narození, případně názvu subjektu a IČA. Pro vyšší spolehlivost správnosti spojení několika záznamů se berou v úvahu i jednotlivé adresní atributy. Výsledkem je identifikace záznamů jakoţto jednoho zákazníka, popř. dodavatele či zaměstnance. Proces čištění dat můţe být součástí ETL procesu, tedy k vyčištění dat jejich konsolidaci dojde ještě před uloţením do datového skladu. Ale častěji je tento proces realizován aţ po uloţení do databáze datového skladu. Proces je při větším objemu dat časově dost náročný. A proto je vhodnější, aby došlo nejprve k uloţení surových dat ze zdrojových systémů a pak teprve se provádělo čištění a standardizace.
25
2.5 Datový sklad a jeho role Datový sklad lze definovat jako centrální úloţiště všech podnikových dat ze zdrojových systémů, které chce management vyuţít jako podklad pro rozhodování v dalším podnikání. Způsob přenosu dat do datového skladu, jejich struktura a způsob prezentace by měly být výsledkem analýzy potřeb podniku. Strategie datového skladu je plán zaloţený na dvou hlediscích, na pohledu IT oddělení a na oddělení businessu, který bude data vyuţívat. Je důleţité, aby se oba týmy postupně shodly na finální podobě, a tím bylo výsledné řešení optimální variantou pro obě strany. Strategie datového skladu usiluje o optimální organizaci podnikových dat tak, aby odpovídala poţadavkům na pouţití. Data jsou do datového úloţiště přenášena v pravidelných intervalech, ideálně kaţdý den a tím jsou uchována i historické záznamy. V rámci datového skladu se provádí také různé agregace, segmentace, kategorizace dle potřeb a výpočty jsou uloţeny pro další zpracování. Datový sklad se skládá z několika logických celků určených tokem a organizací dat. Mezi ně patří dočasné úloţiště dat, centrální úloţiště dat, provozní datový sklad a datové trhy. Ne všechny vrstvy datového skladu musí být nutně budovány, vše závisí na podnikových potřebách. Existují v zásadě dva rozdílné přístupy tvorby datových skladů a to podle Billa Inmona6 nebo podle Ralpha Kimballa7, ale s ohledem na rozsah této bakalářské práce je nebudu podrobněji popisovat včetně výhod či nevýhod pro jednotlivé aplikace. [2]
2.5.1 Dočasné úložiště dat Vrstva dočasného úloţiště dat je velmi jednoduše souborem tabulek, které odpovídají struktuře tabulek ve zdrojovém systému. Tabulky jsou v pravidelných intervalech plněny
6
INMON, W. Building the Data Warehouse, Indianapolis, USA: Wiley Publishing, Inc., 2005. ISBN: 13-978-0-
7645-9944-6 7
KIMBALL, R. Mastering Data Warehousing Aggregates, Indianapolis, USA: Wiley Publishing, Inc., 2006.
ISBN: 13-978-0-471-77709-0
26
zpravidla denními snímky ze zdrojových systémů. Tabulky nebývají indexovány, nejsou na nich zaloţeny primární ani cizí klíče a nemívají ţádné integritní omezení. Jsou ovšem identifikovány příznakem zdrojového systému, aby byl zřejmý původ dat v nich uloţený.[4]
2.5.2 Úložiště datového skladu Úloţiště datového skladu slouţí jako centrální sběrné místo vyčištěných a konsolidovaných dat. Uchovávají se zde velké objemy dat po dlouhou dobu a úloţiště se proto můţe rozrůst do mimořádné velikosti. Tato část je často modelována normalizovaným způsobem, aby bylo zajištěno, ţe podniková data jsou plně rozloţena na základní, popisné a přidruţené komponenty. [2] Jednotlivé datové prvky jsou rozděleny podle jejich povahy a uloţeny procesem ETL do tabulek relační databáze. Mezi tabulkami jsou definovány vztahy. Mezi hlavní entity datového skladu patří zákazník, produkt (a jejich různá podoba v závislosti na typu organizace), transakce, smlouva, objednávka, faktura, adresa, kontakt, dodavatel, zásoba atd. Speciálním typem tabulky je tzv. číselník. Obsahuje textové informace, ale na rozdíl od klasické tabulky pouze omezené mnoţství předem definovaných hodnot. Příkladem můţe být číselník typ osobního dokladu, kde se budou vyskytovat pouze hodnoty jako občanský průkaz, cestovní pas, řidičský průkaz, zbrojní průkaz a moţná ještě doklad o povolení pobytu a ke kaţdému typu vlastní ID. Důvodem zavedení číselníku je úspora místa v databázi a vyhnutí se výskytu opakovaných hodnot typu dokladu v tabulce doklad. Důleţitým znakem úloţiště datového skladu je uchování historie záznamů z jednotlivých transakčních systémů. Data jsou do datového skladu nahrávány v pravidelných intervalech, nejčastěji jedenkrát denně vţdy po ukončení provozu v podniku. [2] Centrální úloţiště dat můţe být namodelováno také v multidimenzionálním schématu, která je tvořena tabulkou faktů a tabulkami dimenzí. Schéma multidimenzionální databáze je rozebráno v kapitole 2.5.4 Datové trhy.
27
2.5.3 ODS – Operational Data Store Operational Data Store (v překladu Provozní datový sklad) je komponentou architektury datových skladů, která slouţí jako místo datové integrace aktuálních dat provozních systémů. ODS je databáze, která podporuje vkládání a modifikace dat v reálném čase, např. pomocí replikací z provozních systémů resp. pomocí integračního serveru. Tím se ODS stává zdrojem konsolidovaných agregovaných dat s minimální dobou odezvy, zdrojem vhodným pro vyuţití dat téměř v reálném čase.[2]
2.5.4 Datové trhy Datový trh představuje určitou podmnoţinu úloţiště dat, která je určena k podrobným analýzám podnikovými uţivateli. Většinou obsahují transformovaná či jinak agregovaná data z centrálního úloţiště. Jsou tvořeny statickými či dynamickými sestavami pravidelně generovanými v rámci datových toků. Můţe se jednat např. o počty zákaznických smluv agregované podle provozoven, oblastí, regionů či států, nebo o detaily průběhu realizace zakázek nebo např. o efektivitu marketingových kampaní. Datové trhy bývají nejčastěji tvořeny multidimenzionální databází, která sestává z tabulek faktů a z dimenzí. Tabulka faktů se nachází v centru multidimenzionálního schématu. Tato tabulka obsahuje data sledovaných ekonomických ukazatelů, jako jsou objemy prodeje, transakce na bankovních účtech či platby kreditní kartou. Jedná se údaje, které se vypočtou jako poţadované funkce údajů jednotlivých dimenzí a to v zásadě dané granularitou8 dimenze. Z povahy dat ve faktových tabulkách vyplývá, ţe jsou data kvantitativní povahy, jsou nestatická a mění se v čase. Naproti tomu dimenzionální tabulky obsahují textové informace o hodnotách uloţených v tabulce faktů. Mezi základní dimenze v datovém skladu v bankovním prostředí patří např.
8
Granularita popisuje úroveň podrobností. Granularita dat můţe u časové dimenze nabývat hodnoty dní, týdnů,
měsíců, čtvrtletí, let atd. Granularita dimenze lokalita je dána např. zemí, regionem, městem nebo provozovnou.
28
zákazník, adresa, bankovní účet, platební karta atd. Sloupce tabulek jsou pak tvořeny detaily k dané tabulce. Pro dimenzionální tabulky je typické velké mnoţství textových atributů, které jsou v čase víceméně neměnné. Například tabulka Zákazník bude obsahovat jméno, příjmení, rodné číslo, datum narození a pohlaví. Číselník je v datovém trhu povaţován za malou dimenzi.[2]
2.5.5 OLAP – Online analytical processing Pod názvem OLAP se skrývají technologie, metody a prostředky, které umoţňují velmi podrobnou analýzu velkého mnoţství dat.[3] Jedná se o určitou modifikaci klasických sestav, které se v podniku generují. Výsledkem zpracování jsou rozsáhlé sestavy, umoţňující uţivatelům dívat se na data z nejrůznějších hledisek. Smyslem OLAP systémů je co nejrychleji poskytnout uţivateli poţadované agregace dat, popřípadě výsledky analýz provedených právě nad těmito agregacemi. Pro zmiňované analýzy existují různé OLAP nástroje, které umoţňují nejen zpracování surových dat, ale také velmi flexibilní zobrazení transformovaných a agregovaných dat. Umoţňují také tisk sestav s modifikací formátu – sestava, graf v různých podobách atd. Před zpracováním je třeba definovat poţadavky na agregace, tedy jaká data bude sestava obsahovat, za jaké období a v jaké geografické oblasti. Jedná se v podstatě o určení faktů a metrik, které chceme sledovat, a dimenzí tvořící základy pro agregaci. Výsledky zpracování jsou uloţeny v multidimenzionálních strukturách databáze, které mají podobu krychle, jak bylo uvedeno v kapitole 2.1.3 Multidimenzionální databáze. OLAP nástroj za podpory multidimenzionální architektury uloţení dat umoţňují okamţité zobrazení výsledků s moţností flexibilního rozbalování nebo sbalování podrobností sestavy. Uţivatel můţe procházet data od nejvyšší po nejniţší úroveň granularity jednotlivých dimenzí. Výsledky zpracování touto technologií v zásadě představují souhrn mnoha sestav v jedné. [2] Uveďme si příklad řetězcového supermarketu, který realizuje prodej potravin v Holandsku, Belgii a v České republice. Potraviny samy o sobě jsou souborem několika stovek artiklů, pro zjednodušení řekněme sto. Supermarket je na trhu pět let. Management podniku se bude zajímat o sumy prodejů jednotlivých artiklů v jednotlivých zemích za určité období.
29
Před zpracováním dat v OLAP krychli je potřeba nejprve nadefinovat jednotlivé dimenze, které se stanou základem pro agregace. Jedna strana by představovala sto druhů potravin, které by bylo moţné seskupit např. na mléčné výrobky, masové výrobky, ovoce, zeleninu, pečivo, nápoje atd. Druhou stranu krychle by tvořily tři státy, rozdělené do jednotlivých regionů, krajů, okresů, měst, a ty by se dále rozpadaly na jednotlivé provozovny. Třetí stranu krychle by reprezentovala časová měřítka, od nejniţších po nejvyšší, tedy od dní, přes týdny, dekády, měsíce, kvartály, roky eventuálně desetiletí. Kalkulace by pak byly provedeny pro všechny moţné kombinace těchto tří dimenzí. Pro datového analytika by bylo téměř nemoţné napsat všechny SQL dotazy, které by obsahovaly výpočty přes všechny kombinace uvedených dimenzí. Jednak by mu vývoj trval velmi dlouho a bylo by obtíţné zajistit, aby některou kombinaci nevynechal. Výsledky by nakonec byly jen statické sestavy s neporovnatelně omezenými moţnostmi zobrazení. Po kalkulaci dat pomocí OLAP kostky, kde jsou výsledky agregací uloţeny v samostatné OLAP databázi, je moţné poţadované sestavy okamţité zobrazit způsobem rozbalení či sbalení dle aktuálních potřeb. Bylo by tak moţné dívat se na prodeje nejprve za jednotlivé země, pak za kraje a regiony. Poté přejít z kalendářního roku na čtvrtletí, měsíce či dny, a v neposlední řadě sledovat detaily prodejů podle jednotlivých artiklů. Je třeba poznamenat, ţe rozbalování či sbalování obvykle probíhá poměrně rychle. Doba odezvy se pohybuje od několika sekund po několik minut.
2.6 Prezentace dat Vrstva uţivatelské prezentace je podle [2] orientována kolem BI nástrojů, jako je Cognos, Business Objects, Microstrategy nebo Crystal reports. Sestavy generované těmito nástroji jsou silně přizpůsobené poţadavkům koncových uţivatelů. Zvolená forma prezentace zpracovaných dat je výsledkem rozhodnutí, jak chtějí uţivatelé kalkulované hodnoty pouţívat. Jestliţe uţivatele zajímají stále se opakující sestavy a reporty, postačí volně dostupné nástroje ke statickému zobrazení dat s komponentou napojení na databázi a prohlíţení jednotlivých tabulek, eventuálně filtrování podle zvolených atributů. Jeli sestava koncipovaná jako vývoj hodnot v čase, či porovnání podílu na trhu, hodil by se k tomu nástroj s grafickými diagramy. Dynamický proces zkoumání dat na jednotlivých 30
úrovních granularity ve všech přidruţených atributech je úkolem nástrojů OLAP – viz kapitola 2.5.5. Tyto nástroje jsou ovšem nákladnou investicí, ovšem pokud budou mít velké vyuţití a výrazně poslouţí jako podklad pro potřebné analýzy, určitě se investice brzy vrátí. Neméně důleţitým poţadavkem této vrstvy je, aby pokročilý uţivatel měl moţnost dotazovat se na data sám pomocí SQL dotazů a tak kdykoliv zjistit potřebné informace na základě adhoc poţadavků. Navíc získá komplexní přehled o datech v jím preferovaných oblastech.
31
3 Konceptuální modelování BI Chce-li podnik vybudovat kvalitní systém datového skladu, je třeba zvolit vhodný návrh databázových struktur jednotlivých vrstev datového skladu v podobě datového modelu. Příliš sloţitá struktura odradí uţivatele od přímého dotazování, ale naopak jednoduchý datový model nebývá příliš pruţný pro moţnosti dalšího vývoje a rozšíření. Je vţdy důleţité vzít v úvahu účel vzniku datového skladu a způsob jeho pouţití. Úloţiště datového skladu bude obsahovat velké objemy dat včetně uchování dat historických. S ohledem na vyuţití úloţiště jako datové základny pro analýzy bude vzhled této části databáze odlišný od datových trhů, jejichţ datové modely budou mít převáţně podobu hvězdicových diagramů. Konceptuální modelování je důleţité kvůli tomu, aby podnikoví uţivatelé měli představu o jednotlivých strukturách a organizaci dat v databázi. [2]
3.1 Úloha modelování Základní úlohou modelování je poskytnout přehled o organizaci dat v datovém skladu a u modelů datových trhů přehled o způsobu vyuţití koncovými uţivateli. Datové modely dále slouţí k podrobné dokumentaci podoby jednotlivých částí datového skladu a mohou být i podkladem pro samotný vývoj. Modelování je významné také z hlediska účelu pouţití datového skladu a datových trhů. Při úvodní analýze jsou definovány poţadavky a podle nich jsou pak navrţeny datové modely. Data v datovém skladu by měla být organizována a strukturována takovým způsobem, který umoţní efektivní dotazování a bude především slouţit podnikovým účelům a uţivatelům. Je vhodné znát alespoň rámcově dotazy na data jiţ v době modelování. To usnadní tvorbu datových modelů a podobu tak, aby zvýšil efektivitu jednotlivých dotazů, a tím usnadní podnikovým uţivatelům práci. Jestliţe není předem známo, jak budou podniková data pouţívána, hrozí riziko vývoje nevhodných datových struktur pro dotazování, dlouhé odezvy a výsledkem můţe být i to, ţe datový sklad nebude pouţíván a zhorší se návratnost investic.[2]
32
3.2 Modelovací jazyky Mezi nejznámější modelovací jazyky patří UML = Unified Modelling Language a modelování pomocí ER Diagramů = Entity Relationship Diagram. V této části se zaměřím na popis a přiblíţení modelování pomocí ER modelů, jelikoţ i praktická část práce je modelována v ER diagramech.
3.2.1 UML – Unified Modellling Language Modelovací jazyk UML je standardem konsorcia Object Management Group (OMG) pro záznam, vizualizaci a dokumentaci artefaktů softwarových systémů. Nakreslený model popisuje, co má systém dělat, neříká však jak to má dělat. Úkolem UML je zpřehlednit návrh systému tak, aby mu porozuměli všichni zainteresovaní. Je vyuţíván jako notace pro zachycení artefaktů, na který je třeba se domluvit. Lze jej vyuţít přímo (nakreslíme obrázek, abychom si upřesnili implementaci), ale i zpětně (obrázek nám poslouţí k pochopení existujícího kódu). Dále UML slouţí jako dokumentační prostředek, ve kterém dokumentujeme systém nebo jeho části. UML je také programovacím jazykem, ze kterého se generují kódy. Pomocí vhodných nástrojů se kreslí diagramy, které upřesňují jednotlivé části chování vyvíjeného softwaru. Mezi základní patří diagram tříd, diagram případů uţití, stavový diagram, diagram aktivit, diagram komponent a další.
3.2.2 ER modely Zkratkou ER modelu je entitně-relační model. Tento typ modelování je zaměřen na datové modelování strukturovaných dat v databázích. V UML se dá vyjádřit pomocí diagramu tříd. ER diagram zavedl a poprvé pouţil Peter Chen9 v roce 1976. Schéma v ER modelu je zaloţeno na dvou typech objektů – entita a vztah. Entita je nezávisle existující objekt
9
Chen, P. P. S. The Entity-Relationship Model: Towards a Unified View of Data, ACM TODS, 1976
33
reálného světa. Vztah je vazba mezi dvěma nebo více entitami. Velikost vazby se nazývá kardinalita. V UML je vazba definována jako asociace mezi třídami. Dalším prvkem ER diagramů je atribut, coţ je vlastnost entity, která nás v kontextu daného problému zajímá. V dalších částech bakalářské práce se nachází ER diagramy, které problematiku modelování entit a vztahu více přiblíţí.
3.3 Typy datových modelů Modelování má svá pravidla a publikace [2] velmi doporučuje je dodrţovat. Existuje několik úrovní modelování pro různé potřeby podniku.
3.3.1 Konceptuální model Na nejvyšší úrovni abstrakce se nachází konceptuální model, který představuje grafický obraz reality. Konceptuální model má za úkol definovat všechny podnikové entity bez uvádění zbytečných detailů. Je nezávislý na typu databáze. Dále má definovat jednotnou terminologii, která bude pouţita i v dalších modelech.
3.3.2 Logický model Konceptuální datový model je dále konkretizován logickým modelem, který je uţ závislý na pouţitém typu databáze (relační, objektově-relační, objektová). Logický model popisuje detailněji jednotlivé zdrojové i cílové databáze, tedy databáze primárních systémů, dočasného úloţiště dat, úloţiště datového skladu a datových trhů. Zdrojové logické datové modely odráţejí strukturu dat v primárních systémech. Lze je mapovat na konceptuální model, aby bylo moţné znázornit soulad s celkovou podnikovou terminologií a tím zajistit, a ţe budou definovány všechny aspekty konceptuálního datového modelu. Logický model úloţiště datového skladu můţe být jak normalizovaný, tak v denormalizované formě, záleţí na přístupu tvůrce. Také je důleţité vytvořit logické modely datových trhů, které znázorňují organizaci dat dle poţadavků podnikových uţivatelů na analýzy a reportování. V mnoha případech má podobu hvězdice či sněhové vločky, ale můţe
34
být modelována i ve třetí normální formě. Tento model vychází z logického modelu úloţiště datového skladu.
3.3.3 Fyzický model Fyzický model představuje model s ohledem na konkrétní databázový systém. Takový model můţe i nemusí přesně odpovídat svému přidruţenému logickému datovému modelu. Jednotlivé modely této úrovně, jak zdrojové, datového úloţiště i datových trhů, obvykle označují fyzické databázové tabulky a sloupce v dané databázi. Všechny modely by měly být navrhovány pomocí jednotného nástroje pro návrh architektury, aby byly ve vzájemném souladu, aby byla zajištěna konzistentní terminologie a soulad mezi datovými typy.
3.4 Modelování úložiště datového skladu Úloţiště datového skladu bývá často modelováno ve třetí normální formě. Vysoce normalizované datové struktury umoţní rychlé ukládání transformovaných, vyčištěných a konsolidovaných dat a poskytnou vhodnou datovou základnu pro analýzy do datových trhů. Na obrázku 6 vidíme na pomyslném začátku tabulku zákazník s osobními údaji, jakoţto atributy tabulky. Na ní jsou navázané tabulky, které blíţe identifikují zákazníka, je jimi osobní doklad a adresa, kaţdá s vlastními atributy. Na tabulku zákazník je navázaná ţádost, která je dále napojena na tabulku bankovní účet. S ní pak souvisí poţadovaný produkt, tedy tabulky karta a/nebo hypotéka.
35
Obrázek 6 Relační model databáze, zdroj vlastní úprava
3.5 Modelování datových trhů Jak uţ bylo uvedeno dříve, datový trh je podmnoţinou dat z datového úloţiště, kde se nacházejí transformovaná či jinak agregovaná data dle dříve definovaných poţadavků podnikových uţivatelů. V souvislosti s podnikovými potřebami a potřebami konkrétního datového trhu budou vypadat i jejich datové struktury. [4] Modely datových trhů sestávají z tabulek faktů a z dimenzí a mívají podobu hvězdice, nebo sněhové vločky. Názvy schémat jsou odvozeny od grafického zobrazení daného schématu.
3.5.1 Tabulky faktů Tabulka faktů je centrální tabulkou ve hvězdicovém nebo vločkovém schématu, která obsahuje podnikové metriky kvalifikované podle dimenzí. Hodnoty faktů lze získat buď ze zdrojových systémů, které představují převzaté hodnoty na nejniţší úrovni granularity, např. jednotlivé transakce na platebních kartách. Metriky mohou být ale také odvozené a jsou výsledkem transformace a agregace jednotlivých řádků v úloţišti datového skladu a jako suma či jiná agregace uloţeny jako jeden řádek do tabulky faktů. V našem příkladu by se mohlo jednat o sumu všech transakcí realizovaných na platebních kartách za jeden den. Na
36
tomto odvození je praktické, ţe lze metriky sledovat zpět k jejich původu, který se nachází v úloţišti datového skladu a zpětně ve zdrojových systémech. Kaţdý řádek v tabulce faktů je jednoznačně identifikován pomocí svého primárního klíče, který je buď uměle vygenerovaný, nebo se skládá z primárních klíčů kaţdé přidruţené dimenze. 3.5.1.1 Typy tabulek faktů [2] Transakční tabulky faktů uchovávají metriky na jejich nejjemnější úrovni granularity, coţ je úroveň, na níţ byla metrika vytvořena. V bankovním prostředí se můţe jednat o jednotlivé transakce na běţných účtech, příjem peněz ze splátek hypoték či výběry peněz z bankomatů. Snímkové tabulky faktů jsou vytvořeny s libovolným poţadovaným časovým obdobím. Metriky objemu finančních prostředků na spořicích účtech klientů lze zaznamenávat kaţdý den a tím sledovat jejich vývoj. Tedy o kolik se zvedla úroveň naspořených finančních prostředků našich klientů kaţdý den, týden, měsíc či rok. Velmi častou variantu představují akumulační snímkové tabulky faktů, které lze aktualizovat. To znamená, ţe určité řádky tabulky se mohou měnit podle nejnovějších metrik. Kaţdý den lze sledovat přírůstky a úbytky na spořicích účtech kumulovaně a i tak sledovat aktuální objem finančních prostředků v bance.
3.5.2 Tabulky dimenzí Dimenze je tabulka, která kvalifikuje metriky v tabulce faktů. Jako dimenze lze vyuţít stávající tabulky v úloţišti datového skladu, ale pokud by její obsah nevyhovoval kvůli nedostatku detailních informací nebo naopak obsahoval moc velké detaily, je vhodné vytvořit samostatnou dimenzi s přesně definovanými atributy pro konkrétní datový trh. Jeden datový trh můţe mít více způsobů aplikace [2] , například k zjištění transakcí realizovaných určitými zákazníky v určitém regionu nebo např. k přehledu souhrnu ţádostí o hypotéku v praţském regionu. Tato rozdílná pouţití závisí na dimenzích, metrikách a granularitě, které jsou odvislé od konkrétního účelu vyuţití.
37
3.5.3 Hvězdicové schéma - STAR Vzhled hvězdicového schématu, jak uţ bylo řečeno, se vyznačuje tím, ţe uprostřed se nachází tabulka faktů a kolem ní jsou rozmístěny dimenze, které data faktů kvalifikují. Na obrázku 7 vidíme uprostřed faktovou tabulku s transakcemi platební kartou. Pokud bychom měli pouze informace z tabulky faktů, není to samo o sobě moc pouţitelné, protoţe to nenese ţádný kontext. Jsou-li ale transakce vztaţeny k určitému zákazníkovi, bankovnímu účtu a platební kartě, získají výsledky měření transakcí větší přínos. I v multidimenzionálním schématu se můţeme setkat s číselníky jakoţto velmi malými dimenzemi. [2] Dimenze ve hvězdicovém schématu jsou silně denormalizované entity, to znamená, ţe veškeré parametry jedné entity jsou obsaţeny v jedné dimenzi. Například v entitě zákazník je uţ uvedena i jeho adresa i osobní doklad. Velká výhoda hvězdicového schématu spočívá v tom, ţe je velmi snadno srozumitelné a pouţitelné.
Obrázek 7 Multidimenzionální model databáze typu STAR, zdroj vlastní úprava
3.5.4 Schéma sněhové vločky - SNOWFLAKE Alternativou hvězdicového schématu je schéma sněhové vločky, které popisuje stejný typ datového modelu jako hvězdice, ale dimenze jsou více normalizované [2] . Na obrázku 8 je tabulka platební karta normalizovaná a její druh je zachycen v samostatné dimenzi. Výhodou tohoto typu modelu jsou moţnosti agregace na různých úrovních dimenzí. Má-li uvedený zákazník dvě a více platebních karet různého typu (Visa, MasterCard, Dinners Club, American Express) či druhu (debetní, kreditní, charge karty), lze transakce na kartách 38
agregovat právě podle jeho typu nebo druhu. Můţeme například získat analýzu transakcí na platebních kartách typu Visa za určité období a zákazníků majících adresu v Praze. Ne ţe by tato analýza ve hvězdicovém schématu nešla, ale normalizace poskytuje pruţnější změny parametrů analýzy.
Obrázek 8 Multidimenzionální model databáze typu SNOWFLAKE, zdroj vlastní úprava
Variabilita sestav a dotazů se v tomto kontextu povaţují za velice flexibilní, protoţe podnikový uţivatel můţe volit granularitu dimenze a tím měnit i celou sestavu. Představme si, ţe datový model zahrnuje šest dimenzí a všechny mají detaily v dalších souvisejících dimenzích. Můţete pak vypočtené analýzy procházet nahoru a dolů podle potřeby. Podnikovým uţivatelům to poskytuje mnoho moţností zpracování a dokonalý přehled o analyzovaných datech.
39
4 Návrh
implementace
BI
pro
sledování
studijních výsledků na BIVŠ Tato část práce bude věnována popisu a způsobu implementace zvoleného řešení. Pro potřeby analýzy výsledků hodnocení bakalářských a diplomových prací navrhuji vytvořit níţe popsané řešení BI.
4.1 Zadání a detailní analýza potřeb Pro potřeby školy je třeba vytvořit vhodný nástroj pro analýzu dat hodnocení bakalářských a diplomových prací na BIVŠ podle metodiky v příloze č. 1. Hodnocení bakalářské či diplomové práce se skládá z několika dílčích hodnocení. Prvním z nich je slovní vyjádření ohodnocení relevantnosti ke studovanému oboru. Spadá sem hodnocení „velká“ ve zkratce „V“ jakoţto velká shoda, dále „S“ jako střední nebo „M“ jako malá shoda. Také se můţe objevit „I“ pro vyjádření irelevantnosti, kde se ovšem vyţaduje vysvětlení, proč vedoucí práce přesto doporučil práci k obhajobě. Dále jsou uděleny známky od vedoucího a od oponenta dané práce. Známky nemusí být vţdy vyjádřeny celým číslem 1, 2, 3 nebo 4, ale lze stanovit známku také 1 aţ 2, 2 aţ 3 nebo 3 aţ 4. Posledním parametrem hodnocení je udělená známka z obhajoby, ta ovšem musí být vyjádřena celým číslem. Ke kaţdému hodnocení je napsán student BIVŠ, jeho jméno, příjmení, učo a studijní skupina. Dále je známo téma a anotace práce. Analýzy dat hodnocení prací spočívají především ve výpočtu průměrných hodnot získaných známek u různých skupin záznamů, např. ve studijní skupině, v určitém regionálním středisku, ve vybraném oboru nebo v různých kombinacích těchto parametrů. Při práci na návrhu řešení jsem dospěla k závěru, ţe by bylo vhodné u kaţdého hodnocení doplnit metodiku a uvést také akademický rok, ve kterém byla práce obhajována. Důvodem je, ţe by bylo moţné počítat průměry také za jednotlivé akademické roky a následně hodnoty meziročně srovnávat.
40
4.2 Stručný přehled navrhovaného řešení Výsledky hodnocení bakalářských a diplomových prací zapsaných v informačním systému BIVŠ (dále jen IS BIVŠ10) jsou zdrojovými daty a tvoří podklad pro analýzy. Tato data lze exportovat do textového souboru a dále zpracovávat procesem ETL. Alternativou je, ţe data se budou načítat ETL nástrojem přímo z databáze IS. Následuje zpracování dat v datovém skladu. Z praktických zkušeností jsem přesvědčena o výhodnosti vyuţívání dočasné vrstvy datového skladu z důvodu prozatímního uchování surových dat kvůli případnému pádu aplikace v průběhu zpracování nebo kvůli kontrole korektnosti zpracování v dalších fázích. Na základě toho navrhuji v prvním kroku vytvořit kopii zdrojových dat a uloţit do jednoduchých databázových struktur této vrstvy. Procesem ETL/ELT, případně SQL skripty budou data dále transformována, upravena a uloţena do databázových struktur úloţiště datového skladu v relační databázi. Třetí vrstvu databáze bude tvořit datový trh s multidimenzionálním databázovým modelem, který bude obsahovat jiţ transformovaná data s výsledky agregací na jednotlivých úrovních. Výsledky výpočtů pak bude moţné sledovat různými způsoby. Je moţné se na data dotazovat přímo do databáze nebo je lze vyuţít exportu do MS Excelu a tvorby grafů k jednotlivým sestavám. Do kaţdé fáze zpracování lze zahrnout kontroly na prázdná pole, chybějící data či výskyt neočekávaných hodnot. Upozornění na chyby bude zapsáno v samostatné databázové tabulce a odkud je moţné je exportovat do excelové tabulky pro kontrolu uţivatelům.
4.3 Fáze zpracování Jak uţ bylo nastíněno v předešlé kapitole, zpracování dat je rozděleno na několik fází. Těmto fázím odpovídá také obrázek architektonického řešení celého systému – viz obrázek 9.
10
IS BIVŠ: https://is.bivs.cz/
41
Obrázek 9 Architektura systému BI pro sledování analýz hodnocení bakalářských a diplomových prací, zdroj vlastní úprava
4.3.1 Export dat Výsledky jednotlivých bakalářských a diplomových prací jsou uloţeny v informačním systému školy BIVŠ (dále jen IS). Pro potřeby zpracování systémem BI je potřeba přidělit administrátorovi přístup do databáze IS, odkud budou data načtena do samostatné databáze datového skladu. Nebo lze příslušná data načítat z předem exportovaného textového souboru např. ve formátu csv11, který bude uloţen na předem domluveném místě na sdíleném disku. Odtud bude databázový program data načítat a importovat do databáze. V tabulce 8 vidíme příklad obsahu textového souboru v csv formátu, který lze pouţít pro načtení do datového skladu. Jedná se o seznam studentů bakalářského a magisterského studia, kteří tento rok nebo v minulých letech psali závěrečnou bakalářskou či diplomovou práci a absolvovali jejich obhajobu. U jednotlivých studentů v tabulce je uvedeno kromě jejich jmen, také učo, coţ je identifikační číslo, pod kterým jsou vedeni v ISu. Dále je uvedena zkratka studijní skupiny, která se skládá ze zkratek pro regionální středisko, obor a formu studia, kterou student navštěvoval. Např. PRA_ITK je zkratkou pro praţské pracoviště, obor informační technologie a formu kombinovanou, zkratka KVA_BMP představuje pracoviště
11
CSV – comma-separated values, (hodnoty oddělené čárkou) je jednoduchý souborový formát určený pro
výměnu
tabulkových
dat.
Wikipedie.
CSV
http://cs.wikipedia.org/wiki/CSV
42
[online].
[cit.
2013-04-20].
Dostupné
z:.
Karlovy Vary, obor bankovní management a prezenční formu. Součástí tabulky je také téma práce a anotace práce. Data, která jsou určená k analýze, nalezneme v následujících čtyřech sloupcích. Sloupec „Obs“ vyjadřuje hodnocení relevantnosti ke studovanému oboru, jehoţ hodnoty V, S a M odpovídají slovnímu vyjádření vysoké, střední a nízké. Sloupec „Ved“ obsahuje známku, kterou obdrţel student za vypracovanou bakalářskou či diplomovou práci od vedoucího práce, další sloupec známku od oponenta a další pak známku, která byla udělena studentovi na základě obhajoby. Sloupec „Obh“ můţe obsahovat pouze známky 1, 2 nebo 3, ve sloupcích „Ved“ a „Op“ se mohou však vyskytovat také známky 1-2, 2-3 či 3-4. Poslední sloupec obsahuje zkratku jména hodnotitele prací, členové jednotlivých kateder BIVŠ (BM = Bohumil Miniberger, LJ = Lubomír Jankových, VBP = Vladimír Beneš Petrovický atd.). Poslední sloupec obsahuje akademický rok obhajoby. Téma
Jméno
Elektronický systém spisové služby Bezpečnost a ochrana dat v operačních systémech Bezpečnost elektronického bankovnictví Bezpečnostní standardy v odvětví platebních karet Cloud Computing CMM (Capability maturity model) a možnosti jeho využití Deduplikační technologie zálohování dat
Učo
Skupina
Vedoucí práce
Obs
Ved Op Obh
Hodn
Ak.rok
A. B.
12665
PRA_ITK
Ing. L. Jankových, CSc.
V
2
2
1
BM
2009/2010
Bc. M. Z.
12618
BBY_FIK
Ing. A. Vogeltanz
V
1
1
1
VBP
2009/2010
Bc. M. P.
12809
PRA_OMK
Ing. A. Vogeltanz
V
1
1
1
VBP
2009/2010
Bc. M. M.
16539
PRA_ITK
Ing. M. Soldánová
V
1
2
1
BM
2009/2010
Bc. T. O.
12110
KVA_BMK
Ing. V. Beneš
M
2
3
1
BM
2009/2010
Bc. V. K.
12621
PRA_MKP
doc. Ing. V. Svatá, CSc.
V
2
2
2
LJ
2009/2010
Bc. K. K.
7152
KVA_BMK
Ing. L. Jankových, CSc.
V
1
1
1
BM
2009/2010
Tabulka 8 Příklad načítacího textového souboru v csv formátu, zdroj vlastní úprava12
4.3.2 Datový sklad Databáze Business Intelligence bude v dočasném úloţišti dat obsahovat samostatnou tabulku extrakt hodnocení, kam budou data z databáze IS nebo z csv souboru 1 : 1 načtena. Tabulka bude obsahovat stejná pole jako zdrojový soubor. Kromě toho bude obsahovat sloupce datum a proces id, které budou vţdy aktualizovány datem zpracování v datovém skladu
12
Jelikoţ bude bakalářská práce veřejně dostupná, z důvodu ochrany osobních dat jsou uváděny v této
bakalářské práci pouze zkratky jmen a příjmení. V mnou navrţeném řešení jsou však údaje v plném tvaru jako v IS BIVŠ.
43
a přiděleným číslem procesu. Schéma databázové tabulky extrakt hodnocení vidíme v logickém datovém modelu na obrázku 10.
Obrázek 10 Vrstva dočasného úložiště dat, zdroj vlastní úprava
Četnost načítání a zpracování dat závisí na potřebách koncových uţivatelů. Data je moţné načítat a zpracovávat automatizovaným spuštěním v pravidelných intervalech - kaţdý den, jednou za týden nebo měsíc atd. Také lze proces spouštět manuálně odpovědnou osobou dle potřeb, například na konci akademického roku, kdy budou v ISu přítomny všechny bakalářské a diplomové práce a jejich hodnocení, příp. pro potřeby hodnocení výchovně vzdělávacího procesu ve výroční zprávě BIVŠ. Do procesu zpracování budou vstupovat vţdy všechna data, která budou v extraktu k dispozici, nejen část záznamu k určitému datu či aktualizovaná data. V rámci vyhodnocení rozdílů budou však do datového úloţiště uloţena pouze nová či rozdílná data od dat jiţ dříve zpracovaných a uloţených. 4.3.2.1 Kontroly a chybová hlášení Po uloţení extraktu do dočasného uloţiště dat je vhodné zahrnout do zpracování úvodní kontroly zdrojových dat. Jde především o zjištění, jestli nejsou některá pole prázdná, jestli nemají špatný formát, jestli neobsahují neočekávané hodnoty, jestli se nevyskytují duplicity atd. Hlavním důvodem kontrol je ujištění, ţe bude zpracován korektní a úplný soubor informací. Při absenci některých dat nebo při výskytu chybných hodnot by mohlo dojít ke
44
zkreslení výsledných kalkulací a tím ke sníţení důvěryhodnosti vypočtených výsledných hodnot. Výsledky kontrol budou při výskytu chyb zapsány do samostatné databázové tabulky chyba. Tabulka můţe obsahovat například následující chybová hlášení – viz tabulka 9. Id
Chyba
1 Prázdná hodnota v poli hodnocení obsahu, počet záznamů: 25 2 Prázdná hodnota v poli posudek vedoucí, počet záznamů: 1 3 Prázdná hodnota v poli posudek oponent, počet záznamů: 1
Proces id
datum
2 2 2
17.3.2013 18:14 17.3.2013 18:14 17.3.2013 18:14
Tabulka 9 Příklad chybových hlášení v tabulce chyb, zdroj vlastní úprava
4.3.2.2 Transformace Po vyhodnocení kontrol můţe začít transformace dat a úprava do nových struktur datového uloţiště. Vzniknou samostatné tabulky pro jednotlivé entity, tedy student (učo), bakalářské a diplomové práce a tabulky s hodnocením, studijní skupina a z ní vyplývající druh a forma studia, studovaný obor, regionální středisko a stát, kde výuka probíhá13. Na obrázku 11 vidíme logický model relační databáze úloţiště datového skladu, kde jsou vyobrazeny výše jmenované entity ve formě databázových tabulek. Ve spodní části uprostřed se nachází tabulka učo, která bude po zpracování obsahovat seznam studentů, jejichţ práce byly hodnoceny. Atributy tabulky jsou detaily studentů jako je id učo, jméno a příjmení, a dále odkazy na id přidělené studijní skupiny a id bakalářské a diplomové práce. Studijní skupina je samostatnou entitou a ve schématu je vidět, ţe se dále rozpadá na stát, středisko, obor, formu a druh, které tvoří samostatné číselníky. Model zobrazuje další vztahy s tabulkou učo a to k tabulkám bakalářských (vlevo dole) a diplomových (vpravo dole) prací. Tyto tabulky obsahují vţdy název příslušné práce, anotaci, jméno vedoucího práce a odkaz na příslušný akademický rok, ve kterém byla práce obhajována. Akademický rok je samostatný číselník. Tabulka učo se dále váţe na tabulky hodnocení bakalářských prací (vlevo) a diplomových prací (vpravo), jejichţ obsahem jsou detaily hodnocení jednotlivých obhajovaných prací.
13
BIVŠ má akreditovaná regionální výuková střediska jak v České republice, tak na Slovensku.
45
Obrázek 11 Vrstva úložiště datového skladu, zdroj vlastní úprava
Úloţiště datového skladu bude vytvořeno v relačním databázovém modelu. Vzhledem k ponechání jména a příjmení, případně i titulů v jednom poli v tabulce učo, a stejně tak jména vedoucích jednotlivých prací, není tím pádem v návrhu databázového modelu splněna třetí normální forma (3. NF). Pro splnění 3. NF by bylo nutné jméno, příjmení a případné tituly ve společném poli vyčistit a syntakticky rozlišit, a pro kaţdý atribut zavést samostatné pole, tedy zvlášť jméno, příjmení a titul. Vzhledem k tomu, ţe v tomto případě není jméno a příjmení sledovaným analytickým prvkem, byly tyto hodnoty ponechány jako jeden atribut v jednom poli stejně jako ve zdrojovém systému. V případě poţadavku na syntaktickou analýzu těchto dat ze strany BIVŠ, by bylo nutné do systému Business Intelligence zasadit také nástroj na čištění a případně i konsolidaci dat.
46
Všechny tabulky uvedené v úloţišti datového skladu mají svá vlastní ID, coţ jsou uměle generované číselné hodnoty, které určují primární klíč tabulek. Výjimku tvoří tabulka učo, kde id učo je stejné jako učo ve zdrojovém souboru, tedy téţ učo v ISu. Důvodem ponechání původního učo je zachovaná jedinečnost této hodnoty napříč celou tabulkou. Bylo by tedy zbytečné vytvářet nové umělé id učo, čímţ by mohlo dojít k chaosu mezi těmito dvěma ID. Také bude takto zachován význam pole id učo v datovém skladu pro potřeby vyhledávání. Obě tabulky hodnocení (hodnocení bakalářských prací a hodnocení diplomových prací) v úloţišti dat datového skladu po transformacích budou obsahovat částečně upravená data ze zdroje. Hodnota v poli hodnocení obsahu je upravená tak, ţe „V“ odpovídá známce 1, „S“ odpovídá známce 2 a „M“ odpovídá známce 3. Hodnoty z polí hodnocení vedoucího a oponenta jsou beze změny, kromě toho, je-li známka mezi jedničkou a dvojkou, dvojkou a trojkou nebo trojkou a čtyřkou. V tom případě jsou data upravena na hodnoty 1,5 nebo 2,5 nebo 3,5. Obě tabulky hodnocení v úloţišti datového skladu navíc obsahují pole poměr hodnocení k obsahu a průměr hodnocení, které jsou matematickými výpočty ze zdrojových hodnot. Pole poměr hodnocení obsahu je výpočtem „1/hodnocení obsahu“ a průměr hodnocení je aritmetickým průměrem hodnot z polí posudků vedoucího a oponenta, a udělené známky z obhajoby. V tabulce 10 níţe vidíme, jak vypadala data na vstupu, tzn., jak bylo zapsáno hodnocení jednotlivých studentů dle učo v ISu a vyexportováno do textového souboru. Tabulka 11 pak obsahuje jiţ transformovaná data v úloţišti datového skladu včetně poměru k obsahu a průměrných hodnot hodnocení. Vidíme úpravu v hodnocení obsahu z alfabetického vyjádření na číselné a dále úpravy udělených známek u vedoucího a oponenta, nebyla-li známka celé číslo, ale 1-2 (vyjádřeno 1.2) či 2-3 (vyjádřeno 2.3). U prvního záznamu vidíme hodnocení vedoucího 2.1, coţ je nejasné vyjádření. V takovýchto případech lze přistoupit k transformaci tak, ţe se vezme celá hodnota, jak bylo učiněno v našem příkladu nebo je moţné na základě úvodních kontrol záznam vůbec nezpracovávat a vytvořit chybové hlášení a tak upozornit tak na špatný/nepřesný údaj ve zdrojových datech.
47
Učo 6632 6910 12665 13558 13872 15187 15838 16539
Jméno
Hodn obs
Ing. J. F. Y. B. A. B. F. H. Bc. P. O. Bc. M. K. Ing. V. V. Bc. M. M., DiS.
Vedoucí Oponent Obhajoba
V S V V V V S V
1 3 2 1 1 1 1 1
2 2 2 1 2 1 1 2
1 3 1 1 1 1 1 1
Tabulka 10 Zdrojová data z ISu, zdroj vlastní úprava
Učo 6632 6910 12665 13558 13872 15187 15838 16539
Jméno
Hodn obs
Ing. J. F. Y. B. A. B. F. H. Bc. P. O. Bc. M. K. Ing. V. V. Bc. M. M., DiS.
Vedoucí Oponent Obhajoba Poměr_obs Průměr
1 2 1 1 1 1 2 1
1 3 2 1 1 1 1 1
2 2 2 1 2 1 1 2
1 3 1 1 1 1 1 1
1 0,5 1 1 1 1 0,5 1
1,33 2,67 1,67 1 1,33 1 1 1,33
Tabulka 11 Transformovaná data v úložišti datového skladu, zdroj vlastní úprava
Jak uţ bylo uvedeno výše, tabulka studijní skupina je samostatnou entitou v úloţišti datového skladu. Obsahuje seznam studijních skupin podle toho, jak je škola eviduje. Jedná se o zkratky, které ve svém názvu skrývají regionální středisko, obor, formu případně druh studia. Při transformacích v rámci ETL procesů byly významy jednotlivých atributů přeloţeny do slovních výrazů, hodnoty uloţeny do jednotlivých číselníků a ke kaţdé skupině přiděleny odkazy na dané číselníky formou id. V tabulce 12 je příklad názvů studijních skupin a dle syntaxe rozklíčované hodnoty státu, střediska, oboru, formy a druhu ke kaţdé zde uvedené studijní skupině. Stud.skupina Stát
Středisko
Obor
Forma
Druh
KVA_BMK LMI_BMK PRA_ITK PRA_MKP PRA_OMK PRA_VSK
Karlovy Vary Liptovský Mikuláš Praha Praha Praha Praha
Bankovní management Bankovní management Informační technologie Makléř Oceňování majetku Veřejná správa a Evropská unie
Kombinovaná Kombinovaná Kombinovaná Prezenční Kombinovaná Kombinovaná
Bakalářské Bakalářské Bakalářské Bakalářské Bakalářské Bakalářské
Česká republika Slovensko Česká republika Česká republika Česká republika Česká republika
Tabulka 12 Seznam studijních skupin a jejich příslušný stát, středisko, obor, forma a druh, zdroj vlastní úprava
Nad jednotlivými tabulkami hodnocení bakalářských a diplomových prací v úloţišti datového skladu budou vytvořeny databázové pohledy view hodnocení bakalářských prací a view hodnocení diplomových prací. Hlavním důvodem je zobrazení detailů a textových popisů 48
z navázaných tabulek učo a studijní skupina, bakalářské a diplomové práce a akademický rok. Nejsou tedy zobrazeny odkazy na id v navázaných tabulkách pomocí cizích klíčů, ale textové popisy. Schéma databázového pohledu view hodnocení bakalářských prací je vidět na obrázku 12 níţe. Všimněme si, ţe pohled neobsahuje pole id studijní skupiny, ale přímo studijní skupina, neobsahuje id akademický rok, ale akademický rok atd. Učo id bylo v pohledu ponecháno pro potřeby vyhledávání. Obdobně je tomu tak u pohledu view hodnocení diplomových prací.
Obrázek 12 Model s databázovým pohledem na tabulku hodnocení bakalářských prací, zdroj vlastní úprava
4.3.3 Datový trh Tabulky hodnocení bakalářských a diplomových prací obsahují data na úrovni jedné bakalářské či diplomové práce, tedy i na úrovni jednoho studenta. Tyto tabulky slouţí jako poklad pro další výpočty a analýzy, jejichţ výsledkem budou agregace dat. Pro výše uvedené analýzy je třeba vytvořit datový trh ve hvězdicovém schématu s faktovou tabulkou průměr a dimenzemi, podle kterých se budou provádět agregace. V našem případě navrhuji faktovou tabulku průměr hodnocení bakalářských prací. K ní budou navázané dimenze studijní skupina, akademický rok, stát, středisko, obor a forma. Pro stanovení úrovně agregace je třeba přidat dimenzi typ agregace, která bude obsahovat seznam moţných způsobů agregace – viz tabulka 13. 49
Id
Název typu agregace
1 2 3 4 8 9 10 18 19 22 23 27 31
Všechny práce Akademický rok Akademický rok a stát Akademický rok, stát a středisko Akademický rok, stát, obor a forma Akademický rok, stát a forma Akademický rok a studijní skupina Stát Stát a středisko Stát, středisko a forma Stát a obor Středisko a obor Obor
Tabulka 13 Příklad obsahu dimenze typ agregace, zdroj vlastní úprava
Na obrázku 13 níţe vidíme hvězdicové schéma pro výše popsanou OLAP analýzu. Agregace budou tedy realizovány na úrovních akademických let, států, regionálních středisek, studijních skupin, oborů, forem a všech jejich kombinací. Obdobně by se pak řešily výpočty průměrů hodnocení diplomových prací, tedy formou faktové tabulky průměr diplomových prací.
Obrázek 13 Hvězdicové schéma tabulky průměru hodnocení bakalářských prací s vazbou na jednotlivé dimenze, zdroj vlastní úprava
50
Přítomnost dimenze typ agregace a záznamy v ní dávají uţivateli moţnost při dotazování na data vybrat si přesně ten typ agregace, který potřebuje. Má-li přání vidět průměrná hodnocení dle zvoleného typu seskupení, např. podle akademických let nebo studijních středisek nebo jejich kombinací, zvolí si příslušný filtr na daný typ agregace. Výsledek dotazu mu pak zobrazí pouze poţadovaná data. Nad tabulkou průměrů a jejími dimenzemi je kvůli lepší přehlednosti pro uţivatele moţné vytvořit databázový pohled s názvem view průměr bakalářských prací – viz obrázek 14, který neobsahuje ID s odkazy na jednotlivé dimenze a číselníky ani ţádná technická data, jako datum běhu zpracování databázového procesu nebo číslo procesu zpracování. Pohled zobrazuje pouze id typu agregace, obecný slovní popis agregace, popis agregace obsahující konkrétní hodnoty atributů, podle kterých bylo agregováno, a dále vypočtené průměrné hodnoty hodnocení.
Obrázek 14 Databázový pohled view průměr bakalářských prací nad tabulkou průměrů s dimenzí typu agregace, zdroj vlastní úprava
V tabulce 14 vidíme příklad agregace podle akademického roku a studijní skupiny. Vzorek dat je vytvořen ze tří různých akademických let a šesti studijních skupin. Ve sloupci popis
51
vidíme, o které skupiny dat se jedná, o který akademický rok a kterou konkrétní studijní skupinu14. Typ
Typ agregace
Popis
Počet
Obs
Ved
Op
Obh
Prům
10
Akad. rok a stud. Skupina
2009/2010 - KVA_BMK
2
10
Akad. rok a stud. Skupina
2009/2010 - PRA_ITK
3
2
1,5
2
1
1,5
1,33
1,83
2
1,67
1,83
10
Akad. rok a stud. Skupina
2009/2010 - PRA_MKP
1
1
2
2
2
2
10
Akad. rok a stud. Skupina
2009/2010 - PRA_OMK
2
1,5
1,5
1,5
1,5
1,5
10
Akad. rok a stud. Skupina
2009/2010 - PRA_VSK
1
2
1
1
1
1
10
Akad. rok a stud. Skupina
2010/2011 - KVA_BMK
2
1
1,5
1
1
1,17
10
Akad. rok a stud. Skupina
2010/2011 - PRA_ITK
3
1,33
1,67
1,67
1,67
1,67
10
Akad. rok a stud. Skupina
2010/2011 - PRA_OMK
3
1,33
2
1,67
2
1,89
10
Akad. rok a stud. Skupina
2010/2011 - PRA_VSK
1
2
1
2
2
1,67
10
Akad. rok a stud. Skupina
2011/2012 - KVA_BMK
11
1,64
1,27
1,82
1,45
1,51
10
Akad. rok a stud. Skupina
2011/2012 - LMI_BMK
5
1,4
1,4
1,6
1,4
1,47
10
Akad. rok a stud. Skupina
2011/2012 - PRA_BMK_JS
3
1,33
1,67
1,67
1,33
1,55
10
Akad. rok a stud. Skupina
2011/2012 - PRA_ITK
6
1,5
1,33
1,25
1,33
1,31
10
Akad. rok a stud. Skupina
2011/2012 - PRA_MKP
4
1,75
1,5
1,25
1,5
1,42
10
Akad. rok a stud. Skupina
2011/2012 - PRA_OMK
5
1,2
1,7
1,4
1,4
1,5
10
Akad. rok a stud. Skupina
2011/2012 - PRA_VSK
4
2
1,25
1,75
1,75
1,59
Tabulka 14 Příklad agregace dat podle akademického roku a studijní skupiny, zdroj vlastní úprava
V uvedených záznamech můţeme srovnávat průměry hodnocení v jednotlivých letech v dané studijní skupině. Ve sloupci počet je uveden počet relevantních bakalářských prací, jejichţ hodnocení bylo průměrováno. Tučně označené jsou záznamy, které by mohly být předmětem zkoumání a porovnávání. V prvním a druhém zkoumaném akademickém roce to byly vţdy tři práce a ve třetím šest. Z výsledků analýzy dat dále vidíme, ţe průměrná hodnocení obsahu se postupem let spíše horšila, zatímco známky od vedoucích, oponentů a z obhajoby se kaţdý rok zlepšoval, tím pádem se zlepšoval i celkový průměr. Pro kontrolu výpočtu tučně zvýrazněných průměrů hodnocení z tabulky 14 jsou v následující tabulce 15 zobrazeny detaily jednotlivých hodnocení, které byly průměrovány.
14
Uţivatelé, kteří si budou prohlíţet transformovaná data, většinou znají význam zkratek studijních skupin,
proto zde nejsou zkratky dále vysvětleny. Nebyl by ovšem problém do databázového pohledu přidat pole s poţadovaným vysvětlením.
52
Učo
Jméno a příjmení
Skupina
Název práce
Akad.rok
Obs
Ved
Op
12811 Bc. J. K. 16539 Bc. M. M., DiS.
PRA_ITK PRA_ITK
12665 A. B.
PRA_ITK
Hry, Facebook a ekon... Bezpečnostní standar...
2009/2010 2009/2010
2 1
2,5 1
2 2
3 1
2,5 1,33
Elektronický systém ...
2009/2010
1
2
2
1
1,67
Průměr 13558 F. H. 13872 Bc. P. O.
PRA_ITK PRA_ITK
Návrh a implementace... IT Asset Management ...
2010/2011 2010/2011
6910
PRA_ITK
Možnosti využití COB...
2010/2011
Y. B.
Průměr 15838 15832 15187 13175 6755
Ing. V. V. Ing. O. K. Bc. M. K. Bc. Z. B. Ing. F. B.
PRA_ITK PRA_ITK PRA_ITK PRA_ITK PRA_ITK
Internetové bankovni... Využití ITIL pro zav... Vývoj aplikací v Clo... Audit informační bez... Laboratoř IT pro ped...
2011/2012 2011/2012 2011/2012 2011/2012 2011/2012
6632
Ing. J. F.
PRA_ITK
Procesní modely podn...
2011/2012 Průměr
Obh Prům
1,33 1,83 2,00 1,67
1,83
1 1
1 1
1 2
1 1
1 1,33
2
3
2
3
2,67
1,33 1,67 1,67 1,67
1,67
2 2 1 1 2
1 1 1 3 1
1 1 1 1,5 1
1 1 1 3 1
1 1 1 2,5 1
1
1
2
1
1,33
1,50 1,33 1,25 1,33
1,31
Tabulka 15 Detaily záznamů hodnocení průměrovaných v tabulce 14, zdroj vlastní úprava
Databázový pohled view průměr bakalářských prací, ze kterého je příklad dat v tabulce 14, obsahuje data se všemi typy agregací. Pro lepší přehlednost pro uţivatele je moţné vytvořit další databázové pohledy s omezeným mnoţstvím dat, např. jen pro určitý typ agregace. Datový model by pak vypadal jako na obrázku 15.
Obrázek 15 Různé alternativy databázových pohledů na tabulku průměr bakalářských prací, zdroj vlastní úprava
Počet a podoba databázových pohledů závisí na poţadavcích koncových uţivatelů, jak by jim to nejvíce vyhovovalo.
53
4.4 Historická data Teorie datových skladů zdůrazňuje hodnotu uchování historických dat. I ve výše popsaném návrhu se počítá s uchování historických dat a to především v tabulkách průměrů bakalářských a diplomových prací. V průběhu času se totiţ budou hodnoty průměrů měnit. Tím jak bude narůstat objem nových záznamů hodnocení, budou se měnit data v jednotlivých kategoriích kromě průměrů na úrovni akademických let. Tam přibydou pouze nová data. Historizace dat je realizována samostatnou historickou tabulkou průměr bakalářských prací history, kde jsou uchovány všechny kalkulované záznamy z minulosti. Historická tabulka obsahuje všechny atributy jako tabulka s aktuálními daty a navíc obsahuje pole platné od a platné do. Pole platné od říká, od kdy je uvedený záznam platný a platné do vyjadřuje koncové datum platnosti daného záznamu. Primární klíč historické tabulky je tvořen atributy id aktuální tabulky a datumovým polem platné od. Tím je zachována jednotnost v číslování a identifikace záznamů jako v aktuální tabulce s tím rozdílem, ţe v historické tabulce se jeden záznam můţe objevit vícekrát s různými intervaly platnosti.
Obrázek 16 Hvězdicové schéma historické tabulky průměru hodnocení bakalářských prací s vazbou na jednotlivé dimenze, zdroj vlastní úprava
54
Logický datový model na obrázku 16 nám ukazuje historickou tabulku průměrů bakalářských prací a její vazby na dimenze. Model je v podstatě stejný jako u aktuální tabulky, ale navíc obsahuje sloupce platné od a platné do pro evidenci intervalu platnosti. Stejně by pak vypadala historická tabulka průměr diplomových prací. Také na tyto historické tabulky lze aplikovat databázové pohledy, ať uţ na všechna data v historické tabulce nebo s omezením na určitou sadu dat – viz obrázek 17.
Obrázek 17 Databázové pohledy na historickou tabulku průměrů bakalářských prací, zdroj vlastní úprava
Ukaţme si na příkladu, jak se změní hodnoty průměrů, pokud do zdrojových dat přidáme jedno hodnocení z oboru PRA_ITK v akademickém roce 2011/2012. V tabulce 16 vidíme data, která jsou určená ke zpracování, kde přibyl jeden záznam hodnocení – zeleně zvýrazněno. Tabulka 17 obsahuje staré výpočty průměrů hodnocení bez přidaného záznamu a tabulka 18 zobrazuje výpočty průměrů po přidání výše zmiňovaného záznamu. Učo 6632 6755 13175 15187 15832 15838 18034
Jméno Ing. J. F. Ing. F. B. Bc. Z. B., DiS. Bc. M. K. Ing. O. K. Ing. V. V. G. P.
Obs Vedoucí Oponent Obhajoba Průměr 1 2 1 1 2 2 1
1 1 3 1 1 1 2
2 1 1,5 1 1 1 2
1 1 3 1 1 1 2
1,33 1 2,5 1 1 1 2
Tabulka 16 Ukázka nové sady zdrojových dat s přidáním jednoho nového záznamu hodnocení, zdroj vlastní úprava
55
Učo
Jméno a příjmení
Ing. J. F. Ing. F. B. Bc. Z. B., DiS. Bc. M. K. Ing. O. K. 15838 Ing. V. V. 6632 6755 13175 15187 15832
Skupina Název práce PRA_ITK PRA_ITK PRA_ITK PRA_ITK PRA_ITK
Procesní modely podn... Laboratoř IT pro ped... Audit informační bez... Vývoj aplikací v Clo... Využití ITIL pro zav...
PRA_ITK Internetové bankovni...
Akad.rok
Obs
Ved
Op
2011/2012 2011/2012 2011/2012 2011/2012 2011/2012
1 2 1 1 2
1 1 3 1 1
2 1 1,5 1 1
1 1 3 1 1
2
1
1
1
2011/2012 Průměr
Obh Prům
1,50 1,33 1,25 1,33
1,33 1 2,5 1 1 1 1,31
Tabulka 17 Staré hodnoty průměrů dat za akademický rok 2011/2012 a studijní skupinu PRA_ITK, zdroj vlastní úprava
Učo 6632 6755 13175 15187 15832 15838
Jméno a příjmení
Skupina
Název práce
Ing. J. F. Ing. F. B. Bc. Z. B., DiS. Bc. M. K. Ing. O. K. Ing. V. V.
PRA_ITK PRA_ITK PRA_ITK PRA_ITK PRA_ITK PRA_ITK
Procesní modely podn... Laboratoř IT pro ped... Audit informační bez... Vývoj aplikací v Clo... Využití ITIL pro zav... Internetové bankovni...
18034 G. P.
PRA_ITK Business Intelligenc...
Akad.rok
Obs
Ved
Op
2011/2012 2011/2012 2011/2012 2011/2012 2011/2012 2011/2012
1 2 1 1 2 2
1 1 3 1 1 1
2 1 1,5 1 1 1
1 1 3 1 1 1
1,33 1 2,5 1 1 1
2011/2012
1
2
2
2
2
Průměr
Obh Prům
1,43 1,43 1,36 1,43
1,40
Tabulka 18 Příklad změny průměrů při přidání jednoho záznamu hodnocení, zdroj vlastní úprava
V tabulce 19 máme výtah z pohledu na historickou tabulku průměrů bakalářských prací historická, která obsahuje jak staré hodnoty průměrů s ukončeným datem platnosti v poli platné do, tak nový záznam s průměrnými hodnotami po přidání jednoho hodnocení. Nový záznam má nastavené koncové datum platnosti daleko v budoucnosti. Oba záznamy mají stejné Id, v našem případě Id = 96. Id Typ Typ agr Popis 96 96
10 10
AR a ss AR a ss
2011/2012 - PRA_ITK 2011/2012 - PRA_ITK
Poč Obs Ved 6 7
Op
Obh Prům
1,50 1,33 1,25 1,33 1,43 1,43 1,36 1,43
1,31 1,40
Valid fr
Valid to
22.3.2013 22.3.2013 23.3.2013 31.12.2999
Tabulka 19 Starý a nový záznam průměrných hodnot z historické tabulky průměrů bakalářských prací, zdroj vlastní úprava
4.5 Zobrazení dat Pro zobrazení dat byl v této práci pouţit MS Excel. Poţadovaná data byla exportována do excelovské tabulky a k nim vytvořen vhodný graf. Tento nástroj je velmi nenákladný a pro grafické zobrazení zpracovaných dat hodnocení bakalářských a diplomových prací postačí. V další části práce jsou vloţeny obrázky s návrhy tří reportů v MS Excel a k nim vytvořených grafů.
56
Obrázek 18 zobrazuje hodnoty průměrů hodnocení všech bakalářských prací za tři různé akademické roky a na sloupcovém grafu je na první pohled vidět výrazné zlepšení v posledním sledovaném roce.
Obrázek 18 Příklad jednoduchého grafického zobrazení, zdroj vlastní úprava
Obrázek 19 Příklad zobrazení dat na pruhovém grafu, zdroj vlastní úprava
57
Obrázek 19 obsahuje průměry hodnocení bakalantů oboru IT (Informační technologie) studujících na praţském pracovišti. Opět máme k dispozici hodnocení za tři různé akademické roky. Vizualizace pomocí pruhového grafu jasně ukazuje postupné zhoršení výsledků hodnocení. Spojnicový graf na obrázku 20 zobrazuje průměry dílčích hodnocení bakalářských prací studentů, kteří absolvovali různé studijní obory, všechny však v akademickém roce 2010/2011. Do dílčích hodnocení je zařazeno hodnocení obsahu, hodnocení vedoucího a oponenta a získaná známka z obhajoby. Poslední sloupec vyjadřuje průměr dílčích hodnocení na za uvedené středisko a obor.
Obrázek 20 Příklad zobrazení zpracovaných dat ve spojnicovém grafu, zdroj vlastní úprava
4.6 Technické řešení V této části je popsán způsob technického řešení výše navrţeného zpracování dat pomocí systému Business Intelligence. Data z IS BIVŠ, která budou předmětem zpracování, by bylo vhodné umístit na sdílený disk do csv souboru, který bude následně načten do datového skladu. 58
Proces ETL není v našem případě zajištěn ţádným samostatným ETL nástrojem. Načtení dat z poskytnutého csv souboru a přenos do první vrstvy datového skladu je realizováno pomocí programu SQL*Plus15. Databáze datového skladu je umístěna na platformě Oracle Express Edition. Tuto databázi poskytuje výrobce zdarma. Jedná se o omezené moţnosti standardní databáze Oracle 11g, ale vzhledem k malému mnoţství zpracovaných dat a k nenáročnosti operací je tato databáze postačující. Na této databázi jsou vytvořeny všechny tři vrstvy datového skladu. Transformace a kompletní tok dat od dočasného úloţiště, přes úloţiště datového skladu aţ po datové trhy jsou realizovány pomocí databázových SQL skriptů, které na sebe logicky navazují. Příklad zdrojového kódu je v příloze č. 2 této bakalářské práce. Pro zobrazení dat byl zvolen tabulkový procesor MS Excel. Pomocí databázového skriptu a programu SQL*Plus je zajištěn automatizovaný export do textového souboru s příponou csv. Soubory tohoto formátu lze snadno otevřít v MS Excel a k zobrazeným datům připojit vhodný graf. Chybová hlášení o chybách ve zdrojových datech jsou obdobně exportována do textového souboru s příponou csv pro další zpracování v MS Excel.
15
SQL*Plus je utilitka pro přístup k databázi Oracle a slouţí ke zprostředkování komunikace koncového
uţivatele s databázovým serverem.
59
Závěr Cílem této bakalářské práce bylo ukázat moţnosti Business Intelligence pro hodnocení náleţitostí a obsahu závěrečných bakalářských a diplomových prací na BIVŠ. Tento přístup vychází z objektivních dat, která jsou uchována ve studijním informačním systému BIVŠ za všechny studenty BIVŠ a studijní obory v časových řadách za posledních několik let. Jako srovnávací hledisko pro hodnocení kvality závěrečných prací byla zvolena „Metodika pro analýzu formálních náleţitostí a obsahu závěrečných BP a DP oborů IT a ITaM“, která je přílohou této bakalářské práce včetně ilustračního příkladu. Podrobnější analýzy, příp. tvorba prognóz by si vyţádaly sofistikovanější matematicko-statistické modely, které by však byly nad rámec zadání této bakalářské práce. Nicméně datová základna pro tato modelování by byla plně vyuţitelná. Mnou navrţený model je také pouţitelný i pro jiné vysoké školy a mohl by být vyuţit i pro komparativní šetření kvality výuky na jiných vysokých školách v rámci MŠMT.
60
Seznam obrázků: Obrázek 1 Datový tok v systému Business Intelligence, zdroj vlastní úprava ........................................ 9 Obrázek 2 Architektura systému Business Intelligence, zdroj vlastní úprava .......................................11 Obrázek 3 Hierarchický model databáze, zdroj [7] ................................................................................12 Obrázek 4 Multidimenzionální model, zdroj [8] ....................................................................................18 Obrázek 5 Porovnání procesu ETL a ELT v grafické podobě, zdroj [6] ...............................................20 Obrázek 6 Relační model databáze, zdroj vlastní úprava ......................................................................36 Obrázek 7 Multidimenzionální model databáze typu STAR, zdroj vlastní úprava ................................38 Obrázek 8 Multidimenzionální model databáze typu SNOWFLAKE, zdroj vlastní úprava .................39 Obrázek 9 Architektura systému BI pro sledování analýz hodnocení bakalářských a diplomových prací, zdroj vlastní úprava ......................................................................................................................42 Obrázek 10 Vrstva dočasného úloţiště dat, zdroj vlastní úprava ...........................................................44 Obrázek 11 Vrstva úloţiště datového skladu, zdroj vlastní úprava........................................................46 Obrázek 12 Model s databázovým pohledem na tabulku hodnocení bakalářských prací, zdroj vlastní úprava .....................................................................................................................................................49 Obrázek 13 Hvězdicové schéma tabulky průměru hodnocení bakalářských prací s vazbou na jednotlivé dimenze, zdroj vlastní úprava.................................................................................................................50 Obrázek 14 Databázový pohled view průměr bakalářských prací nad tabulkou průměrů s dimenzí typu agregace, zdroj vlastní úprava ................................................................................................................51 Obrázek 15 Různé alternativy databázových pohledů na tabulku průměr bakalářských prací, zdroj vlastní úprava .........................................................................................................................................53 Obrázek 16 Hvězdicové schéma historické tabulky průměru hodnocení bakalářských prací s vazbou na jednotlivé dimenze, zdroj vlastní úprava ................................................................................................54 Obrázek 17 Databázové pohledy na historickou tabulku průměrů bakalářských prací, zdroj vlastní úprava .....................................................................................................................................................55 Obrázek 18 Příklad jednoduchého grafického zobrazení, zdroj vlastní úprava .....................................57 Obrázek 19 Příklad zobrazení dat na pruhovém grafu, zdroj vlastní úprava .........................................57 Obrázek 20 Příklad zobrazení zpracovaných dat ve spojnicovém grafu, zdroj vlastní úprava ..............58
61
Seznam tabulek Tabulka 1 Příklad databázové tabulky Osoba, zdroj vlastní úprava ......................................................15 Tabulka 2 Databázová tabulka Osoba v 1NF a databázová tabulka Telefon v 1NF, zdroj vlastní úprava ................................................................................................................................................................16 Tabulka 3 Příklad databázové tabulky Sklad, zdroj vlastní úprava ........................................................16 Tabulka 4 Databázová tabulka Sklad ve 2NF a databázová tabulka Výrobce ve 2NF, zdroj vlastní úprava .....................................................................................................................................................16 Tabulka 5 Příklad databázové tabulky Zaměstnanec, zdroj vlastní úprava............................................17 Tabulka 6 Databázová tabulka Zaměstnanec ve 3NF, zdroj vlastní úprava...........................................17 Tabulka 7 Databázová tabulka Adresa ve 3NF a databázová tabulka Funkce ve 3NF, zdroj vlastní úprava .....................................................................................................................................................18 Tabulka 8 Příklad načítacího textového souboru v csv formátu, zdroj vlastní úprava ...........................43 Tabulka 9 Příklad chybových hlášení v tabulce chyb, zdroj vlastní úprava ...........................................45 Tabulka 10 Zdrojová data z ISu, zdroj vlastní úprava ...........................................................................48 Tabulka 11 Transformovaná data v úloţišti datového skladu, zdroj vlastní úprava ..............................48 Tabulka 12 Seznam studijních skupin a jejich příslušný stát, středisko, obor, forma a druh, zdroj vlastní úprava .........................................................................................................................................48 Tabulka 13 Příklad obsahu dimenze typ agregace, zdroj vlastní úprava ...............................................50 Tabulka 14 Příklad agregace dat podle akademického roku a studijní skupiny, zdroj vlastní úprava ...52 Tabulka 15 Detaily záznamů hodnocení průměrovaných v tabulce 14, zdroj vlastní úprava ................53 Tabulka 16 Ukázka nové sady zdrojových dat s přidáním jednoho nového záznamu hodnocení, zdroj vlastní úprava .........................................................................................................................................55 Tabulka 17 Staré hodnoty průměrů dat za akademický rok 2011/2012 a studijní skupinu PRA_ITK, zdroj vlastní úprava ................................................................................................................................56 Tabulka 18 Příklad změny průměrů při přidání jednoho záznamu hodnocení, zdroj vlastní úprava .....56 Tabulka 19 Starý a nový záznam průměrných hodnot z historické tabulky průměrů bakalářských prací, zdroj vlastní úprava ................................................................................................................................56
62
Seznam použité literatury: Publikace [1]
CONOLLY, T., C. BEGG a R. HOLOWCZAK. Mistrovství – databáze: Profesionální průvodce tvorbou efektivních databází. Brno: Computer Press, 2009. ISBN 978-80251-2328-7.
[2]
LABERGE, Robert. Datové sklady – Agilní metody a business intelligence. Brno: Computer Press, 2012. ISBN 978-80-251-3729-1.
[3]
LACKO, Luboslav. Databáze: datové sklady, OLAP a dolování dat s příklady v Microsoft SQL Serveru a Oracle. Brno: Computer Press, 2003. ISBN 80-7226-9690.
[4]
NOVOTNÝ, O., J. POUR a D. SLÁNSKÝ. Business Intelligence: Jak využít bohatství ve vašich datech. Praha: Grada Publishing, 2005. ISBN 80-247-1094-3.
[5]
VOŘÍŠEK, Jiří. Strategické řízení informačního systému a systémová integrace. Management Press, Praha 2003. ISBN 80-85943-40-9.
Elektronické zdroje [6]
Oracle Czech BI/DW Blog. Rozdieľ medzi ETL a ELT [online]. 2007-2012 [cit. 201302-18]. Dostupné z: http://bidwcz.blogspot.cz/2007/03/rozdie-medzi-etl-elt.html
[7]
Databáze. Databázové modely [online]. 2010 [cit. 2013-02-18]. Dostupné z: http://www.databaze.chytrak.cz/modely.htm
[8]
Finanční zpráva IS CEDR III. Definice termínů používaných v IS CEDR [online]. 2013 [cit. 2013-02-18]. Dostupné z: http://cedr.mfcr.cz/Cedr3INetHelp/CommonPages/Terminologie0001.aspx
63
Příloha č. 1
Metodika pro analýzu formálních náleţitostí a obsahu závěrečných BP a DP oborů IT a ITaM Metodika pro analýzu formálních náležitostí a obsahu závěrečných BP a DP oborů IT a ITaM. Zpracoval: Doc. Ing. Bohumil Miniberger, CSc. tel.: +420 251 114 538, e-mail:
[email protected]
Úvod: Sdělením rektorky Prof.Ing. Bojky Hamerníkové, CSc., ze dne “Thursday, October 27, 2011 10:18 AM” e-mailem: Kolegium širší; Miniberger Bohumil; Pašek Jan; Marková Jana, byl uložen tento úkol: V návaznosti na záměry Dlouhodobého záměru školy na tento a příští rok a v souvislosti se snahou zvyšovat kvalitu výuky studia a absolventů ukládám: - panu prorektoru doc. Bendovi dokončit analýzu úspěšnosti státních závěrečných zkoušek v AR 2010/11 a předložit tuto zprávu nejpozději do 15. 11. 2011 - jmenuji komisi k analýze relevantnosti a aktuálnosti témat bakalářských závěrečných prací a diplomových prací vyhlášených v akademickém roce 2010/11 a připravovaných na rok 2011/12 v ČR v následující sestavě: doc. Ing. Jana Marková, CSc. - předsedkyně komise, doc. Ing. Jan Pašek, Ph.D. – člen, prof. JUDr. Richard Pomahač, CSc. – člen. - Analýza by měla proběhnout do 15. 12. 2011 s odpovídajícími závěry a návrhy. - jmenuji komisi k analýze formálních náležitostí a obsahu závěrečných a bakalářských a diplomových prací obhájených v AR 2010/11 na všech oborech a to v sestavě: garanti všech oborů - předsedové komisí, dva členové z oborových kateder dle námětu garantů oborů a s odpovídajícími zkušenostmi.
Návrh metody pro zajištění analýzy formálních náležitostí a obsahu závěrečných a bakalářských a diplomových prací obhájených v AR 2010/11: Část metodická: a) Metodickými pokyny pro vypracování bakalářské, resp. diplomové práce na Bankovním institutu vysoké škole v Praze, byla jasně a jednoznačně definována jak obsahová, tak formální stránka BP resp. DP, včetně požadovaných příloh. b) Tyto metodické pokyny jsou dostupné jak všem bakalantům a diplomantům, tak samozřejmě jejich vedoucím a i oponentům z řady interních a externích pracovníků BIVŠ prostřednictvím Informačního systému BIVŠ, dostupné on-line po autorizaci přístupu, prostřednictvím https://is.bivs.cz/auth/do/6110/general/SO-JK/BP_DP/Metod_pokyny.doc
Část věcná Analýza formálních náležitostí a obsahu závěrečných a bakalářských a diplomových prací obhájených v AR 2010/11 pro studijní obor bakalářského studia IT a pro obor magisterského studia ITaM, byla provedena komisí schválenou rektorkou dne 31.10. 2011 prof. Hamerníkovou v tomto složení: Doc. Ing. Bohumil Miniberger, CSc.,- předseda. Ing. Vladimír Beneš – člen, ved. K101. Ing. Lubomír Jankových, CSc. – člen.
64
Vlastní analýza byla provedena tak, že nejprve byly porovnány názvy BP resp. a DP zda se významově shodují s obsahy sylabů studijního oboru, ve kterém bakalant resp. diplomant předložil BP resp. DP. Další posouzení bylo, zda se název BP resp. DP neopakoval v některém minulém sledovaném období. Při čemž se vycházelo z podobnosti témat, jak jsou uvedena v IS BIVŠ. Výsledek tohoto zmapování je pak vyjádřen v následujících tabulkách a to zvlášť pro BP a pro DP*:
Vyhodnocení BP Název BP
Studijní skupina
Učo
Jméno bakalanta
Učo
Jméno diplomanta
Jméno vedoucího
Anotace převzatá z BP
Relevantnost ke studovanému oboru
Známka z obhajoby
Hodnotitel
Relevantnost ke studovanému oboru
Známka z obhajoby
Hodnotitel
Vyhodnocení DP Název DP
Studijní Skupina
Jméno vedoucího
Anotace převzatá z BP
Tyto tabulky budou vytvořeny v MS Excelu Legenda: Zkratky hodnotitelů: B. Miniberger – „BM“ V. Beneš Petrovický- „VBP“ Lubomír Jankových - „LJ“ Znaky pro hodnocení relevantnosti ke studovanému oboru, založené na pohledu hodnotitele, protože neexistuje exaktní metrika: „V“ ( velká) „S“ (střední) M“ (malá) „I“ Irelevantní – která by se v komentáři měla dodatečně vysvětlit, proč vedoucí práce nebo konzultant přesto doporučil práci k obhajobě. Toto hodnocení by se ale fakticky nemělo objevovat u prací, které byly obhájeny.
Celkové vyhodnocení a závěr Poznámka: Uvedené seznamy obsahují pouze obhájené BP a DP. Seznam neobhájených BP a DP, lze obdobně vytvořit stejnou metodikou, na základě dokumentace, která je součástí protokolů ze „ Státních závěrečných zkoušek“, které jsou archivovány v písemné podobě na studijním oddělení BIVŠ.
V Praze dne: 8. 11. 2011 Doc. Ing. Bohumil Miniberger, CSc.,- předseda ……………………………………………………………………………… Ing. Vladimír Beneš – člen, ved. K101……………………………………………………………………………………………. Ing. Lubomír Jankových, CSc. – člen……………………………………………………………………………………….………
65
Příloha č. 2
Vzory zdrojových kódů pro plnění některých tabulek v datovém skladu
66
67
68
69
