Systém pro extrakci, transformaci a nahrávání dat pro datový sklad

České vysoké učení technické v Praze Fakulta elektrotechnická

Diplomová práce

Systém pro extrakci, transformaci a nahrávání dat pro datový sklad Lukáš Vítek

Vedoucí práce: Ing. Ivan Halaška

Magisterský studijní program: Elektrotechnika a informatika, dobíhající navazující

Obor: Výpočetní technika Květen 2008

Poděkování Rád bych poděkoval vedoucímu mé diplomové práce Ing. Ivanu Halaškovi za vstřícné vedení a hodnotné připomínky.

Prohlášení Prohlašuji, že jsem svou diplomovou práci vypracoval samostatně a použil jsem pouze podklady uvedené v přiloženém seznamu. Nemám závažný důvod proti užití tohoto školního díla ve smyslu §60 Zákona č. 121/2000 Sb., o právu autorském, o právech souvisejících s právem autorským a o změně některých zákonů (autorský zákon).

V Praze dne 18. 5. 2008 ……………………………………………………………….

Abstrakt Práce prezentuje návrh a implementaci systému pro extrakci, transformaci a nahrávání dat do datového skladu. V prvních kapitolách se práce zabývá teorií a principy datových skladů, upozorňuje na implementační úskalí budování datových skladů a nabízí jejich řešení. V druhé polovině práce detailně popisuje vlastní implementaci ETL enginu a přináší ukázku vzorového zpracování.

Abstract This work presents a design and an implementation of a system for extraction, transformation and loading data into a data warehouse. In the first chapters, this work deals with a theory and principles of a data warehouse with an advice of implementation difficulties and it offers solutions. In the second half of this work, there is a detail description of the writer’s implementation of ETL engine and examples of sample processes.

1.

Úvod ................................................................................................................... 7 1.1. Účel datových skladů ................................................................................... 7 1.2. Definice datových skladů ............................................................................. 7 1.3. OLAP / MOLAP/ ROLAP - Datové kostky .................................................. 10

2.

Objekty datových skladů .................................................................................. 12 2.1. Dimenze ..................................................................................................... 13 2.2. Faktové tabulky .......................................................................................... 15 2.3. Stage a persistent stage objekty ................................................................ 16 2.4. Interface tabulky ......................................................................................... 17 2.5. Externí tabulky ........................................................................................... 17 2.6. Integritní omezení – náhrada NULL hodnot ............................................... 17 2.7. Materializované pohledy ............................................................................ 18

3.

Příklad návrhu části datového skladu............................................................... 19

4.

Architektura datových skladů ........................................................................... 20

5.

ETL – Extraction, Transformation, Loading ...................................................... 23 5.1. Definice ETL a důvod implementace ETL enginu ...................................... 23 5.2. Extraction ................................................................................................... 24 5.2.1. Fyzické schéma extrakčního modelu ................................................... 25 5.3. Transformation and Loading ...................................................................... 26 5.4. Parametrizace spuštění ............................................................................. 28

6.

Implementace DW – business logika ............................................................... 29 6.1. Návrh a popis datových struktur ................................................................. 30 6.2. Popis a implementace výkonných funkcí ................................................... 32

7.

Implementace DW – prezentační vrstva .......................................................... 40 7.1. Implementace DW – prezentace průběhu zpracování ............................... 40 7.2. Implementace DW – prezentace modelování zpracování .......................... 40

8.

Vzorové zpracování ......................................................................................... 42 8.1. Dimenze ..................................................................................................... 43 8.2. Fakta .......................................................................................................... 44

9.

Závěr ................................................................................................................ 46

10.

Seznam použitých zkratek............................................................................. 47

11.

Seznam použité literatury .............................................................................. 47

12.

Seznam obrázků ........................................................................................... 48

1. Úvod Cílem této diplomové práce je navrhnout a realizovat systém pro extrakci, transformaci a uložení dat - ETL framework pro datové sklady. První část této práce se zabývá základy problematiky datových skladů. V jednotlivých kapitolách jsou popsány logický návrh databáze datového skladu, fyzický návrh a prostředky databázových systémů využívaných v datových skladech. Nejdůležitějším bodem této práce je seznámení s principy a součástmi ETL enginu, na které navazuje vlastní implementace ETL – SQL workflow. V závěrečné části práce jsou uvedena vzorová zpracování pro dimenze a faktové tabulky.

1.1.

Účel datových skladů

Datové sklady (DW) přináší prostředek nezávislé analýzy dat, tzn. umožňují analyzovat data přes různorodé primární systémy přenášené do datového skladu. Je třeba si uvědomit, že datový sklad je prostředkem pro analýzu dat, nikoliv nástrojem analýzy, z čehož vyplývá, že datový sklad pouze poskytuje (připravuje) data pro následnou reprezentaci. Mezi nástroje reprezentace informací z datových skladů patří různé manažerské informační systémy (MIS), případně CRM systémy (Customer Relationship Management).

1.2.

Definice datových skladů

Datové sklady jsou prostředkem pro analýzu dat velkého rozsahu v závislosti na dimenzi času. Data jsou v procesu ukládání do datového skladu upravena pro potřeby další analýzy. Datový sklad často slučuje (konsoliduje) data z více transakčních systémů, které mohou mít různorodou vnitřní datovou strukturu. Strana 7 / 48

Data v datovém skladu obsahují svou historii, která pochází ze změn v produkčních transakčních systémech. Data jsou do datových skladů ukládána vstupním zpracování. Datové sklady lze charakterizovat následujícími vlastnostmi [2]:

•

Subject Oriented

•

Integrated

•

Nonvolatile

•

Time Variant

Subject Oriented – orientace na obsah Datové sklady jsou stavěny se zaměřením na obsah dat. Integrated – konzistence dat

Datový sklad musí zaručit konzistenci dat. Data jsou často získávána z více systémů, které se mohou lišit datovou strukturou. Vstupní zpracování (ETL) musí tedy zajistit konsolidaci dat, například odstranění kolizí jmen, používání různých jednotek a klíčů. Nonvolatile – stálost dat

Datový sklad (přístup do skladu) musí zaručit stálost – neměnnost dat ve skladu. Po vložení dat do datového skladu by tato data již neměla být měněna jinak než formou definovaného zpracování. Ruční zásah do DW by měl být tedy pouze velmi výjimečnou operací. Time Variant – variace času

Analytické procesy datového skladu vyžadují kladení dotazů v závislosti na čase. V tom spočívá jeden z nejdůležitějších rozdílů od klasické transakční databáze, kde jsou používána povětšinou pouze aktuální data. Strana 8 / 48

Další rozdíly OLTP vs DW Datové schéma Jedním z hlavních rozdílů mezi klasickými transakčními databázemi (OLTP) a datovými sklady je v tom, že schéma datových skladů nebývá v 3NF, která je základem klasických databází. 3NF je důvodně porušována, databáze obsahují duplikovaná a agregovaná data. U relačních databází je snaha nemít ve schématu žádné duplicitní informace a každou potenciální agregaci dat získávat až v době vyhodnocení dotazu. Naproti tomu datové sklady použití tabulek s obsahem agregací nijak nevylučují, ba dokonce naopak. Nad DW se často vytváří tzv. agregační tabulky, které obsahují souhrnné informace za dané období, např. koncový stav na účtech klientů ke konci měsíce, místo dohledávání tohoto údaje ze všech transakcí. Důvodem je vysoká náročnost agregačních výpočtů vzhledem k velkému objemu dat. Z důvodu rychlosti se proto tyto výpočty připravují dopředu. V rámci návrhu ETL je však nutné pamatovat na udržování konzistence agregačních a zdrojových tabulek. Obsah dotazů Datové sklady musí být navrženy pro libovolné dotazy nad velkým množstvím dat, často v závislosti na čase. OLTP jsou oproti tomu většinou navrženy a optimalizovány pro dotazy předem definované. Modifikace dat Data v DW jsou modifikována pouze na základě pravidelných ETL procesů (denních, měsíčních, …). Koncový uživatelé datového skladu data nikdy nemění. Naproti tomu v OLTP jsou modifikace dat běžným úkonem i pro koncové uživatele. Historická data Zachování historických dat je základem DW. V OLTP jsou často zachovávána pouze aktuální data a změna dat vyvolá přepsání (změnu) hodnot místo vytvoření historie.

Strana 9 / 48

1.3.

OLAP / MOLAP/ ROLAP - Datové kostky

Technologie datových skladů se rozlišují podle způsobu fyzického uložení dat. Nejoblíbenějším a nejlépe odpovídajícím způsobem reprezentace dat pro datové sklady jsou datové kostky, kdy jsou analyzovaná data mapována na multidimenzionální kostku rozdělenou na jednotlivé kostičky. Tyto kostičky datové kostky prezentují hodnoty sledované veličiny. Dimenze (osy) OLAP kostky jsou kategorie vztahů analyzovaných veličin. Na našem příkladu je kostka prodeje pečiva ve dvou českých městech za tři měsíce. Dimenzemi kostky jsou tedy měsíce, města a druhy pečiva.

Obrázek 1 - Datová kostka

Rozšíření této struktury o další města, měsíce či druhy pečiva se realizuje jednoduše přidáním položek dimenze. Přidání dalších sledovaných vztahů (např. rozdělení prodeje podle velikosti prodejny) se realizuje přidáním dalších dimenzí kostky. Počet dimenzí datové kostky je teoreticky libovolný, v praxi omezený pouze technologií DW. Grafická prezentace formou kostky při více jak třech dimenzích již ztrácí svůj vypovídací potenciál.

Strana 10 / 48

Jsou-li data v datovém úložišti uložena přímo v podobě datové kostky, mluvíme o technologii MOLAP (Multidimensional Online Analytical Processing). V případě, že datovou kostku rozložíme do relační databáze do dimenzionálních a faktových tabulek hovoříme o technologii ROLAP (Relační OLAP). Dále se budu věnovat právě této technologii.

Strana 11 / 48

2. Objekty datových skladů Mezi objekty datového skladu počítáme všechny databázové objekty, jako jsou tabulky, pohledy, indexy a další. Strukturu návrhu nazýváme datové schéma. Schéma používané v datových skladech vychází z požadavků na možnost kladení libovolných dotazů nad skladem a optimální ukládání velkého množství dat.

Obrázek 2 - Hvězdicové schéma (star schema)

V datových skladech se většinou používá (multi-)dimensionální hvězdicový model (star schema, viz Obrázek 2) nebo model sněhové vločky (snowflake schema, viz Obrázek 3).

Obrázek 3 - Schéma sněhová vločka (snowflake schema)

Strana 12 / 48

Tabulky v datovém skladu v obou schématech jsou rozděleny do dvou základních skupin. Tyto dva nejdůležitější typy jsou označovány jako dimenze a faktové tabulky. Uprostřed obou zmiňovaných schémat je faktová tabulka, která má vazbu na libovolné množství dimenzí. Vazba mezi dvěma dimenzemi je tedy vždy až na výjimky realizována pouze přes faktovou tabulku. Schéma sněhové vločky navíc umožňuje tvořit vazby mezi dimenzemi, které se používají pro agregaci dat v dimenzích, např. agregace měst do krajů, zemí, kontinentů.

2.1.

Dimenze

Data v dimenzích mají podobu číselníků nebo seznamů. Typickým příkladem dimenze jsou např. čas, osoba, produkt, atd. Dimenze nezobrazují vztahy mezi daty (kromě hierarchie u snowflake schema), ale pouze obsah dat. Mapování dimenze na OLAP kostku je jednoduché, termín dimenze se v obou modelech používá ve stejném významu. Historie nad dimenzemi Dimenze může obsahovat historii. Historie nad dimenzí zachycuje změnu obsahu dat v závislosti na čase. Typickou změnou je například změna adresy, rodinného stavu nebo jistiny u osoby. Historické i nové záznamy jsou buď ponechány ve stejné tabulce, nebo jsou historické záznamy přesunuty do vlastní tabulky. Přesunutím historických záznamů lze zmenšit počet záznamů v tabulce a tím zrychlit práci nad aktuálními daty. V rámci ETL je nutné definovat, nad kterými sloupci se má vést historie a nad kterými ne. Jinak řečeno, je nutné definovat, při změně jakých sloupců se má vytvořit nový záznam a původní prohlásit za historický a při změně jakých sloupců má pouze nastat aktualizace záznamu. Tato definice je nutná, aby se množství dat v dimenzi nezvětšovalo při každé drobné změně záznamu. Historie se v principu musí držet nad informacemi, které se objevují na písemných dokladech nebo slouží k identifikaci záznamu v tabulce, např. jméno, příjmení, adresa, jméno produktu atd. Strana 13 / 48

Na následujícím obrázku si vysvětlíme, jakým způsobem lze elegantně řešit historii nad dimenzí. Dimenze kromě hodnotových sloupců (name, surname,…) obsahuje i identifikátory záznamu (person_key a person_id) a identifikátory času historie (valid_from, valid_to, deleted_flag a deleted_date). Postupně projdeme tyto rozšiřující sloupce a jejich význam.

class datov y model - priklady DIM_PERSON «column» person_key: number person_id: number person_name: varchar(50) person_address: varchar(50) person_maritial_status: char(1) valid_from: timestamp valid_to: timestamp deleted_date: timestamp deleted_flag: char(1)

Obrázek 4 - Příklad návrhu dimenze

Person_key je unikátní identifikátor řádku v rámci celé dimenze. Jedná se o klasický primární klíč (PK), generovaný ze sekvence při vložení nového záznamu. Person_id je naproti tomu unikátní identifikátor (ID) záznamu v dimenzi. Hodnota pro ID sloupec se zásadně používá unifikovaný identifikátor ze zdrojového systému. Vezměme například osobu Jan Novák. Při prvním vložení záznamu do tabulky získá záznam unikátní person_key a person_id. Pan Novák se přestěhuje a tím dojde ke změně sloupce address. Místo aktualizace tohoto sloupce, kterou bychom nejspíše realizovali v transakční databázi, je vložen v datovém skladu nový řádek s unikátním person_key. Jedná se stále o stejnou osobu, a proto mají oba záznamy jednotný person_id. Máme tedy pro jednu osobu v dimenzi nw_person dva záznamy, každý s unikátním PK. Abychom rozlišili, který záznam je v určité době platný, použijeme sloupce valid_from a valid_to. Ty určují dobu, po kterou je určitý záznam v tabulce platný. Poslední dva sloupce si představíme ve spojení s mazáním dat.

Strana 14 / 48

Mazání dat Z dimenzí nejsou nikdy data odmazávána. Neplatná data jsou pouze označena za nevalidní, což zaručuje konzistenci databáze a možnost tvorby dotazů nad historickými daty. Pro označení smazaných dat použijeme sloupce deleted_date a deleted_flag. Místo fyzického smazání dat jsou data pouze označena za smazaná (deleted_flag = true) a je nastaven datum smazání dat (deleted_date).

2.2.

Faktové tabulky

Faktové tabulky určují vztah mezi dimenzemi. Kromě cizích klíčů na primární klíče dimenzí obsahuje faktová tabulka často i další informace. Typickým příkladem faktové tabulky jsou objednávky klientů. Kromě cizích klíčů na dimenze osoba, čas a produkt, bude taková faktová tabulka obsahovat i informace o počtu kusů, jednotkové ceně a celkové ceně. Na tomto příkladě vidíme důvodně porušenou 3NF, protože celková cena je pouze vynásobení počtu kusů a jednotkové ceny. Umožníme tím ale efektivní dotazy jako např. na průměrnou nákupní cenu výrobku, tak i např. na celkový objem objednávek. Faktové tabulky jsou v podobě OLAP kostky reprezentovány kostičkami celé kostky.

class datov y model - priklady FACT_DAILY_ORDERS «column» person_key: number product_key: number time_key: bigint product_count: number product_prize: decimal(10,2) product_total_prize: decimal(10,2)

Obrázek 5 - Příklad návrhu faktové tabulky

Strana 15 / 48

Historie nad faktovými tabulkami Faktová tabulka už ze své podstaty obsahuje historii. V zásadě každá faktová tabulka obsahuje cizí klíč na dimenzi času a také znázorňuje vztah mezi dimenzemi k určitému datu. Mazání dat Vzhledem k tomu, že datový sklad může obsahovat jen limitované množství dat, hledáme řešení jak toto množství omezit. Proto definujeme, jestli potřebujeme kompletní historii faktových tabulek, nebo jestli se u starých záznamů spokojíme s agregací za delší než denní období. Například můžeme definovat, že chceme uchovávat data za dva měsíce a ze starších dat pouze poslední data v měsíci (vhodné například pro zůstatky na účtech klientů).

2.3.

Stage a persistent stage objekty

Pro správnou a efektivní činnost zpracování datového skladu je nutné použít dočasných, tzv. stage objektů. Tyto objekty slouží pouze jako mezikroky při přípravě dat pro uložení do finálního umístění ve skladu. Z důvodu velkého množství zpracovávaných dat je totiž efektivnější připravit si některá potřebná data do dočasných objektů (tabulek) a ty potom dále použít. Rozsah platnosti stage objektů je vždy pouze na jedno zpracování. O persistent stage objektech mluvíme v případě, že je nutné stage objekty ponechávat pro další zpracování i v dalších dnech. Příkladem může být situace, kdy zdrojová data dostáváme ve formě inkrementálních extraktů, ale zpracování potřebuje plné snímky. V tomto případě se vytváří persistent stage objekty, ve kterých jsou uměle vytvářeny plné snímky z inkrementů. Data ve stage objektech nejsou nikdy přístupná pro koncové uživatele.

Strana 16 / 48

2.4.

Interface tabulky

Interface tabulka je vstupně výstupním prostředkem datových skladů. Mohli bychom říci, že faktová tabulka je interfacem pro styk skladu s okolním světem. Interface tabulka je svojí podstatou klasická tabulka transakční databáze, svojí strukturou se shoduje se zdrojovou tabulkou, pouze je rozšířena o pomocné informace (systémové sloupce). Ty mohou obsahovat např. informaci, za který datum jsou data pořízena, informaci o stavu „otisku“ zdrojových dat (data nová, nahraná do DW, chyba v datech). S interface tabulkami se ještě setkáme v kapitole 4 jako součástí staging area.

2.5.

Externí tabulky

Externí tabulky jsou dalším vstupním prostředkem datového skladu. Na rozdíl od interface tabulky, která byla plněna ze zdrojového systému, jsou externí tabulky plněny ručně. Externí tabulky mohou sloužit buď jako zdroje dat statických číselníků, jako např. číselník měn či zemí, nebo můžou data v externí tabulce sloužit k parametrizaci ETL (ovlivnění funkce).

2.6.

Integritní omezení – náhrada NULL hodnot

Integritní omezení (IO) jsou běžnými prostředky všech databází. V datových skladech navíc od běžných IO často striktně vyžadujeme omezení na NOT NULL nad všemi sloupci tabulek. NULL hodnoty je nutné ve vstupních datech nahradit jinými, předem definovanými zástupnými hodnotami. Například

pro

datumové

záznamy

lze

použít

rok

dostatečně

daleko

v budoucnosti, pro textové záznamy hodnoty NA, XNA,….

Strana 17 / 48

V případě hodnot cizích klíčů faktových tabulek však nelze z důvodu integritního omezení vložit náhradovou hodnotu, ale je třeba vložit hodnotu sloupce z cílové tabulky. Pro tento účel jsou do dimenzí přidávány umělé řádky, na které se ukazují cizí klíče z faktových tabulek. Tento umělý řádek obsahuje předdefinované hodnoty, označující neznámé, či nedefinované hodnoty, které jsou vhodně odlišeny od ostatních dat.

2.7.

Materializované pohledy

Materializované pohledy (materialized views) patří mezi prostředky moderních databází. Na rozdíl od klasických pohledů (view), kde se v podstatě jedná pouze o pojmenování SQL dotazu, mají materializované pohledy i fyzickou implementaci. Tím je možné zrychlit časově náročné dotazy, které jsou předem spočteny. Z pohledu fyzické implementace je materializovaný pohled podobný klasické tabulce a svým chováním indexu. Její použití je k uživatelům transparentní. Použití materializovaných pohledů má dobrý vliv na výkonnost datového skladu a je často používán u tzv. pseudo online datových skladů, kde dochází k aktualizaci dat (např. agregací) v několikaminutových intervalech.

Strana 18 / 48

3. Příklad návrhu části datového skladu V předchozích kapitolách byly představeny objekty datových skladů a jejich základní schémata. V této kapitole si proto ukážeme praktický příklad návrhu jedné části datového skladu.

Obrázek 6 - Návrh části datového skladu

Jednoduchý příklad prezentuje 4 dimenze (čas, produkt, zákazník a prodejní místo) a jednu faktovou tabulku, která reprezentuje vztah mezi těmito dimenzemi Na našem příkladu můžeme prezentovat neomezenou škálu dotazů nad touto databází. Uvedeme si několik dotazů, které by bylo složité na tradiční transakční databázi v 3NF realizovat: •

Kolik určitého zboží bylo prodáno v určitém období?

•

Kolik a jaké zboží koupil zákazník?

•

Jaká je průměrná prodejní cena určitého zboží v určitém období?

•

Jak se mění počet prodeje v závislosti na čase?

•

Které prodejní místo má nejvíce tržeb?

Jak je vidět, mohli bychom vymyslet mnoho příkladů takovýchto dotazů a těžko bychom hledali dotaz, který nelze nad těmito daty položit.

Strana 19 / 48

4. Architektura datových skladů V této kapitole se seznámíme s architekturou datových skladů, definujeme si zdrojové systémy a ostatní potenciální zdroje dat. Představíme si součásti datového skladu a ukážeme si dva možné přístupy uživatelů k datům. Nejdříve si popíšeme funkční celky na základní architektuře. Základní architektura Základní architektura datového skladu je na obrázku 7. Tato architektura je čistě teoretická a v praxi se nepoužívá pro množství nedostatků, které řeší další architektury. Uvádím ji však proto, že je základem ostatních architektur.

Obrázek 7 - Základní architektura DW

Vstupem datových skladů (data sources) jsou produkční systémy nebo jiné zdroje dat (soubory v tabulkových procesorech, XML soubory, data v externích tabulkách, …). Na druhé straně jsou uživatelé a systémy, které čerpají z dat datového skladu.

Strana 20 / 48

Součástí datového skladu jsou Raw data (klasické tabulky - dimenze, fakta, atd.), metadata (popis objektů) a agregační objekty (tabulky nebo pohledy obsahující agregaci dat). Architektura s použitím staging area Základní architektura často nepostačuje požadavkům a je použita architektura s tzv. staging areou. Staging area je přechodné úložiště pro data, která přichází do datového skladu. Využití takového úložiště přináší několik výhod: •

data do tohoto úložiště jsou vkládána ze vstupních systémů. To zaručuje aktuálnost těchto dat.

•

data do datového skladu jsou vkládána offline. Například při výpadku načítání dat do DW a jeho pozdějšího načtení nemůže dojít k nahrání nesprávných dat. Data totiž mohou být ve vstupním systému v době zpracování dat (nahrání do DW) již změněna.

Obrázek 8 - Architektura za použití staging area

Za nejjednodušší vstupní stage areu můžeme vzít interface tabulky. Pro lepší oddělení datových skladů (a další systémů) od zdrojových systémů se staging area implementuje nezávislá na datovém skladě, nejlépe na samostatném hardware se Strana 21 / 48

samostatnou databází. Takovéto „překladiště“ dat lze poté použít pro poskytování zdrojových dat i jiným systémům než datovému skladu. Navíc přináší výhodu lepšího oddělení jednotlivých systémů a tím lepší odolnosti proti chybám způsobených výpadkem. Architektura s použitím staging area a data marts Posledním vylepšením architektury datového skladu je přidání tzv. data martů. Data mart je prostředkem jak přizpůsobit architekturu datového skladu cílovým skupinám uživatelů. Tabulky data martu v datovém skladu jsou postaveny různým způsobem pro různé cílové skupiny uživatelů.

Obrázek 9 - Architektura s použitím staging area a data marts

Tato architektura je nejtypičtější architekturou datových skladů.

Strana 22 / 48

5. ETL – Extraction, Transformation, Loading V minulých kapitolách jsme se věnovali podstatě datových skladů, důvodem jejich budování a zmínili jsme několik důležitých databázových součástí, které jsou pro datové sklady důležité. V této kapitole se budeme věnovat nejdůležitějšímu tématu datových skladů, kterým je ETL engine – nástroj pro manipulaci s definovaným ETL zpracováním.

5.1.

Definice ETL a důvod implementace ETL enginu

ETL je proces skládající se ze tří částí, které spolu funkčně velmi blízce souvisí. V některých implementacích se často nedají od sebe ani přesně rozlišit. V následujících kapitolách postupně probereme všechny principielní sub-procesy a vysvětlíme si jejich obsah. Nejdříve se však zaměříme na podobu ETL enginu a na důvod, proč jsou pro datové sklady implementovány ETL nástroje.

Obrázek 10 - Schéma ETL procesu

Důvod, proč se implementuje ETL engine místo naplnění datového skladu ze zdrojového systému pomocí (PL-)SQL skriptu, je nutnost řešení situace zotavení z nečekané situace. V případě, že dojde k nečekané situaci, potřebujeme nástroj pro bezpečné zotavení a po odstranění důvodu problému potřebujeme možnost pokračovat od bodu, kde problém nastal.

Strana 23 / 48

Z tohoto důvodu jsou ETL enginy implementovány jako posloupnost spouštění podčástí zpracování, které nazýváme moduly. Jednotlivé moduly si můžeme představit jako nedělitelné části celkového zpracování, logické celky, které se dají spustit samostatně bez ohledu na to, jak (a jestli) další krok (modul) proběhne. Proto, abychom od sebe oddělily jednotlivé kroky, které modifikují data, se používá při zpracování dalších dočasných (temporary, stage, ...) tabulek. V případě, že by došlo během vykonávání jednoho z modulů k problému, je poté jednoduché určit, která data zapříčinila vzniklý problém, odstranit tento problém a pokračovat ve zpracování opětovným spuštěním chybového modulu. Rozčlenění zpracování do modulů také přináší výkonnostní výhodu v tom, že určité moduly mohou být spouštěny paralelně. Takto spouštěné moduly musí splňovat podmínku vzájemného neovlivňování. Poslední výhodou rozčlenění zpracování do modulů je také vytvoření části modulů jako globální moduly. Tyto globální moduly jsou využívány více moduly a jsou při volání parametrizovány.

5.2.

Extraction

Extraction – Extrakce je proces, při kterém se zdrojová data načítají ze zdrojových systémů pro další transformaci a uložení do datového skladu. Důležitým faktorem je způsob přenosu těchto dat. Vstupní data dělíme do dvou kategorií: •

databázové systémy,

•

textové soubory (Flat Files).

V případě, že vstupem datového skladu je databázový systém, má tento způsob implementace mnohé výhody (rychlost, jednoduchost implementace,…). Může se ale i v případě vstupu z databázového systému stát, že databáze vstupního systému není kompatibilní s databází datového skladu. V tom případě je nutné použít druhé vstupní metody – textové soubory. Použití textových souborů (extraktů) umožňuje nahrávat data do datového skladu i z nekompatibilního vstupního systému.

Strana 24 / 48

Zajímavá je také otázka, která strana bude plnit tuto interface tabulky. Nutné je určit, jestli tyto tabulky budou plněny zdrojovým systémem (tzv. push model) nebo jestli budou data nahrána databázovým systémem datového skladu (tzv. pull model). Doporučení zní ve vytvoření nezávislého meziúložiště dat, kam zdrojové systémy push modelem nahrávají data a z kterého si systémy zpracovávající tato data pull modelem odebírají.

5.2.1. Fyzické schéma extrakčního modelu Extrakční proces rozdělujeme v závislosti na způsobu získávání zdrojových dat na dvě kategorie: •

Online extrakty

•

Offline extrakty

Online extrakty V případě online extraktů jsou zdrojová data získávána přímo ze zdrojových systémů. Extrakční proces jednoduše uloží zdrojová data do interface tabulky. Důvodem, proč se jedná o opravdu online extrakt, je, že v případě, kdy z nějakého důvodu nedojde k přenosu tohoto extraktu, jsou informace daného extraktu ztraceny. Online extrakt totiž zachycuje stav zdrojových dat ve zdrojovém systému v přesně určený okamžik. U těchto dat by měla být zdrojovým systémem zaručena pro okamžik extraktu neměnnost. To lze většinou jednoduše zajistit tím, že jsou tyto extrakty provedeny v nočních hodinách. Museli bychom získávat extrakt opožděně a tím bychom ho vystavovali riziku, že některá data už budou změněna.

Strana 25 / 48

Offline extrakty Offline extrakty přináší řešení nedostatků online extraktů. Data ze zdrojového systému nejsou získávána přímo, ale prostřednictvím dalších prostředků. Uveďme několik použitelných způsobů realizace offline extraktů:

•

Textové extrakty

Téma textových extraktů bylo již zmíněno v kapitole 4 ve spojitosti s řešením nekompatibility databázových systémů zdrojového systému a datového skladu. V tomto případě není jiná možnost než přenášet data ve formátu, který jsou oba systémy schopny zpracovat. Nejpoužívanější jsou formáty XML a CSV (určeným znakem oddělené sloupce).

•

Redofiles a archive logy

Redo a archiv logy jsou typickým prostředkem databází Oracle. Původně byly určeny pro zálohování databáze a zotavení databáze z nečekané události. Použitelné jsou však i pro přenos změn mezi databázemi (instancemi databáze).

•

Transportable tablespaces

Transportable tablespaces jsou opět prostředkem databází Oracle. Jedná se o prostředek efektivního přenosu velkého množství dat.

5.3.

Transformation and Loading

Poslední dvě fáze ETL enginu jsou popsány najednou, neboť funkčně a implementačně spolu velmi blízce souvisí. Často se totiž funkce těchto fází provede v jednom kroku. Co tedy obsahují fáze transformation and loading – transformace a uložení? Jak již název napovídá, v posledních dvou fázích jsou data z interface tabulek (případně jiných zdrojů) upraveny (transformation) a uloženy do datového skladu (loading). Na začátku transformační fáze by měla proběhnout kontrola vstupních dat, nalezení a označení možných problémů v mapování dat na datový sklad. Důležité Strana 26 / 48

je poté určit, jak se má zachovat zpracování v případě, že je tento problém detekovaný. Správným chováním může být označení chybných záznamů a přerušení zpracování vyvoláním definované výjimky. Následující obrázek zobrazuje rozdělení zpracování faktové tabulky do několika základních kroků.

Obrázek 11 - Postup uložení dat do faktové tabulky

Zpracování je nutné přizpůsobit logickému schématu. Sledovaným faktorem je komplexnost dat ve zdrojovém systému. Podle tohoto kritéria rozdělujeme vstupní data na: •

Full Extraction (plné extrakty)

•

Incremental Extraction (inkrementální extrakty)

U full extraktu jsou ze zdrojového systému získány vždy plné denní (měsíční,…) extrakty. Jako příklad pro vysvětlení rozdílu mezi full a incremental extraktů použijeme dimenzi zákazníků, která se vyskytuje snad ve všech datových skladech. V případě plného extraktu by zdrojem datového skladu byl vždy otisk (aktuální stav) tabulky zákazníků. To znamená, že dochází k přenosu všech informací (záznamů) i v případě, že v nich nedojde ke změně. Strana 27 / 48

Inkrementální extrakty jsou extrakty, které obsahují pouze přírůstkové (změněné) informace (záznamy). Při použití našeho příkladu dimenze zákazníků by byly ze zdrojového systému přenášeny informace obsahující pouze změny na zdrojových datech, tzn. noví zákazníci, změny u zákaznických dat, ukončení platnosti záznamu zákazníka.

5.4.

Parametrizace spuštění

U datových skladů je třeba mít na mysli, že zpracování – plnění datového skladu, je nutné spouštět nad daty každého dne a to vždy postupně. Může se však stát, že několik dní není zpracování spuštěno. Z toho důvodu je nutné implementovat parametrizaci spuštění, kdy jako parametr zpracování určíme datum, nad kterým zpracování spouštíme.

Strana 28 / 48

6. Implementace DW – business logika V této kapitole se věnuji popisu své implementace business logiky datového skladu. Tzn. funkční části datového skladu, jejímž účelem je provádět uložené SQL a PL-SQL příkazy, které dohromady tvoří ETL zpracování. Až do této kapitoly jsem všechny informace uváděl z pohledu teorie s co největším důrazem na obecnost. Od této kapitoly dále se věnuji pouze své konkrétní realizaci implementačně závislé na databázi Oracle 10g. Struktura ETL zpracování Jak jsem již dříve uvedl, z důvodu možného znovuspuštění částí zpracování, ETL rozdělíme do jednotlivých modulů – dále nedělených SQL či PL-SQL bloků. Z modulů budeme tvořit ETL stromy. Ty se od stromů z teorie grafu liší v tom, že libovolný uzel může mít směrem ke kořeni více uzlů za předchůdce. To nám dovolí definovat libovolné závislosti ETL modulů a přesto zachovat co největší přehlednost. Místo ohodnocení hrany budu používat podmínku přechodu, která při splnění či nesplnění bude určovat průchodnost či neprůchodnost hrany.

sd podmínky v e zpracov ání

naplnění pomocné tabulky daty

tabulka obsahuje záznamy

tabulka neobsahuje záznamy

normalizuj data v tabulce

Vytv oř normalizov aná data do tabulky

další krok zpracov ání

Obrázek 12 - Příklad větvení zpracování za použití podmínek

V případě potřeby definování více podmínek přechodu mezi dvěma moduly, bude možné jednoduše přidat další přechody s vlastní podmínkou. Pro vyhodnocení přechodu budou muset být splněny všechny parciální podmínky. Strana 29 / 48

Podporování proměnných a parametrizace spuštění Proměnné budu podporovat jak lokální, tak globální. Lokální proměnné budou hodnoty nastavené ke konkrétnímu spuštěnému modulu. Hodnota bude moci být použita buď z předem určeného modulu, nebo z modulu, který spustil daný modul. Globální proměnné budou hodnoty nastavené pro celý ETL engine. Pro určení hodnot proměnných při spuštění budu implementovat parametrizaci spuštění, kde spouštěnému modulu předám seznam jmen proměnných a jejich hodnot.

6.1.

Návrh a popis datových struktur

Pro potřebu business logiky je potřebné navrhnout několik tabulek, které budou obsahovat všechna potřebná data o ETL zpracování. dm datov y model

RUN_MODULES +PK_ETL_MODULES 1 ETL_MODULES

ETL_MODULE_DEPENDENCIES «column» FK ETL_MODULE_KEY: NUMBER(38) FK DEPENDS_ON: NUMBER(38) LEFT_COND: VARCHAR2(2000 BYT E) RIGHT_COND: VARCHAR2(2000 BYT E) COND: VARCHAR2(2 BYTE) = '='

+MOD_FK1 0..*

+MD_FK2

+PK_ETL_MODULES

+MD_FK1

+PK_ETL_MODULES

«column» *PK MODULE_KEY: NUMBER(8) FK PARENT_MODULE_KEY: NUMBER(8) * MODULE_NAME: VARCHAR2(50 BYTE) MODULE_SOURCE: VARCHAR2(4000 BYTE) SOURCE_MODULE_KEY: NUMBER

+PK_ETL_MODULES +RM_FK1

«PK» + PK_ETL_MODULES(NUMBER)

«FK» + MD_FK1(NUMBER) + MD_FK2(NUMBER)

«FK» + MOD_FK1(NUMBER)

+PK_ETL_MODULES

«PK» + PK_RUN_MODULES(NUMBER)

+PK_ETL_MODULES

«FK» + RM_FK1(NUMBER)

ETL_LOG «column» LOG_KEY: NUMBER(38) LOG_TIME: DATE JOBID: NUMBER LOG_TYPE: VARCHAR2(40 BYT E) LOG_MESSAGE: VARCHAR2(500 BYT E)

«column» *PK RUN_KEY: NUMBER(8) * JOB_ID: NUMBER(8) SOURCE_CODE: VARCHAR2(4000 BYTE) * RUN_STAT US: VARCHAR2(50 BYTE) * RUN_DATE: DATE RESTART _DATE: DATE MODULE_NAME: VARCHAR2(80 BYTE) ROWS_AFFECTED: NUMBER FINISH_DATE: DATE FK ETL_MODULE_KEY: NUMBER(38) MODULE_ERROR: VARCHAR2(500 BYTE) PARENT_MODULE_KEY: NUMBER ROOT_MODULE_FLAG: CHAR(1 BYTE) = 'N'

(ETL_MODULE_KEY = MODULE_KEY) +VAR_MODULE_KEY

ETL_VARIABLES «column» *PK VAR_KEY: NUMBER(8) FK ET L_MODULE_KEY: NUMBER(8) = 0 * VAR_NAME: VARCHAR2(50 BYTE) * VAR_VALUE: VARCHAR2(50 BYTE) * VAR_GLOBAL_FLAG: CHAR(1 BYTE) = 'N' VAR_FROM_CALLER: CHAR(1 BYTE) = 'N' VAR_MODULE_KEY: NUMBER VAR_SOURCE_NAME: VARCHAR2(50) «PK» + VAR_PK(NUMBER)

+RUN_VAR_ETL_MODULE_KEY

RUN_VARIABLES «column» FK ETL_MODULE_KEY: NUMBER JOB_ID: NUMBER VAR_NAME: VARCHAR2(50 BYTE) VAR_VALUE: VARCHAR2(50 BYTE) «FK» + RUN_VAR_ETL_MODULE_KEY(NUMBER)

«FK» + VAR_MODULE_KEY(NUMBER)

Obrázek 13 - Datové struktury

Tabulka ETL_MODULES Hlavní tabulkou je tabulka ETL_MODULES, která obsahuje informace o ETL modulech – nejmenších částí ETL zpracování. Primární klíč modulu (module_key), Strana 30 / 48

jméno modulu (module_name) a zdrojový kód modulu (module_source). Ve sloupci Source_module_key je cizí klíč na modul, ze kterého se má použít zdrojový kód namísto aktuálního. Tímto způsobem je možné implementovat tzv. globální moduly. Globální moduly jsou moduly, které obsahují univerzální kód. Při tvorbě zpracování poté stavíme zpracování z globálních částí kódu, které pouze parametrizujeme předávanými parametry. Posledním atributem je parent_module_key, který určuje kořenový modul v ETL stromu. Tento atribut identifikuje celý ETL strom. Tabulka ETL_MODULE_DEPENDENCIES Tabulka obsahuje závislosti modulů v rámci ETL. Kromě klíče spouštěného modulu a modulu, na kterém daný modul závisí, obsahuje tabulka i podmínku přechodu. Podmínka je určena svojí levou a pravou stranou a operátorem porovnání. V případě, že je mezi dvěma moduly více podmínek, je pro splnění přechodu nutné splnění všech podmínek. Tímto způsobem je možné vytvořit libovolné závislosti mezi moduly. Tabulka ETL_VARIABLES Proměnné pro zpracování jsou definovány v tabulce ETL_VARIABLES. Proměnné jsou rozděleny do dvou kategorií – lokální a globální. Toto rozdělení určuje var_global_flag příznak. U každé proměnné lze nastavit jméno a lokální hodnotu. U lokálních proměnných je navíc možné nastavit i další tři parametry určující, odkud pochází hodnota pro tuto proměnnou. Na výběr zdroje jsou tři možnosti: • Lokální hodnota – použije se aktuální hodnota nastavená v tomto záznamu, • Hodnota z volajícího – použije se hodnota z modulu, který tento modul při zpracování spustil (výhodné pro větvení zpracování), • Hodnota z určeného modulu – použije se hodnota z předem určeného modulu. Navíc je možné nastavit ve sloupci var_source_name i jméno (výraz), které určuje jméno zdrojové proměnné. Hodnotou může být i složení více proměnných, případně doplnění o další text.

Strana 31 / 48

Tabulka RUN_VARIABLES Tabulka obsahuje obraz ETL_VARIABLES při spuštění. Kromě aktuální hodnoty proměnné obsahuje tabulka i JobId. Tabulka ETL_LOG Tabulka obsahuje informace logů. Tabulka RUN_MODULES Tato tabulka obsahuje informace o spuštěných modulech. Zdrojové kódy po nahrazení proměnných jsou překopírovány z tabulky ETL_MODULES do této tabulky (atribut source code). Skupinu modulů, které patří do konkrétního spuštění ETL stromu, určíme podle job_id. Run_status určuje status spuštěného modulu. Výčet navržených statusů, uvedeme později. Run_date určuje datum spuštění, případný restart je určen atributem restart_date. Datum dokončení modulu je určen pomocí finish_date. Počet řádků se změnou (při update modulu), vložených řádků (při insert modulu) nebo smazaných řádků (při delete modulu) je zapsán v atributu rows_affected. Poslední parametr module_error určuje chybu ve zpracování modulu (jestli se nějaká vyskytla).

6.2.

Popis a implementace výkonných funkcí

Bussiness logika ETL je implementována pomocí 4 PL/SQL balíků: ETL_CONDITIONS

Balík, který slouží pro vyhodnocování podmínek.

ETL_RUNJOB

Balík, který slouží ke spouštění modulů.

ETL_UTILS

Pomocný balík s obecnými funkcemi, např. logováním.

ETL_VARIABLE

Balík, který slouží pro práci s proměnnými.

Balík ETL_RUNJOB, který obsahuje následující procedury a funkce: /* Start zpracovani, nacte parametrizaci a spousti pres RunModule */ function StartModule(I_ModuleKey IN NUMBER,var IN varchar2:=null) return number; /* Vytvori zaznam z etl_modules do run_modules a modul spusti

Strana 32 / 48

po uspesnem dokonceni sveho zdr. kodu spusti pres RunDependants moduly, ktere na aktualni zavisi */ PROCEDURE RunModule(I_ModuleKey IN NUMBER,I_JobId IN NUMBER,v_debug varchar2, I_seq number:=0); -- spusteni modulu /* Restart modulu po chybe */ function RestartModule(I_RunKey IN NUMBER) return number; /* pomoci dbms_job.submit spusti RunModule */ function SubmitModule(I_ModuleKey IN NUMBER,I_JobId IN NUMBER,I_seq number:=0) return number; /* Spusteni modulu zavisejicich na aktualnim */ Function RunDependants(I_ModuleKey integer,I_JobId integer) return REC_SUBMITED_JOBS_T; /* Preskoceni modulu, stav modulu je nastaven na skipped a zpracovani pokracuje jako po uspesnem konci*/ function SkipModule(I_RunKey IN NUMBER) return number;

Spuštění zpracování, procedura RunModule Zpracování je spuštěno voláním funkce StartModule. Prvním parametrem je klíč modulů, který má být použit jako kořen zpracování. Druhým parametrem je parametrizace spuštění. Příklad spuštění modulu: declare a varchar2(100):='current_date=11.5.12008,g_debug=true'; b number; begin b:=etl_runjob.STARTMODULE(1,a); end;

Moje implementace umožňuje pouze předávání textových proměnných. Zpracování s tím tedy musí počítat a pro svoji potřebu provést potřebnou konverzi. Průběh spuštění popisuje vývojový diagram na obrázku 14. Spuštění začíná tím, že se ze sekvence vytvoří nové JobId. Identifikátor JobId slouží k jednoznačné identifikaci spuštěného ETL stromu a jeho hodnota je brána při spuštění generována ze sekvence. V případě, že je funkci předána textová hodnota parametrizace, jsou tyto hodnoty načteny a uloženy pro zpracování do tabulky RUN_VARIABLES. Tato parametrizace je jednak použita jako nastavení proměnných kořenového modulu ETL stromu, tak jako rozšíření globálních proměnných pro celé zpracování. Je tedy vhodné této parametrizaci předávat hodnoty jako je datum, za který zpracování Strana 33 / 48

spouštíme. Jako součást parametrizace můžou být uvedeny i dvě parametrizace ETL frameworku. Jedná se o parametr g_debug nastavující podrobné logování průběhu zpravování do tabulky ETL_LOG (implicitní hodnota je nastavena na false – podrobné logování vypnuto) a parametr g_jobid, kterým lze ze nastavit identifikátor stromu.

sd spuštění zpracov ání StartModule(etl_module_key, variables)

Získání unikátního JobId

nastav ení proměnných inicializace zpracov ání

SubmitModule(etl_module_key)

return JobId

Obrázek 14 - Spuštění zpracování

Po načtení proměnných je spuštěn kořenový modul zpracování voláním procedury StartModule s následujícími parametry: I_ModuleKey

primární klíč definice modulu z tabulky ETL_MODULES, který má být považován za kořen ETL zpracování,

I_JobId

(unikátní) identifikátor spuštěného stromu.

Funkce SubmitModule Tato funkce slouží pro naplánování spuštění modulu za pomocí Oracle funkce DBMS_JOB.sumbit(). Balík DBMS_JOB slouží pro paralelní spouštění úloh. Funkce SubmitModule vytvoří nový dbms_job, který volá funkci RunModule.

Procedura RunModule Procedura RunModule po startu načte zdrojový kód z tabulky ETL_MODULES. V případě, že modul obsahuje zdrojový kód, je provedeno nahrazení nejdříve lokálních a poté globálních parametrů pomocí funkcí z pomocného balíku ETL_UTILS. Dále jsou informace o připraveném modulu zapsány do tabulky RunModules, kam se zapíše upravený zdrojový kód, datum (včetně času), kdy byl modul spuštěn a stav modulu (modul spuštěn). Dále je provedena kontrola na nahrazení všech parametrů. V případě, že jsou nalezeny nenahrazené

Strana 34 / 48

parametry, je tato informace zapsána do RunModules a vyvolána definovaná výjimka.

sd RunModule RunModule(moduleKey,jobId)

Zj isti splnění záv islostí pro spuštění modulu

Nahrej zdroj ov y kod modulu

source_module_key

Check_for_Not_Replaced_Args()

not null

nahrej zdroj ov y kod modulu daneho proměnou source_module_key

[Vsechny promenne nejsou nahrazeny] Exception - nenahrazene parametry Null

modul obsahuje zdrojový kód

Modul obsahuje zdrojovy kod

Spustit zdroj ov y kod modulu Exception při běhu

Vloz informace do tabulky RunModules

modul obsahuje zdrojový kód

ne

Označ modul za ukončený

ano

Nahrad lokalni promenne (Replace_Local_Args) «structured» Spust moduly zav isle na aktualnim modulu (RunDependants) Nahrad globalni promenne (Replace_Global_Args)

Označ modul za spuštěný

Obrázek 15 - Popis funkce RunModule

V případě, že modul obsahuje zdrojový kód, je spuštěn. Po dokončení je do RunModules zapsána informace o počtu změněných záznamů. Pomocí funkce RunDependants jsou následně spuštěny všechny moduly, které na tomto modulu závisí a mohou být spuštěny. Popis této procedury je uveden dále. Strana 35 / 48

Zpracovávání modulu je ukončeno až po dokončení všech spuštěných modulů pro zjištění koncového stavu celého stromu. Po dokončení jsou opět zapsány informace do tabulky RunModules.

Procedure ReplaceLocalArgs, nahrazení lokálních proměnných Proměnné jsou ve zdrojovém kódu uzavřeny do dvojitých závorek s prefixem #. Například proměnná datum by se zadala jako #{dnesni_datum}.

act Nahrad lokalni promenne (Replace_Local_Args) pro každý vyskyt #{local_var}

Načti informaci z etl_v ariables pro daný modul a local_v ar

Not null

var_module_key

Načti hodnotu v ar_v alue z run_v ariables pro danou instanci spuštěného modulu

null

var_from_caller

true

Načti hodnotu v ar_v alue z run_v ariables pro modul, který tento modul spustil

false Použij aktuální hodnotu z etl_v ariables

Zapiš informaci o hodnotě použité proměnné do run_v ariables

local_var.var_source_name může obsahovat výraz, který je nutný vyhodnotit pro hodnotu jména. Např. tmp_#{schema}.#{table_name}_stage. Každá proměnná v #{} je nahrazena aktuální hodnotou z daného modulu dané proměnné

Obrázek 16 - Popis funkce ReplaceLocalArgs

Způsob nalezení hodnoty pro každou proměnnou je popsán v diagramu na obrázku 16. Pro každou lokální proměnou, definovanou v tabulce etl_variables (s příznakem global_flag = ‘N’), se provede nahrazení ve zdrojovém kódu. Zdrojový kód se prohledává na přítomnost jména lokální proměnné uzavřené do #{}. V případě nalezení se nahradí za hodnotu proměnné. Strana 36 / 48

Tabulka etl_variables obsahuje navíc i definice, odkud se má použít hodnota pro tuto proměnnou. V případě, že je nastavena hodnota var_module_key, použije se jako zdroj hodnoty záznam v tabulce run_variables pro modul s daným klíčem. V opačném případě, kdy hodnota var_module_key není nastavena, se vyhodnocuje podmínka na příznak var_from_caller, který určuje, že se má použít hodnota z modulu, který aktuální modul spustil. Je-li příznak nastaven, použije se opět tabulka run_variables, tentokrát pro spouštějící modul. V obou případech, kdy je použita hodnota z jiného než aktuálního modulu, je navíc kontrolován sloupec var_source_name, který určuje výraz pro název proměnné. Tím je umožněno např. použití hodnoty z proměnné jiného jména, nebo spojení

hodnot

#{stg_table_name}

více

proměnných

(a statického

můžeme

textu).

Např.

proměnnou

předefinovat

jako

tmp_#{schema}.#{table_name}_stage. Poslední možností v případě nenastavení předchozích hodnot je použita přímo hodnota z etl_variables. Tato možnost se používá pro iniciální nastavení proměnných (kořenového) modulu. V případě, že definujeme iniciální nastavení proměnných, měli bychom to provádět pouze v kořenovém modulu. Tím zvýšíme přehlednost zpracování a zjednodušíme možné úpravy. Po nalezení výsledné hodnoty, jsou tato hodnota a jméno proměnné zapsány do tabulky run_variables. Hodnota pro název je použito jméno proměnné z var_source_name, je-li prázné tak var_name

Funkce RunDependants, vyhodnocení podmínek přechodu Funkce RunDependants slouží pro spuštění všech modulů závislých na určeném modulu. Funkce vyhledá v seznamu závislostí modulů (tabulka ETL_MODULE_DEPENDENCIES) všechny moduly, které na daném modulu závisí. Podle dat v tabulce RUN_MODULES se ověří splnění všech jejich ostatních závislostí. Tím je zajištěno, že všechny závislé moduly jsou spuštěny až po úspěšném konci všech definovaných předchůdců. Závislé moduly jsou spuštěny vždy posledním úspěšně dokončeným modulem z jejich závislostí. Tento fakt umožňuje snadný restart modulu při chybě a konzistentní pokračování ve zpracování. Návratovou hodnotou funkce je seznam spuštěných modulů. Průběh funkce RunDependants je zobrazen na obrázku 17. Strana 37 / 48

act RunDependants RunDependants

Vytv oř seznam záv islých modulů

jsou polozky v seznamu [seznam je prazdny]

Vezmi prv ní záv islý modul

Ov ěř splnění v šech podmínek přechodu

Naplánuj spuštění modulu SubmitModule

Return seznam naplánovaných jobů

Obrázek 17 - Popis funkce RunDependants

Restart modulu, funkce RestartModule V případě, že při běhu modulu došlo k chybě, je nutné zpracování modulu restartovat. Je vhodné poznamenat, že chyba v modulu a tím zastavení zpracování je žádoucí například, když zpracování nemá k dispozici data, nebo data nejsou ve vhodném stavu. Pro restart modulu slouží funkce RestartModule, která nastaví do tabulky RUN_MODULES stav modulu na Restarting a znovu pomocí SubmitModule modul spustí.

Strana 38 / 48

Přeskočení modulu, funkce SkipModule Během zpracování může dojít k tomu, že modul, ve kterém došlo k chybě, nechceme restartovat, ale byly bychom rádi, kdyby zpracování běželo dále jako by modul skončil správně. Pro přeskočení modulu slouží funkce SkipModule, která označí stav modulu na Skipped a spustí závislé moduly.

Strana 39 / 48

7. Implementace DW – prezentační vrstva Prezentační vrstvu nad datovým skladem je vhodné rozdělit na tři skupiny. Jednou je prezentace zpracování datového skladu, sledování průběhu spouštěných zpracování a s tím související realizace reakcí na stavy zpracování (např. chyby při zpracování). Druhou skupinou prezentace je způsob definování – modelování zpracování. Poslední skupinou je prezentace dat datového skladu. Poslední dvě skupiny prezentace jsou velmi obsáhlé, a proto se ve své implementaci budu zabývat pouze první skupinou, potřebnou pro prezentaci výsledků implementace business logiky DW.

7.1.

Implementace DW – prezentace průběhu zpracování

Pro prezentaci jsou nutné především následující přehledy a funkce: •

Přehled modulů

•

Vzájemné vazby modulů, ETL stromy

•

Přehled a definice lokálních a globálních proměnných

•

Přehled spuštěných modulů včetně zdrojového kódu, výstupu případných chyb ve zpracování

•

7.2.

Možnost spuštění, zastavení a restartování modulů

Implementace DW – prezentace modelování zpracování

K modelování zpracování existují dva hlavní přístupy. Prvním je modelování pomocí grafického nástroje metodou sestavování funkčních bloků, druhou metodou je přímé modelování pomocí SQL či PLSQL kódu. Výhoda grafického modelování je zejména v jednoduchosti a přehlednosti pro méně zkušené uživatele. Přehlednost takového modelu zpracování je značná hlavně u jednoduchých zpracování. Při potřebě složitých podmínek je již výhodnější použití přímého modelování (PL-)SQL kódu.

Strana 40 / 48

Metoda přímého modelování není tak jednoduchá, ale může být pro zkušené uživatele výhodnější. Navíc také přináší možnost přímého ovlivňování SQL modulů a tím i optimalizaci dotazů. Pro svou implementaci prezentační vrstvy jsem zvolil druhou metodu, tzn. přímé modelování. Grafické modelování lze však kdykoliv přidat, protože ve výsledku je z grafické implementace vygenerován sled SQL či PLSQL příkazů. Má implementace prezentace je založena na Java technologii JSF a aplikačním serveru Tomcat. Implementace umožňuje přes webové rozhraní modelovat všechny základní úkony definované v kapitole 7.1. Jelikož v rozsahu mé diplomové práce není vytvoření prezentační vrstvy, navrhl a

implementoval

jsem

pouze

základní

podporu

pro

potřeby

práce

s implementovaným ETL frameworkem, ověření funkčnosti a prezentaci výsledků.

Strana 41 / 48

8. Vzorové zpracování V této kapitole uvedu dvě vzorová zpracování, jedno pro naplnění dimenze a druhé pro faktovou tabulku. Obě zpracování jsou založena na využití globálních modulů. Tvorba dalších zpracování proto vyžaduje psaní pouze minimálního množství kódu. Globální moduly jsou připraveny pro obecné funkce jako např. smazání tabulky, přidání partition a další. Název globálního modulu vždy začínám prefixem G_ pro odlišení od ostatních modulů. Všechny definované proměnné těchto modulů mají nastavený příznak var_from_caller, tzn. jako hodnoty parametrů se vždy použije hodnota z volajícího modulu.

Obrázek 18 - Prezentační vrstva - globální moduly

Na obrázku 18 jsou zobrazeny definované ETL stromy v systému. Do jednoho stromu jsou zařazeny všechny globální moduly pro větší přehlednost.

Strana 42 / 48

8.1.

Dimenze

Zpracování dimenzí obsahuje 6 modulů, polovina z nich místo vlastního kódu využívá globálních modulů. Při tvorbě podobného zpracování je tedy nutné připravit pouze 3 moduly obsahující zdrojový kód. Volání globálního modulu je znázorněno dodatkem „uses:“ a jméno globálního modulu uvedeného za jménem modulu.

Obrázek 19 - Prezentační vrstva – dimenze

V prvním modulu (číslo 201) je smazána pomocná tabulka, aby byla hned v dalším kroku (číslo 202) vytvořena ze zdrojových dat. Dalším krokem (číslo 203) je volání globálního modulu g_compare. Ten pomocí sady uložených procedur porovná data v tabulce vytvořené v modulu číslo 202 s daty v datovém skladě. Výsledkem porovnání jsou následující tabulky: • #{table_name}_ins - nové záznamy, které nejsou v datovém skladě nebo nové záznamy při vytvoření historie. Tyto záznamy jsou v modulu č. 205 vloženy do DW. • #{table_name}_upd - záznamy, které jsou již v datovém skladě, ale u kterých došlo ke změně některých položek. Podle těchto záznamů je aktualizován datový sklad v bodě 204. • #{table_name}_del - záznamy, které jsou ze zdrojových systémů smazány, a které je potřeba v datovém skladě označit za smazané (bod 204). Strana 43 / 48

Jeden ze vstupních parametrů globálního modulu g_compare je seznam sloupců (history_list), nad kterými se má držet historie. Změna v ostatních povede pouze k aktualizaci záznamu. Posledním krokem zpracování (bod 206) je provedení spočítání statistik nad tabulkou datového skladu.

8.2.

Fakta

Zpracování faktové tabulky i přesto, že obsahuje 12 modulů, vyžaduje vytvoření SQL kódu pouze pro jeden modul. Zpracování vytváří partišnovanou denní faktovou tabulku, partišnovanou podle datumu.

Obrázek 20 - Prezentační vrstva - faktové tabulky

Ve zpracování faktové tabulky je v prvním kroku (modul číslo 251) smazána dočasná tabulka. V dalším kroku je tato tabulka opět vytvořena pomocí globálního modulu. Ten vytváří prázdnou tabulku stejné struktury jako má cílová tabulka Strana 44 / 48

pomocí příkazu create table as select s podmínkou where 1=2. Cílové tabulce je v modulu č.253 vytvořena nová partišna, která bude sloužit jako cíl pro nová data. V modulu č. 254 je provedena výměna partišen mezi pomocnou tabulkou a nově vytvořenou partišnou v cílové tabulce. Následující modul vloží do pomocné tabulky nová data ze zdrojových systémů. Nad těmito daty je vytvořen primární klíč, který je hned v dalším kroku zrušen. Tímto je provedena kontrola vložených dat. V modulu č. 258 jsou vyměněny partišny z bodu č. 254 zpět. Důvody proč měníme partišny tam a zpět jsou následující: •

Výměna partišny je rychlejší než vkládání dat z pomocné tabulky do cílové, protože insert do cílové tabulky vyžaduje přepočítání všech indexů nad tabulkou, výměna partišen označí tyto indexy pouze za „unusable“ a tyto jsou pak přepočítány najednou.

•

Dvojí výměna zaručí, že data v cílové tabulce nejsou fragmentované a jsou umístěny ve správném tablespace.

Po výměně partišen jen nutné provést přepočítání všech indexů nad tabulkou (modul č.259). Dále je vhodné dealokovat přebytečné místo v cílové partišně (č. 261), protože tabulka už nebude dále plněná a místo by zůstalo nevyužité navždy. Závěrem zpracování stejně jako u dimenze je vhodné přepočítat statistiky pro zaručení efektivních exekučních plánů při dotazování.

Strana 45 / 48

9. Závěr V prvních kapitolách této práce jsem se věnoval obecně tématice datových skladů. Vysvětlil jsem základní dělení datových skladů podle technologie a do hloubky jsem v dalších kapitolách rozvedl technologii ROLAP, neboli datový sklad založený na relační databázi. Cílem mé diplomové práce bylo navrhnout a realizovat systém pro extrakci, transformaci a uložení dat, tzn. ETL frameworku pro datové sklady. Proto jsem uvedl nutné požadavky na tyto systémy a navázal praktickou implementací. Implementaci jsem popsal pomocí dokumentace datového schématu a popisu funkčností jednotlivých součástí. Popis je doplněn o vývojové diagramy zachycující chování systému. Nad navrženým ETL frameworkem jsem vytvořil základní prezentační vrstvu. Jelikož je tato vrstva již nad rámec mé diplomové práce, uvádím jen základní popis. V poslední kapitole popisuji navržené vzorové zpracování pro plnění dimenze a faktové tabulky.

Strana 46 / 48

10. Seznam použitých zkratek

Zkratka

Anglický význam

Český význam

3NF

3rd normalized form

Třetí normalizovaná forma

CRM

Customer relationship management

DW

Data warehouse

ETL

Extraction, transformation, loading

ID

Identificator

IO

Datový sklad identifikátor Integritní omezení

MIS

Managment information system

Manažerský informační systém

MOLAP

Multidimansional OLAP

Multidimensionální OLAP

OLAP

Online analytical procesing

OLTP

Online transaction procesing

PK

Primary Key

Primární klíč

ROLAP

Relational OLAP

Relační OLAP

Flat Files

Textové extrakty

11. Seznam použité literatury [1] Dokumentace Oracle [2] Building the Data Warehouse by William Inmon (John Wiley and Sons, 1996) [3] http://freedatawarehouse.com/ [4] http://datamining.xf.cz – popis datových kostek [5] Semestrální práce Datové sklady, Lukáš Vítek

Strana 47 / 48

12. Seznam obrázků Obrázek 1 - Datová kostka ...................................................................................... 10 Obrázek 2 - Hvězdicové schéma (star schema)..................................................... 12 Obrázek 3 - Schéma sněhová vločka (snowflake schema) ..................................... 12 Obrázek 4 - Příklad návrhu dimenze ....................................................................... 14 Obrázek 5 - Příklad návrhu faktové tabulky............................................................. 15 Obrázek 6 - Návrh části datového skladu................................................................ 19 Obrázek 7 - Základní architektura DW .................................................................... 20 Obrázek 8 - Architektura za použití staging area .................................................... 21 Obrázek 9 - Architektura s použitím staging area a data marts............................... 22 Obrázek 10 - Schéma ETL procesu ........................................................................ 23 Obrázek 11 - Postup uložení dat do faktové tabulky ............................................... 27 Obrázek 12 - Příklad větvení zpracování za použití podmínek ............................... 29 Obrázek 13 - Datové struktury ................................................................................ 30 Obrázek 14 - Spuštění zpracování .......................................................................... 34 Obrázek 15 - Popis funkce RunModule ................................................................... 35 Obrázek 16 - Popis funkce ReplaceLocalArgs ........................................................ 36 Obrázek 17 - Popis funkce RunDependants ........................................................... 38 Obrázek 18 - Prezentační vrstva - globální moduly ................................................. 42 Obrázek 19 - Prezentační vrstva – dimenze ........................................................... 43 Obrázek 20 - Prezentační vrstva - faktové tabulky .................................................. 44

Strana 48 / 48