Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Katedra informačních technologií
Studijní program: Aplikovaná informatika Obor: Informační systémy a technologie
Provoz a udržitelný rozvoj datového skladu DIPLOMOVÁ PRÁCE
Student
:
Pavel Hník
Vedoucí
:
Doc. Ing. Jan Pour, CSc.
Oponent :
Ing. Zuzana Šedivá, Ph.D
2012
Prohlášení Prohlašuji, že jsem diplomovou práci zpracoval samostatně a že jsem uvedl všechny použité prameny a literaturu, ze které jsem čerpal.
V Praze dne 12. prosince 2012
................................
Jméno a příjmení studenta
Poděkování
Děkuji vedoucímu své diplomové práce panu doc. Ing. Janu Pourovi, CSc. za jeho cenné rady a všudypřítomnou trpělivost a ochotu, se kterou mi pomáhal.
Abstrakt Předkládaná diplomová práce se zabývá oblastí velkých datových skladů z pohledu jejich udržitelného provozu a rozvoje. Práce si klade za cíl zanalyzovat klíčové aspekty, které ovlivňují provoz datového skladu ve velkém podniku. Práce je rozdělena do tří hlavních částí, kterým předchází krátký teoretický úvod. První část je zaměřená na vysvětlení architektury datového skladu. Nejvíce prostoru je věnováno popisu hlavních komponent celého řešení a rozdílným způsobům jejich technické realizace. Druhá část přibližuje způsob řízení a provozu datového skladu. Dílčí kapitoly druhé části popisují nejdůležitější provozní činnosti. Poslední část je zaměřená na práci s klientskými nástroji ETL platformy Informatica Powercenter. Vlastní přínosy práce spočívají v porovnání vlivu řešení jednotlivých komponent na jednoduchost a kvalitu rutinního provozu. Platí, že datový sklad se provozuje tak dobře, jak je navržen a implementován. V práci vycházím především ze svých přímých zkušeností a předaných znalostí z provozu datového skladu ve velké tuzemské společnosti.
Klíčová slova Datový sklad, ETL, BI, Change Management, Informatica Powercenter.
Abstract The present diploma thesis focuses on the subject matter of large data warehouses under the aspect of their sustainable operation and development, seeking to analyze the key factors which influence the operation of data warehouses at large businesses. The thesis is divided into three main sections, preceded by a short theoretical introduction. The first part aims at explaining and illustrating the architecture of data warehouses, with the bulk of this section being devoted to a description of the key components of data warehouse solutions as a whole, and the various available alternatives for their technical implementation. The second section familiarizes the reader with the manner in which data warehouses are managed and operated, discussing the most important operational tasks in individual sub-sections. The final section focuses on working with client tools within the ETL platform of Informatica Powercenter. This work's original contribution lies in the comparison of how the design of individual components expresses itself in the simplicity and quality of routine operations. As a matter of fact, data warehouses can only be operated as conveniently and competently as they were designed and implemented. In this thesis of mine, I primarily draw on my immediate experience and acquired knowledge from data warehouse operations at a large domestic enterprise.
Keywords Data Warehouse, ETL, BI, Change Management, Informatica Powercenter.
Obsah Úvod ...................................................................................................................................................................... 9 Vymezení tématu......................................................................................................................................... 9 Cíle práce a způsob jejich dosažení ...................................................................................................... 9 Vlastní přínosy práce .............................................................................................................................. 10 Předpoklady a omezení práce ............................................................................................................. 11 Cílový okruh čtenářů .............................................................................................................................. 11 Rešerše zdrojů ........................................................................................................................................... 11 1
2
Vymezení a definice pojmů .............................................................................................................. 12 1.1
Business Intelligence ................................................................................................................. 12
1.2
Primární systémy ........................................................................................................................ 13
1.3
Extract Transform and Load ................................................................................................... 14
1.4
Data Warehouse .......................................................................................................................... 14
1.5
Analytické nástroje ..................................................................................................................... 15
Principy a předpoklady budování DWH ..................................................................................... 15 2.1
Budování datového skladu ...................................................................................................... 15
2.1.1
Předpoklady a omezení velkých BI/DWH řešení................................................... 15
2.1.2
Etapy vývoje DWH ............................................................................................................. 18
2.1.3
Výchozí dokumentace pro vývoj DWH ...................................................................... 20
2.2
Zdrojové systémy podniku ...................................................................................................... 21
2.2.1
Extrakce dat ze zdrojových systémů .......................................................................... 21
2.2.2
Typy kódování dat ............................................................................................................. 23
2.2.3
Údržba číselníků ................................................................................................................. 24
2.3
Způsob uložení dat v DWH ...................................................................................................... 25
2.3.1
Data Warehouse Appliance ............................................................................................ 26
2.3.2
Architektura řešení DWA Teradata ............................................................................. 27
2.3.3
Architektura řešení DWA Exadata ............................................................................... 28
2.3.4
Srovnání DBMS Oracle a Teradaty ............................................................................... 30
2.3.5
Rozdělení trhu DWH ......................................................................................................... 34
2.3.6
Shrnutí .................................................................................................................................... 36
2.4
2.4.1
ETL scripty vs ETL nástroje............................................................................................ 37
2.4.2
Vývoj ETL nástrojů............................................................................................................. 37
2.4.3
Rozdíl mezi ETL a ELT ...................................................................................................... 39
2.4.4
Analýza trhu ETL nástrojů .............................................................................................. 43
2.5 3
Způsob realizace ETL................................................................................................................. 37
Shrnutí ............................................................................................................................................. 44
Řízení a provoz datového skladu ................................................................................................... 45 3.1
Provoz datového skladu ........................................................................................................... 45
3.1.1 3.2
Týmy zabezpečující fungování DWH .......................................................................... 45
Load dat do DWH ........................................................................................................................ 47
3.2.1
Iniciální nápočet DWH...................................................................................................... 47
3.2.2
Denní inkrementální load ............................................................................................... 47
3.2.3
Aktuální stav „D-1“ a zpoždění DWH .......................................................................... 48
3.2.4
Inkrementální vs stavový load ...................................................................................... 49
3.2.5
Délka loadu ........................................................................................................................... 49
3.3
Spouštění a řízení loadů ........................................................................................................... 50
3.3.1
Principy řízení loadu ......................................................................................................... 50
3.3.2
Inteligentní nástroj k řízení loadu ............................................................................... 50
3.3.3
Spouštění loadu................................................................................................................... 51
3.4
Monitorování loadu .................................................................................................................... 51
3.4.1
Přístupová oprávnění ....................................................................................................... 51
3.4.2
Vzdálené přístupy do prostředí DWH ........................................................................ 52
3.4.3
Inteligentní způsoby monitoringu ............................................................................... 52
3.4.4
Monitoring databází a databázových serverů ......................................................... 56
3.5
Ochrana dat v DWH .................................................................................................................... 57
3.5.1
Přístupová práva ................................................................................................................ 57
3.5.2
Bezpečnostní pohledy v databázi ................................................................................. 57
3.5.3
Fallback .................................................................................................................................. 57
3.5.4
Backup and Restore (BAR) Architektura .................................................................. 58
3.5.5
Servisní odstávky ............................................................................................................... 59
3.6
3.6.1
Prostředí infrastruktury .................................................................................................. 59
3.6.2
Požadavky na změnu a způsob jejich realizace ...................................................... 61
3.7 4
Change Management.................................................................................................................. 59
Shrnutí ............................................................................................................................................. 62
Vývoj a provoz DWH na platformě Informatica Powercenter ........................................... 62 4.1
Práce v klientských nástrojích Informatica Powercenter ........................................... 62
4.1.1
Informatica Powercenter server .................................................................................. 62
4.1.2
Architektura Powercenter .............................................................................................. 63
4.1.3
Klientské nástroje Informatica Powercenter .......................................................... 65
4.1.4
Alternativa ke klientským nástrojům ......................................................................... 71
4.1.5
Performance ETL řešení .................................................................................................. 71
4.2
Technická realizace zpracování dat ..................................................................................... 73
4.2.1
Operační klíče ...................................................................................................................... 73
4.2.2
Historizace dat..................................................................................................................... 74
4.2.3
Cílové vrstvy DWH ............................................................................................................. 75
4.2.4
Kroky ETL architektury ................................................................................................... 76
4.3
Způsob opravy chyb ................................................................................................................... 78
4.3.1
Workaround ......................................................................................................................... 78
4.3.2
Fix, hotfix ............................................................................................................................... 78
4.3.3
„Návrat“ DWH ...................................................................................................................... 78
4.4
Kategorizace nejčastějších chyb ............................................................................................ 79
4.4.1
Chyby na datech z primárních systémů..................................................................... 79
4.4.2
Nedobíhající úloha ............................................................................................................. 79
4.4.3
Úlohy se skrytými chybami ............................................................................................ 80
4.5
Shrnutí ............................................................................................................................................. 81
Závěr .................................................................................................................................................................. 81 Terminologický slovník.............................................................................................................................. 84 Seznam literatury ......................................................................................................................................... 87 Seznam obrázků a tabulek ........................................................................................................................ 93
Úvod Vymezení tématu Závěrečné práce z oblasti Business Intelligence jsou většinou zaměřené na porovnání specializovaných BI/ETL nástrojů nebo na analýzy implementace nějakého BI řešení. Původně jsem chtěl obdobně porovnávat rozdíly ETL nástrojů, konkrétně z technologického a výkonnostního hlediska. I když jsem již měl k dispozici výkonný server vhodný pro testy na větším množství dat, od záměru jsem nakonec upustil. Velkým omezením byl přístup k aktuálním, srovnatelným verzím nástrojů. Porovnávání starších verzí by snížilo věrohodnost testů. Je sice možné získat studentské licence, ty však nejsou výkonnostně plnohodnotné a umožnují například výpočty pouze na jednom procesorovém jádru. Vzhledem k tomu, že cena licencí komerčních ETL nástrojů je odvozena právě od počtu jader, které může zákazník využívat, teoreticky správné by bylo i měření a porovnávání výkonu ETL nástrojů pouze na jednom procesorovém jádru. Výsledek takto realizovaného výkonnostního srovnání by byl přesto velmi nejistý. V závěru roku 2011 jsem nastoupil do společnost Teradata. Teradata je jedním z největších dodavatelů řešení datových skladů na světě. Zaměřuje se na vývoj ETL řešení a na prodej a podporu vlastního specializovaného hardware pro datové sklady. Po domluvě s mým vedoucím práce, doc. Ing. Janem Pourem, CSc., jsem se rozhodl téma změnit a více využít své zkušeností z praxe. Na rozdíl od vytváření ETL benchmarku založeném na kompromisu, by navíc práce na aktuálně zvolené téma měla déle udržet svou hodnotu. Má diplomová práce je proto zaměřena na problematiku provozu velkých datových skladů. Provoz datového skladu úzce souvisí s budováním, implementací a kontinuálním rozvojem datových skladů. Značná část práce je věnována architektuře datových skladů a způsobu jejich technologického řešení. Architekturu DWH vnímám jako základ a bez jejího důkladného pochopení není možné DWH provozovat. Při provozu může nastat mnoho různých chyb v různých fázích ETL a znalost architektury může hledání problému velmi ulehčit. Existuje mnoho přístupů k architektuře DWH, které mohou provoz skladu obecně velmi zjednodušit a související činnosti pak dokáže zabezpečit relativně malý tým o dvou nebo třech lidech. Nevhodná řešení mohou naopak provoz velmi zkomplikovat a snížit dostupnost a důvěryhodnost dat v DWH.
Cíle práce a způsob jejich dosažení Hlavním cílem práce je komplexně popsat problematiku provozu DWH. Tento hlavní cíl se pokusím naplnit pomocí tří dílčích cílů. Prvním dílčím cílem je vysvětlení architektury DWH, druhým cílem je zhodnotit optimální způsob provozu DWH, třetím pak poukázat 9
na chyby, na které jsem narazil v praxi. K dosažení cílů jsem rozdělil práci do tří odpovídajících kapitol: První část je zaměřená na vysvětlení architektury datového skladu. Nejvíce prostoru je věnováno popisu hlavních komponent DWH a rozdílným způsobům jejich technického řešení. Úvodní odstavce rozebírají budování DWH a specifické odlišnosti od klasických IT projektů. Tato oblast sice souvisí z velké části s projektovým řízením, považuji však za důležité jí v práci věnovat určitý prostor. Navíc se ukazuje, že zde existuje mnoho paralel s neúspěchů IT projektů obecně. Na konci první části je zařazen blok věnovaný ETL nástrojům, jejich rozdělení a pozicím na trhu. ETL nástroje hrají při implementaci moderních DWH významnou úlohu a v práci budou ještě mnohokrát zmíněny, proto je zde pro úplnost i krátký historický přehled jejich vývoje. Druhá část se přibližuje způsob řízení a provozu DWH. Jsou zde zařazeny kapitoly rozebírající kroky ETL řešení, způsoby řízení na denní bázi i rozvoje DWH v dlouhodobém měřítku. Dílčí kapitoly popisují jednotlivá prostředí DWH, release management a odlišení vývojových prostředí. Při návrhu a implementaci DWH se na budoucí provoz řešení příliš nehledí, a proto zde stručně analyzuji dopady této v principu chybné, ovšem běžné praxe. Nedílnou součástí bude i popis provozních činností a reportingu chyb. V poslední třetí části se budu věnovat obvyklým chybám, se kterými jsem se setkal v prostředí provozu DWH ve velkém tuzemském podniku. Výhody a nevýhody ETL nástrojů budu demonstrovat na platformě Informatica Powercenter 8.6.1 s hotfixem 13. Kromě nedostatků tohoto konkrétního produktu se poté pokusím analyzovat hlavní nedostatky portfolia tvůrců produktů datové integrace.
Vlastní přínosy práce Konsolidované DWH se nacházejí zejména v největších společnostech na tuzemském trhu. K informacím z tohoto prostředí není snadné se dostat. V práci budu vycházet především ze svých přímých zkušeností a předaných znalostí z provozu DWH v oblasti telekomunikace, bankovnictví a pojišťovnictví. Vzhledem k tomu, že téma přede mnou, pokud je mi známo, na VŠE nikdo nezpracovával, nebylo možno si práci usnadnit navazováním na dostupná již zpracovaná díla. Neexistují žádné obecně stanovené metriky, podle kterých lze hodnotit architekturu nebo jednoduchost provozu DWH. Neexistuje ani žádný obecný návod, jak vytvořit DWH takzvaně „na klíč“. Zdrojová data, dodavatel řešení, zvolené nástroje i rozpočet projektu se vždy liší. Základní prvky architektury však zůstávají veskrze stejné a do jisté míry lze identifikovat optimální řešení, o což se v práci také pokusím. 10
Předpoklady a omezení práce Přestože v oblasti DWH pracuji již rok, největší omezení stále vidím v nedostatku svých praktických zkušeností. Architektury velkých DWH jsou relativně složité, a stejně tak je složité jim porozumět. Mé předchozí zkušenosti z BI/DWH oblasti byly pouze teoretické a první půl rok mi v podstatě trvalo se v řešení zorientovat. A to i přesto, že projekt, na kterém pracuji, má relativně jednoduchou architekturu. Podle ohlasů technicky zdatnějších a zkušenějších kolegů jde spíše o obvyklý případ. I když je každá architektura DWH unikátní, existují mezi nimi jisté analogie. Pracovníkovi s praxí v oblasti pak zabere pochopení nové architektury a ETL procesů mnohem kratší dobu. Problémy samozřejmě mohou nastat, zvláště pokud není předána projektová dokumentace. Poznávání nezdokumentovaných ETL procesů ve formě zpětné analýzy procesů (reengineringu) je však pro zkušené pracovníky ovšem také mnohem jednodušší. Úspěšnost BI/DWH projektů určuje mnoho různých faktorů a v práci je určitě všechny nebudu schopný identifikovat. Pracovně jsem se k datovému skladu dostal až ve stádiu relativní stability. DWH se ovšem stále rozšiřuje a upravuje a v důsledku toho se stále objevují nové problémy a na povrch vyplouvají znovu i některé nedostatky.
Cílový okruh čtenářů Vzhledem k zaměření a cílům by práce mohla být zajímavá nejen pro studenty BI, ale i pro začínající pracovníky v BI/DWH oblasti, od vývojářů po analytiky, zejména by jim mohla pomoci s rychlejší orientací v architektuře a v činnostech spojených s provozem datového skladu. Ze stejných důvodů je práce vhodná i pro projektové manažery, kteří kromě povědomí o architektuře chtějí získat i určité základní technické znalosti. Pro senior pracovníky na vývoji nebo provozu nebude mít práce jako celek pravděpodobně dostatečně velkou hodnotu, přesto jsem přesvědčen, že by v některých jejích částech mohli najít i některé nové informace.
Rešerše zdrojů Manuál k internímu školení Teradata Factory Účelem školení je přiblížit novým zaměstnancům fungování databáze Teradata. Manuál k týdennímu školení obsahuje necelých 3 500 stran a je rovnoměrně rozdělen na stránky se slidy a stránky s podrobným vysvětlením problematiky. Prakticky všechny informace o DBMS Teradata lze nalézt i online, ovšem manuál všechny podstatné informace agreguje na jedno místo v logické posloupnosti. Využití manuálu tak dokáže ušetřit velké množství času.
11
Dokumentace k platformě Informatica Powercenter Desítky dokumentů v elektronické formě zaměřené od instalace a funkcionality produktu po tutoriály k produktu. K dokumentaci jsem získal přístup přes kolegy v zaměstnání. Příkladem dokumentu je manuál ke školení Informatica Developer, který jsem využil ve velmi omezené míře. Jde o placené školení českého dodavatele technologií Informatica (dostupné na adrese http://www.infapartner.cz/). Manuál je určen jako učební materiál pro začínající vývojáře. Projektová dokumentace K dispozici jsem měl projektovou dokumentaci k několika různým projektům. Nejvíce pak samozřejmě k tomu, na kterém pracuji. Vzhledem k tomu, že jde o interní projektové materiály, s využitím takového zdroje samozřejmě souvisí i diskrétnost. Z dokumentace jsem tak mohl interpretovat pouze obecné koncepty řešení. Příkladem projektové dokumentace jsou dokumenty k architektuře datového skladu. Jedná se celkem o 8 dokumentů každý v rozsahu zhruba 20 až 100 stránek, přičemž každý architekturu popisuje z jiného pohledu. Jedná se tak například o:
datovou architekturu popisující vrstvy datového skladu a způsobu zpracování dat,
logickou architekturu popisující jednotlivé kroky ETL řešení,
technickou architekturu popisující hardwarovou a softwarovou infrastrukturu,
provozní architekturu věnující se monitoringu, bezpečnosti a správě změn v prostředí datového skladu.
Dokumentace Oracle (dostupná na http://docs.oracle.com/) Rozsáhlá online dokumentace pokrývající veškeré potřebné aspekty administrace a vývoje v databázi Oracle. Dokumentace se týká posledních verzí databáze označené verzí 11g. K vyhledávání konkrétních témat v rámci těchto stránek jsem sice využíval vyhledávač Google, který tradičně poskytuje nejlepší výsledky. Musím však ocenit i interní vyhledávač Oraclu (dostupný na adrese http://search.oracle.com/). Je sice mnohem pomalejší než Google, výsledky však dává velmi relevantní.
1 Vymezení a definice pojmů 1.1 Business Intelligence Business Intelligence (BI) je v oblasti IT často skloňovaný pojem. Spojení slov Business Intelligence se dá volně přeložit jako obchodní znalost, přesněji jako znalost vlastního podnikání. Právě dobrá znalost toho, jak podnikáme, je nezbytnou podmínkou k naplnění smyslu existence jakékoliv obchodní společnosti – generování finančního zisku. Systémy Business Intelligence jsou schopny nám poskytnout souhrnná data například o 12
našich příjmech a výdajích za jednotlivé pobočky ve zvolených časových intervalech. Můžeme pak jednoduše odhalit, kdy a která pobočka byla či nebyla zisková, jaké jsou sezonní trendy v odbytu našeho zboží, jak zákazníci zareagovali na změnu ceny našeho produktu apod. Pro úplnost přikládám volně přeloženou definici, kterou považuji za nejvýstižnější (1): Business Intelligence (BI) je moderním způsobem užití informačních technologií při analýzách, plánování a rozhodování firmy. Aplikace BI zahrnují činnosti spojené s podporou rozhodování, zprostředkovávání odpovědí na dotazy (query) a vytváření reportů, OLAP technologii, statistickou analýzu, předpovídání trendů a data mining. Přesné a výstižné definice jsou základem dalších úvah v každé oblasti lidského zkoumání. V žádné definici nesmí nic podstatného chybět, ale ani by v ní nemělo být nic navíc. V definici uvedené výše podle mého názoru nic podstatného nechybí, zdá se mi však, že je v ní něco navíc, a to slovo „široká“. Není podstatné, zda je skupina aplikačních programů BI „široká“ či „úzká“. Podstatné je, jaké aplikační programy a technologie zahrnuje a k jakým činnostem slouží. Pokud potřebné aplikační programy a technologie zahrnuje a k uvedeným činnostem slouží, je to nepochybně BI, bez ohledu na to, zda je široká, či ne.
1.2 Primární systémy Data pro BI analýzy pocházejí z primárních systémů. Primární systémy jsou systémy pro podporu obchodních aktivit společností. Podporou je myšlena integrace a automatizace podnikových procesů od výroby a prodeje až po podnikové účetnictví. Nejznámější typy podnikových informačních systémů jsou ERP, CRM, SCM. Primárním systémům se také říká
transakční,
OLTP
(online
transaction
processing).
OLTP
systémy
jsou
optimalizované na zpracování velkého množství malých databázových transakcí v databázovém jazyku SQL (structured query language). V OLTP systémech se jedná nejčastěji o DML (data manipulation language) příkazy INSERT, UPDATE, DELETE, tedy příkazy na vytvoření, úpravu nebo smazání záznamu v tabulce. Transakce charakteristická tím, že se provede buď celá, nebo se neprovede vůbec. Nikdy se nemůže provést pouze částečně. Data v OLTP systémech se týkají převážně záznamů o prodeji produktů. Primární systémy mají přímý dopad na business společností. Ačkoliv jde o netypický příklad, chyba v algoritmu na primárním systému může pro společnost znamenat obrovské finanční ztráty. Existují však i BI výstupy, které mají také velký dopad na business společnosti. Krásným příkladem jsou roční výkazy, které banky na začátku nového roku musí odevzdat České národní bance. Pokud banka termín nestihne, ČNB může bance
13
snížit rating a banka přijde o obrovskou sumu peněz. Pracovníci provozu datových skladů v bankovních ústavech se tak na přelomu roku příliš nevyspí.
1.3 Extract Transform and Load Čím větší je podnik, tím zpravidla generuje více dat. S velikostí podniku zároveň roste složitost informačního systému. Vstupní situace před implementací BI řešení je pak taková, že máme obrovský objem dat rozprostřených na množství systémů. Tyto systémy také z historických důvodů mají data uložená v odlišných formátech. K tomu, abychom tato „hrubá“ data dostali do strukturované podoby vhodné pro další zpracování, se využívají nástroje ETL. ETL, Extract, Transform and Load, neboli načtení, vyčištění a uložení dat. V první fázi se data načtou ze zdrojových systémů do dočasného uložiště, aby se při dalším zpracování zdrojové systémy zbytečně nezatěžovaly. Fáze čištění spočívá v syntaktické (formát) i sémantické (obsah/význam) úpravě dat, tak aby data byla unifikovaná a šly nad nimi provádět analýzy. V poslední fázi ETL se data „nalévají“ do datového skladu. ETL je základním pilířem řešení Business Intelligence. Pokud se tato fáze provede správně, pracujeme dále s daty, které poskytnou reálný obraz podniku. Pokud se provede špatně, výsledné analýzy BI řešení jsou neúplné, v horším případě zkreslené a zavádějící. ETL je také časově nejnáročnější částí BI řešení, jelikož je zde nutné převést logiku úpravy dat do ETL nástroje (programu) a vše nastavit pokud možno co nejpřesněji.
1.4 Data Warehouse Data Warehouse (DWH, datový sklad) slouží jako unifikovaná datová základna pro výstupní analýzy BI řešení. V datovém skladu se tedy uchovávají všechna podniková data ve vhodné formě. Data zde musí být setříděná, mít správný formát, časové označení vzniku záznamu a jeho business platnosti a musí být jedinečné, respektive neobsahovat duplikáty. Definice datového skladu dle Billa Inmona: Datový sklad je subjektově orientovaný, integrovaný, časově rozlišený a neměnný soubor dat pro podporu metod vytváření rozhodnutí managementu. (2) Dva nejznámější přístupy k vytvoření datového skladu se liší tím, zda má jeden sklad obsahovat veškerá podniková data, nebo zda má existovat více menších skladů. Těmto menším skladům se říká datová tržiště (Data Mart). Výhodou datových tržišť je rychlejší uvedení do provozu a menší náklady na implementaci i údržbu díky zjednodušení procesu ETL. Nevýhodou je to, že datová tržiště nemohou sloužit jako zdroj dat pro komplexní analýzy nad všemi daty v podniku, ale vypovídají jen o vybrané části.
14
1.5 Analytické nástroje Analytické nástroje nebo také dotazovací a reportingové nástroje tvoří jakousi pomyslnou finální vrstvu BI řešení. Jedná se o nástroje s grafickým rozhraním, ve kterých se dají tvořit číselné i grafické analýzy dat z datového skladu. Analytické nástroje jsou navržené tak, aby byly uživatelsky přívětivé a mohli je tak využívat přímo zaměstnanci zainteresovaných obchodních oddělení společnosti, kteří by businessu firmy měli rozumět nejlépe. Tento scénář ovšem v realitě nenastává a reporty vytvářejí BI konzultanti dle zadání a potřeb obchodních oddělení. Místo sofistikovanějších analytických nástrojů se navíc v praxi často používá pouze Microsoft Excel.
2 Principy a předpoklady budování DWH 2.1 Budování datového skladu 2.1.1 Předpoklady a omezení velkých BI/DWH řešení Business Intelligence je v širším pojetí sběr, integrace a analýza a interpretace obchodních dat. Čím větší je podnik, tím složitější se tyto činnosti stávají. V současném světě plném informačních technologií využívá nějakou formu Business Intelligence prakticky každá komerční společnost. Od jednoduchých neprocesních ad-hoc analýz u malých podniků po sofistikované korporátní BI řešení. K čemu se BI používá konkrétně? Nejčastěji jde o zpětné vyhodnocení kampaní, úspěšnost jednotlivých částí organizace, počítání výkonů a odměn zaměstnanců. 2.1.1.1 BI řešení v závislosti na velikosti podniku Ačkoliv je BI celosvětově na vzestupu, s ETL nástroji má stále zkušenosti relativně málo pracovníků v oboru IT. I když jsou tyto nástroje relativně jednoduché k pochopení, souvisí s nimi specifická infrastruktura, znalosti a zejména náklady na pořízení. Existují ovšem i bezplatné open source nástroje, ale v prostředí malé společnosti o jedné databázi je mnohem jednodušší vytvořit několik procedur v SQL, než instalovat a vymýšlet řešení v nějakém ETL nástroji. Většinou se nedělají ani žádné analýzy a řešení se nedokumentuje, což při jednoduchých požadavcích není problém. Jakékoliv složitější požadavky sice mnohdy nejsou úspěšně realizovány, pro chod malé firmy to ale nemá žádný velký dopad. U středně velkých podniků je větší množství dat a zpětné analýzy dat jsou složitější. Pokud to však není absolutně nutné (což není nikdy), podniky své systémy ani databáze neintegrují. Integrace je časově i finančně nákladná a nějaké hluboké analýzy vlastně nikdo v podniku nepotřebuje. Stále si tak lze vystačit s procedurálním SQL. Některé podniky přesto sofistikovanější BI řešení využívají. Open source nástroje sice stále 15
nejsou příliš známé ani rozšířené, zajímavý ETL nástroj nabízí společnost Microsoft u vyšších verzí svého SQL serveru. Firmy za cenu v řádech stovek tisíc Kč (existuje několik typů licencí, přičemž se platí za server nebo procesorová jádra viz obrázek níže) mohou využívat právě zmíněný ETL nástroj SSIS. Z ETL nástrojů se kterými jsem se setkal, patří k nejpochopitelnějším a lze pomocí něj jednoduše debugovat. Není příliš vhodný pro velká řešení, tomu se však budu věnovat až v pozdějších kapitolách. Jiným řešením je BI v cloudu. V tomto oboru je známá společnost Salesforce, která podnikům spravuje CRM v cloudu. Jako benefit nabízí Salesforce svým zákazníkům cloudové BI analýzy ve spolupráci se společností Gooddata. Data nahrává zákazník přes internet, Gooddata zpracovává data přes ETL nástroj českého původu s opensource licencí, CloverETL. Gooddata a konkurenční Birst jsou dvě největší společnosti v ČR zaměřené na BI v cloudu a patří i mezi největší dodavatele na světě.
Obrázek 1 Typy a ceny licencí MS SQL server 2012 (3)
Velké podniky mají velmi často obrovské množství dat rozptýlených po různých databázích primárních systémů. Taková společnost prakticky nemá možnost analyzovat svá data, pokud je nějakým způsobem neintegruje. Z toho důvodu si velké společnosti pořizují konsolidované datové sklady (DWH). BI analýzy nad funkčním DWH řešením mohou velkým společnostem ušetřit obrovské částky. Na druhou stranu špatně implementovaný DWH je příčinou pravého opaku, zvýšení nákladů na IT a z plánované konkurenční výhody se projekt DWH stane pro společnost zátěž. Projekty DWH patří v oblasti IT k těm největším. S velkými projekty obecně souvisí i velké problémy. Je složité takové projekty řídit, složité odhadovat pracnost i termíny dokončení 16
jednotlivých etap. Přitom problémy jako zpoždění, nenaplnění cílů, zvýšení nákladů, případně krach projektu a výměna dodavatele nejsou v IT oblasti ojedinělé, spíše se bohužel stávají pravidlem i u malých projektů, natož pak u velkých. I když projekt dopadne úspěšně, po letech vývoje lze náklady spojené s vytvořením datového skladu měřit v řádech desítek nebo stovek milionů korun a návratnost investice lze posléze odhadovat jen obtížně 2.1.1.2 Závislost na outsourcingu při budování DWH Vývoj prakticky vždy probíhá ve formě outsourcingu, jelikož společnosti nedisponují znalostí ani pracovníky k nasazení DWH. Vývoj DWH je tak prakticky vždy delegován na specializovanou externí společnost. Tato situace není optimální, je ale až na výjimky bohužel nevyhnutelná. V rámci snižování nákladů se ovšem přistupuje na mnohdy značně nešťastné řešení. Outsourcovaná firma dále outsourcuje činnosti do asijských zemí, zejména Indie a Pákistánu kvůli levnější pracovní síle. To samo o sobě vyžaduje jiný, direktivnější způsob řízení. Outsourcing v principu funguje pouze ve striktně procesně založené společnosti. Tuto činnost většinou nemohou projektoví manažeři ani koordinovat, jelikož nemají dostatečné technické znalosti. Proto vznikla role vývojových koordinátorů. Provoz velkých řešení DWH je po startu produkčního stavu řízen také pomocí outsourcingu a zpravidla dodavatelem, který DWH implementoval. V rámci snižování nákladů a závislosti na dodavateli se tato činnost někdy zajištuje vlastními silami. Příkladem může být tuzemský podnik, který 2 roky platil 600 tisíc měsíčně externímu dodavateli za provoz DWH. Po vypršení kontraktu si dodavatel řekl o dvojnásobek, podnik novou smlouvu neuzavřel a místo toho si vytvořil vlastní tým, jehož náklady byly v součtu pouze 300 tisíc. 2.1.1.3 Praktický příklad využití DWH Podpora managementu marketingových kampaní přes specializovaný software ARM (Aprimo relationship management) u telekomunikační společnosti. Denně automaticky probíhá až 150 kampaní na úzce cílené skupiny (200-1000 lidí) formou SMS. Příkladem cílové skupiny jsou zákazníci, kteří mají předplacenou kartu a utrácejí vyšší částky a firma jim proto nabízí paušál. Nebo může jít o zákazníky, kteří svým chováním vykazují tendence odejít ke konkurenci. Indikace takového rozhodnutí může být snížení využívání služeb operátora od volání po využívání internetu. Seznam takových lidí lze automaticky předat oddělení pro zákaznickou podporu, které se pomocí výhodných nabídek snaží zákazníka udržet. Všechny tyto informace o zákaznících lze získat z datového skladu.
17
Jiným zajímavým využitím z hlediska marketingu je propojení marketingového systému s datovým skladem obsahujícím polohu trvalého bydliště občanů ve formě GPS souřadnic. Údaje o adrese má ve formě GPS souřadnic například Česká pošta, ale polohu dle adresy zákazníků si může vyhledat vlastně jakákoliv společnost, které zákazníci adresu při objednávce zadají. Nově otevírané obchody v lokalitě tak mohou (za úplatu) prostřednictvím operátora všechny své zákazníky v okruhu o určené velikosti o této skutečnosti informovat. V mém okolí jsem se s takovou praktikou již setkal, takto na sebe upozorňoval jeden nejmenovaný retailový řetězec skrze nejmenovaného mobilního operátora. Telekomunikační společnost s aktivním datovým skladem by teoreticky mohla cílit i na aktuální polohu zákazníků. Každý zákazník, který by prošel do nějaké vzdálenosti od obchodu právě zaměřeného marketingovou kampaní, by na svůj mobil dostal reklamní sms zprávu lákající ho na slevové výhody. I když je sledování polohy zákazníků omezené legislativou, podle mne je jen otázkou času, kdy se nám takové zprávy začnou na telefonech objevovat. 2.1.1.4 Shrnutí Konsolidovaný DWH je dlouhodobý projekt, který lze naplno začít využívat mnohdy až po několika letech. DWH se buduje ve společnostech, kde kvůli velkému objemu dat není možné využít klasické přístupy. DWH je řešení pro velké a bohaté společnosti, které chtějí část svých finančních rezerv v dlouhodobém horizontu vyměnit za lepší (faktově založenou) podporu svého businessu. 2.1.2 Etapy vývoje DWH IT projekty lze rozdělit do několika vývojových etap. V následujících šesti subkapitolách se budu věnovat jejich rozdělení na etapy obvyklé v prostředí mezinárodní společnosti. U každé etapy je zmíněn i její ekvivalent z metodiky MMDIS z VŠE. Etapy vývoje DWH zde zmiňuji jednak pro úplnost, ale také i kvůli zdůraznění specifik DWH oproti běžnému IT projektu. 2.1.2.1 Idea formulation, IF (MMDIS – úvodní studie) Specifikace zadání na obecné úrovni. Jaké systémy bychom chtěli integrovat, jaké výstupy budou potřeba. Požadavek na řešení vychází z interních potřeb podniku. 2.1.2.2 Project definition PD (MMDIS – globální analýza a návrh) Druhá fáze má dát odpověď na otázku, jakým způsobem řešení realizovat z projektového hlediska. PD trvá několik měsíců. Vypracovává se projekt řešení – odhady nákladů, pracnosti a analyzují se případné dopady do ostatních systémů. Po dokončení specifikace se vypisuje výběrové řízení na dodavatele řešení. 18
2.1.2.3 Solution design SD (MMDIS – detailní analýza a návrh) Ve třetí fázi se rozpracovává řešení do technického detailu ve spolupráci mezi interními pracovníky a zvoleným externím dodavatelem řešení. SD v projektech DWH trvá měsíce až roky. Probíhá kompletní analýza řešení. Jaké části budou spolu komunikovat, performance, analýza kostry hlavních prvků řešení, tvorba UML diagramů (relačních, dataflow). Výstupem této fáze je částečně dokončená základní technická dokumentace – datový model, návrh architektury. Tato fáze je zakončená takzvaným „proof of concept“, kdy vybraný dodavatel řešení předvede část funkčního ETL řešení. 2.1.2.4 Implementace (MMDIS – implementace) V této fázi se dopracovává datový model a z kompletních částí analytici vytvářejí předpisy transformací. Podle předpisů transformací vytvářejí vývojáři buď kód v ETL nástroji nebo pomocí komplexních SQL procedur. Procesně je lepší vytvářet řešení pomocí ETL nástroje, existují však stále důvody pro tvorbu pomocí procedur. Prvním důvodem je hledisko výkonnosti. ETL nástroje na ETL serverech zpracovávají data pomaleji, než dobře napsaná SQL procedura na mnohem silnějším serveru datového skladu. Druhým, avšak v principu špatným, důvodem je situace, kdy jsou senior vývojáři zvyklí na psaní SQL procedur místo toho, aby řešení vyvíjeli v ETL nástroji. Za třetí jsou zde i důvody „politické“. Vzhledem ke komplexnosti řešení je pro vlastníka DWH mnohem výhodnější mít řešení vyvinuté ve standardním prostředí ETL nástroje. Pro dodavatele je naopak výhodnější dodat složitější řešení řízené procedurami, které vlastník nebude schopný rychle a efektivně převzít a dále rozvíjet. Vlastník DWH je tak donucen k uzavření nové smlouvy s původním dodavatelem. Během implementace musí probíhat i testování kódu ze strany vývojářů (unit testy a systémové testy) i zákazníka (user acceptance testy). K testování se později ještě vrátím. Implementace končí iniciálním loadem datového skladu. V iniciálním loadu načteme do skladu všechna data z primárních systémů k určenému datu. Datem iniciálního loadu tak začíná historie dat v datovém skladu. I když se před samotným provedením iniciálního loadu provádí jeho předběžné simulace, stejně může dojít k jeho opakování. V závislosti na objemu dat a infrastruktuře může iniciální load zabrat několik dní, ale i týdnů. Po dokončení iniciálního loadu je nutné do skladu postupně načíst i nová data, za dny, kdy běžel úvodní load. Po úspěšném provedení iniciálního loadu se řešení předává zákazníkovi. Jen pro představu, tato fáze na mém projektu trvala 2 roky při plném využití 12 vývojářů. Řešení se skládalo celkem zhruba ze 4 000 mapování (logiky ETL úlohy) v nástroji Informatica Powercenter. Z toho 3 000 mapování bylo z větší části předgenerovaných vlastním nástrojem, který informatická mapování generoval na 19
základě vstupních parametrů převzatých z datového modelu. Některé komponenty ETL řešení byly zakoupeny od subdodavatelů (Globtech – historizační a deduplikační komponenta). Důležitým prvkem úspěchu celého řešení jsou zkušenosti dodavatele. S každým dalším úspěšným DWH projektem se zlepšují postupy vývojového týmu. Standardizuje se metodika a vývoj a čas je věnován vylepšením DWH, které se posléze nabízejí zákazníkovi jako přidaná hodnota. 2.1.2.5 Produkční fáze – Small enhancement SE (MMDIS – provoz a údržba) Po dokončení implementace a iniciálního loadu rozvoj DWH nekončí. Vývoj datového skladu musí reflektovat požadavky zákazníka a rozvoj (změnu dat) na primárních systémech. Této fázi bude věnována většina textu ve druhé a třetí části práce. Požadavkem vlastníka a snahou dodavatele řešení by mělo být vytvoření DWH, který lze trvale udržovat a rozvíjet. 2.1.3 Výchozí dokumentace pro vývoj DWH 2.1.3.1 Architektura DWH Dokumentace architektury DWH se skládá z několika dílčích dokumentů, které popisují řešení z několika pohledů. Je zde popsána HW infrastruktura jednotlivých prostředí (produkční, testovací, vývojové), účel a způsob propojení jednotlivých HW prvků. Dále je v architektuře obsažen popis vrstev datového skladu (Stage, Target, Datamarty) a technické zajištění zpracování dat. Je zde samozřejmě popsáno i to, jak budeme data zpracovávat logicky, respektive jaké kroky bude ETL řešení obsahovat. Součástí architektury by měl být i dokument týkající se provozu řešení od monitoringu a release managementu po backup a restore strategii. Architektura DWH částečně vychází z datového modelu. 2.1.3.2 Datový model DWH Datový model DWH popisuje vztahy mezi tabulkami a daty v (budoucím) datovém skladu. Nejznámější software k vytváření datových modelů je Powerdesigner. Na správnosti datového modelu závisí kvalita celého řešení. Datový model vytváří a upravuje datový modelář ve spolupráci s analytiky, kteří z datového modelu déle vytvářejí předpisy transformací pro vývojáře. Role datového modeláře kromě své náročnosti tak sebou nese i obrovskou zodpovědnost. Datový model vychází z rozložení dat na stávajících informačních systémech zákazníka. Ty je nutné nejprve zmapovat a poté vytvořit modely jednotlivých vrstev DWH od stage (L0) a target (L1) po datamarty (L2) a to tak, aby odpovídaly zadání zákazníka. Z datového modelu vytvářejí analytici předpisy transformací dat, zadání pro vývojáře.
20
2.2 Zdrojové systémy podniku 2.2.1 Extrakce dat ze zdrojových systémů Zdrojem dat pro datový sklad jsou primární systémy podniku. Primární systémy generují data s různou důležitostí v rozdílných frekvencích a v odlišných datových formátech. Jak data z primárního systému získáme? Existují tři základní typy přístupu, se kterými jsem se setkal v praxi a nalezl v literatuře a na internetu (4). 2.2.1.1 Přístup k datům napřímo První variantou je přístup přímo do databáze primárních systémů. Tato varianta je sice nejjednodušší z hlediska souvisejících činností pracovníků provozu primárního systému, ovšem tím výhody končí. Největší nevýhodou je nízká bezpečnost přímého přístupu k datům, což je pro většinu korporací neakceptovatelné. Dále se při extrakci dat nepravidelně zatěžují databáze primárních systémů, což je de facto opět problém i z hlediska bezpečnosti. Struktura dat na primárních systémech je uzpůsobená tak, aby co nejvíce vyhovovala způsobu zpracování ze strany aplikací. Data jsou tak rozdrobená do množství tabulek a souborů v různých datových typech, což ve výsledku znamená i komplexnější ETL procesy. Z výše uvedených důvodů se tento způsob u velkých společností až na výjimky nevyužívá. 2.2.1.2 Přístup skrze rozhraní primárních systémů Druhou variantou je přístup skrze rozhraní (interface) primárního systému. Rozhraní vytváří dodavatel (provozovatel) primárního systému a je za něj i zodpovědný. Primární systém může mít jedno nebo více rozhraní podle typu a struktury uložených dat. Rozhraní může být ve formě databázových pohledů nebo aplikačních služeb, které poskytují požadovaná data. Výhodou řešení je vyšší bezpečnost, jasné rozdělení zodpovědnosti a určité zjednodušení oproti přímému přístupu na primární systémy. Nevýhodou je nutnost úzké spolupráce (předávání informací) mezi pracovníky DWH a primárních systémů. Z 5 českých různě starých DWH projektů, ze kterých jsem v této práci čerpal, je na rozhraní primárních systémů založen pouze jeden. Ostatní využívají řešení, kterému se budu věnovat v následující kapitole. Přístup k datům skrze rozhraní primárních systémů se využívá v jedné nejmenované tuzemské bance. Podnik má zhruba 50 rozhraní primárních systémů v rozdílných datových kódováních (ANSI, ISO, Windows, UTF-8). Rozdílnost kódování je dána tím, že systémy v bance přibývaly postupně a měli je na svědomí různí dodavatelé s rozdílnou metodikou návrhu. O každé rozhraní se starají správci daného primárního systému zvlášť. Rozdílnost datových formátů částečně blokovala rozšíření ETL a z hlediska provozu působila komplikace při verifikaci přicházejících dat. 21
2.2.1.3 Přístup prostřednictvím pravidelných dodávek dat Pravidelný způsob předávání dat má na starosti opět provozovatel primární aplikace. Data se dodávají v dohodnuté struktuře v dohodnutých intervalech na dohodnuté uložiště. V tomto případě se data většinou dodávají ve formě souborů, vzhledem k jednoduchosti ukládání, exportu i importu. V principu je tento způsob nejlépe zvládnutelný z hlediska systémových procesů, poskytuje nejvyšší bezpečnost řešení i optimální plánování vytížení primárních systémů. Na druhou stranu toto řešení proti předchozímu typu mírně ztrácí pružnost a ponechává problém s rozdílným kódováním. Mimochodem ve 2 ze 4 DWH řešení využívající pravidelné dodávky dat se na zvoleném uložišti extraktů objevují problémy s nedostatečnou kapacitou. V obou případech je důvodem špatně zvolený proces zálohování a odmazávání extraktů i zvolení velikosti uložiště. Extrakty z minulosti se na uložišti uchovávají kvůli případnému „vrácení“ DWH, pokud by se v datech vlivem chybného vývoje objevila neopravitelná chyba. Více se tomuto tématu budu věnovat ve druhé části diplomové práce věnované provozu a řízení DWH. Sofistikovanější datové sklady využívají takzvanou transportní vrstvu, malé ETL řešení, které by se také dalo říkat Prestage. Transportní vrstva je fyzicky uložena na jednom transportním serveru. Využití transportní vrstvy sice komplikuje infrastrukturu, výrazně ovšem zjednodušuje tradiční ETL řešení. Místo připojení na 50 míst nám stačí připojení pouze na jedno. Nicméně díky transportní vrstvě lze tradiční ETL řešení mnohem jednodušeji implementovat (částečně vygenerovat) i spravovat. Primární systémy mohou dodávat data v několika různých rozhraních. Rozhraní je v tomto případě vnímáno jako sada definovaných extraktů či tabulek s danou periodicitou a definovaným místem uložení v transportní vrstvě. V jednom časovém okně se zatíží primární systémy a data se odešlou do transportní vrstvy. Dodávka dat v jednom rozhraní v konkrétním čase se nazývá dávka. Data jsou do transportní vrstvy přebírána jako celé dávky a nejsou nad nimi prováděny žádné transformace. 2.2.1.4 Aktivní předávání dat Ještě pokročilejší variantou než načítání dat do transportního serveru je aktivní řešení pomocí technologie Oracle GoldenGate a ODS, operačního datového skladu. Řešení funguje podobně jako transportní server, jen v reálném čase. ODS nahrazuje transportní server a Oracle GoldenGate zabezpečuje zasílání dat. Jakmile proběhne úprava dat na primárním systému, GoldenGate úpravu zachytí například ve formě SQL scriptu (DDL nebo DML). Script se poté skrze GoldenGate zašle do ODS a spustí nad tabulkou v ODS. Místo upravených záznamů v tabulce, kterých mohly být tisíce, tak může přijít pouze update script o několika řádcích. Komunikace probíhá online, v ODS tak máme prakticky aktuální stav dat z primárního systému. 22
Obrázek 2 Architektura Oracle GoldenGate (5)
2.2.1.5 Shrnutí Shrnutím výše uvedených informací lze odvodit, že kvalita předání vstupů z primárních systémů je přímo úměrná složitosti infrastruktury a výši počátečních investic. Odměnou je pak kromě kvality vstupů i velké zjednodušení architektury ETL. Mluvíme však o řešení pro velké podniky. Malé podniky využívají pro ETL řešení přímý přístup do databáze. 2.2.2 Typy kódování dat Kromě databázových formátů jako integer, string nebo date je při operacích s daty velmi důležité zohlednit i to, v jakém znakovém kódování jsou uloženy. Nejužívanější kódování je ASCII, standardy ISO (latin-2 s češtinou), Windows-1250 (čeština), Windows-1252 (západoevropský, také známý jako ANSI) a Unicode (UTF-8, UTF-16 a UTF-32). Rozdílné kódování dat je dané historickým vývojem podnikových informačních systémů. Již zhruba dva až tři roky je standardní při vývoji v javě nebo dotnetu používat kódování UTF-8 pokrývající všechny znaky i známé jazyky. V minulosti tomu tak nebylo, přičemž velké podniky postupně dokupovaly nové systémy od různých dodavatelů. Výsledkem je tak mnoho dat v různých typech kódování, což samozřejmě komplikuje datovou integraci. 23
ASCII je 7 bitový standard (maximálně 27 = 128 znaků), což dostačuje k pojmutí anglické abecedy (velkých a malých písmen), interpunkce, čísel, klávesových zkratek (ctrl-c) a netisknutelných znaků jako TAB, RETURN, BACKSPACE (6). Standardy ISO a Windows zapisují data 8 bitově neboli 1 byte (maximálně 28 = 256 znaků). Unicode je tabulka znaků všech existujících abeced a obsahuje více než 110 tisíc znaků (obsahově může pojmout více než milion znaků) a je tím pádem i neuniverzálnější. Nejznámějším typem unicode je UTF-8, které používá proměnnou délku znaků od 1 byte do 6 byte (8-48 bit). Z hlediska ETL procesů je vždy nutné znát kódování zdrojových dat a jejich omezení. Nastavíme-li text v češtině do západoevropského kódování Latin1, nebudou se nám správně zobrazovat písmena „č“ a „ř“, švédského zákazníka zase není možné zapsat do systému provozovaného na východoevropském Latin2. V případě ASCII se budou na výstupu správně zobrazovat pouze písmena bez diakritiky. ETL nástroje pouhou změnou kódování nedokážou znaky automaticky přeložit. Zdálo by se tak výhodné využívat všude formát UTF-8, který pokrývá všechny známé znaky. Výhoda využití omezených datových formátů (INT) a datových kódování (ASCII) spočívá v menší velikosti dat, což se projeví vyšší rychlostí zpracování. Data v 7 bitovém ASCII pošleme po síti a zpracujeme vždy o něco rychleji, než stejná data v kódování ISO, Windows nebo UTF-8. Při malém objemu dat tento problém nemusíme řešit, při přelévání tabulek o desítkách až stovkách milionů záznamů můžeme ušetřit obrovské množství času. Jako částečná řešení mohou sloužit alternativní formáty, například Alt32 UTF-8 společnosti Oracle. Prvních 256 znaků (1-255) se posílá jako ASCII. Pokud 256 znaků pro zápis dat nestačí, zbytek se posílá v UTF-8. Toto kódování tak teoreticky splňuje požadavky jak na velikost, tak na pokrytí znaků. Problém však může být ve vyhodnocení dat – jaká část je v ASCII a jaká v UTF-8. V praxi jsem se s Alt32 UTF-8 v DWH nesetkal a výhody a nevýhody tak nemohu bohužel hodnotit, teoreticky by však tento formát mohl řešit mnoho problémů spojené s kódováním dat. 2.2.3 Údržba číselníků Číselníky obsahují výčtové typy, které se příliš nemění. Bez číselníků by transakční systémy nemohly fungovat (7). Typickým příkladem číselníku je katalog měn, seznam poboček podniku nebo seznam měst. Seznam produktů se často mění, proto není považován za číselník. Naopak příkladem číselníku je kategorizace produktů (hypotéka určená k výstavbě, neúčelová hypotéka, hypotéka s cílem refinancování úvěru). Stejně tak existuje kategorizace, identifikace zákazníků. I když by se číselníky neměly teoreticky příliš měnit, velké podniky mají velké množství číselníků a každý den se nějaký z nich změnit může. Logicky tak vystává problém i s jejich stárnutím v DWH a otázka o způsobu aktualizací. Číselník může obsahovat od několika záznamů (katalog 24
měn) nebo také tisíce řádků (produkty v retailovém řetězci). Teoreticky může existovat číselník o jediném záznamu. Stejně jako ostatní data se číselníky mohou do DWH dostat ve formě tabulek nebo souborů. Kvůli malé velikosti číselníků a jednoduchosti načtení se často volí forma souborů. Při manuální úpravě číselníku je vždy nutné ho následně uložit ve správném kódování. Na projektu jsme se setkali s případem, kdy uživatele BI hlásili nekorektní data v tabulce smluv v DWH, ačkoliv z hlediska ETL procesů proběhlo vše korektně. Brzy se ukázalo, že problém byl způsoben hotfixem číselníku, kdy vývojář upravený číselník uložil ve výchozím kódování Excelu. ETL nastavené na jiné kódování dat záznamy zpracovalo chybně a místo názvů se v tabulce zobrazovaly nesmyslné znaky. Kromě formy je nutné vymyslet i způsob, jak a kdy číselníky aktualizovat. Existují dva způsoby aktualizace. Prvním způsobem je aktualizace číselníků na produkčním DWH při nasazování nového kódu v rámci release managementu. Nové číselníky se tak aktualizují nepravidelně v průměru 5-15× za měsíc v závislosti na naplánovaných projektech. Druhým způsobem je aktualizace na denní bázi v rámci loadu. Stejně jako u běžných dat může načítání probíhat
přímým
přístupem
do
databáze,
formou
rozhraní
nebo
v rámci
pravidelných/nepravidelných dodávek dat. Z hlediska bezpečnosti se volí poslední varianta. Velké podniky mají velké množství primárních systémů a z toho důvodu i počtu a typů číselníků. Ze své praxe mohu zobecnit, že podniky většinou využívají oba způsoby zároveň, tedy v rámci loadu i release managementu. Rozdíl je pouze v tom, že některé podniky vyžadují aktualizaci na denní bázi a ostatní nepravidelně. Požadavky na frekvenci aktualizací v DWH tak závisí na typu podnikání vlastníka DWH. U běžného datového skladu bude nejkratší perioda aktualizace jeden den, u aktivního datového skladu teoreticky do několika minut po vytvoření nového záznamu v číselníku.
2.3 Způsob uložení dat v DWH Relační databáze je srdcem datového skladu. Správné navržení a stabilní fungování databáze rozhoduje o úspěšnosti DWH projektu. V této kapitole se budu věnovat odlišnostem DWH DBMS oproti klasickému relačnímu DBMS. I když tato samotná kapitola by svým obsahem mohla pokrýt celou diplomovou práci, pokusím se ve stručnosti pouze shrnout principy DWH DBMS a vysvětlit hlavní rozdíly oproti klasickému DBMS. Toto téma považuji za velmi důležité, jelikož mnoho podniků volí pro své datové sklady stejné DBMS jako používají v OLTP systémech. Datový sklad samozřejmě s klasickou databází funguje, ovšem taková volba přináší mnoho problémů od nižší výkonnosti po složitější údržbu datového skladu. Musím podotknout, že opět 25
vycházím z prostředí velkých DWH s objemem dat v řádu od deseti až po stovky TB, kde se výhody DBMS a specializovaného hardwaru projeví nejvíce. Pro podniky s databázemi o objemu dat v řádu několika GB jsou naopak vhodné klasické DBMS, jelikož benefity specializovaného DWH řešení poměrově přebijí příliš vysoké počáteční náklady. 2.3.1 Data Warehouse Appliance Databázové systémy můžeme rozdělit na dvě skupiny. První skupinou jsou klasické relační databáze, které byly původně navržené pro OLTP, například produkty společnosti Oracle. Druhou skupinou jsou databáze, které byly specificky navržené pro účely datových skladů (DWH RDBMS). Databáze specificky navržené pro DWH jsou založené na technologii paralelního zpracování (MPP – Multi Parallel Processing). MPP databáze se dodávají společně s hardwarem, který je pro tento typ databází přímo optimalizován. DWA, data warehouse appliance, je často skloňovaná zkratka v oblasti datových skladů. Termín DWA lze volně přeložit do češtiny jako „zařízení datového skladu“. DWA představuje spojení databázového softwaru a serveru, tedy jakési all-in-one řešení. Zákazník si koupí speciálně navržený server, ve kterém je předinstalovaný databázový software. Výše zmíněná MPP databázová řešení se prodávají právě formou DWA s takzvanou shared nothing architekturou (SN). Server založený na SN architektuře je rozdělen na několik nezávislých částí, které mají své vlastní procesory, RAM a disková pole. Jak si v následujících odstavcích ještě ukážeme, DWA jsou velmi výkonné, nenáročné na údržbu, optimalizované pro dotazy nad velkým množstvím dat a výborně škálovatelné. Pokud dojde místo nebo nestačí výkon, stačí dokoupit další server. V podstatě jde o ideální řešení pro velké datové sklady. Jaké mají DWA nevýhody? Jednu velkou, totiž velmi vysokou pořizovací cenu. Cena se většinou počítá podle kapacity systému, orientačně začíná zhruba na jednom milionu korun za 1 TB diskového prostoru. Pro větší firmu tak DWA znamená investici v řádech desítek milionů korun. Mnohem levnější je pořídit si zvlášť server a databázové licence, například od společnosti Oracle. O volbě řešení většinou nakonec rozhodují manažeři, kteří o IT nic nevědí, a jejich hlavní motivací je co nejvíce ušetřit. V některých případech je šetření samozřejmě opodstatněné, nicméně je otázka, zda není horším řešením vydávat peníze průběžně za větší množství databázových administrátorů, bez jejichž zásahů se výkonově nedostatečný DWH nedá provozovat.
26
2.3.2 Architektura řešení DWA Teradata 2.3.2.1 Uživatelské rozhraní Teradata dodává ke svým řešením (8) balíček nástrojů nazvaný „Teradata Utility Pack“ (9). Uživatel přistupuje k databázi datového skladu nejčastěji prostřednictvím dvou aplikací Teradata SQL Assistant a Teradata Administrator. Teradata SQL Assistant je nástroj pro editaci a spouštění SQL dotazů. Teradata Administrator je nástroj pro administraci databáze. Administrátor umožnuje i spouštění SQL dotazů, jejich editace však není o mnoho lepší než v Notepadu. Oba uvedené nástroje jsou spustitelné v prostředí MS Windows. Existují i jiné edice těchto nástrojů určené pro jiná prostředí jako Linux nebo MacOS. Oracle naproti tomu používá aplikaci SQL Developer, která integruje obě funkcionality. SQL developer využíváme pro připojení k Oracle databázi transportního serveru. Osobně více preferuji uživatelský interface od Teradaty, je to ale pravděpodobně hlavně proto, že jsem na něj více zvyklý. Ke sledování vytížení serveru se využívají jiné nástroje, těm se budu blíže věnovat až ve třetí části práce. Přestože každý DBMS používá k uložení dat jiný formát, na databázi Teradaty se lze připojit přes aplikaci SQL Developer od společnosti Oracle. SQL Developer má mnohem více funkcí, než SQL Assistant, při připojení na Teradatu je však většina z nich nefunkční. Stejně tak je možné se pomocí SQL Assistanta připojit na databázi Oracle. Aplikace se na databáze připojují skrze ODBC (Open Database Connectivity), standardní aplikační rozhraní pro přístup k datům. K databázi Teradaty se tak lze připojit i přes Eclipse s Teradata Plug-inem. 2.3.2.2 Komponenty DWA Teradata Architektura
řešení
Teradata se skládá ze čtyř základních komponent. Parsing Engine (PE) je komponenta, která slouží jako
prostředník
v komunikaci aplikací
a
mezi databází.
Parsing Engine funguje jako
řídící
databáze.
jednotka Obrázek 3 Komponenty DWA Teradata (51)
BanYan NETwork (BYNET) slouží jako prostředník pro předávání interní komunikace mezi PE a AMPem. BYNET je kombinace vysokorychlostního síťového softwaru a 27
hardware. BYNET je doplňován PDE (Parallel Database Extension), který slouží jako rozšíření Operačního systému, tak aby šlo Teradatu spustit v paralelním prostředí. Access Module Processor (AMP) je samostatná nezávislá jednotka s vlastním CPU a RAM. AMP obsluhuje část databáze, respektive má kontrolu nad částí každé tabulky v systému. AMP vykonává veškerou práci nad daty, generování odpovědí, řazení záznamů, agregování dat, formátování a konvertování dat. Data, se kterými AMP pracuje, jsou fyzicky uložené na Vdisku. Virtual disk (Vdisk) je virtuální diskový prostor, který je přiřazen AMPu a využívá se k vlastnímu ukládání dat. Vdisk vždy obsluhuje pouze jeden AMP a je tvořen několika fyzickými disky. 2.3.2.3 Proces fungování TD PE nejdříve zkontroluje, zda je SQL dotaz validní. Jednotlivé části dotazu (jména tabulek) poté přiřadí k systémovým identifikátorům uložených v data dictionary (jakémsi databázovém slovníku). Dále musí proběhnout kontrola, zda má uživatel právo k dotazovaným objektům přistupovat. Následně PE vytvoří exekuční plán. Exekuční plán je vlastní interpretace SQL dotazu. PE nejdříve vypočítá nejefektivnější způsob realizace SQL dotazu. Výsledek rozdělí do několika přesně definovaných kroků. PE si zároveň ukládá dříve provedené exekuční plány do cache, aby je při opakovaném volání nemusel znovu generovat. Kroky exekučního plánu PE předá skrze integrační vrstvu MPL (BYNET) do AMPu. Práci s daty vykonávají jednotlivé AMPy. Výsledná data, která se vrátí jako odpověď z AMPu se poté posílají přes BYNET a PE zpět do aplikace k uživateli. 2.3.3 Architektura řešení DWA Exadata Exadata je řešení DWA od společnosti Oracle. Jedná se o all-in-one řešení postavené na DBMS Oracle 11g a silného serveru, který se snaží rychlost i funkcionalitu DBMS co nejvíce hardwarově podpořit. První Exadata byla postavena na serverech společnosti HP. Vykazovala problémy s řešením uložiště a příliš dobře se neprodávala (10). V roce 2010 odkoupil Oracle společnost Sun a servery pro Exadatu ve verzi 2 a 3 již dodává Sun. Motivace pro vytvoření Exadaty vycházela kromě požadavků na zvýšení rychlosti také ze snahy efektivněji konkurovat zavedeným DWA řešení jako byla Teradata nebo Netezza. Do té doby disponoval Oracle pouze SMP řešeními. SMP (symmetric multiprocessing) je označení pro klasický systém s více procesory, které sdílejí stejnou RAM. Exadata je hybridní SMP systém (sdílená RAM) s MPP prvky (storage servery).
28
2.3.3.1 Komponenty DWA Exadata Kromě rychlosti HW komponent přidává Exadata i napodobení přístupu konkurence. Princip spočívá v přenesení výkonu blíže k datům. V tradičním OLTP řešení databáze přistupuje k datům napřímo do uložiště. Exadata přidává takzvané storage servery (nazývané také jako storage cell), jejichž účel spočívá v jakémsi předzpracování
dat.
Tento
přístup
značně
redukuje množství dat, které přicházejí z uložiště ke zpracování do DBMS. Menší tok dat uleví pomalému
uložišti
a
síťové
infrastruktuře
serveru. Zároveň klade nižší požadavky na zpracování pro samotnou DBMS. Storage server je jakýmsi ekvivalentem Teradata AMPu. Rozdíl je v tom, že data jsou po AMPech rozdělena rovnoměrně, zatímco ve storage discích
Obrázek 4 Komponenty DWA Exadata (46)
ne. Teoreticky by tak měly při SQL dotazu pracovat všechny AMPy a Teradata by měla ve srovnání s Exadatou lépe benefitovat z MPP architektury. Snížením objemu dat protékajících ze storage serverů do DBMS odstranilo „úzké hrdlo“ klasického serveru s Oraclem. O kolik je rychlejší Exadata proti běžnému přístupu? V prezentačním videu k Exadatě je zmíněno až desetinásobné zrychlení náročných ETL a BI operací (11). Exadata se kromě předzpracování dat na storage serverech snaží ulevit síťové infrastruktuře a uložišti i jinak. 2.3.3.2 Hot Cold data Data, se kterými Exadata často pracuje, se nahrávají na SSD disky. Teradata stejným způsobem rozděluje data na takzvané „hot“ (zhruba 25 % celku) a „cold“ (75 %). „Hot“ data jsou taková, se kterými se pracuje nejčastěji. V „hot“ kategorii jsou automaticky obsažena data přibližně za poslední měsíc. V kategorii „cold“ dat jsou všechna ostatní historická data. Oracle SSD disky prezentuje tak, že jsou výkonné jako RAM a levné jako klasické disky. SSD jsou ve skutečnosti samozřejmě mnohem dražší než HDD a zdražují celý server, výkonnostně však za RAM na rozdíl od HDD až tolik nezaostávají. Zpracování dotazů nad velkými tabulkami se stovkami milionů záznamů SSD znatelně urychlí. Dalším vylepšením je komprese dat. Komprese ve výsledku opět uleví síťové infrastruktuře za cenu vyššího zatížení procesorů DBMS. Komprese je zvolena tak, aby kompromis mezi 29
datovými toky a náročností zpracování byl co nejvýhodnější. Síťová infrastruktura je mimo jiné v Exadatě posílena komunikační linkou InfiniBand s propustností 40 Gbit/s. V porovnání s tím je nejrychlejší Ethernet schopen dosáhnout propustnosti pouze 10 Gbit/s. Teradata využívá svou vlastní komunikační linku BYNET u které rychlost neuvádí. Systém funguje na jiném principu a propustnost BYNETu je jednoduše brána jako dostačující. 2.3.3.3 Exadata jako business appliance Pokud se však nějaká společnost rozhoduje o možnosti upgradu svých business OLTP serverů na databázi Oracle, Exadata se jistě nabízí jako jednička v oboru. V marketingovém videu Oraclu jsou zmíněné dva příklady benefitů Exadaty. Jeden z největších poskytovatelů komunikačních serverů vyměnil 11 klasických OLTP serverů za 1 Exadata server. 250 TB dat se zkomprimovalo na 25 TB a výkon se zvýšil 10×. Druhým příkladem byla japonská společnost, která 36 klasických serverů vyměnila za 3 Exadaty, přičemž výkon se zvýšil 8x. Ve videu není zmíněné, jaké servery zmíněné společnosti nahrazovaly. Dá se však předpokládat, že to byly právě starší servery s Oraclem. 2.3.3.4 Shrnutí DWA Exadata Když to shrneme, Exadata je hybridní kombinací silného OLTP serveru a storage serverů. Spíše než klasické DWA lze Exadatu chápat jako OLTP apliance, kterou lze využít i pro potřeby data warehousingu. Exadata těží ze silného hardwaru. Nicméně z hlediska využití DWH jeho nevýhoda oproti Teradatě tkví v DBMS. Na rozdíl od Oraclu byl DBMS Netezzy nebo Teradaty navržen specificky pro účely DWH. Netezza i Teradata kvůli odlišnému DBMS samozřejmě využívají i jinou architekturu serveru. Zmíněné HW prvky jako SSD, vysokou kapacitu RAM, rychlou komunikační linku a podpůrnou kompresi dat využívají všechny DWA. Exadata se slabším softwarem se konkurenci snaží dohnat větší silou hardwaru. Rozdílům v DBMS se budu věnovat v následující kapitole. 2.3.4 Srovnání DBMS Oracle a Teradaty Když srovnáváme databázi Oraclu a Teradaty, komponenty databáze jsou odlišné. Odlišné je ukládání i získávání dat do databáze, odlišná je identifikace řádků, odlišná je práce s primárními a cizími klíči, odlišné je využívání indexů. Tyto odlišnosti zde ve stručnosti zmíním. Nebudu se zde pouštět do podrobného srovnání a analýzám návrhu DBMS. O tom tato práce není. K pochopení problematiky stačí „high level“ srovnání. 2.3.4.1 Indexy obecně Nejlepším způsobem, jak urychlit vyhledávání dat ve velkých tabulkách, je využití indexů. Pokud zadáme SQL dotaz do tabulky bez indexů, databáze musí z uložiště nejdříve vyextrahovat všechna data v tabulce, zanalyzovat data a nakonec zahodit 30
záznamy, které neodpovídají SQL dotazu. Velký datový sklad se bez indexů neobejde. Oracle i Teradata vytvářejí a používají indexy jiným způsobem. V běžných relačních databázích se musí indexy na tabulce dopočítávat a aktualizovat. K identifikaci záznamů a vztahů mezi tabulkami se využívají primární a cizí klíče. V Teradatě se místo primárních klíčů používají primární indexy, jejichž smyslem je určit, jakým způsobem řádek uložit a jak k němu přistupovat. Primární index je většinou i primárním klíčem, ovšem teoreticky může Teradata fungovat i bez primárních klíčů. Hlavní výhodou takového přístupu je přítomnost indexů na všech řádcích v databázi a dále to, že se indexy nemusejí přepočítávat 2.3.4.2 Indexy v DWA Teradata Všechny řádky v Teradatě jsou uložené na AMPech s přiřazeným diskovým prostorem (Vdisk). K identifikaci správného AMPu, na kterém je řádek přítomný se používá primární index. Dále je nutné zjistit, kde konkrétně je řádek na AMPu uložen. K tomu slouží další dva (systémové) indexy, master index a cylinder index. Master index je systémová struktura, která obsahuje odkazy do cylinder indexů. Cylinder index je zjednodušeně popis uložení dat na disku. Každý disk v AMPu má svůj cylinder index. Cylinder indexy obsahují odkazy na všechny datové bloky na disku. Datový blok je sekvence bytů nebo bitů reprezentující řádek nebo volné místo na disku. Díky těmto systémovým indexům je Teradata schopná rychle identifikovat řádek v tabulce a efektivněji alokovat místo na discích. 2.3.4.3 Indexy v DWA Oracle Oracle DBMS využívá několik typů indexů (12), které zjednoduší vyhledání řádků odpovídajících SQL dotazu. V SMP řešení musí všechnu práci odvést DMBS. V MPP řešení Exadata lze data vyhledávat sofistikovaněji. Exadata storage servery jsou podle indexů schopné identifikovat, jaké části disku záznamy neobsahují. DBMS posílá do storage serverů část SQL dotazu formou protokolu iDB (Intelligent Database) (13). Díky tomu storage server dotaz částečně zpracuje a může zaslat pouze relevantní datové bloky obsahující řádky. Identifikace relevantních datových bloků probíhá pomocí takzvaných „storage indexů“, které mají podobně jako master a cylinder indexy identifikovat, kde se řádek na disku fyzicky nachází. Funguje však na jiném principu, konkrétně pomáhá storage serveru určit, kde se záznam fyzicky nenachází. V praxi storage index funguje tak, že rozděluje hodnoty záznamů do intervalů. Když jsou v databázi intervaly 1-2 a 3-4 a náš dotaz se týká hodnoty 2, nebude se interval 3-4 načítat ke zpracování do DBMS. Storage indexy jsou do Exadaty přidány právě z důvodu zefektivnění full table scanů v data warehousingu. Při využití Exadaty pro OLTP se tyto indexy nevyužívají.
31
Obrázek 5 Exadata Storage Index (13)
2.3.4.4 Teradata optimizer Pro zpracování SQL dotazu postupuje DBMS vždy podle vlastního plánu zpracování. U Teradaty se tento plán jmenuje exekuční plán a vytváří ho parsing engine (kap 2.3.2.2). Konkrétně komponenta parsing enginu nazvaný optimizer. Optimizer je složitý program, který se v každé verzi databáze postupně vylepšuje. Optimizer společnosti Teradata funguje na principu cost-based. Cílem cost-based optimizeru je vytvořit co nejrychlejší a nejjednodušší způsob zpracování SQL dotazu. Optimizer nejdříve přiřadí costy (odhady náročnosti a délky zpracování) jednotlivým krokům a poté vyhodnocuje, zda neexistuje lepší varianta řešení. Když optimizer dokončí volbu jednotlivých kroků, odešle je ke zpracování do AMPu. Optimizer plány neukládá a pro každý SQL dotaz je dynamicky vytvořen nový plán. Plán se může změnit podle toho, jak se mění systém. V rámci exekučního plánu jsou i odhady, kolik řádek bude nutné zpracovat a jak dlouho bude krok trvat. Tyto odhady i plán samotný se tvoří z velké části na informacích z databázových statistik. Databázové statistiky jsou dynamická metadata, která analyzují rozdělení dat v tabulkách. Příkladem statistiky je rozdělení pohlaví v tabulce zákazníků. Statistiky vlastně dělají jakousi databázovou „defragmentaci“ tabulek. Statistiky se sbírají nad sloupci, na které se pomocí SQL nejčastěji dotazujeme. V Teradatě by se statistiky měly aktualizovat vždy nad primárními indexy. Sbírání statistik nad jinými sloupci v tabulce záleží vždy na typu dotazu, jaké budeme nad tabulkou nejčastěji spouštět. Čím jsou aktuálnější statistiky v systému, tím se vytváří optimálnější exekuční plány zaručující rychlejší zpracování SQL dotazů. U Teradaty je specifické, že uživatel nemůže vynutit změnu exekučního plánu, tedy pokud nezmění SQL dotaz nebo nepřepočítá statistiky. 2.3.4.5 Oracle optimizer Oracle používá také cost-based optimizer, ovšem s tím rozdílem, že byl dlouho vyvíjen se zaměřením na OLTP účely. Pro zefektivnění zpracování SQL dotazů v data warehousingu je možné vynutit změnu plánu vytvořeného optimizerem. Respektive uživatel může systému natvrdo vnutit způsob, jakým má databáze SQL dotaz zpracovat. Těmto 32
zásahům do exekučního plánu se říká „Optimizer Hints“. Oracle vysvětluje hinty tak, že aplikační návrhář může znát „svá“ data lépe než databáze. Například může vědět, že ke zpracování některých dotazů je lepší využívat odlišný index, než který by použil plán navržený optimizerem. Smyslem hintů je tedy upravit plán tak, aby byl efektivnější. Nevýhodou hintů je skutečnost, že se musí vytvářet, kontrolovat a udržovat další kód. Se změnou databáze nebo ETL procedur se staré hinty stávají nepoužitelnými. K návrhu plánu jsou k dispozici různé typů hintů. Jejich detailní popis je v oficiální webové dokumentaci Oraclu (14). Vzhledem ke zvýšené pracnosti spojené s údržbou hintů je však v dokumentaci zmíněno, že je vhodnější využívat jiné přístupy zefektivnění exekučního plánu. Zejména přístupy založené na optimalizaci samotných SQL dotazů, aby systémový optimizer sám vytvářel efektivnější plány. 2.3.4.6 Shrnutí administrace systému Z toho, co bylo zmíněno, vyplývá, že kromě pořizovacích nákladů je u datových skladů velmi důležité sledovat i náročnost údržby databázového systému. Tomuto tématu se věnují například marketingové dokumenty (15) a (16) společnosti Netezza porovnávající své řešení s Exadatou. V materiálech se tvrdí, že databázový systém konkurenční společnosti Oracle není pro DWH účely vhodný kvůli vysokým nákladům na údržbu. IT oddělení vlastníka DWH prý stráví až 90 % času prací bez přidané hodnoty. Tento údaj je nutné brát s velkou rezervou, nicméně je pravda, že databáze Oracle je z hlediska údržby náročná. Porovnání Oracle a Netezzy z těchto materiálů je rozdělené do několika bodů. Zaprvé, jak jednoduchá je obsluha systémů, kolik potřebujeme operátorů a jak efektivně lze řešit zadané úkoly. U Netezzy prý stačí jeden až dva zaměstnanci jednou za 3 měsíce a údržba je jednoduchá. Zadruhé, jak jednoduše lze kontrolovat všechny systémové prostředky (CPU, HDD, RAM, network). Workload (zatížení databáze) lze kontrolovat pomocí jediné aplikace a může jí ovládat kdokoliv, kdo zná SQL a Linux. Zatřetí Oracle využívá své vlastní SQL s mnoha specifiky. Z hlediska rozvoje DWH nestačí najmout vývojáře a administrátory se zkušenostmi z jiných systémů, ale je nutné je přeškolit na Oracle nebo najmout přímo Oracle specialisty. Přitom obě varianty znamenají zvýšení nákladů, jelikož Oracle je složitý systém a školení jsou drahá. Jako poslední bod se zde zmiňuje i architektura systému (které se věnuji v předchozí kapitole). Exadata podle Netezzy není pravá MPP DWA, ale hybridní SMP řešení s MPP prvky (storage servery) Jelikož tok dat v systému je velmi neefektivní, Exadata se snaží konkurenci dohnat velmi silným hardwarem. Netezza naproti tomu využívá vlastní nezávislé MPP nody. Jde o marketingové materiály, takže sdělení samozřejmě směřuje k tomu, že uvedené problémy řeší zakoupení Netezzy jako DWH řešení. Přitom se zákazník nemusí obávat 33
toho, že by připojení na jeho stávající OLTP systémy od Oraclu znamenalo nějaký problém. Celkově se mu tak zvýší návratnost investice (ROI – return of investments) a sníží náklady na řešení (TCO – total cost of ownership). Jaký na to mám názor? V první řadě každý datový sklad potřebuje v čase udržovat. Z toho, co jsem zaslechl o administraci datového skladu, Oracle skutečně nepatří k jednoduchým systémům. Je třeba provádět neustálé aktualizace indexů a aktualizace hintů, kontrolu zatížení databáze, „odstřelování“ uživatelů, kteří pouštějí špatné dotazy, aktualizace statistik. Například v Teradatě se indexy udržovat nemusí, stačí je správně navrhnout v datovém modelu. Statistiky se však musí aktualizovat také. Co se týká zatížení systému, Oracle server u nás na projektu vykazuje v porovnání s Teradatou mnohem nižší stabilitu. Protože s administrací Oraclu nemám osobní zkušenosti, nemohu kompetentně soudit, čím je to způsobené. Administrace a sledování zatížení Teradaty je jednoduché a provádí se pomocí jedné aplikace. Každý uživatel má definované prostředky, které může využít. Celému řešení napomáhá, že vývojáři umějí psát SQL dotazy správně a business uživatelé k Teradatě přistupují skrze BI aplikace, které dotazy umí optimalizovat. 2.3.5 Rozdělení trhu DWH Podle společnosti Gartner je celosvětový trh datových skladů rozdělen mezi šest velkých dodavatelů. Na prvních dvou příčkách je Teradata zaměřená na velké řešení datových skladů a Oracle, který s přehledem dominuje trhu relačních databázi obecně. Oraclu velmi pomohl odkup společnosti Sun (leden 2010), která
momentálně
zastává
výhradního
dodavatele HW pro Oracle RDBMS. Stejně tak pomohlo představení druhé a třetí verze DWA Exadata s HW od Sunu. Exadata V1 se serverem od HP příliš úspěšná nebyla. Na třetím místě je společnost IBM, která v září roku 2010 odkoupila již zmíněnou společnost
Obrázek 6 Magický čtyřúhelník DWH DBMS (52)
Netezza. Na čtvrtém místě se umístila společnost Greenplum, která se řadí po boku Teradaty a Netezzy (IBM) k paralelním DWA řešením se shared nothing architekturou. Greenplum navíc odkoupila v červenci 2010 společnost EMC. Díky akvizicím je EMC největším
34
dodavatelem řešení datových uložišť na světě. Greenplum je známý hlavně velmi dobrým poměrem cena/kapacita (a výkon) svých řešení (17). Na páté příčce se umístila společnost Sybase, kterou v květnu
Tabulka 1 Srovnání ceny DWA za TB (53)
roku 2010 zakoupila společnost SAP. Na tomto spojení je zajímavé, že Sybase nabízí řešení založené na databázi Sybase IQ a SAP v květnu letošního roku 2012 začal nabízet řešení SAP HANA založené na své vlastní databázi. SAP HANA je v současnosti velmi často zmiňovaný produkt a podobně jako Exadata je postavená na velmi silném hardwaru. Vůdčí dodavatele uzavírá na šestém místě Microsoft se svou databází SQL server 2012 s integrovaným ETL nástrojem SSIS (SQL Server Integration Services). Hardware Microsoftu dodávají společnosti Dell a HP (18). 2.3.5.1 Oracle vs Teradata v číslech Vzhledem k tomu, že v předchozích kapitolách jsem srovnával zejména společnosti Oracle a Teradata, v následujících řádcích tyto společnosti porovnám i z obchodního hlediska. Teradata se úzce specializuje na řešení velkých DWH řešení. Mezi její zákazníky se řadí retailový řetězec Wal-Martu, největší korporace na světě s obratem přes 400 miliard dolarů, nebo eBay, největší webový aukční systém na světě. Dalšími zákazníky Teradaty jsou například společnosti Amazon, AT&T Inc., Coca Cola, Bank of America, Google a mnoho dalších. Oracle je zase s velkým předstihem nejúspěšnější dodavatel řešení klasických relačních databází pro OLTP účely. Oracle v oblasti DWH Teradatě konkuruje zejména řešením Exadata. Společnost Teradata byla založená v roce 1979, společnost Oracle v roce 1977. Oracle zaměstnává po celém světě zhruba 115 000 zaměstnanců, její příjmy za poslední rok dosáhly 37 miliard dolarů při čistém zisku 10,18 miliard dolarů a na burze se obchoduje téměř 27 let (1986) (19). Teradata zaměstnává 8 600 zaměstnanců, její příjmy dosáhly 2,6 miliard dolarů při čistém zisku 405 milionů dolarů a na burze se obchoduje 5 let (2007) (19). Z počtu zaměstnanců i obratu je patrné, že Oracle je více než desetkrát větší společností, než Teradata. Několik let se i spekuluje, že Oracle Teradatu koupí (20). Pro úplnost ještě dodávám rozdělení a vývoj trhu databází mezi roky 2010 a 2011. Čísla za letošní rok budou k dispozici až na začátku roku 2013.
35
Tabulka 2 Rozdělení trhu DBMS dodavatelů (21)
2.3.6 Shrnutí Provoz DWH závisí z hlediska architektury na třech oblastech. Na zdrojových systémech, kterým jsem věnoval předchozí kapitolu, na výběru ETL nástroje, kterému věnuji následující kapitolu, a na řešení databáze datového skladu. Jaký je praktický rozdíl mezi administrací běžné databáze Oraclu a Teradaty? Teradatu lze velmi jednoduše obsluhovat a není třeba na denní bázi aktualizovat indexy. Statistiky se musí aktualizovat na všech DWH. V případě Oraclu je třeba udržovat i Hinty. Z hlediska workload managementu (řízení zatížení serveru) je na Teradatě velmi jednoduché přiřadit uživatelům menší porci systémových prostředků a naopak loadu přiřadit většinu prostředků. Teradata je dostatečně silná a schopná zpracovat i neoptimálně napsané SQL a zároveň dostatečně stabilní. Pro databázové administrátory Oracle je kontrola zatížení serveru časově náročnější. Exadata je velmi zajímavé řešení a obrovský posun proti kombinaci databáze a běžného OLTP serveru. Nicméně DWH databáze jsou vytvořeny za účelem čtení velkého množství aktuálních a historických dat v datovém skladu, a to Oracle není. Pokud bych vybíral řešení pro velký datový sklad, volil bych některé z MPP řešení jakým je Teradata, Netezza nebo Greenplum. V článku srovnávajícím specifikace Exadaty s konkurencí jsem našel docela výstižný komentář. „Specifikace Exadata hardwaru jsou velmi působivé, využitý software už tak působivý není“ (22). Netezza ve svém marketingovém videu ve volném překladu říká „odlišné přístupy přinášejí odlišné výsledky“. Přesto je nutné říci, že Oracle drží neotřesitelné první místo na trhu DBMS. Podle CEO Oracle Larryho Ellisona získávají zákazníky na úkor IBM a nyní svou pozornost Oracle upírá na konkurování Teradatě (23). Oracle stejně jako Teradata nepatří zrovna mezi levné dodavatele. Server bez databázových licencí vyjde od Oraclu zhruba na 10 milionů korun (24). DWA se hodí 36
pouze pro velké společnosti s objemem dat v databázích převyšující několik TB. Pořizovat si DWA do podniku s objemem dat o několika GB nemá kvůli vysoké pořizovací ceně smysl. Rozdíly mezi DWA můžeme hledat v náročnosti údržby systému, rychlosti dotazů do databáze, návratnosti investice a hodnotě pro business. Další zajímavé a aktuální informace o DWA lze najít například na stránkách DBMS2 (25).
2.4 Způsob realizace ETL 2.4.1 ETL scripty vs ETL nástroje ETL se dá technicky realizovat pomocí scriptů nebo pomocí ETL nástrojů (26). ETL scriptem je myšlen program vytvořen v některém programovacím jazyku, například v C++, Perlu, Javě nebo SQL. Oba způsoby mají své výhody i nevýhody, kterým se budu věnovat v následujících odstavcích. ETL nástroje stále nenabízejí veškerou potřebnou funkcionalitu pro vytvoření a provoz datového skladu. Na druhou stranu řešení datového skladu pouze pomocí ETL scriptů není efektivní ani dlouhodobě udržitelné. Obvykle se tak na BI/DWH projektech setkáváme s kombinací obou přístupů. Analýzy dat v malých podnicích s jednou databází se často realizují pouze pomocí SQL procedur. Realizovat velký projekt o mnoha zdrojových systémech pouze pomocí scriptů není prakticky možné. Dokážu si sice představit nějaké alternativní řešení bez ETL nástrojů, bylo by však natolik složité, že by pravděpodobně nikdy nevzniklo. Pokud by nějakým zázrakem řešení vzniklo, žádný jiný tým vývojářů by nebyl schopen řešení efektivně upravit. Peníze, které bychom ušetřili za licence ETL nástroje bychom utopili v nákladech na vývojáře a jako odměnu bychom měli nefunkční řešení. Moderní ETL nástroj je framework s uživatelským rozhraním, který slouží k integraci a úpravě vybraných zdrojových dat ve standardních konvencích. ETL nástroj poskytuje množství připravených objektů od definic zdrojů, cílů a transformací. ETL pak vývojář vytváří na obrazovce, kam tyto objekty přesouvá stylem drag-and-drop a poté je upravuje. Pomocí těchto objektů lze relativně rychle vytvořit malou část celého ETL řešení. Díky tomu, že prostředí i objekty jsou standardní, může vývojář prakticky okamžitě převzít práci po jiném vývojáři. Navíc není třeba disponovat hlubokými znalostmi programovacích jazyků, bohatě stačí znalost daného ETL nástroje a SQL. Vyvíjet ETL tak může i relativně technicky neznalý člověk. 2.4.2 Vývoj ETL nástrojů Rozdělení na různé generace ETL nástrojů se dnes již příliš nepoužívá, přesto si myslím, že pohled do historie nástrojů dokáže částečně vysvětlit i nové trendy. Má například souvislost s další kapitolou, rozdílem mezi ETL a ELT.
37
2.4.2.1 Historický vývoj DWH řešení, vývojové stupně ETL nástrojů Koncept databází vznikl v 60. letech 20. století a ve stejném desetiletí vznikly i pojmy dimenze a faktová tabulka. Relační databázový model definoval Edgar F. Codd v roce 1970, ve stejném desetiletí začal Bill Inmon užívat pojem Data Warehouse (datový sklad). V té době ovšem pro využití relačních databází neexistoval dostatečně silný hardware. V roce 1983 představila společnost Teradata první DBMS pro podporu rozhodování a o rok později první paralelní datový sklad a datové tržiště. V roce 1989 definoval Howard Dresner (později analytik společnosti Gartner) pojem Business Intelligence jako „koncepty a metody ke zlepšení podnikového rozhodování na základě faktově založených podpůrných systémů“. V roce 1991 společnost založená Billem Inmonem představila software pro tvorbu datových skladů, prakticky první ETL nástroj. Dva roky poté vznikla společnost Informatica, která je v současnosti největším dodavatelem ETL nástrojů. 2.4.2.2 První generace ETL nástrojů ETL nástroje první generace fungovaly jako generátory zdrojového kódu. Projekty řešené tímto způsobem byly proti novodobým integracím relativně jednoduché díky mnohem menšímu objemu dat i počtu zdrojových systémů. Vygenerovaný zdrojový kód museli vývojáři nejdříve upravit. Finální verzi kódu bylo poté třeba zkompilovat pomocí překladače pro operační systém serverů, kde mělo ETL probíhat (27). ETL nástroje umožnovaly generovat zdrojový kód v různých programovacích jazycích, například COBOL, C/C++, Java nebo Fortran 90 (28). Volba programovacího jazyku se odvíjela od specifik projektů nebo od zkušeností vývojářů, kteří měli ETL řešení implementovat. V té době byla většina dat uložena na mainframech, kde je stále nejpoužívanějším programovacím jazykem COBOL (případně ještě starší Fortran 90). Nejčastěji se tak pro účely ETL generoval právě kód v jazyku COBOL. Ostatní programovací jazyky byly relativně nové a na ETL nevhodné. Programovací jazyk C je kvůli nutnosti alokovat paměť příliš složitý. V C++ lze pomocí vnořených funkcí napsat velmi krátký, ale obsáhlý a nečitelný kód. Java byla v té době ještě v plenkách a hlavní nedostatek spočíval v malém výkonu. První generace ETL nástrojů se datuje do 90. let minulého století, generátory kódu se však používaly i na začátku 20. století. V České republice bylo tímto způsobem vytvořeno ETL řešení České pošty, nyní je však již nahrazeno jiným. Příkladem dodavatele první generace nástrojů je společnost Prism Solutions nebo onen zmíněný první ETL nástroj Billa Inmona.
38
2.4.2.3 Druhá a třetí generace ETL nástrojů Druhá generace nástrojů jsou ETL nástroje tak, jak je většinou známe nyní. Jde o nástroje, které k transformacím využívají svůj vlastní engine. Engine ETL nástrojů je nainstalován na ETL serveru. K práci není třeba znát žádný programovací jazyk. Vývojáři pracují v grafickém prostředí ETL nástroje a jediným technickým předpokladem k práci je znalost jazyka SQL. ETL nástroje je možné využít k integracím dat na jakékoliv platformě. Výhodou i nevýhodou je to, že všechna data musí být zpracována enginem ETL nástroje. Z hlediska výkonu se tak úzkým hrdlem řešení stává ETL server, přes který všechna data procházejí. S pokrokem hardwaru je sice možné tento nedostatek jednoduše odstranit zvýšením výkonu serveru, nicméně dodavatelé ETL nástrojů jsou si této nevýhody vědomi a za plné využití svého softwaru (enginu) si nechávají velmi dobře zaplatit. Druhou generaci reprezentovaly společnosti Informatica s produktem Powercenter, IBM (dříve Ascential software) s produktem datastage nebo Ab Initio. Jako ETL nástroje třetí generace (27) lze chápat vylepšené nástroje druhé generace, které odbourávají povinnost všechnu práci zpracovávat na ETL serveru. Transformace se vytváří stejným způsobem jako u nástrojů druhé generace, tedy v grafickém prostředí ETL nástroje. Zpracování u třetí generace ETL ovšem může probíhat kromě ETL serveru i v databázi datového skladu nebo na zdrojovém systému. Překážkou je sice skutečnost, že různé databáze využívají i odlišný typ SQL. Například Oracle má svůj PL/SQL, Microsoft T-SQL, Teradata svoje Teradata SQL. ETL nástroj třetí generace je však schopen propojit se na jakoukoliv databázi, vytvořit z transformací specifický SQL kód a poté jej spustit přímo na databázovém serveru. Informatica tomuto přístupu říká pushdown. Výpočty tak nemusí probíhat pouze na jednom místě jako u druhé generace ETL nástrojů. Kromě ETL serveru lze data zpracovávat i na databázových serverech a celý proces tím tak urychlit. 2.4.3 Rozdíl mezi ETL a ELT Kromě tradiční zkratky ETL se ve spojitosti s integrací dat objevuje i zkratka ELT. K extrakci dat z primárních systémů, které vyjadřuje písmeno E, musí dojít logicky vždy nejdříve. Ve zkratkách jsou prohozená písmena TL, vyjadřující transformaci a load. Pořadí činností je tak sice jasné, ale jaký je vlastně rozdíl mezi ETL a ELT v praxi? Na toto téma jsem sice absolvoval několik rozhovorů s kolegy, ovšem možných vysvětlení se vždy objevilo více. Ani odborné články a blogy (29) (30) (31) na toto téma většinou nedávají konkrétní a přesnou odpověď. Většinou však zaznívá jedna odpověď. Rozdíl mezi ETL a ELT a spočívá v tom, kde se data fyzicky zpracovávají. Tím pádem se vracíme i k předchozí kapitole o vývojových stupních ETL nástrojů.
Druhá generace ETL nástrojů zpracovává data vždy jako ETL na ETL serveru, 39
třetí generace ELT nástrojů dokáže data zpracovávat i jako ELT v databázi DWH.
Obrázek 7 ETL versus ELT (32)
2.4.3.1 Nevýhody ETL zpracování ETL je datový proces charakteristický tím, že se k transformaci dat využívá ETL server na kterém je spuštěn engine ETL nástroje. Rychlost transformací přímo úměrně závisí na výkonu ETL serveru. Nebo lépe řečeno, výkon ETL serveru závisí na tom, kolik je společnost ochotná zaplatit za licence ETL nástroje. S ETL serverem jsou vždy spojeny náklady na zakoupení softwarových licencí, které jsou mnohem vyšší, než náklady na rozšíření HW infrastruktury. Za licence bychom museli něco zaplatit i v případě využití open source ETL nástrojů. K open source nástrojům vždy existuje i jejich placená varianta. Dodavatele takových nástrojů jsou si velmi dobře vědomi toho, že bezplatné licence nejsou pro rozsáhlou integraci dostatečné. 2.4.3.2 Licence ETL nástrojů Jedním z nejrozšířenějších ETL nástrojů ve velkých společnostech je Informatica. Informaticu lze aktuálně zakoupit v jedné z nabízených 6 typů edicí (33). Základní edicí je „standard edition“. Cena za základní edici (se základními nástroji) začíná zhruba na 2 milionech korun se základní konektivitou a základními pluginy. Pokud si chce podnik s odstupem času zvýšit výkon serveru přidáním procesorových jader nebo rozšíření konektivity na jiné databáze, cena za licenci začíná zhruba na 100 tisících (ODBC connector na databázi) až po částky přesahující milion Kč (rozšiřující nástroje). Je taky nutné podotknout, různé verze Informaticy mají také odlišnou cenu licencí. Například zákazníci s Informaticou ve verzi 8.6.1 (zakoupené mezi roky 2008 a 2009) za případná rozšíření platí více, než zákazníci s Informaticou ve verzi 9.1. 40
Pro ilustraci, cena za rozšíření ETL serveru ze 4 CPU (16 jader) na 8 procesorů (32 jader) by na reálném českém projektu vycházela zhruba na 4 miliony Kč. Tedy zhruba 1 milion Kč pouze za licenci za procesor se 4 jádry. Hardware se počítá zvlášť. Náklady za licence k ETL serveru se dají počítat v řádech milionů korun. Některé licence jsou přitom časově omezené na rok nebo na dva. Dodavatelé ETL nástrojů nemají na internetu žádné ceníky. Jednání probíhá většinou individuálně mezi podnikem a oficiálním distributorem softwaru pro ČR. Na internetu lze orientační ceny dohledat pouze v neoficiálních diskuzích (34) nebo studiích. 2.4.3.3 ELT zpracování (ETLT) ELT proces je založený na zpracování dat mimo ETL server. Evidentní výhodou ELT je využití výkonu silného MPP DWA jako je například Teradata. Pokud se používá správný proces přípravy dat, business uživatele datový sklad většinou příliš nevytíží a nehrozí tak přetížení DWA serveru. V opačném případě samozřejmě ELT řešení nedává příliš smysl. Mimo to zpracování dat ze zdrojových systémů začíná v noci, kdy datový sklad prakticky žádní uživatelé nevyužívají. Jaké nástroje podporují ELT? Ze známých ETL nástrojů mají přímou podporu ELT zpracování jak IBM Datastage, tak Informatica se svým push-down. Mimo to jsou schopné tyto přístupy kombinovat. Mohou tak vznikat hybridní řešení ETLT, TETLT a podobně (30). Část dat se může zpracovat na zdroji, část na ETL serveru, část na serveru DWH. Vlastní ELT řešení lze vytvořit s jakýmkoliv ETL nástrojem. Jednoduše lze vyvinout SQL proceduru v databázi DWH a spouštět jí přes ETL nástroj. Variant zpracování dat je mnoho. Kritériem výběru typu zpracování jsou kromě požadavků zákazníka i architektura. Pokud máme silné ETL server (servery) s licencemi a k tomu relativně slabý DWH, dává smysl data zpracovávat na ETL serveru. Pokud máme silné ETL servery i DWA, volbu tomu můžeme přizpůsobit a využít například nějaké hybridní řešení. ELT má jednu velkou nevýhodu. Do datového skladu načítáme oproti ETL více dat. Připomínám, že 1 TB místa v DWA stojí zhruba milion Kč. U ETL zpracování zase musíme investovat více financí do ETL serveru. Veškerá rozhodnutí se většinou odvíjejí od známého poměru ceny a výkonu. Ve fázi přípravy projektu však plnohodnotnou analýzu výkonu nikdo nevytvoří a po implementaci je zase pozdě něco měnit. Spíše se tak rozhoduje v rovině obchodní. Ve výsledku jsou pouze dvě možnosti, buď se zaplatí více za licence ETL serveru nebo za databázový server budoucího datového skladu. 2.4.3.4 Oracle Data Integrator (ODI) Nejzajímavějším představitelem ELT řešení je nástroj Oracle Data Integrator (ODI). Počátky ODI se datují do října roku 2006, kdy Oracle odkoupil společnost Sunopsis 41
s jejím produktem Data Conductor. ODI je jediný nástroj od počátku vytvořený pro zpracování formou ELT. ODI generuje nativní SQL databázový kód podobně jako Informatica se svým push-down řešením. K ODI lze najít na známém serveru YouTube mnoho prezentací i instruktážních videí, podobně jako ke konkurenčním nástrojům. ODI je velmi zajímavou možností k produktu Exadata. Exadata může teoreticky fungovat jako OLTP server, tak i DWH server. Oracle se tím ve svých materiálech sám chlubí. Pomocí nástroje ODI tak může vzniknout komplexní ELT řešení, kdy data bude fyzicky zpracovávat pouze jeden server. Nástroj ODI by samozřejmě byl nainstalovaný na jiném serveru, nemusely by skrz něj ovšem téct žádná data. Odlišný způsob fungování ODI se projevuje i jinak. Práce s ODI je totiž v mnoha podstatných ohledech odlišná od všech ostatních ETL nástrojů. Nemusíme vymýšlet, jakým způsobem se mají data transformovat. Definujeme pouze zdroje, jaký chceme výstup a kde se mají data zpracovávat. Způsob jak data transformovat si vybereme z předdefinovaných templatů. Zní to velice jednoduše. Od kolegy jsem slyšel, že vývoj je v praxi složitější než v Informatice. ODI je však relativně nové řešení, které Oracle stále vyvíjí. ODI má tak velký potenciál stát se za několik let nejlepším ETL nástrojem na trhu. Rozdělení trhu bude věnována následující kapitola. Předtím se ovšem pojďme podívat na dva ilustrační obrázky k ODI.
Obrázek 8 ODI versus klasický návrh ETL (32)
42
2.4.4 Analýza trhu ETL nástrojů Podle společnosti Gartner i Infotech Research Group trhu nástrojů datové integrace dominují společnosti Informatica a IBM. Produkty těchto společností jsou velmi drahé. Zákazníci Informaticy a IBM jsou tak prakticky pouze velké společnosti. Informatica má svou platformou Powercenter a IBM nástroj Datastage. Oba tyto produkty jsou zaměřené primárně na zpracování formou ETL, oba ovšem nabízejí i ELT funkcionalitu generováním nativního SQL kódu. Spuštění kódu lze vyvolat v ETL nástroji a o zpracování se stará server datového skladu. U Informaticy se tomuto přístupu říká Pushdown, u Datastage je to Balanced Optimizer. Na třetím místě se umístila společnost
Obrázek 9 Infotech DataIntegration Tools Vendor Landscape (51)
Obrázek 10 Gartner Magic Quadrant for Data Integration Tools 2012 (50)
SAP. V roce 2007 odkoupil SAP společnost Business Objects. Od té doby SAP nabízí kromě svého vlastního nástroje SAP BW (SAP NetWeaver Business Intelligence) i nástroj Business Objects Data Integrator. Relativně dobře se umístily i dodavatelé Talend a Pervasive, kteří se specializují na open source software. Open source software v širším pojetí znamená řešení zadarmo nebo s minimálními náklady. Talend i Pervasive sice nabízejí licenci zadarmo, ovšem s minimální funkcionalitou. Podniky, které chtějí plnohodnotný ETL nástroj, si musí samozřejmě připlatit. Jen pro zajímavost lze zmínit, že Talend byla první společnost, která začala dodávat open source ETL nástroj, a to konkrétně v roce 2006. Microsoft ani v jedné studii neobsadil příliš lichotivou příčku. Nástroj SSIS dodávaný k vyšším typům licencí SQL Serveru 2012 je vnímán spíše jako řešení pro malé a střední podniky. V České republice se naproti tomuto obecnému vnímání rozbíhá velký DWH projekt založený právě na technologiích Microsoftu se zpracováním formou ELT (ETLT 43
podobně jako Pushdown). Hlavní roli bude hrát velmi výkonný server datového skladu s databází SQL Server 2012 a 1 TB RAM. Oracle se s ODI umístil ve srovnání s Microsoftem lépe. Lze však očekávat, že v následujících letech bude Oracle dále růst. Je jen otázkou času, než se Oracle stane jednou z vedoucích společností na trhu s nástroji datové integrace. Pro lepší představu ještě přikládám obrázek s vývojem počtu zákazníků dodavatelů ETL nástrojů mezi roky 2008-2012 v sestupném pořadí dle studie společnosti Gartner. Tabulka 3 Počet zákazníků dle studií Gartner Magic Quadrant 2008-2012
2.5 Shrnutí ETL lze realizovat dvěma způsoby. Prostřednictvím SQL procedur nebo pomocí ETL nástrojů. SQL procedury jsou vhodné pro řešení ETL v menších podnicích. SQL procedury lze ovšem efektivně použít i pro vytvoření datových tržišť v DWH nad výstupními daty z ETL nástrojů. SQL kód však vyvíjejí lidé, a procedury jsou tím pádem náchylnější na chybovost. Navíc se složitostí návrhu rostou i nároky na vývojáře. SQL poskytuje více možností, jak kód napsat, a tím pádem se řešení složitěji udržuje. Vývojář svou práci musí poctivě dokumentovat, jinak ani sám po čase nemusí přesně vědět, jak kód funguje. Práce v ETL nástroji se dokumentuje sama, jelikož se využívají standardní objekty. Práce ve specializovaném nástroji dává vývojáři mnohem větší flexibilitu. Na druhou stranu existují scénáře, kdy požadovanou funkcionalitu lze vytvořit rychleji a levněji pomocí procedury. Zpracování ETL znamená, že data se zpracovávají na ETL serveru, a poté se nahrají do databáze datového skladu. Naopak ELT je založen na tom, že se data nejdříve beze změn nahrají do datového skladu (EL). Transformaci pak zpracovává DBMS datového skladu (T). Výstupní data z transformací se většinou ukládají do jiné databáze v datovém skladu, než ve které jsou uložena vstupní data z fáze EL. Vzhledem k tomu, že se na datovém skladu využívají minimálně dvě databáze, místo zkratky ELT bychom mohli používat například ELTL. Zpracování ETL a ELT lze striktně odlišit tím, zda se ke zpracování dat využívá nebo nevyužívá ETL server. 44
Výběr správného ETL nástroje i způsobu zpracování může přinést výkonnější a méně nákladný systém. To, zda se rozhodneme pro ETL a ELT, bude také dále určovat, jakým způsobem bude třeba řešení vyvíjet a provozovat. Pojďme se blíže podívat na oba přístupy. Je však nutné předem upozornit, že většina nástrojů na trhu funguje na principu ETL. Lze vytvořit i hybridní řešení. Část dat se poté zpracovává ETL a část dat ELT. Na projektech Teradaty jde o běžný jev, jelikož se snaží co nejvíce využít velkou sílu MPP DWH proti pomalejšímu SMP ETL serveru. Nevýhodou ELT je větší zaplnění databáze datového skladu, nevýhodou ETL je závislost na ETL serveru. Řešení datového skladu může být perfektně navrženo a implementováno od rozhraní zdrojových systémů přes skvěle implementované transformace v ETL nástrojích po nejlepší DWA na trhu. Data v datovém skladu však může zcela znehodnotit špatná koncepce řízení a provozu. Koncepci provozu a prevenci problémů se budu věnovat v následující části diplomové práce.
3 Řízení a provoz datového skladu 3.1 Provoz datového skladu Do datových skladů proudí data ze zdrojových systémů každý den. Datový sklad je pro podnik užitečný pouze tehdy, když obsahuje korektní a aktuální data. Velké podniky neustále upravují nabídku produktů, rozšiřují síť poboček, business uživatelé mění své požadavky. Když se mění požadavky i data, musí se postupně vyvíjet i řešení datového skladu. Změny realizují lidé, a tím pádem je vždy prostor pro chyby. Na velkých projektech je proto vždy vyhrazený tým lidí, který má za úkol starat se pouze o provoz. O datové sklady se kromě provozu stará mnoho dalších týmů, bez kterých by řešení nebylo dlouhodobě provozovatelné. Činnosti provozu a ostatních týmů se mnohdy prolínají. 3.1.1 Týmy zabezpečující fungování DWH Činnosti se dají rozdělit na proaktivní a reaktivní. Proaktivní jsou preventivní činnosti, kterými se snažíme problému zabránit. Reaktivní činnosti reagují na již vzniklé problémy. 3.1.1.1 Provozní tým Úkolem provozního týmu je udržet datový sklad v provozuschopném stavu. Činnosti s tím spojené lze rozdělit na monitoring a podporu rozvoje DWH. Podpora je většinou na bázi 24/7, tedy 24 hodin, 7 dní v týdnu. Práce na provozu je rozdělena na denní pracovní dobu a noční služby. Většinu práce spojenou s provozem lze vykonávat během pracovní doby. Některé provozní aktivity se však musí provádět i mimo pracovní dobu, v noci 45
nebo ráno. V prvé řadě dohled nad loadem dat ze zdrojových systémů do datového skladu. Většinou jde o rutinní kontrolu, zda se load spustil a zda běží podle plánu. V rámci podpory rozvoje DWH má provoz na starosti i nasazování nového kódu. Pokud je v kódu chyba, může způsobit pád loadu. Pracovník provozu by měl dohledat důvod chyby a odstranit jí, aby mohl load pokračovat. Vždy však musí existovat nějaká pravidla, jakým způsobem takové problémy řešit. Oprava může být neúplná nebo může dojít k jiné chybě, která bude mít dopad na správnost dat. Celkově jde o relativně složitou problematiku a budu jí věnovat zbytek této práce. Monitoring i podpora rozvoje se týká většiny systémů a nástrojů, které se pro load využívají. Tedy databáze zdrojů i datového skladu, prostředí ETL nástroje a ETL serveru a zejména prostředí pro řízení loadu. Load lze řídit pomocí ETL nástroje nebo pomocí externího, většinou vlastního externího nástroje. 3.1.1.2 Databázoví administrátoři Úkolem databázových administrátorů (DBA) je zabezpečit činnosti spojené s údržbou databází v celém podniku. Datový sklad je tak pouze jedna z oblastí, o kterou se starají. Činnosti související s DWH jsou podobné jako u ostatních systémů. DBA mají v prvé řadě na starost udržovat databáze v provozuschopném stavu. To se skládá jednak z proaktivních činností a monitoringu. Příkladem proaktivních činností je hlídání volného místa v databázích nebo nasazování opravných patchů. Reaktivní činnosti souvisí s opravou problémů, v lepším případě odstranění zámku na tabulce, v horším případě odhalování příčiny pádu databáze a její opětovné spuštění. Specifickou činností DBA je vytváření uživatelských účtů a přidělování práv uživatelům. Podobně DBA řeší i pravidelné zálohování a případné požadavky na obnovu dat. Některé činnosti DBA se prolínají s tím, co řeší provozní tým. Mezi týmy tak musí fungovat dobrá spolupráce. O problémech se týmy vzájemně informují. Specifické problémy řeší vždy DBA vzhledem k větším zkušenostem s databázemi. 3.1.1.3 Vývojový tým Vývojový tým vytváří kód podle požadavků business oddělení. Jedná se o nejpočetnější tým a lze jej rozdělit podle rolí jednotlivých pracovníků. Nejvíce pracovníků zastává pozice vývojářů a analytiků. Vývojáři vyvíjí kód v SQL procedurách nebo v ETL nástroji. Analytici kontrolují data dle datového modelu. Činnosti vývojářů a analytiků se také mnohdy prolínají. Tým má svého vedoucího, který koordinuje jednotlivé projekty. Kromě vedoucího má hlavní slovo datový architekt. Datový architekt se stará o datový model, provádí analýzy zadání business oddělení a naceňuje pracnost jednotlivých projektů. Datový architekt je 46
zároveň i tím, kdo kontroluje dodržování metodik při vývoji. V metodice je například seznam povolených objektů v ETL nástroji a způsob použití těchto objektů. Datový architekt tím zabezpečuje, že se nová řešení vyvíjejí dle stanovených procesů.
3.2 Load dat do DWH I když se data do datového skladu dostávají procesem ETL nebo ELT, pro zjednodušení celému procesu říkáme jednoduše load. 3.2.1 Iniciální nápočet DWH První load do datového skladu se nazývá iniciální load. Iniciální load se spouští po tom, co skončí hlavní část vývojových (implementačních) prací projektu. Pokud iniciální load proběhne správně, nikdy se už neopakuje. Iniciální load je vlastně stavový nápočet dat ze všech systémů. Před spuštěním load se musí stanovit konkrétní datum. Tímto datem vlastně začíná život DWH. Na zdrojových rozhraních (nebo na transportním serveru) se připraví velké dávky dat ke stanovenému datu. Objem vstupních dat se může pohybovat od několika MB (číselníky) po několik TB (smlouvy s klienty) za každý zdrojový systém. Iniciální load trvá několik dnů, někdy dokonce více než týden. Na mém stávajícím projektu trval iniciální load zhruba dva týdny. Přestože je iniciální load rozdělen na několik částí a zkouší se předem na testovacím prostředí, neodladí se předem všechny provozní ani vývojové chyby. S iniciálním loadem tak souvisí velmi náročná práce pro toho, kdo load řídí. Po provedení iniciálního loadu je nutné dostat data v DWH do aktuálního stavu. To znamená doloadovat data, která se mezitím každý den tvořila na zdrojových systémech. 3.2.2 Denní inkrementální load Každý den o půlnoci zdrojový systém označí data vygenerovaná za posledních 24 hodin a vytvoří z nich denní přírůstek (inkrement). Z takto označených dat se vytvářejí dávky pro load ve formě souborů (flat file) nebo tabulek. Dávky se poté uloží do rozhraní zdrojového systému nebo v lepším případě na nějaké konsolidované uložiště, například do databáze transportního serveru. Příprava dodávky nějakou dobu trvá, nová data jsou tak pro load k dispozici až po půlnoci. Data na mém aktuálním projektu přicházejí postupně celou noc. První systém dodává data krátce po půlnoci, poslední zhruba v 6 hodin ráno. Některá data, například číselníky, lze však většinou zpracovat ještě před půlnocí. Kromě dat je také nutné provést i provozní činnosti, například zálohy dat z ETL serveru. Loady se tak na většině projektů spouštějí ještě před půlnocí. Dodávky dat z rozhraní zdrojových systémů do DWH lze definovat dvěma parametry: kritičnost dodávky rozhraní a SLA. Data z rozhraní mohou být kritická nebo nekritická. Bez kritických dat nemůže load pokračovat a čeká se na jejich doručení. SLA definuje, 47
kdy data do skladu přicházejí, zda je to pravidelně na denní bázi nebo nepravidelně jednou za měsíc. V praxi to znamená, že když z nějakého důvodu nepřijdou kritická data, která měla přijít, musí se zastavit load, než zdrojové systémy data dodají. Proces ETL je většinou nastaven tak, že v případě prodlevy kritických dat se load zastaví automaticky. Identifikace kompletnosti dávek se zajištuje pomocí indikačních souborů. Dokud dávka nemá indikační soubor, není připravená ke zpracování. 3.2.3 Aktuální stav „D-1“ a zpoždění DWH Aktuálnímu stav ve zkratce označujeme jako „D-1“. „D“ jako dnešní den, „-1“ vyjadřuje data ze zdrojových systémů za předešlý den. Pokud nemáme aktivní datový sklad, do kterého nahráváme data průběžně, nejaktuálnější záznamy vždy budou pocházet z „D1“. V běžném provozu se může stát, že z důvodů odstávky nebo nějaké chyby nabere datový sklad zpoždění proti stavu „D-1“. Abychom opět dostali data v DWH do aktuálního stavu, musí se během jednoho dne stihnout více než jen jeden inkrementální load. Jakmile jeden load skončí, po rychlé kontrole ihned spouštíme další load. Stavu, kdy nahráváme data ve snaze dostat DWH do aktuálního stavu „D-1“ označujeme jako „dohánění DWH“. Délka dohánění DWH závisí na tom, jaké máme zpoždění a kolik loadů jsme schopni za jeden den stihnout. Po iniciálním loadu můžeme mít zpoždění například zmíněných 14 dnů. Když jeden load trvá 8 hodin, jsme schopní během dne stihnout 3 loady. Musíme si však uvědomit, že i během dohánění zdrojové systémy každý den generují nová data. Čtrnáctidenní zpoždění tak v tomto případě určitě nedoženeme za necelých 5 dnů (14/3), jelikož během těch 5 dní se vygenerovala data pro dalších 5 loadů. Zpoždění tak doženeme až sedmý den ((14+7)/3). Pokud na datech v datovém skladu závisí práce mnoha business uživatelů, jakékoliv zpoždění datového skladu není rozhodně vnímáno dobře. Business uživatelé mohou být typicky manažeři, ale například i operátoři bankovního call centra na vymáhání nesplacených úvěrů. Ti musí mít hned ráno k dispozici údaje, zda dlužník své závazky předchozí den uhradil. Pokud ne, dlužníkovi zavolají a na závazek ho upozorní. V žádném případě ale nemohou operátoři volat na základě neaktuálních údajů. Pokud klient již zaplatil a operátor mu zavolá, aby zaplatil znovu, klient patrně moc vlídný ani slušný nebude. S tím souvisí i možná ztráta takového klienta. Příklad v předchozím odstavci je samozřejmě situací z běžného provozu. Rozhodně se nejedná o stav po iniciálním loadu. Tam datový sklad prakticky žádné business uživatele nemá. V té fázi se spíše vyhodnocuje, zda se datový sklad podařilo správně implementovat. Existují ovšem i datové sklady, které moc business uživatelů nemají ani v běžném provozu a zpoždění tak není kritické. Otázkou pak sice zůstává, k čemu pak podnik vlastně svůj datový sklad má, to se však už odchylujeme od tématu mé 48
diplomové práce. Pokud shrneme předešlé informace, je vždy nutné zohlednit, kolik má datový sklad business uživatelů a jak moc jsou na něm uživatelé ke své každodenní práci závislí. V případě, že máme hodně uživatelů závislých na aktuálních datech, pro provoz datového skladu je kritické se do zpoždění nedostat a udržovat stav D-1. To samozřejmě není úplně jednoduché, jelikož prostředí DWH se v čase mění. 3.2.4 Inkrementální vs stavový load Do datového skladu načítáme tabulky různých velikostí. Tabulky můžeme načítat celou znovu (stavově), nebo načítat pouze nové, změněné záznamy (inkrementálně). Rozhodnutí, jaký typ načítání zvolit, závisí na dvou indikátorech – počtu záznamů v tabulce (velikost tabulky) a jaké informace záznamy v tabulce reprezentují. Pokud načítáme číselník, kde je 100 záznamů, můžeme ho načítat pokaždé celý znovu. Pokud však načítáme tabulku se zákazníky o 100 milionech záznamů, je vhodné načítat pouze nové a změněné záznamy. Způsob načítání dat ze zdrojů je popsán v datovém modelu, který vytváří datový modelář. Datový model slouží jako popis celého procesu práce s daty. Od výběru dat na zdrojových systémech přes způsob transformace až po load do databáze. 3.2.5 Délka loadu Délka loadu do datového skladu je odvozena od množství vstupních dat, komplexnosti ETL řešení a výkonnosti hardwarové infrastruktury. Pokud load nedobíhá včas, lze buď optimalizovat ETL kód nebo nakoupit rychlejší servery (a licence). Jiné varianty neexistují. Tyto faktory musí být zohledněny při návrhu a rozpracování projektu. Pokud se plánuje dokoupení výkonnějších serverů po skončení implementace, odhady lze dále během implementaci doladit. Výběr optimálního hardwaru závisí i na takzvaném sizingu, což je předběžný výpočet velikosti databází DWH podle kritérií jako je velikost dat na zdrojových systémech, formát a struktura dat nebo počet budoucích business uživatelů. Aktivně využívaný datový sklad má mnoho business uživatelů a ti mnohdy čekávají, že zpracovaná data z předchozího dne budou mít k dispozici na začátku pracovní doby. Uzávěrka dat je každý den o půlnoci. Primárním systémům pak nějakou dobu trvá, než data připraví do formy, kterou bude ETL zpracovávat, tedy do takzvaných dávek. Kvůli zálohování a dalším technologickým procesům se loady většinou spouštějí již kolem 23 hodiny, hlavní část dat však ke zpracování dostanou až po půlnoci. Pracovní doba ve většině obchodních podniků začíná v 8 nebo 9 hodin, tím pádem je okno pro zpracování obchodně důležitých dat necelých 8 hodin od půlnoci do 8. hodiny ráno. Objem dat však většinou neumožnuje systémům data během ranních hodin zpracovat. Business data tak lze zpracovávat pomocí doplňkového řešení ODS a datamartu. Aktuální data D-1 pro 49
reporting v rámci podpory rozhodování jsou v hlavním datovém skladu většinou načtená až v průběhu odpoledne. Kontrola délky loadu je konstantní úkol při provozu DWH. V rámci kontroly se musí zmapovat takzvaná kritická cesta loadu. Jde o odhalení nejdéle trvajících workflow. Ty by se nejlépe měly vždy zpracovávat zvlášť, tak aby měly k dispozici větší množství systémových prostředků. Optimalizací posloupnosti jednotlivých částí loadu lze celý proces významně zrychlit.
3.3 Spouštění a řízení loadů ETL se ve velkém podniku skládá z několika set nebo spíše tisíců úloh, proto není možné nastavovat u každé úlohy individuálně interval, kdy se má spustit. Prostředí datového skladu je navíc dynamické, v případě odstávky kritického primárního systému je třeba čekat na data a není možné časy úloh nějak rozumně měnit. 3.3.1 Principy řízení loadu Řízení loadu probíhá na základě závislostí. Úlohy jsou na sobě vzájemně závislé. ETL řešení se skládá z posloupnosti různých transformačních kroků. Závislost si tak lze představit jako závislost jednoho kroku na druhém. Stejně tak některé tabulky potřebují ke zpracování integrovat data z více jiných tabulek. Tyto zdrojové tabulky se proto musí zpracovat dříve, než k integrační úloze může dojít. Informatica ani jiné ETL nástroje nejsou k řízení loadu o velkém množství úloh a závislostí vhodné. V krajním případě to možné je. Z praxe znám podnik, kde se takovou cestou vydali. Provozovat takové řešení je však za trest. Efektivně monitorovat takový systém je prakticky nemožné. Druhý podnik, který využívá k řízení interní scheduler informaticy dělí úlohy do stromové struktury. Jedna komplexnější úloha je rozdělená na více podúloh. Druhý projekt má zároveň početnější provozní tým a možnost přidávat do ETL úloh i provozní notifikace. Pokud nějaká úloha padne, pracovníci provozu dostanou informaci SMS a mailem. Monitorovat takový systém je samozřejmě o mnoho jednodušší, než v předchozím případě. 3.3.2 Inteligentní nástroj k řízení loadu Na projektu, kde pracuji, se úlohy v loadu spouští přes vlastní naprogramovavý řídící nástroj, který komunikuje s Informatica ETL enginem přes příkazy, konkrétně příkaz pmcmd. Informatica se od ostatních ETL nástrojů liší tím, že veškeré příkazy ke zpracování lze volat kromě klientských nástrojů i přes příkazy (ostatní příkazy jsou v následující části práce věnované Informatice). PDC, Process Dispatch Center, vyvinula společností Teradata. Aktuálně se PDC využívá v řízení loadu na projektu datového skladu České pojišťovny a Vodafone ČR. Metadata 50
k řízení loadu jsou uložené v tabulkách na Oracle. Logika nástroje je napsaná v programovacím jazyku Perl. Program je přítomný na ETL serveru a přes příkazovou řádku komunikuje s Informaticou. Příkladem jiného externího řídícího nástroje je Jlauncher, který byl vytvořen IT týmem Ebanky. Aktuálně se využívá v Raiffeisen bance, která Ebanku v roce 2008 odkoupila. 3.3.3 Spouštění loadu 3.3.3.1 Interní Scheduler K zahájení loadu v určitou hodinu lze využít interní scheduler (spouštěč úloh), se kterým se Informatica dodává. Scheduler poskytuje pouze základní funkcionalitu, kdy se má jaká úloha zapnout. Čas lze stanovit napevno nebo intervalově. Pomocí interního scheduleru sice lze spouštět load na velkém projektu, není to však určitě optimální řešení. Časy je možné nastavovat pouze pomocí GUI klientských nástrojů, žádným přehledným způsobem. Se schedulerem jsou navíc problémy a někdy požadovanou úlohu nespustí. Informatica tak sice scheduler obsahuje, lze jej však považovat pouze za nouzové řešení. 3.3.3.2 Externí Scheduler Vzhledem k omezením interního scheduleru je vhodnější využívat externí scheduler. Příkladem nejznámějších externích schedulerů je Autosys, Crontab, Control M. Externí scheduler spouští informatické úlohy přes příkazovou řádku. Prakticky všechno nastavení a spouštění lze v Informatice provádět přes příkazy. Tím se liší od všech ostatních nástrojů. Ve vybraný čas je tak možné nastavit inicializaci loadu externím schedulerem například přes bat soubor. Spouštěný bat soubor obsahuje konkrétní informatický příkaz.
3.4 Monitorování loadu Monitoring loadu je nezbytnou součástí provozu datového skladu. 3.4.1 Přístupová oprávnění Monitoring se týká všech komponent ETL řešení. Každý pracovník na provozu DWH tak musí mít přístupy k následujícím komponentám ETL řešení:
do databáze datového skladu,
do repository ETL serveru prostřednictvím klientských nástrojů,
na file systém ETL serveru,
na rozhraní zdrojových systémů (případně na transportní server),
do GUI externího scheduleru (pokud se používá),
k databázi scheduleru (pokud se používá).
Všechny tyto přístupy je třeba nastavit pro každé prostředí zvlášť (DEV, TEST, PROD). 51
3.4.2 Vzdálené přístupy do prostředí DWH ETL procesy můžeme monitorovat z pracoviště takzvaně on-site nebo vzdáleně off-site. On-site je v rámci pracovní doby v budově podniku. Vzhledem k tomu, že podpora produkčního DWH prostředí je většinou 24/7, je nutné mít možnost vzdáleného připojení, tedy off-site. Off-site se konkrétně realizuje jako připojení v noci z domova případně během dne z jiné budovy. Pro oba typy monitoringů je třeba mít kromě nainstalovaných a správně nastavených aplikací i oprávnění. K připojení on-site přes podnikový intranet není kromě těchto předpokladů nic dalšího potřeba. Pokud se chce pracovník připojit vzdáleně, bezpečnostní politika podniků vyžaduje připojení skrze chráněné připojení. Dříve se k tomuto účelu využívala VPN. VPN však stále nebyla považována za dostatečně bezpečnou. Z toho důvodu se přešlo na připojení přes Citrix. Pokud se uživatel připojí přes Citrix, nemá možnost využívat aplikace nainstalované na svém notebooku (případně PC). Uživatel zároveň nemá možnost na Citrix doinstalovat potřebné aplikace k připojení ke komponentám ETL řešení. Monitorovat load lze i bez tohoto přístupu například pomocí externího scheduleru s webovým GUI. Když load spadne, je třeba mít aplikace k dispozici. Z toho důvodu se používá další pracovní server, který je fyzicky přítomný v budově podniku a připojen k podnikovému intranetu. Na serveru jsou nainstalované všechny potřebné aplikace. Typický vzdálený dohled se tak skládá z připojení přes Citrix do sítě zákazníka a z Citrixu poté přes vzdálenou plochu na pracovní server. Z Citrixu se lze obdobným způsobem připojit k ETL serverům. Tam však mnohdy nejsou k dispozici uživatelské aplikace k připojení na databáze, proto je pracovní server většinou nezbytnou součástí řešení. 3.4.3 Inteligentní způsoby monitoringu Velké IT projekty vznikají zpravidla pod časovým a finančním tlakem. Návrh i implementace systémů jsou proto zaměřené na funkcionalitu řešení, ne však na provoz. Vznikají tak často řešení, které je velmi obtížné monitorovat a dohledávat v nich chyby. Některé projekty vznikají i s ohledem na provoz, a datová kvalita DWH je pak většinou vyšší. V opačném případě si pracovníci provozu musí vyvíjet vlastní způsoby, jak si monitoring ulehčit. Load přes ETL server lze sledovat mnoha způsoby. Pokud řešení využívá ETL nástroj, load lze sledovat prostřednictvím monitorovací aplikace. Produkční ETL řešení se většinou skládá z mnoha stovek ETL workflow. V monitoru lze sice zjistit, jaké workflow jsou právě spuštěny a jaké byly úspěšně dokončeny, ucelený pohled na průběh loadu neposkytuje. Pokud pracovník provozu zná, kdy a jaké workflow mělo běžet, dokáže
52
odhadnout, v jaké fázi load je. To však není efektivní monitorování, jelikož se přímo spoléhá na znalost operátora. Dodavatelé nástrojů datové integrace žádný kvalitní software k monitorování velkých ETL řešení neposkytují. Efektivně monitorovat velké ETL řešení tak lze v současnosti jen pomocí vlastních řešení. 3.4.3.1 Informace zasílané ETL nástrojem Pokud to datový architekt dovolí, je možné si ETL workflow upravit tak, aby samo zasílalo informace o svém delším běhu nebo o svém pádu. Základem takového řešení v Informatica powercenter je takzvaný „command task“ nebo „post session command“. Post session command slouží také ke spouštění příkazů po dokončení session. Command task dělá to samé s rozdílem, že se vytváří samostatně mimo sessionu. Příkaz může posílat emaily na vybrané adresy nebo sms přes sms bránu na vybraná telefonní čísla. K příkazu lze samozřejmě definovat podmínky, ve kterých se má email nebo sms poslat. Typickou podmínkou je pád sessiony. Pomocí vlastních přídavných objektů lze podobným způsobem nadefinovat interval (treshhold), ve kterém má workflow dobíhat. Pokud workflow běží déle, command task opět pošle email nebo sms. Takový systém je sice schopný upozornit na chybu, sledování loadu je přesto těžkopádné. Řešení navíc není možné použít v případě, kdy metodika projektu nepovoluje úpravy ETL. Takovou činnost může povolit pouze datový architekt, ale ten pravděpodobně bude chtít, aby zavedená pravidla byla nadále respektována. Jediným důvodem ke změně pravidel by byl neuspokojivý stav provozu datového skladu, vysoká chybovost a nemožnost řešit problémy jiným způsobem. Pokud je ETL řízeno pomocí nevhodného interního scheduleru, jiný způsob, než využití command tasků a post session commandů však prakticky nezbývá. 3.4.3.2 Interní monitorovací nástroj Powercenter Workflow Monitor je klientský nástroj ke sledování průběhu workflow (ETL úloze) dodávaný k platformě Informatica Powercenter. Aktuální detailní průběh jednotlivých workflow je vidět pouze v tomto nástroji. Workflow Monitor poskytuje údaje o tom, kolik řádků z jednotlivých tabulek se zpracovalo a jak dlouho workflow probíhá. Údaje jsou aktualizované v reálném čase. Tím však výhody končí. Nejde o žádný sofistikovaný nástroj, který by poskytoval odhady, kdy by mělo workflow doběhnout, nelze přes něj nahlížet na zpracovaná data, GUI není úplně vhodné pro sledování velkého loadu o tisicích úloh. V případě, když workflow nedobíhá, workflow monitor neposkytuje žádné bližší informace. Stejně jako u ostatních klientských nástrojů Informaticy občas vypadne spojení se serverem. Ve zkratce nejde o žádný geniální nástroj, ale k práci je dostatečný a většinou funguje tak, jak má. 53
Problém nastává, pokud nemáme žádnou jinou notifikaci o průběhu loadu a musíme používat ke kontrole stavu loadu právě Workflow Monitor. K tomu, abychom mohli sledovat průběh loadu se tak musíme připojit skrze Citrix, dále pracovní server a poté teprve spustit Workflow Monitor. Letmý pohled na Workflow Monitor nám navíc příliš neřekne o reálném stavu loadu, pokud sami nemáme přesnou představu, jaká úloha má běžet. Navíc představa toho, že člověk se vzbudí v půl čtvrté ráno, podívá se na mobil a jde znovu spát, je o něco příjemnější, než představa, že musí vstát, spustit notebook, přilogovat se skrze dva servery, spustit aplikaci a poté luštit, zda běží to, co by v tu dobu nejspíše běžet mělo. 3.4.3.3 Externí monitor Externí monitor poskytuje velmi jednoduchý a efektivní způsob k řízení a monitorování loadu. Hlavní výhodou externího scheduleru je přehledné menu, které v reálném čase přenáší informace o průběhu loadu. Informace sice nejsou na rozdíl od interního Workflow Monitoru detailní, souhrnné informace však poskytuje mnohem lépe. Dodavatelé nástrojů datové integrace žádný podobně efektivní způsob monitoringu neposkytují. Scheduler je tak většinou vlastní řešení vývojového týmu, který ho vytváří mimo jiné i pro své vlastní účely, tedy efektivnější testování kódu. Kromě toho, že pomocí externího scheduleru je možné load efektivně řídit a monitorovat, lze i pomocí SNMP propojit se systémem pro vyhodnocování událostí a skrze něj posílat SMS konkrétním členům provozního týmu. Tomu se budu věnovat v následující kapitole. Příkladem externího monitoru je PDC. PDC se primárně využívá k řízení loadu, obsahuje však i webové GUI k monitorování loadu. GUI poskytuje velmi přehledný pohled na stav loadu. Jaké úlohy se aktuálně zpracovávají, jaké úlohy je třeba zpracovat a jaké úlohy jsou již dokončené. Samozřejmostí je i zobrazení úloh, které skončily s chybou. PDC padlé úlohy dále posílá přes SNMP protokol do systému pro vyhodnocování událostí.
54
Obrázek 11 Externí monitorovací nástroj PDC (Process Dispatch Center)
3.4.3.4 Systém k vyhodnocování událostí Inteligentní monitoring je založen na systému s automatickým sledováním a vyhodnocováním událostí. Základem systému jsou aktivní a pasivní monitorovací systémy. Aktivní systém generuje sám informace o svém stavu. Pasivní systém toho naopak schopen není a jeho stav musí zjistit dotazovací program. Informace z monitorovacích
systémů
jsou
chybové
hlášky,
překročení
procentuální
zaplněnosti databáze a podobně. Systém komunikuje pomocí SNMP protokolu (simple network management protocol). SNMP protokol slouží k zasílání hodnot mezi zařízeními v síti. Protokol funguje za pomoci dvou softwarových komponent, managera a agenta. Manager vznáší požadavky agentovi na zaslání informací, agent zajišťuje realizaci této komunikace. SNMP trap je specifický typ zprávy, založený na modelu agent/manager s asynchronní komunikací. Součástí monitorovacího řešení je systém ke zpracování SNMP trapů. Trap je nutné nejdříve přijmout pomocí komponenty „trap reciever“. Podle typu trapu lze definovat akci, která se po přijetí trapu provede. Přiřazení akcí provádí komponenta „rules engine“. Akce může být například notifikace, přeposlání, generování jiné události, start aplikací, konverze do emailové podoby. Realizaci akce provádí komponenta „action processor“.
55
Obrázek 12 SNMP trap handling (35)
Pomocí systému ke zpracování SNMP trapů lze přijímat a zpracovávat informační, chybové a varovné hlášky (info, error, warning). Všechny tyto informace poté podle předdefinovaných pravidel mohou chodit ve formě emailů a sms pracovníkům provozu DWH 3.4.4 Monitoring databází a databázových serverů Ke standardnímu monitorování databází patří zejména kontrola zatížení databází, dále kontrola zaplněnosti databází a někdy i bezpečnostní monitorování serverů, kteří uživatele se na servery připojují a zda na to mají oprávnění. Kromě těchto činností je třeba kontrolovat i stav databázových serverů, zda neselhal nějaký hardware, například pevné disky. Tuto činnost většinou neprovádí DBA, ale servis databázových serverů. Pevné disky (HDD) stále selhávají relativně často. Nedílnou součástí velkých uložišť je již po mnoho let takzvaný RAID, technologie k propojení pevných disků. V databázích se k ochraně proti hardwarovému selhání disků využívají typy RAID1 a RAID5. RAID1 je nejjednodušší varianta složená ze dvou disků, kdy se data „zrcadlově“ ukládají na oba disky zároveň. Selhání disku lze monitorovat softwarově. Pokud jeden disk selže, stejná data jsou přítomná na druhém disku. Při selhání jednoho disku tak pouze stačí přijít k serveru a vyměnit nefunkční disk za nový. Diskové pole tvoří desítky disků. U každého disku je LED dioda, indikací spuštěného disku je zelené světlo, indikací nepoužívaného disku žluté světlo a indikací disku s chybou červené světlo. Identifikovat vadný disk (disky) tak není problém.
56
3.5 Ochrana dat v DWH 3.5.1 Přístupová práva Uživatelé se mohou dostat do databáze pouze v případě, když mají v databázi účet a mají oprávnění k přístupu do databází. Databáze představují jednotlivé vrstvy datového skladu. Žádosti o uživatelské přístupy do databází musí DBA ve velkém podniku řešit prakticky každý den. Kvůli zrychlení a zprůhlednění tohoto procesu se využívají takzvané role. DBA tak nemusí pokaždé přiřazovat přístupová práva, ale stačí pouze přiřadit roli, ve které jsou tato práva již nadefinovaná. Nejlepším způsobem k ověřování uživatelů ve velkých podnicích je prostřednictvím Single Sign-on (SSO). SSO znamená, že k přístupům ke všem komponentám ETL řešení využívá uživatel pouze jeden účet. Pokud dá uživatel výpověď, všechna práva lze zrušit velmi jednoduše zrušením SSO účtu. Produktů SSO řešení je na trhu velké množství. Vybrat ten správný a integrovat do něj všechny komponenty ETL řešení však určitě není jednoduché. S politikou přístupových práv souvisí i důležitá zmínka o pravidlech pro vytváření hesel. Většina velkých podniků si uvědomuje, že mnoho uživatelů je nezodpovědných a hesla si nastavuje příliš jednoduchá, a ty pak nemění. Přes SSO není problém nastavit pravidla pro vytváření hesel (číslice, velká, malá písmena) a interval resetu hesla. Často se ovšem zapomíná na technické uživatele k databázi datového skladu. Největší oprávnění má technický uživatel DBC a hned po něm DWHADMIN. Velké podniky mají sice mnohdy často promyšlenou údržbu uživatelských účtů, technickému uživateli DBC však ponechávají základní heslo DBC. Ten může kdokoliv jednoduše zneužít. Velké podniky sice většinou mají oddělení bezpečnosti, které podezřelé přístupy do databází monitoruje, této skutečnosti si můžou všimnout až po čase. 3.5.2 Bezpečnostní pohledy v databázi BI aplikace ani business uživatelé nepřistupují přímo k tabulkám, ale k bezpečnostním pohledům (view). Pohled poskytuje uživateli stejná data ve stejné podobě jako tabulka, data v něm však nejsou fyzicky uložená. Jde pouze o předpis, jak mají být data získána z jiných tabulek a pohledů. K tabulkám mají přístupová práva pouze DBA a pracovníci provozu. Uživatelské role obsahují pouze práva na čtení těchto pohledů. 3.5.3 Fallback To, že jsou data v ideálním případě rozložené po všech AMPech, s sebou teoreticky nese i rizika. Pokud by některý AMP přestal fungovat z důvodů sofwarové nebo hardwarové chyby, mohli bychom přijít o data. Pokud by zároveň přestaly fungovat dva AMPy,
57
databáze by se automaticky vypnula. K výpadkům sice naštěstí nedochází, data je přesto nutné chránit. Softwarově mohou být data v Teradatě chráněna například pomocí fallbacku. Fallback funguje tak, že ke každému řádku v tabulce vytvoří jeho kopii. Originál i kopie řádku jsou poté podle speciálního algoritmu uloženy na dva různé AMPy. Při pádu jednoho AMPu je tabulka s fallbackem stále dostupná včetně všech svých záznamů. Fallback s sebou nese i dvě nevýhody. Zaprvé, u každé řádky v tabulce se ukládá i její kopie a tabulka proto zabírá dvojnásobnou kapacitu. Zadruhé, DML operace (insert, update, delete) nad fallback tabulkou trvají déle, jelikož se vždy musí provést na dvojnásobném počtu řádků. SELECT do tabulky však trvá stejnou dobu. V databázi využívající RAID i fallback se tak každý řádek zapíše čtyřikrát. Fallback má jistě své opodstatnění, cena DWH je většinou však odvozena od kapacity. Kvůli nákladům na kapacitu se fallback využívá pouze u kritických tabulek nebo se nevyužívá vůbec. Existují ovšem samozřejmě i podniky s obrovskými DWH a fallbackem na všech tabulkách. 3.5.4 Backup and Restore (BAR) Architektura Nejefektivnější a nejjistější ochranu dat poskytuje zálohování na externí uložiště. Pokud by nám v extrémním případě server shořel, výše zmíněné ochranné technologie by nám nepomohly. Další obrovskou výhodou je to, že data máme chráněná i vůči chybám v ETL. Pokud není možné výstupní data efektivně opravit, můžeme se vrátit k záloze a ETL zopakovat. Zálohy obvykle probíhají denně nebo jednou za dva dny. Existuje mnoho DWH projektů, kde se zálohování na pásky příliš neřeší. K záloze je třeba mít opět relativně komplexní infrastrukturu s několika servery. K zálohám dat z datových skladů se nejčastěji využívají magnetické pásky. Magnetické pásky totiž dosahují v porovnání s pevnými disky vyšší rychlosti čtení i zápisu a z hlediska ceny za kapacitu jsou navíc levnější než disky. Cílem BAR (backup and restore) strategie je pravidelně zálohovat data z datového skladu do páskové knihovny (tape library). Pásková knihovna je tvořena páskovými mechanikami, jakýmsi ekvivalentem diskového pole. Nejznámější dodavatelé páskových knihoven jsou společnosti IBM, Quantum, Oracle a HP. Přenos dat z BAR serveru do páskové knihovny zprostředkovává řídící server páskové knihovny. U páskové knihovny od IBM je to IBM Tivoli Storage Manager nebo také TSM. IBM TSM neumožnuje přímo propojit Teradatu s páskovou knihovnou. K zálohám se tak musí používat další server, takzvaný backup nebo také data movement server. Prvním krokem je zkopírovat data z datového skladu na tento BAR server. K tomu slouží speciální zálohovací software, v případě Teradaty se nazývá ARCMAIN. Oracle obdobným způsobem využívá RMAN (36). Druhým krokem je přenést data z BAR 58
serveru do páskové knihovny. Přenos řídí TSM server. Úzké hrdlo zálohování není rychlost diskového (páskového) pole, ale kapacita sítí. 3.5.5 Servisní odstávky Na serverech probíhají pravidelné servisní odstávky. Důvodem odstávky je většinou aktualizace databázového softwaru. Při odstávkách jsou spoušťěny preventivní softwarové kontroly nad serverem. Odstávky probíhají i z jiných důvodů, například kvůli servisnímu zásahu na síťové infrastruktuře nebo na jakémkoli jiném prvku DWH řešení. Nejčastěji se však odstávky a kontroly týkají právě serveru datového skladu. Servisní odstávky je vždy nutné provést v domluvených časových oknech tak, aby se dotýkala co nejméně business uživatelů a aby zároveň nebyl zpožděn load. Ten samozřejmě při odstávce nemůže být spuštěný. Časové okno pro odstávky jsou ve velkých podnicích většinou naplánované mezi 20 hodinou a půlnocí. Servisní odstávky nerealizují pracovníci provozu ani DBA, ale servisní oddělení. Provoz i DBA musí na odstávce spolupracovat minimálně ověřením, že zásah neměl dopad na funkčnost databáze.
3.6 Change Management Projekt vývoje DWH nekončí s předáním řešení zákazníkovi a zahájením produkčního provozu. Žádný návrh řešení IT projektu není dokonalý. V čase se mění požadavky a potřeby zákazníka na výstupy z DWH. Zároveň probíhá vývoj na primárních systémech a mění se vstupní data. Datový sklad takové změny musí reflektovat, jinak by se stával businessiově a časem i technicky nepoužitelným. Tento fakt je nutné respektovat již na začátku projektu v dokumentaci. Produkční provoz musí být stabilní a zároveň měnitelný. Change management je v překladu změnové řízení nebo také řízení změn. Change managementem jsou definované procesy k řízení uživatelských požadavků na změnu funkcionality ETL. Změny jsou přímo spjaté s vytvořením nebo úpravou zdrojového kódu využívaných softwarových produktů. V rámci změn je tak i přesně definované řízení různých verzí jednotlivých aplikací. Cílem change managementu je zajistit efektivní a bezproblémovou implementaci schválených změn na produkčním prostředí. Činnosti související s change managementem se přímo nebo nepřímo se týkají všech pracovníků DWH projektu. 3.6.1 Prostředí infrastruktury Z důvodů efektivního řízení změn v DWH existuje po vzoru ostatních IT projektů více oddělených prostředí. Prostředí je reprezentované hardwarovými prvky a aplikacemi. Obvykle se využívá architektura 3 prostředí, produkční (PROD), vývojové (DEV), testovací (TEST).
59
Produkční prostředí slouží k dennímu zpracování dat ze zdrojových systémů do DWH. Jde o klíčové prostředí, ze kterého čerpají data navázané aplikace a business uživatelé. Chybovost nebo výpadky aktivně využívaného DWH pro podnik znamenají finanční ztráty. Stabilita je jedním z hlavních hodnotících kritérií úspěšnosti celého řešení datového skladu. Vhodně zvolený change management má za úkol stabilitu produkčního prostředí zajistit. Na produkčním prostředí nelze logicky vyvíjet nový kód a testovat, zda funguje. Na vývojovém prostředí probíhá vývoj nových mapování a SQL procedur jednotlivých projektů. Vývoj zabezpečuje vývojový tým. Nový vyvinutý kód je nutné před nasazením na produkci nejprve otestovat na produkčních datech a právě k tomu slouží testovací prostředí. K udržování více prostředí se vztahují i vyšší náklady. Na projektech, kde se používá pouze vývoj a produkce, se však po nasazení nového kódu zpravidla objevuje mnohem více problémů. To, zda je takový stav udržitelný, závisí na množství faktorů od velikosti projektu, schopnosti vývojového týmu, kvality testování a závislosti business uživatelů na datech. Pokud má být druhý den na základě výstupů z datového skladu zadána práce zaměstnancům business oddělení, nemůže si podnik výpadky dovolit. Vývojové a produkční prostředí bez toho testovacího takovou jistotu nedokážou poskytnout. Pokud podnik vyžaduje maximální stabilitu a je ochoten za ní zaplatit, přidává se ke zmíněným prostředím i takzvané preprodukční prostředí. Na preprodukčním prostředí se udržuje stejný stav dat i metadat jako na produkčním prostředí. Jde tak o perfektní prostředí pro otestování kódu. Udržovat čtyři prostředí však stojí více peněz a nepřináši zase o tolik užitku navíc. Pokud se na projektu používá klasický ETL nástroj, který data zpracovává způsobem ETL, používá se zpravidla pro každé prostředí vlastní ETL server. Každé prostředí většinou má i vlastní server datového skladu a další doplňující software a hardware. Většinou se tak setkáme s architekturou 3 prostředí. Nejsilnější hardware je samozřejmě na produkci. Test i vývoj běží kvůli úsporám na méně výkonné infrastruktuře. Na mém současném projektu se v implementační fázi používaly pouze dvě prostředí, vývoj a produkce. Před spuštěním iniciálního loadu se pořídil nový silnější server datového skladu a k němu i zbytek infastruktury včetně ETL serveru a transportního serveru. Nové nejsilnější prostředí je od té doby to produkční, stará produkce se stala testem a vývoj zůstal. Z hlediska úspor by tak mohlo být zajímavé zpracovávat data způsobem ELT místo ETL například pomocí nástroje Oracle Data Integrator. ELT nepotřebuje mít silný ETL server. Výpočty by probíhaly na serverech datových skladů, loady na testu a vývoji by byly rychlejší a podnik by navíc ušetřil relativně velké částky za jednodušší infrastrukturu.
60
K tomuto tématu považuji za nutné ještě zmínit stáří dat a metadat na jednotlivých prostředích proti produkčnímu. Data vyjadřují již upravená transformovaná data v datovém skladu, metadata definují, podle jaké logiky se budou data integrovat. Na vývojové a testovací prostředí se data kopírují z produkčního datového skladu.
Produkční prostředí obsahuje vždy nejaktuálnější data „D-1“.
Vývojové prostředí má vůči ostatním prostředím nejstarší data. Pro vývoj nejsou potřeba všechna data z produkce, stačí pouze vzorek k nějakému datu. Data se příliš často neaktualizují. Na vývoji se vyvíjí nový kód, proto obsahuje i nejnovější metadata.
Testovací prostředí obsahuje starší data než na produkci, ale novější metadata. Metadata jsou mapování, které testujeme, data se kopírují z produkce v předem domluvených intervalech, například jednou za měsíc.
3.6.2 Požadavky na změnu a způsob jejich realizace 3.6.2.1 Typy požadavků Existují dva typy změnových požadavků. Prvním typem je požadavek na novou funkcionalitu nebo úpravu té stávající. Nové požadavky vycházejí od BI uživatelů DWH (business oddělení). Druhým typem jsou požadavky na opravu. Ty vycházejí ze zjištěných problémů na produkčním prostředí. Nedostatky se mohou projevit nekorektním ETL zpracováním některých dat. Na takové chyby většinou upozorní uživatele BI nebo analytici při dodatečných testech na produkčních datech. Jiným typem chyb jsou provozní chyby. Ty se projevují například prostřednictvím „zahozených“ záznamů, což jsou záznamy, které se místo do cílové tabulky přesunou do chybové. Stejně tak může dojít k neakceptovatelnému prodloužení doby zpracování dat. Tento typ chyb většinou vzniká špatnou analýzou nebo nedostatečně navrženým nebo realizovaným kolem testů. Provozní chyby odhalují při kontrolách pracovníci provozu. Chybám v datovém skladu lze z větší míry předcházet, žádné opatření však není stoprocentní. Se stroji vždy pracují lidé, takže prostor pro chybu tu bude vždy přítomný. 3.6.2.2 Příprava a vývoj požadavků Realizaci každé změny lze chápat jako malý projekt. Po předání zadání je třeba obecné zadání konkretizovat a odhadnout pracnost řešení. Pracnost se měří v jednotce manday (MD), v překladu do češtiny člověkoden. MD odpovídá 8 hodinám práce jednoho vývojáře. Dále je nutné analyzovat dopady na jednotlivé prvky v architektuře ETL, respektive jakých z nich se úpravy budou týkat. Jinými slovy jaké konkrétní informatické 61
(ETL) objekty (mappingy, sessiony a workflow) a databázové objekty (tabulky a views) budeme upravovat. Z pohledu řízení a koordinace vývoje je nutné přesně evidovat případnou provázanost změn – zda se dva změnové požadavky netýkají stejného objektu v ETL nástroji nebo v databázi. Jako příklad mohou posloužit sdílené ETL objekty. Typickým sdíleným objektem je definice zdroje. První vývojář může vyvíjet změnu při využití stávající definice zdroje. Druhý vývojář definici zdrojového objektu naopak potřebuje upravit. Pokud dojde k úpravě zdroje, první vývojář nebude schopen svou práci dokončit. Kde správnému koordinování vývoje je nutné udržovat aktualizovaný seznam objektů ve vývoji. K výše nastíněné situaci poté nikdy nemůže dojít. Vzájemnou závislost změn však není nutné zohlednit pouze při vývoji, ale i při koordinaci nasazování na testovací a posléze produkční prostředí. U předchozího příkladu bylo na sdíleném zdrojovém objektu závislých více workflow. Nastavení vývojového harmonogramu stejně tak může řízeně způsobit závislost mezi dvěma nebo více migračními balíčky. Takové balíčky je nutné nasazovat spolu a někdy i v přesně daném pořadí.
3.7 Shrnutí Projekty datových skladů nejsou statické, ale v čase se neustále mění. Z toho důvodu je zvláště důležité udržovat prostředí infrastruktury. Změnám ETL řešení je nutné přizpůsobit i procesy pro provoz a řízení datového skladu. Souhrnně jsou tyto procesy řízení nazývané Change Management. Se změnami souvisejí možné chyby. Kvůli možným chybám je nutné řešení důkladně monitorovat. Monitoring si lze velmi ulehčit v závislosti na tom, jak je projekt navržen a implementován. Pro většinu vývojových
týmů
je
hlavní
prioritou
korektně
implementovat
požadovanou
funkcionalitu. V případě, kdy řešení závisí hlavně na interní funkcionalitě ETL nástrojů, se provoz stává mnohem komplikovanějším.
4 Vývoj a provoz DWH na platformě Informatica Powercenter 4.1 Práce v klientských nástrojích Informatica Powercenter Informatica je primárně navržená pro zpracování dat způsobem ETL. Hlavní komponentou řešení je proto ETL server. 4.1.1 Informatica Powercenter server ETL server Informaticy (37) se skládá z několika částí. První z nich je doména. Doména je pomyslná obálka pro administraci serveru. V doméně se nachází nastavení uživatelů, 62
dále jsou zde vidět všechny prostředky serveru, které jsou k dispozici. Například zakoupené databázové connectory nebo maximální využitelný počet procesorových jader. Pod doménou se nachází takzvaný „Node“. Node je logickou reprezentací serveru v doméně. V doméně může být více nadefinováno více nodů. Node se skládá z enginu a uložiště metadat Informatica objektů. Engine se stará o zpracování dat. Engine se v Informatice nazývá Integration Service. Definice toho, jaká data zpracovávat a jaké transformace s nimi provádět, jsou uložené ve formě metadat. Uložiště metadat zajištuje v Informatice Repository Service nebo také jednoduše repository. K administraci nastavení serveru slouží administrační (nebo také admin) konzole. Admin konzole je webová aplikace přístupná na ETL serveru skrze webový prohlížeč na adrese http://localhost:6001/adminconsole. 4.1.2 Architektura Powercenter Klientské nástroje Informatica Powercenter pro vývoj nových ETL objektů jsou tvořené třemi hlavními nástroji:
Designer,
Workflow Manager a
Repository Manager.
Veškeré objekty, které vývojář vyvine se průběžně ukládají do repository na repository server. Repository Server je tvořený malou databází, většinou (ale ne nezbytně) Oraclem. Jakmile jsou ve všech třech nástrojích vytvořené potřebné objekty, práci lze uložit, nechat automaticky zkontrolovat (zvalidovat) a spustit. Nejnadřazenější objekt, který se spouští, se nazývá workflow. Po spuštění workflow se úkol předá enginu ETL serveru (Integration Service) ke zpracování. Zpracování probíhá na základě metadat na Repository Serveru.
63
Obrázek 13 Architektura Informatiky (38)
Zpracování probíhá ve více fázích. Nejdříve se provedou přípravné práce, takzvaná inicializace. V rámci inicializace se načtou veškerá metadata o objektech z repository včetně všech jejich nastavení – například z jakého serveru se mají data načítat, pokud jsou uložené v souborech, jak se soubory jmenují a podobně. Mnoho nastavení nejsou provedená přímo zadáním hodnot, ale přes parametry. Parametr je dynamická reference na konsolidovaný seznam hodnot. Parametry jsou v informatice ukládány ve formě parametrických textových souborů na file systém. Výhoda parametrů je to, že je možné objekty podle potřeb efektivně měnit. Nemusí se upravovat vše zvlášť, stačí upravit pouze parametrický soubor. Jakmile skončí inicializační fáze, přichází na řadu samotné čtení zdrojů pomocí komponenty „Reader“. Informatica nabízí pro různé typy zdrojů i různé typy Readerů. Další komponenta data dle nastavení transformuje, poslední zapisuje na cíl. Pro různé typy cílů existují i různé typy „Writerů“. Zpracování více zdrojů neprobíhá najednou stylem vše načíst, vše transformovat, vše zapsat, ale většinou postupně. Zpracování je však vždy závislé na použitých typech transformací. Některé dovolí data zpracovávat postupně, některé naopak vynucují zpracovávat data najednou. Princip vysvětlím na nástroji Microsoft SSIS. I když funguje jinak než Informatica, pro představu však určitě postačí. Transformace v SSIS jsou rozdělené do tří kategorií: Non-Blocking, Partially Blocking, Totally Blocking. Skrze neblokující transformace mohou data postupně protékat. Každá 64
řádka se může zpracovat zvlášť nezávisle na ostatních záznamech. Příkadem neblokující transformace je lookup nebo konverze datových typů. Blokující transformace jsou naopak transformace, které musí veškerá data načíst do paměti a „zablokovat“, dokud neproběhne celé zpracování dat. Typickým příkladem blokující transformace je řazení záznamů (sort). Na pomezí jsou částečně blokující transformace. Příkladem částečně blokující transformace je join tabulek (spojení). Informatica využívá jiné transformace i jiný způsob zpracování než SSIS, princip je však podobný. K výsledku se dá dostat mnoha různými způsoby, a pokud využijeme ten optimání, bude naše ETL rychlejší. Video k SSIS lze naleznout zde (39). 4.1.3 Klientské nástroje Informatica Powercenter Všechny nástroje mají podobný interface. V horní části obrazovky nalezneme standardní menu se záložkami File, Tools, Help a podobně. Stejně jako u aplikací z jiných oblastí IT slouží toto menu k vytváření a ukládání nových projektů, zakládání jednotlivých objektů, úpravě a zapínání jednotlivých ovládacích panelů. Pod záložkami jsou ikony pro usnadnění práce včetně těch, reprezentujících veškeré dostupné transformace. Levou část obrazovky zabírá Repository Navigator. Vizuálně vypadá navigátor jako menu v hierarchické struktuře. V repository lze zakládat složky a na jednotlivé složky přidělovat práva. Pokud jde o repository pro tréninkové účely, existují v něm složky se jmény nebo jiným odlišením uživatelů. Každý uživatel má právo přidávat a upravovat objekty pouze ve své složce. V reálném projektu repository obsahuje složky s jednotlivými fázemi ETL od extrakce dat ze zdrojových systémů po load dat do databáze. Dle názvů složek tak můžeme jednoduše zjistit počet ETL kroků. V projektu na kterém pracuji, je to 12 kroků od dávek ze zdrojových systémů po load transformovaných a zahistorizovaných dat do datového skladu. Dále existují složky s definicemi zdrojových a cílových objektů a složky k BI přípravě dat pro několik datamartů. Celkem se jedná zhruba o 40 složek. V ETL řešení jiného podniku jsou jednotlivé kroky rozdělené do stromové struktury. Jeden krok má tak pod sebou více podřízených kroků. V repository navigatoru ve velkém podniku tak může být i přes 100 složek s ETL objekty. V každé složce jsou většinou desítky dalších objektů. Celkově repository ve velkém podniku obsahuje několik tisíc objektů. Řazení složek má sice svou logiku, první dojem z takového pohledu však není pro nového pracovníka na provozu úplně příjemý. Největší plocha nástrojů je vyhrazena takzvanému workspace v pravé části obrazovky. Ve workspace definujeme, upravujeme a spojujeme jednotlivé ETL objekty.
65
Obrázek 14 Informatica Powercenter Designer
4.1.3.1 Powercenter Designer: Informatica Powercenter Designer je hlavním nástrojem k návrhu ETL. Tvoří jej vcelku přehledné a barevně příjemně sladěné GUI. Vzhledem k tomu, že se v Designeru vytváří logika ETL, takzvané mappingy, věnuji tomuto nástroji poměrně větší prostor, než ostatním. Součásti Powercenter Designeru jsou následující komponenty: Source Analyzer Source Analyzer je nástroj k definici struktury zdrojů. Informatica podporuje práci s mnoha typy zdrojů, od flat file, XML přes výstupy primárních systémů Peoplesoft či Salesforce po tabulky z databází IBM DB2, Oracle či Teradata. Jak jsem již zmiňoval, praxe je obvykle taková, že se data připravují do takzvaných zdrojových dávek na jedno konkrétní místo. Jedná se buď o nějakou konkrétní databázi, nebo příprava dat do filů na file systém. Na projektech, které znám z praxe, se používá právě buď Oracle, či file systém.
66
Obrázek 15 Definice struktury zdroje v nástroji Source Analyzer
V source analyzeru najdeme 4 záložky. První, Transformation, shrnuje obecná metadata o zdroji – název, druh zdroje, jeho umístění v repository. Na druhé záložce nazvané Ports, definujeme jednotlivá pole, jejich datové typy a datové typy (v Informatice nazvané Precision). Vyplnění dalších dvou záložek je nepovinné. V Properties lze definovat název zdrojové tabulky a vlastníka objektu. Poslední záložkou jsou metadata extensions. Metadata extensions umožnují vlastní definici propojení a uložení objektu v rámci Informatické repository (40). Target Designer Kromě definice zdrojového objektu musíme vytvořit i definici toho cílového. K tomu slouží Target Designer, jak napovídá jeho název. Target Designer má podobnou strukturu jako výše zmíněný Source Analyzer. Záložky jsou opět 4: Table, Columns, Properties, Metadata Extensions. Jejich smysl je s drobnými rozdíly prakticky stejný jako v předchozím případě. První dvě záložky jsou povinné, definice objektu a polí. Na záložce Properties definujeme formát datumu, a numerické oddělovače u tisíců a desetinných čísel. Pokud je cílový objekt tabulka v databázi, musí tyto oddělovače korespondovat. Poslední záložka je opět Metadata Extensions. 67
Mapping Designer Mapping Designer je nástroj k vytváření mapingů. Maping je logické uspořádání zdrojových a cílových objektů a transformací. Informatica nabízí 20 možných transformací. Většina z nich se v praxi nevyužívá. Naopak metodiky projektů jsou omezené většinou zhruba na 6 základních transformací. Transformace je pak pro vývojáře jednoduše čitelná a lze v ní jednodušeji odhalit chyby. Informatica je specifická transformací nazvanou Source Qualifier. Ke každému zdrojovému objektu, který chceme někam naloadovat přes nástroje Informatica, se vytváří Source Qualifier. Vytváří se pokaždé, bez ohledu, zda chceme zdrojová data nějak transformovat nebo pouze někam „přelít“. Source Qualifier je vlastně mediátor mezi zdrojovými a cílovými metadaty. Source Qualifier reprezentuje řádky, které čte Informatica Server, když spouští sessionu. Transformation Developer Transformation Developer je nástroj k vytváření takzvaných „Custom Transformation“, vlastních znovupoužitelných transformací. V Transformation Developeru se vytváří komplexní historizační a deduplikační komponenty. Historizační komponenta slouží k historizaci záznamů podle jejich typu historizací SCD 2 a SCD 2,5. Deduplikační komponenta odstraňuje duplikátní záznamy v první kroku ETL při extrahování dat ze zdrojových systémů. Jeden identický záznam může být uložen na více zdrojových systémech. V datovém skladu nás zajímají pouze unikátní záznamy. Vlastní transformace se vytváří mimo Informaticu v programovacích jazycích C++ nebo Java. Mapplet Designer Mapplet Designer slouží k vytváření mappletů. Mapplet je obálka pro znovupoužitelné mapování. Na projektu Mapplet využíváme ke generování operačních klíčů (OK). Rozdíl mezi Mappletem a Custom Transformation je v tom, že Mapplet lze vytvořit standardními objekty v Informatice. 4.1.3.2 Powercenter Workflow Manager Workflow manager slouží k vytváření informatických workflow. Workflow je posloupnost (schéma) provádění činností. Workflow slouží ke spouštění mappingů vytvořených v Powercenter Designeru. Součástí jsou komponenty „Task Developer“ a „Workflow Designer“ Task Developer Mapping samotný obsahuje pouze logiku transformací. Součástí mappingu není z jaké tabulky nebo z jakého souboru má mapping data načítat ani kam se mají data zapisovat. Všechny tyto informace obsahuje Session. Session obsahuje vždy pouze jeden mapping. 68
Session obsahuje veškerá nastavení nutná ke spuštění mappingu. Většina těchto nastavení je v Session zanesená pouze zprostředkovaně přes parametry. Parametry odkazují na hodnoty uložené v parametrických souborech. Parametrické soubory jsou uložené na file systému ETL serveru, tedy mimo Workflow Manager. Parametry lze nastavovat i v prostředí Workflow Manageru. Z důvodu jednoduššího sdílení a orientaci v parametrických souborech se využívá spíše file systém. Ke každé Session lze vždy definovat, jaká akce se má provést před jejím spuštěním a po jejím dokončení. Jde o takzvané Pre-Session a Post-Session commandy. Pre-Session Command může například smazat tabulku před tím, než do ní začněme načítat data. Post-Session Command jde nastavit pro případ úspěšného dokončení Session nebo pro neúspěšný běh, kdy Session skončí pádem. Pomocí těchto příkazů si můžeme nechat například poslat mail v případě, kdy Session padne. Kromě Session se v Task Developeru vytvářejí i Command Tasky. V Command Tasku je možné spouštět své vlastní unixové nebo shellové příkazy. Jiný způsob využití je spuštění nějakého souboru na file systému ETL serveru. Na projektu se takto využívá Command Task k napočítávání statistik na tabulce (v L1 vrsvě) poté, co se do ní naloadovaly nové záznamy. Workflow Designer Workflow designer slouží k vytváření workflow. Workflow je posloupnost úkolů, které se mají provést. Kromě Session a Command Tasků lze do Workflow nadefinovat i různé další specifické úlohy, například Event Waiter k řízení loadu. Event Waiter je úkol, který očekává nějakou událost. Ve chvíli, když se očekávaná událost stane, Event Waiter spustí následující úkol ve workflow. Event Waiter lze používat pro řízení loadů, pokud se na projektu nepoužívá nějaký externí řídící nástroj (scheduler). Event Waitery se používají při zahájení loadu pro verifikaci příchodu dat ze zdrojových systémů. Data ze zdrojových systémů přicházejí sice periodicky, ovšem s jistými časovými odchylkami. ETL je nutné provádět nad kompletní dávkou, ne pouze nad její částí. Když ze zdrojového systém přenese celou dávku na definované rozhraní, vytvoří takzvaný indikační soubor. Event Waiter na indikační soubor čeká. Jakmile se indikační soubor objeví, Event Waiter spustí úlohu k načítání dat z definovaného rozhraní Další tasky fungují na podobném principu. Decision Task, Timer Task, Assignment Task. V rozumně navrženém prostředí ETL řešení nejsou tyto tasky potřeba. Na ostatní tasky lze pohlížet tak, že je Informatica poskytuje pro případy, kdyby je náhodou někdo někdy
69
potřeboval. Většina vývojářů je však nikdy potřebovat ani využívat s vysokou pravděpodobností nebude. 4.1.3.3 Powercenter Repository Manager Repository Manager je nástroj určený pro správu repository. V Repository Manageru lze například označovat (labelovat) objekty, které chceme přenést z jednoho prostředí do druhého. Objekty přenášíme buď kvůli testování z vývojového prostředí na testovací, nebo k přenášení otestovaných objektů na produkční prostředí. V Repository Manageru lze také spouštět dotazy do repository pomocí jednoduchého rolovacího menu. Workflow se skládá z množství podřízených objektů. K tomu, abychom mezi prostředími přenášeli všechny potřebné objekty, slouží takzvané Release Group. Při vytváření Release Group lze dohledat veškeré závislosti mezi objekty a tyto objekty jednotně označit. Poté není složité tyto objekty z Repository vyextrahovat ve formě XML souborů nebo zkopírovat na jiné prostředí porovnáním obou repository pomocí informatických příkazů. Mimochodem přenášení objektů mezi repository pouze pomocí Repository Managera je řešené naprosto špatně. Ano, přenášet objekty je možné, ale velmi pomalu a neobratně. Pokud například upravujeme ETL jedné tabulky ve všech ETL krocích, změna se týká například 5 workflow. ETL kroky jsou rozdělené na složky a každá složka se pomocí Repository Manageru přenáší zvlášť. Pro každou složku je tak nutné absolvovat odkliknutí zhruba osmi oken, všude znovu nadefinovat stejné podmínky a mezitím čekat, než Informatica jednotlivé kroky nad repository provede. Nasazení několika workflow pomocí Repository Managera tak může zabrat několik hodin. V případě velkého objemu workflow celý den. K nasazování je tak nutné používat vlastní nástroje. Další důležitou funkcí Workflow Manageru je takzvaný Purge. Všechny objekty v informatice lze verzovat. Jakmile vývojář změní nějaký objekt, stará verze se zaverzuje a je přístupná pro případ, kdyby bylo třeba se k ní vrátit. K problému může dojít v případě, když vývojář nějaký objekt vytvoří a poté jej z nějakého důvodu smaže. Například z důvodu, když si myslí, že ho již nebude potřebovat. Informatica takový objekt ovšem nesmaže, nechá ho v Repository a znepřístupní v klientském nástroji. Po nějaké době vývojář objekt znovu vytvoří pod stejným jménem. Informatica v té chvíli vytvoří nový objekt s jiným ID, ovšem v repository jsou najednou dva objekty se stejným jménem. Přenášení mezi prostředími u nás na projektu probíhá porovnáváním jmen objektů v Repository. Kvůli tomu, že jsou v Repository dva objekty se stejným jménem, deployment tool neví, jaký objekt přesunout a proto spadne. K trvalému smazání ETL objektů z Repository slouží právě Purge.
70
4.1.4 Alternativa ke klientským nástrojům Alternativou nebo doplněním ke klientským nástrojům Informaticy jsou příkazy. Pomocí příkazů lze efektivně řídit Informatica ETL server, tedy jaké úlohy se mají spouštět a s jakým nastavením. Nejdůležitější jsou následující příkazy: Pmrep Pmrep slouží k připojení k uložišti ETL objektům Repository jako alternativa k Repository Manageru. Pmrep se používá se například při přenášení objektů mezi vývojovým a testovacím prostředím. Pmcmd Pmcmd slouží ke komunikaci s ETL enginem jako alternativa k Workflow Manageru. Pmcmd umožňuje spouštět workflow, nebo sofistikovaně řídit load vlastním externím nástrojem. Infacmd Infacmd slouží k nastavování Informatických služeb částečně jako alternativa k admin consoli. Přes infacmd lze nastavit engine Informatiky nebo službu zajištující chod Repository. Infasetup Infasetup slouží k nastavování ETL serveru jako alternativa k admin consoli. Pomocí příkazu infasetup lze například zálohovat nastavení domény nebo zálohovat a restorovat repository. 4.1.5 Performance ETL řešení Jedna z prvních otázek při návrhu ETL zní, jak budeme přistupovat k datům. Většina aplikací se připojuje k datům prostřednictvím ODBC. ODBC je aplikační rozhraní pro přístup k datům, které je přímou součástí operačního systému. U zrodu ODBC stála společnost Microsoft, nicméně ODBC funguje i mimo platformu Windows. V současnosti jde o standardní API využívané na systémech Linux, Unix, MacOS a dalších. Ve Windowsech a MacOS je ODBC přítomné vždy, na Unix i Linux je třeba jej doinstalovat. ODBC zprostředkovává komunikaci mezi aplikací a DBMS pomocí dvou komponent, ODBC ovladačů (ODBC Driver) a správce těchto ODBC ovladačů (ODBC Driver Manager).
Když do aplikace zadáme SQL dotaz, aplikace volá správce ODBC ovladačů.
Správce ODBC ovladačů zajištuje výběr správného ODBC ovladače a předání SQL dotazu. Pomocí správce ovladačů je možné přistupovat k více ovladačům a tím pádem i k více zdrojům dat najednou. Správce ovladačů (Driver Manager) tak funguje jako přepínač (switch) mezi jednotlivými ovladači (ODBC Driver). 71
ODBC ovladač zaslaný požadavek zpracuje a přeloží do nativního SQL jazyka konkrétního DBMS. Přeložený požadavek následně pošle do DBMS.
DBMS provede zpracování požadované operace a vrátí výsledek ODBC ovladači. Přes správce ovladaču se pak data vrací zpět do aplikace.
Díky integraci mnoha ovladačů umožňuje ODBC
předávat
data
prakticky
mezi
jakoukoliv aplikací a jakoukoliv databází. Řízení ODBC ovladačů skrze „správce“ však znamená, že data musí proudit skrze obě komponenty (správce ovladačů a konkrétní ovladač). Hlavní výhoda ODBC tak má za důsledek nižší rychlost provádění operací přes ODBC. Aplikaci a databázi lze propojit i pomocí nativního ovladače DBMS, který žádného „správce ovladačů“ nepotřebuje. Při objemu dat, které protékají ETL serverem každý
Obrázek 16 Připojení na databázi skrze ODBC a nativní connector (48)
den, se tak připojení přes nativní ovladač jeví jako mnohem lepší řešení než ODBC. Nejen, že je rychlejší, ale dokáže i využívat plnou funkcionalitu cílové databáze (další zvýšení rychlosti). To si však moc dobře uvědomují i tvůrci ETL nástrojů a za nativní ovladače si nechávají velmi dobře zaplatit. V úvodu kapitoly jsem psal, že ODBC je součástí operačního systému. ETL nástroje však obsahují své vlastní, lépe optimalizované ODBC connectory (connector je ekvivalentem ovladače), které jsou oddělené od těch v operačním systému. Informatica se základní licencí obsahuje neplacené connectory pouze pro připojení na repository ETL nástroje. Repository může být na jedné z databází Oracle, Teradata, DB2 nebo Sybase. Pro připojení na databázi zdrojových systémů nebo cílovou databázi datového skladu je vždy třeba si připlatit. V případě ODBC menší částku, v případě rychlejšího nativního connectoru pak částku mnohem vyšší. U platformy Informatica se nativnímu connectoru říká Powerchannel. V konkrétních částkách vyjde licence ODBC connectoru zhruba na 100 000 Kč, licence Powerchannelu pak stojí mezi jedním a dvěma miliony Kč.
72
4.2 Technická realizace zpracování dat 4.2.1 Operační klíče K identifikaci záznamů a jejich vztahů v relační databázi slouží takzvané klíče. V klasické relační databázi k identifikaci postačují dva typy klíčů, primární klíč a cizí klíč. Primární klíč se volí tak, aby mohl jednoznačně identifikovat každý záznam v tabulce. Příkladem primárního klíče je rodné číslo. Pokud v tabulce takové jednoznačné pole neexistuje, primární klíč může tvořit i více polí. Cizí klíče pak slouží k vyjádření vztahů mezi tabulkami. V databázi datového skladu se integruje množství dat z různých zdrojových systémů. Jeden záznam v datovém skladu může vzniknout kombinací pěti zdrojových záznamů. Kombinace přirozených klíčů zdrojových dat sice nový záznam jednoznačně identifikuje, je však příliš složitá pro vyhledávání. K identifikaci nových záznamů v datovém skladu používáme umělé klíče, většinou označované jako surrogate key (SK) (41) nebo operační klíč (OK). Operační klíč je generovaná hodnota, která jednoznačně identifikuje nový záznam v datovém skladu. Příkladem primárního klíče může být rodné číslo, příkladem umělého klíče je sekvenčně vygenerované číslo. Dokud záznam v databázi existuje, operační klíč se nemění. OK má datový typ integeru, zároveň tím tak napomáhá i zmenšení objemu datových přenosů v datovém skladu. Operační klíče jsou jednou z nejdůležitějších technologických součástí datového skladu. Pomocí těchto klíčů záznamy identifikujeme a vyhledáváme. Pokud bychom přišli o tabulky operačních klíčů, datový sklad se stane nepoužitelným. Takový scénář většinou naštěstí nehrozí a operační klíče jsou důkladně zálohované. Problém však může nastat při jejich generování. ETL nástroje používají ke zpracování dat svůj file systém. Datový sklad používá operační klíče ve formě tabulek v databázi. Pokud file systém a databáze DWH nejsou synchronní, ETL nástroj může vygenerovat takový operační klíč, který je již v databázi
DWH.
Při loadování
duplikátního klíče
do databáze
DWH load
pravděpodobně spadne. Pokud by k pádu nedošlo, můžeme mít po takovém loadu v databázi DWH několik milionů řádek se dvěma různými identifikátory (OK). Takový stav je pochopitelně nežádoucí. V podstatě jde o jeden z nejhorších scénářů, jaký se při loadu může stát. Většina DWH operační klíče používá. Podle OK vytváříme historii v datovém skladu. Existují však i řešení datových skladů, kdy se umělé klíče nepoužívají. A to v případě, pokud potřebujeme vidět pouze aktuální záznamy a ty historické nás nezajímají. Článek na toto téma z letošního roku je k dispozici zde (42). Takové řešení si však dokážu představit pouze jako nějaké podpůrné nebo v malém podniku. Bez historie totiž ztrácí DWH pro velký podnik částečně smysl. 73
4.2.2 Historizace dat Pohled na historii dat v čase je jednou z největších výhod datového skladu a BI řešení. Velký podnik se neustále mění, a s tím se mění i jeho data. V této souvislosti se setkáváme s pojmem slowly changing dimensions (SCD), nebo také pomalu měnící se dimenze. „Pomalu měnící se“ představuje to, že se data mění postupně a neperiodicky. Dimenze logicky sdružuje stejný typ dat jako například informace o pobočkách, produktech nebo zákaznících. Existuje více typů historizací. Základním typem je SCD 1. SCD 1 funguje na principu přepsání starého záznamu novým záznamem. Tabulka s historizací SDC 1 tak obsahuje pouze aktuální záznamy a neposkytuje žádný pohled do minulosti. Druhým typem je SCD 2. V tabulce zákazníků reprezentuje jednoho zákazníka jeden řádek. Zákazník změní adresu svého trvalého bydliště a my chceme aktualizovat řádek, který ho reprezentuje. V SCD 1 bychom starý řádek zahodili a nahradili ho aktuálním. V SCD 2 starý řádek ponecháme v tabulce a do tabulky nahrajeme nový řádek. Oba řádky (záznamy) budou v rámci datového skladu unikátně identifikovány vlastním operačním klíčem. Aby bylo možné identifikovat staré a nové záznamy v tabulce, SCD 2 přidává údaj o platnosti záznamu. Jedná se o dvě pole, kdy byl záznam do datového skladu nahrán a do kdy záznam platí. Pokud zákazník změní svou adresu k 10. 1. 2013, bude toto datum ukončovat platnost starého záznamu. Nový záznam bude platný od 11. 1. 2013. Datum, do kdy bude nový záznam platný, se většinou uvádí jako datum daleko v budoucnosti. Typický příklad z českých projektů je pro aktuální záznamy použití datumu 31. 12. 3000. Kromě zmíněných historizací existují i další varianty SCD. Příkladem může být varianta SCD 2 definovaná Ralphem Kimballem v knize „The Data Warehouse Toolkit“ (2) jako SCD 6. SCD 6 ke zmíněným polím s daty platnosti od a do, přidává ještě pole s historickými změnami. Ve výše zmíněném příkladu se změnou adresy zákazníka by nový záznam kromě data platnosti obsahoval i pole s novou i starou adresou. Výhodou SCD 6 je velmi jednoduché dohledávání změn, nevýhodou růst velikosti tabulky jak po obsahové, tak i velikostní stránce. V praxi se setkáváme i s typy, které nejsou v knihách definované. Na projektu, kde pracuji, se používá historizace SCD 2,5. SCD 2 přidává pole s platností záznamu v datovém skladu (takzvaná DWH platnost). SCD 2,5 ke zmíněné DWH platnosti přidává i takzvanou efektivní platnost. Efektivní platnost určuje například platnost smluv. Když si zákazník při návštěvě 10. 1. 2013 založí novou smlouvu s platností od 30. 1. 2013 do 30. 1. 2014, tyto údaje budou v datovém skladu uložené pod historizací SCD 2,5. Platnost záznamu je popsána na dvou pomyslných časových osách, na ose platnosti záznamu v datovém skladu a na ose business platnosti. SCD 2,5 se používá pouze u záznamů, kde 74
nějaká business platnost existuje, například u smluv (pojišťovacích, bankovních, atd.). Samotný zákazník žádnou business platnost nemá, na tabulkách se zákazníky se tak používá historizace SCD 2 pouze s DWH platností. Historizace SCD 2 a SCD 2,5 se vytvářejí pro většinu záznamů v L1 vrstvě datového skladu. Historizaci je vyhrazen jeden krok celého ETL procesu. Vzhledem k tomu, že historizace musí proběhnout nad většinou nových záznamů v DWH, je třeba ji realizovat podle „znovupoužitelné“ logiky. Vymyslet a zrealizovat takovou „znovupoužitelnou“ komponentu je pro vývojáře časově náročné. U historizace navíc není prostor pro chyby, jelikož jde o jeden z hlavních pilířů úspěšného fungování DWH. Funkční historizační komponentu lze koupit od jiných specializovaných dodavatelů. V ČR se na podobně komplexní komponenty v ETL nástroji Informatica specializuje firma Globtech. Na mém projektu se používá právě taková zakoupená komponenta. Komponenta je vytvořená v jednom z programovacích jazyků, nejčastěji C++ nebo Java a je integrovaná do informaticy přes objekt nazvaný Custom Transformation. Cena za komponenty se odvíjí od její složitosti a znovupoužitelnosti. Odhad částky za podobnou komponentu je zhruba půl milionu Kč. Vzhledem k ušetřenému času a jistotě správného zpracování historizace na projektu jde určitě o dobrou investici. 4.2.3 Cílové vrstvy DWH Jednotlivé datové vrstvy si lze také představit jako databáze v DWH. Databáze jsou fyzicky uloženy na stejném serveru. Ke všem vrstvám datového skladu musí existovat detailní popis jejich obsahu, datový model. Datový model obsahuje pojmenování objektů (tabulky, views, procedury, relace mezi objekty, indexy, statistiky) (43). Z datového modelu musí být jasné, z jakého zdroje data pocházejí a jaké změny nad daty byly v rámci ETL provedeny. S vývojem datového skladu se datový model musí neustále aktualizovat. Pečlivá údržba datového modelu je pro rozvoj datového skladu klíčová. 4.2.3.1 L0 (stage) L0 je vrstva datové základny. Zdrojem dat pro L0 jsou přímo zdrojové systémy, v sofistikovanějším řešení je zdrojem transportní vrstva nebo ODS. Data se před nahráním do L0 transformují pouze minimálně. Optimálním zpracováním vznikají 3 typy tabulek. Stavové, Inkrementální a Full, průnik obou. Tabulka se většinou nahrává inkrementálně nebo stavově (variantně se používá pojmenování „nahrávání stavem“), v závislosti na velikosti a typu tabulky. Například číselníky jsou co do velikosti malé, proto se nahrávají stavem. Nedává však smysl pokaždé znovu nahrávat celou tabulku o 100 milionech záznamů. Snaha je naopak snížit množství dat k přenosu a nahrávat pouze nově vzniklé a změněné záznamy. Je však možné provést takzvanou opravu stavem z L0 do L1. To znamená, že se manuálně změní způsob zpracování úpravou 75
load_date na full tabulce na L0. Při transformaci se se záznamy pracuje, jako by byly nově příchozí. S příchodem záznamu do DWH se k němu přiřadí i datum aktuálního loadu, takzvaný load_date, K identifikaci aktuálních záznamů slouží kromě load_date i takzvaný active flag. Je to pole s příznakem 1 nebo 0 a znamená, zda je záznam aktuální, či nikoliv. 4.2.3.2 L1 (target) L1 je cílová vrstva transformovaných dat z L0. Velikostně je L1 zhruba na polovině objemu L0. V rámci ETL kroků se pro záznamy nejprve vygenerují operační klíče, které transformované záznamy v DWH unikátně identifikují. Poté dochází k samotné transformaci dat z L0. V rámci transformace se data agregují, upravují datové typy a mění do podoby definované v datovém modelu, respektive v předpisech mapování. Dalším důležitým krokem je historizace dat. Existuje několik typů historizací, například denní, týdenní, měsíční, roční. Volba té správné historizace vychází z typu a významu dat a je opět definovaná v datovém modelu, respektive předpisu mapování. Obsahově se tak L1 skládá ze zahistorizovaných dat navázaných na vygenerované operačními klíče. 4.2.3.3 L2 (datamarty) L2 je vrstva datamartů. L2 obsahuje specificky upravená data z L1 pro další analýzy v BI nástrojích. BI nástroje by teoreticky mohly přistupovat do L1, ovšem generování požadovaného pohledu by trvalo několik hodin. Jakákoliv úprava pohledu by byla také časově náročná. Proto je jednodušší připravit data na konci ETL procesu. Práce s BI nástroji je tak nesrovnatelně rychlejší a efektivnější. Velikost datové základny L2 je zhruba na polovině L1. Na projektu, kde pracuji, se L2 vrstva napočítává pomocí SQL procedur přímo na DWH serveru. Přitom L0 a L1 se napočítává pomocí informatiky na ETL serveru. Hlavním důvodem pro zvolení procedur bylo plné využití DWH serveru, který je výkonnostně mnohem silnější, než ETL server. Ušetří se tak čas při napočítávání dat. Teradata prodává své DWA, proto využití jejich plného potenciálu dává smysl i obchodně. 4.2.4 Kroky ETL architektury Zpracování dat způsobem ETL je vždy rozdělené do několika po sobě následujících kroků. 4.2.4.1 Inicializační fáze Prvním krokem je inicializační fáze. Jedná se o technologickou přípravu loadu. Součástí je například změna data loadu, příprava parametrických souborů, backup file systému ETL serveru.
76
4.2.4.2 Extrakce dat ze zdrojových systémů na FS Druhou částí je extrakce, tedy stažení zdrojových dat z rozhraní primárních systémů (nebo transportního serveru) na file systém ETL serveru. Data se neukládají do tabulek, ale na disky ve formě textových souborů. Důvodem je to, že engine ETL nástrojů se dlouhou dobu optimalizovaly ke zpracování dat sekvenčním způsobem. Je to případ i nejrozšířenějších ETL nástrojů, Informaticy i IBM Datastage. Součástí prvního kroku je odstranění duplikátních záznamů, takzvaná deduplikace. Duplikátní záznamy vznikají tak, že identický produkt (smlouva) nebo zákazník může být uložen na více systémech zároveň. V datovém skladu nás zajímají pouze unikátní záznamy. 4.2.4.3 Načtení dat do vrstvy L0 Třetím krokem je uložení dat i do databáze datového skladu, konkrétně do L0. Data se ukládají ve dvou typech tabulek, S a C. S jsou stavové tabulky, tedy tabulky, které se nahrály celé. C jsou inkrementální tabulky, tedy pouze přírůstek posledního dne. Ve čtvrtém kroku se S a C tabulky v datovém skladu konsolidují do konsolidované F (full) tabulky. Full tabulky využívají historizaci SCD1. Vrstva L0 datového skladu tak obsahuje deduplikovaná data ze zdrojových systémů bez žádných transformací. 4.2.4.4 Synchronizace a generování OK OK je údaj vytvoření za účelem identifikace záznamu (řádky v tabulce) v datovém skladu. V pátém kroku se nejdříve synchronizují operační klíče. Vzhledem k tomu, že generování probíhá na ETL serveru, musí být klíče synchronní s databází, aby nedošlo k vygenerování nekorektních klíčů. Po synchronizaci se v šestém kroku generují nové operační klíče pro nově příchozí záznamy. 4.2.4.5 Transformace dat Deduplikovaná data ze zdrojových systémů v sedmém kroku transformujeme. Transformace spojují záznamy, vypočítávají nové hodnoty a upravují datové typy. K identifikaci nových záznamů se používají vygenerované operační klíče. Velikost a množství dat se zmenší zhruba na polovinu 4.2.4.6 Historizace dat V osmém kroku se opět vytahují data z databáze na file systém ETL serveru. ETL veškeré úkoly zpracovává právě na file systému. Osmý krok se nazývá export historizace. V devátém kroku probíhá samotná historizace. Oba soubory se porovnají, nové záznamy získají DWH platnost do 31. 12. 3000, staré, upravené záznamy se uzavřou.
77
4.2.4.7 Load dat do vrstvy L1 datového skladu V desátém kroku probíhá load zahistorizovaných dat do datového skladu, konkrétně do vrstvy L1. L1 je hlavní vrstva datového skladu. Data jsou připravená pro další úpravu k BI účelům, tedy do vrstvy L2.
4.3 Způsob opravy chyb 4.3.1 Workaround Workaround je v mnoha společnostech „zakázané“ slovo. Pomocí workaroundu se řeší problémy neprocesně a dočasně, ale jednodušší a rychlejší cestou, než kdybychom zvolili procesní řešení. Workaround je jednorázová, dočasná, manuální oprava, aby proces ETL (load) mohl běžet dál. Příkladem Workaroundu je přidání statistik na tabulku, aby korektně dobíhala SQL procedura, případně aby se předešlo product joinu. Jiným příkladem workaroundu je odmazání duplicit nebo jiná oprava dat. Chyby nejčastěji způsobuje nasazení nového releasu na produkci. Chyby mohou vzniknout v celém procesu release managementu od nedostatečného otestování, chyby při nasazení nebo chyby při analýze dopadů na jiné prvky ETL. Chyba poté zastaví load na produkci. Kromě workaroundu lze chybu samozřejmě řešit i trvale, opravou kódu 4.3.2 Fix, hotfix Způsoby opravy kódu (a znovuspuštění loadu) jsou dva, fix a hotfix. Fix prochází standardním procesem – analýza chyby a jejích dopadů, vyvinutí opravy, nasazení na produkci, restart úlohy. Hotfix je neprocesní a provádí ho pracovník provozu na vlastní riziko. Analýzu i opravu si vytvoří a nasadí sám. Rozdíl mezi workaroundem a hotfixem je v tom, že workaround je pouze dočasné jednorázové řešení pro danou chvíli. Hotfix je oprava příčiny, ne důsledku, a lze jej tak teoreticky chápat jako vyřešení problému. Hotfix se provádí pouze u jednoduchých záležitostí, kdy si je pracovník provozu jistý tím, co dělá. Špatný hotfix neušetří čas, ale naopak může nevratně poškodit data a jedinou opravou poté může být návrat DWH. 4.3.3 „Návrat“ DWH Návrat DWH je pomyslné poslední řešení problému s daty. Představme si, že vznikne chyba na ETL, které připravuje tabulku na L0, která se používá pro přípravu dalších 30 tabulek na L1. Přitom na chybu v ETL přijdeme až po několika dnech. Jedním způsobem je opravit data v každé tabulce zvlášť. Druhým způsobem je „návrat“ DWH k datu, než se chyba v DWH objevila. Tedy k zálohám k datu před nasazením releasu, který chybu způsobil. Návrat je nutné realizovat na více prostředích:
78
Návrat databáze k zálohám z externího uložišťě (z magnetických pásek, provádí DBA),
návrat repository ETL nástroje (původní objekty – zdroje, cíle, mappingy, workflow),
návrat file systému ETL serveru (parametrické soubory),
návrat externího scheduleru (nejkomplikovanější činnost).
Oba přístupy mají své pro i proti. Oprava tabulek je pracná pro vývojový tým a znamená i zásah do historie. „Návrat“ DWH zase znamená velkou pracnost pro provoz datového skladu a DBA. Zároveň se vracíme ke starým datům, takže business uživatelé nemají přístup k těm aktuálním. Je tak nutné vždy rychle analyzovat dopady chyby a odhadnout, jak dlouho bychom případně doháněli ztrátu způsobenou „návratem“ DWH.
4.4 Kategorizace nejčastějších chyb Chyby při vývoji a provozu znehodnocují zpětný historický pohled a tím pádem i podstatu celého datového skladu. 4.4.1 Chyby na datech z primárních systémů Většina dat na primárních systémech se generují automaticky nebo přicházejí z jiných informačních systémů, některá data do primárních systémů ovšem vkládají ručně zaměstnanci společnosti. To, že se systémy pracují lidé, automaticky znamená riziko chybovosti. Automatické kontroly tuto chybovost snižují, ovšem ani ty nemusí být perfektní. Z praxe znám příklad, kdy obchodník na primárním systému zadal nevalidní znak do pole s adresou zákazníka. V aplikaci na poli s adresou žádná kontrola nevalidních znaků nebyla. Na transportní vrstvě ani v ETL krocích se s parsováním nevalidních znaků také nepočítalo. Záznam se nahrál do databáze datového skladu. Na první pohled není taková chyba nijak závažná a za běžných podmínek by se na ní patrně ani nepřišlo. Z dat se následně pomocí databázových procedur připravila dávka (extrakt) pro datamart partnerské společnosti zákazníka. Partnerská společnost však nebyla schopná kvůli tomuto nevalidnímu znaku nahrát data do své databáze a vzhledem k nastaveným smlouvám žádala o opravu dávky. Vzhledem k časové náročnosti nebyla provedena změna ETL kódu ani procedur. Metodika projektu neumožnovala zasahovat do ETL kódu a vytvářet jakési workaroundové řešení. Nevalidní znak se tak musel každý den ručně mazat do doby, než byl záznam upraven na primárním systému. 4.4.2 Nedobíhající úloha Při provozu datových skladů se setkáváme s různými typy chyb. Nejčastěji se setkáváme s chybou, která zpracování předčasně ukončí. V takovém případě dostaneme informaci 79
pomocí inteligentního monitorovacího nástroje a chybu jsme většinou schopni pomocí informace z logu relativně jednoduše odhalit. Hůře dohledatelnou chybou jsou úlohy, které nedobíhají. Úlohy můžeme rozdělit na ty, které spouštíme prostřednictvím ETL nástroje a ty, které jsou spouštěny v rámci databáze datového skladu. Důvodů nedobíhání úloh může být v obou případech několik. U databáze je typickým scénářem nedobíhání špatně napsaného SQL dotazu. Jiným, z technického hlediska zajímavějším důvodem, jsou špatně napočítané nebo chybějící statistiky. V takovém případě se může vytvořit exekuční plán na základě předpokladu, že tabulka obsahuje několik desítek záznamů, a ve skutečnosti tabulka obsahuje několik milionů záznamů. Řešení spočívá v analýze exekučního plánu, odhalení problematické tabulky a přepočítání statistik. Nedobíhající úlohy v ETL nástroji mohou být způsobeny nedostatkem systémových prostředků ETL serveru u extrémně náročných úloh. Jiným důvodem je zaseknutí z důvodu výskytu dat, které z nějakého důvodu nevyhovují logice ETL. Může jít například o neodfiltrované duplikátní záznamy. Nejčastěji jde o špatná data na zdroji nebo nekorektně implementovanou logiku ETL. Například když se snažíme načíst prázdnou hodnotu do tabulky s polem definovaným jako „not null“. 4.4.3 Úlohy se skrytými chybami Nejhorší variantou chyb jsou ty, které nejsou na první pohled patrné pádem nebo nedoběhnutím úlohy. Tyto chyby jsou charakteristické tím, že úloha doběhne z provozního pohledu korektně, některé záznamy však „zahodí“ do logu nebo do chybové tabulky v databázi datového skladu. Důvody takového chování úloh v ETL nástroji jsou stejné jako v předchozím případě chybou na datech nebo v logice ETL. I když jsem se setkal s mnoha takto padlými úlohami, striktně definovat důvody není možné. Zákeřnost těchto chyb spočívá v tom, že si jich nevšimneme ihned, ale až dodatečně. Některé důležité tabulky navíc přicházejí ze zdrojových systémů nepravidelně a to celou situaci dále komplikuje. Pokud se taková chyba stane na důležité tabulce, jejíž data se používají k transformaci dalších 30 tabulek a my si problému všimneme až za týden, jedinou rozumnou nápravou v té chvíli často zbývá „návrat DWH“. Tedy návrat k poslední záloze před chybou a poté dohánění do aktuálního stavu. ETL nástroje neobsahují žádnou funkcionalitu, jak takové chyby odhalit. Jediným způsobem je tak napsat si vlastní parsery (analyzátory) logů, které se automaticky spouští po loadu.
80
4.5 Shrnutí Každé řešení velkých datových skladů obsahuje komplexní posloupnost transformačních kroků. Aby se celý proces co nejvíce zjednodušil, využívají vývojové týmy při implementaci pouze zlomek funkcionality ETL nástrojů. Informatica poskytuje zhruba 20 možných transformačních objektů. Na projektu, kde pracuji, se z této nabídky využívá pouze třetina. Přes veškerá zjednodušení se při rozvoji datového skladu neustále objevuje množství chyb, které není jednoduché při provozu odfiltrovat. Řešení je tak nutné doplnit vlastními programy k analýzám chyb.
Závěr Prvním cílem této práce bylo analyzovat různé přístupy k návrhu řešení datového skladu, porovnat různé typy přístupů ke zpracování dat a nalézt nejvhodnější řešení extrakce dat. V této části jsem z velké části čerpal z manuálů a dalších zdrojů, které jsem měl k dispozici. Při porovnávání jednotlivých řešení jsem dospěl k závěru, že optimální řešení datového skladu je postavené na řešení Data Warehouse Appliance od specializovaných dodavatelů jako je Teradata, Greenplum nebo Netezza. Oproti konkurenci mají tyto nástroje výhodu jiného typu databáze, který byl od svého vzniku navržen k maximálnímu využití paralelního zpracování. Konkurence sice svá neefektivní řešení vyvážuje předimenzovaným hardwarem, z hlediska provozu jsou však stále výhodnější specializovaná DWA. Poměrem ceny a výkonu nejlépe vychází řešení od společnosti Greenplum. Zpracování dat ze zdrojových systémů lze realizovat způsobem ETL nebo ELT. ETL je tradiční způsob, který ke zpracování dat využívá ETL server. ETL server je výkonově mnohem slabší, než server datového skladu s paralelní architekturou. K využití síly datového skladu se využívá způsob ELT, který data nejdříve načte do datového skladu a až poté je zpracuje. V této oblasti má největší potenciál nástroj Oracle Data Integrator. Nástroj je stále ve vývoji, za několik let však velmi pravděpodobně bude patřit k nejlepším nástrojům na trhu. Tradiční dodavatelé Informatica a IBM si sílu ELT uvědomují a byli nuceni jí přidat do svých nástrojů, ačkoliv tak ztrácejí možnost inkasovat více peněz na drahých licencích ETL serverů. Extrakci v tradičním datovém skladu lze nejlépe realizovat skrze transportní server. Transportní server vytváří mezivrstvu mezi tradičním přístupem rozhraní zdrojových systémů. Z hlediska extrakce je mnohem jednodušší přistupovat pouze na jeden fyzický server, kde jsou již data předpřipravená v jednotném kódování. Řešením budoucnosti
81
jsou aktivní datové sklady, které načítají data ze zdrojových systémů téměř v reálném čase. Tuto funkcionalitu umožnuje spojení řešení Oracle Golden Gate a ODS. Druhým cílem bylo porovnat různé přístupy k řízení a provozu datových skladů. Provozní činnosti jsou prakticky u všech datových datových skladů podobné, jelikož vycházejí ze stejného cíle. Totiž zabezpečit bezchybný průběh zpracování dat ze zdroje až do cíle. V této části jsem vycházel pouze z vlastních zkušeností. Bylo třeba vycházet z několika předpokladů. Pokud chce podnik využívat data z minulého dne již na začátku pracovní doby, na load zbývá okno pouze necelých osmi hodin. Ve velkých podnicích za tuto dobu zpracování všech dat není možné stihnout. Pokud je nutné zpracovat některá data před začátkem pracovní doby, musí se použít nějaké doplňující řešení, například zpracování dat do ODS. S provozem, který lze považovat za správný a dlouhodobě udržitelný, souvisí efektivní monitorování loadu. Monitoring si lze velmi ulehčit v závislosti na tom, jak je projekt navržen a implementován. Pro většinu vývojových týmů je hlavní prioritou korektně implementovat požadovanou funkcionalitu. Hledisko provozu v té době nikoho tolik nezajímá. Monitoring je však z velké části závislý na tom, zda se používají vlastní vyvinuté nástroje k řízení loadu. V případě, kdy řešení závisí hlavně na interní funkcionalitě ETL nástrojů se provoz stává mnohem komplikovanějším. Projekty datových skladů nejsou statické, ale v čase se neustále mění. Z toho důvodu je důležité udržovat více prostředí infrastruktury. Tedy produkční prostředí, vývojové prostředí, testovací prostředí a v ideálním případě ještě předprodukční prostředí. Změnám ETL řešení je nutné přizpůsobit i procesy pro provoz a řízení datového skladu. Souhrnně jsou tyto procesy řízení nazývané Change Management. Se změnami souvisejí možné chyby. Horší úroveň monitoringu může přispět k tomu, že se na chyby nepřijde včas a je nutné vracet se k datovým zálohám. Z toho důvodu k provozu datových skladů neodmyslitelně patří i pravidelné zálohovaní a jiné typy ochrany dat. Přitom stále existují projekty, kde se zálohování ani případné nouzové scénáře nijak neberou v potaz. V případě závažnějšího problému pak může být kvalita dat v datovém skladu vážně ohrožena. Posledním cílem bylo přiblížit provoz z praktického hlediska a určit nejdůležitější součásti ETL řešení. Řešení velkých datových skladů současnosti je obvykle založené na ETL nástroji. Funkcionalita ETL nástroje a způsob jeho fungování tak z velké části ovlivňuje koncepci, jak řešení provozovat a dále rozvíjet. Z toho důvodu jsem v poslední části věnoval velkou část analýze platformy Informatica Powercenter. V porovnání s jinými ETL nástroji 82
poskytuje specifickou architekturu ke zpracování velkých objemů dat, její výhody jsou však vykoupené vysokou cenou licencí a v některých ohledech nedostatečnou funkcionalitou klientských nástrojů. Informatica naštěstí umožnuje funkcionalitu klientských nástrojů nahradit pomocí příkazů. Díky příkazům tak lze na Informatice postavit velmi sofistikované a dobře monitorovatelné řešení. V závěru práce jsem se snažil poukázat na nejdůležitější kroky celého ETL řešení. Při té příležitosti jsem některé z nich porovnal proti teoretickým základům. Díky tomu bylo možné poukázat na jisté odlišnosti mezi teorií a praxí. Případné chyby v ETL řešení je možné řešit procesně a neprocesně. Lepší variantou je procesní oprava přes vývojový tým. Neprocesní varianta opravou přes pracovníky provozu je zase levnější. Provoz velkých datových skladů je komplexní problematika, kterou nelze nějak jednoduše shrnout. Zpracovaná témata v mé diplomové práci jsou pouze průřezem celé oblasti provozu DWH. Velké podniky, které si chtějí pořídit datový sklad, musí počítat s investicí v řádu mnoha desítek či spíše několika stovek milionů korun. To, jaký podnik zaujme postoj ke všem výše zmíněným aspektům návrhu a provozu, se po úspěšné implementaci v krátkodobém horizontu neprojeví. Z dlouhodobého hlediska je to však zásadní.
83
Terminologický slovník Termín
Zkratka
Význam (zdroj)
Business Intelligence
BI
Business Intelligence (BI) je moderním způsobem užití informačních technologií při analýzách, plánování a rozhodování firmy (1).
Data Warehouse
DWH
Datový sklad je subjektově orientovaný, integrovaný, časově rozlišený a neměnný soubor dat pro podporu metod vytváření rozhodnutí managementu (2).
Extract Transform and Load
ETL
ETL je proces získávání dat ze zdrojových systémů a skládá se ze tří částí. Extrakce dat ze zdrojových systémů, Transformace dat do požadované podoby, Load dat do cílové databáze.
Extract Load and Transform
ELT
ELT proces je založený na zpracování dat mimo ETL server. Výhodou ELT je využití výkonu silného serveru datového skladu.
Informatica Powercenter
INFA
Produkt společnosti Informatica k realizaci ETL.
Oracle Data Integrator
ODI
Produkt společnosti Oracle k realizaci ETL způsobem ELT.
Online Transaction Processing
OLTP
OLTP systémy jsou systémy optimalizované na
zpracování
velkého
množství
databázových transakcí. Data Warehouse Appliance
DWA
„Zařízení
datového
skladu“.
DWA
představuje spojení databázového softwaru a serveru, tedy jakési all-in-one řešení. Teradata
Dodavatel řešení velkých datových skladů na principu DWA.
Parsing Engine
PE
Komponenta, která slouží jako prostředník v komunikaci mezi aplikací a databází Teradaty.
Banyan Network
BYNET
BYNET je kombinace vysokorychlostního síťového softwaru a hardware používaného v DWA Teradata.
Access Module Processor
AMP
AMP je samostatná nezávislá jednotka 84
s vlastním CPU a RAM. AMP obsluhuje část databáze, respektive má kontrolu nad částí každé tabulky v systému. Virtual Disk
Vdisk
Virtuální diskový prostor, který je přiřazen AMPu.
Master Index
Systémová struktura, která obsahuje odkazy do cylinder indexů.
Cylindex Index
Cylinder index popisuje uložení dat na disku tím, že obsahuje odkazy na všechny datové bloky na disku.
Exadata
Exadata je řešení DWA od společnosti Oracle. Hybridní řešení, které lze použít jako datový sklad nebo jako OLTP server.
Storage Server
Server pro předzpracování dat v DWA Exadata.
Storage Index
Index
využívaný
Storage
Serverem
k efektivnějšímu vyhledání záznamu. Cose-based optizer
Databázový program k vytvoření vlastního plánu realizace SQL.
Load
Každodenní zpracování dat ze zdrojových systémů do datového skladu.
Stav datového skladu D-1
Označení aktuálního stavu dat v datovém sladu.
„D“
znamená dnešní
den,
„-1“
vyjadřuje data ze zdrojových systémů za předešlý den uzavřené o půlnoci. Stavový load
Načtení celé tabulky znovu.
Inkrementální load
Načtení pouze nových datových přírůstků.
Process Dispatch Center
PDC
Fallback
PDC je externí nástroj k řízení loadu. Softwarová ochrana dat v databázi Teradata. Fallback ke každému řádku v tabulce vytvoří jeho kopii. Originál i kopie řádku jsou poté podle speciálního algoritmu uloženy na dva různé AMPy.
Backup and Restore
BAR
Strategie k pravidelnému zálohování dat z datového skladu do páskové knihovny.
Pásková knihovna
Složitý zálohovací systém využívající jako zálohovací médium magnetické pásky místo 85
klasických pevných disků Slowly Changing Dimensions
SCD
Pomalu měnící se dimenze. „Pomalu měnící se“ představuje to, že se data mění postupně a neperiodicky. Dimenze logicky sdružuje stejný typ dat jako například informace o pobočkách, produktech nebo zákaznících.
Operační klíče
OK
Operační klíč je generovaná hodnota, která jednoznačně
identifikuje
nový
záznam
v datovém skladu. Change Management
Řízení změn v datovém skladu. Change managementem jsou definované procesy k řízení uživatelských požadavků na změnu funkcionality ETL.
Repository
Repository je uložiště metadat ETL nástroje. Repository obsahuje všechny objekty ETL nástroje, které definují logiku ETL.
Pmcmd
Příkaz
k Informatice,
který
slouží
pro
komunikaci s enginem ETL nástroje. ODBC
Open
Database ODBC
Connectivity
Aplikační rozhraní pro přístup k datům, které
je
přímou
součástí
operačního
systému. Powerchannel
PWX
Nativní rozhraní ETL nástroje Informatica pro připojení k databázi.
86
Seznam literatury 1. OTA NOVOTNÝ, JAN POUR, DAVID SLÁNSKÝ. Business intelligence : jak využít bohatství ve vašich datech. Praha: Grada, 2005. ISBN 80-247-1094-3. 2. KIMBALL, RALPH. The data warehouse toolkit: the complete guide to dimensional modeling. New York: John Wiley & Sons, 2002. ISBN 0-471-20024-7. 3. MAWLA, MOHAMMED. SQL server Licensing changes “may” mean something for DBAs. pythian.com. [Online] 28. prosinec 2011. [cit. 2012-01-04.] http://www.pythian.com/news/29617/sql-server-licensing-changes-may-meansomething-for-dba/. 4. ŠPRUNGL, PETR. Bazén bez vody aneb jak nejlépe získat data do datového skladu. AQblog. [Online] 24. listopad 2011. [cit. 2012-10-20.] http://www.aquasoft.eu/blog/nazor_odbornika.php?nazor=444. 5. KMPDBA. Oracle Golden Gate Installation Steps. [Online] 16. prosinec 2011. [cit. 2012-09-02.] http://kmpdba.wordpress.com/2011/12/16/oracle-goldengateinstallation/. 6. TECHSUPPORT. What is Unicode, ASCII, and ANSI? iOpus forum. [Online] 1. březen 2007. [cit. 2012-09-30.] http://forum.iopus.com/viewtopic.php?t=2783. 7. VAVRUŠKA, JINDŘICH. ETL a kvalita dat. systemonline.cz. [Online] březen 2003. [cit. 2012-09-25.] http://www.systemonline.cz/clanky/etl-a-kvalita-dat.htm. 8. SMITH, LYLE. Teradata Data Warehouse Appliance 2690 Now Available. storagereview.com. [Online] 15. únor 2012. [cit. 2012-12-02.] http://www.storagereview.com/teradata_data_warehouse_appliance_2690_now_availa ble. 9. TERADATA. Teradata Utility Pack. [Online] 2012. [cit. 2012-11-07.] http://www.teradata.com/tools-and-utilities/utility-pack/. 10. CUNNINGHAM, LEWIS. ExaThis, ExaThat - What are these Exadata and Exalogic thingies? http://it.toolbox.com. [Online] 3. říjen 2010. [cit. 2012-10-02.] http://it.toolbox.com/blogs/oracle-guide/exathis-exathat-what-are-these-exadata-andexalogic-thingies-41644. 11. ORACLE. Oracle Exadata: World's Fastest Database Machine. youtube.com. [Online] 2. srpen 2011. [cit. 2012-10-15.] http://www.youtube.com/watch?v=ZHlFDgci9Fc. 87
12. ORACLE. Oracle Database Data Warehousing Guide 11g Release 1 (11.1). docs.oracle.com. [Online] 2012. [cit. 2012-10-15.] http://docs.oracle.com/cd/B28359_01/server.111/b28313/indexes.htm. 13. NANDA, ARUP. Smart Scans Meet Storage Indexes. oracle.com. [Online] červen 2011. [cit. 2012-10-16.] http://www.oracle.com/technetwork/issue-archive/2011/11may/o31exadata-354069.html. 14. ORACLE. Oracle® Database Performance Tuning Guide 11g Release 2 (11.2). http://docs.oracle.com. [Online] 2012. [cit. 2012-11-14.] http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm. 15. FRANCISCO, PHIL. Oracle Exadata and Netezza TwinFin Compared. ibm.com. [Online] 2012. [cit. 2012-11-18.] http://www05.ibm.com/innovation/uk/facts/resources/exadata-twinfincompared.pdf?lnk=neiotuk_competitiveq32011&lm=S&lsr=analytics_main_ebook_pdf&lot=A&lpg=capabilities_competitive. 16. NETEZZA. Data Warehouse Appliance Comparison Netezza vs Exadata. http://www.youtube.com. [Online] 31. březen 2012. [cit. 2012-10-20.] http://www.youtube.com/watch?v=mQFyUTNF7eE. 17. MALIK, OM. With New Appliance, EMC Takes on Teradata, Oracle. gigaom.com. [Online] 13. říjen 2010. [cit. 2012-11-07.] http://gigaom.com/cloud/with-newappliance-emc-takes-on-teradata-oracle/. 18. MICROSOFT. SQL Server. microsoft.com. [Online] 2012. [cit. 2012-11-07.] http://www.microsoft.com/sqlserver/en/us/solutions-technologies/appliances.aspx. 19. WOLFRAMALPHA. www.wolframalpha.com. [Online] 1. prosinec 2012. [cit. 201212-01.] http://www.wolframalpha.com/. 20. JANNISE, STEPHEN. Oracle Mergers & Acquisitions: Who’s Next? blog.softwareadvice.com. [Online] 3. srpen 2010. [cit. 2012-11-24.] http://blog.softwareadvice.com/articles/enterprise/oracle-mergers-acquisitions-whosnext-1080310/. 21. GRAHAM, COLLEEN. Oracle the clear leader in $24 billion RDBMS market. itknowledgeexchange.techtarget.com. [Online] 12. duben 2012. [cit. 2012-10-08.] http://itknowledgeexchange.techtarget.com/eye-on-oracle/oracle-the-clear-leader-in24-billion-rdbms-market/.
88
22. LIU, MACLEAN. Exadata X2-2 vs EMC Greenplum DCA vs Netezza TwinFin. askmaclean.com. [Online] 23. říjen 2011. [cit. 2012-10-18.] http://www.askmaclean.com/archives/exadata-x2-2-vs-emc-greenplum-dca-vsnetezza-twinfin12%E4%B8%BB%E8%A6%81%E9%85%8D%E7%BD%AE%E5%A4%A7%E5%AF%B 9%E6%AF%94.html. 23. EVANS, BOB. Larry Ellison: As We Thump IBM, Our Next Target Is Teradata. forbes.com. [Online] 28. září 2012. [cit. 2012-11-18.] http://www.forbes.com/sites/oracle/2012/09/28/larry-ellison-as-we-thump-ibm-ournext-target-is-teradata/. 24. DIJCKS, JEAN-PIERRE. Price comparison for Big Data Appliance. blogs.oracle.com. [Online] 14. březen 2012. [cit. 2012-11-15.] https://blogs.oracle.com/datawarehousing/entry/price_comparison_for_big_data. 25. MONASH, CURT. Data warehouse appliances. dbms2.com. [Online] 2012. [cit. 201211-15.] http://www.dbms2.com/category/database-management-system/datawarehouse-appliances/. 26. SCHILLER, MARTIN. SystemOnLine. [Online] březen 2003. [cit. 2012-07-10.] http://www.systemonline.cz/clanky/co-se-skryva-pod-zkratkou-etl.htm. 27. BUSINESSINTELLIGENCE.COM. Third Generation ETL: Delivering the Best Performance (Part 1). http://businessintelligence.com. [Online] 1. květen 2004. [cit. 2012-09-20.] http://businessintelligence.com/third-generation-etl-delivering-the-bestperformance-part-1/. 28. SEMANTICDESIGNS. DMS® Software Reengineering Toolkit™. semdesigns.com. [Online] 2003. [cit. 2012-09-30.] http://www.semdesigns.com/Products/DMS/DMSToolkit.html. 29. LOPEZ, JORGE A. ETL vs. ELT: A Tale of Staging and Set Based Processing. blog.syncsort.com. [Online] 25. březen 2011. [cit. 2012-11-15.] http://blog.syncsort.com/2011/03/etl-vs-elt-a-tale-of-staging-and-set-basedprocessing/. 30. KAJEEPETA, SREEDHAR. Is it Time to Switch to ELT? enterpriseinformationmanagement.wordpress.com. [Online] 8. červen 2010. [cit. 201211-20.] http://enterpriseinformationmanagement.wordpress.com/2010/06/08/is-ittime-to-switch-to-elt/. 89
31. EDMONDSON, GARRETT. To ETL or To ELT? garrettedmondson.wordpress.com. [Online] 4. listopad 2012. [cit. 2012-11-20.] http://garrettedmondson.wordpress.com/2012/11/04/to-etl-or-to-elt/. 32. GERARD, NICOLAS. ODI - The key features and benefits. gerardnico.com. [Online] 11. leden 2010. [cit. 2012-10-10.] http://gerardnico.com/wiki/dit/odi/key_feature. 33. INFORMATICA. Powercenter. informatica.com. [Online] 2012. [cit. 2012-11-10.] http://www.informatica.com/us/products/enterprise-data-integration/powercenter/. 34. MCBURNEY, VINCENT. Oracle BI Gambit: Cutting the Price of Informatica by over 70%. it.toolbox.com. [Online] únor 2009. [cit. 2012-10-30.] http://it.toolbox.com/blogs/infosphere/oracle-bi-gambit-cutting-the-price-ofinformatica-by-over-70-30160. 35. NETMECHANICA. Advanced SNMP trap handling. www.netmechanica.com. [Online] 2012. [cit. 2012-11-20.] http://www.netmechanica.com/SNMP_Trap_Handling/. 36. ORACLE. Oracle® Database 2 Day + Data Warehousing Guide 11g Release 1 (11.1). http://docs.oracle.com. [Online] 2012. [cit. 2012-11-20.] http://docs.oracle.com/cd/B28359_01/server.111/b28314/tdpdw_bandr.htm. 37. FOLKSTALK. Informatica Architecture Tutorial - Version 8 / 9. http://www.folkstalk.com. [Online] červenec 2012. [cit. 2012-11-20.] http://www.folkstalk.com/2012/07/informatica-architecture-tutorial.html. 38. DWH. Informatica Architecture. http://dwhetltool.blogspot.cz. [Online] 29. duben 2012. [cit. 2012-11-20.] http://dwhetltool.blogspot.cz/2012/04/informaticaarchitecture.html. 39. FOX, STEVE. Tuning SQL Server Integration Services (SSIS) packages so they run faster. www.youtube.com. [Online] 30. říjen 2012. [cit. 2012-11-20.] http://www.youtube.com/watch?feature=player_embedded&v=LWgsv0F8Ycw#!. 40. INFORMATICA. what is the "metadata extensions" tab in Informatica. http://www.dwbiconcepts.com. [Online] 8. únor 2012. [cit. 2012-11-20.] http://www.dwbiconcepts.com/business-intelligence-forum/what-is-the-metadataextensions-tab-in-informatica.html. 41. ITTOOLBOX. Surrogate Key. http://datawarehouse.ittoolbox.com. [Online] 17. březen 2003. [cit. 2012-11-20.] http://datawarehouse.ittoolbox.com/documents/surrogate-key-18121. 90
42. RAINARDI, VINCENT. A Data Warehouse with No Surrogate Keys. http://dwbi1.wordpress.com. [Online] 9. květen 2012. [cit. 2012-11-20.] http://dwbi1.wordpress.com/2012/05/09/a-data-warehouse-with-no-surrogatekeys/. 43. POLÁŠEK, MAREK. Jak poznat kvalitní systém business intelligence? systemonline.cz. [Online] červen 2003. [cit. 2012-11-20.] http://www.systemonline.cz/clanky/jakpoznat-kvalitni-system-business-intelligence.htm. 44. TOOLKIT, DATA WAREHOUSE ETL. Data Warehouse ETL Toolkit. euniversity.wisdomjobs.com. [Online] 2012. [cit. 2012-11-20.] http://euniversity.wisdomjobs.com/data-warehouse-etl-toolkit/chapter-1321201/extracting.html. 45. SYSTEMONLINE. Dodavatelé řešení Datawarehouse a Business Intelligence. http://www.systemonline.cz. [Online] 20. listopad 2012. [cit. 2012-11-20.] http://www.systemonline.cz/dodavatele-it-sluzeb-a-reseni/dodavatele-dw-bi/. 46. DBASOLUTIONS. Exadata Command Reference. dbasolutions.wikispaces.com. [Online] 2012. [cit. 2012-11-20.] http://dbasolutions.wikispaces.com/Exadata+Command+Reference. 47. MCBURNEY, VINCENT. Informatica Too Pricy in Gartner Magic Quadrant for Data Integration Tools 2012. http://it.toolbox.com. [Online] 31. říjen 2012. [cit. 2012-11-20.] http://it.toolbox.com/blogs/infosphere/informatica-too-pricy-in-gartner-magicquadrant-for-data-integration-tools-2012-53777. 48. INFORMATICA. Leading Market Researcher Identifies Informatica as Data Integration “Champion”. http://www.informatica.com. [Online] 18. červen 2012. [cit. 2012-11-20..] http://www.informatica.com/us/company/news-and-eventscalendar/press-releases/06182012-infotech-vendor-landscape-dataintegrationchampion.aspx. 49. MARK A. BEYER, DONALD FEINBERG, MERV ADRIAN, ROXANE EDJLALI. Magic Quadrant for Data Warehouse Database Management Systems. http://www.gartner.com. [Online] 5. březen 2012. [cit. 2012-11-20..] http://www.gartner.com/technology/reprints.do?id=1-196T8S5&ct=120207&st=sb. 50. KLOPP, ROB. Price/Performance of HANA, Exadata, Teradata, and Greenplum. robklopp.wordpress.com. [Online] 15. listopad 2012. [cit. 2012-11-20.] http://robklopp.wordpress.com/2012/11/15/priceperformance-of-hana-exadatateradata-and-greenplum/. 91
51. TERADATA. Teradata Architecture. www.teradata-tutorials.com. [Online] 2012. [cit. 2012-11-20.] http://teradata-tutorials.blogspot.cz/2010/06/teradataarchitecture.html.
92
Seznam obrázků a tabulek Obrázek 1 Typy a ceny licencí MS SQL server 2012 (3) ................................................................ 16 Obrázek 2 Architektura Oracle GoldenGate (5) ............................................................................... 23 Obrázek 3 Komponenty DWA Teradata (51) .................................................................................... 27 Obrázek 4 Komponenty DWA Exadata (46)....................................................................................... 29 Obrázek 5 Exadata Storage Index (13) ................................................................................................ 32 Obrázek 6 Magický čtyřúhelník DWH DBMS (52) ........................................................................... 34 Obrázek 7 ETL versus ELT (32) .............................................................................................................. 40 Obrázek 8 ODI versus klasický návrh ETL (32) ................................................................................ 42 Obrázek 9 Infotech DataIntegration Tools Vendor Landscape (51)......................................... 43 Obrázek 10 Gartner Magic Quadrant for Data Integration Tools 2012 (50) ......................... 43 Obrázek 11 Externí monitorovací nástroj PDC (Process Dispatch Center) ........................... 55 Obrázek 12 SNMP trap handling (35) .................................................................................................. 56 Obrázek 13 Architektura Informatiky (38) ........................................................................................ 64 Obrázek 14 Informatica Powercenter Designer ............................................................................... 66 Obrázek 15 Definice struktury zdroje v nástroji Source Analyzer ............................................ 67 Obrázek 16 Připojení na databázi skrze ODBC a nativní connector (48) ............................... 72
Tabulka 1 Srovnání ceny DWA za TB (53) .......................................................................................... 35 Tabulka 2 Rozdělení trhu DBMS dodavatelů (21) ........................................................................... 36 Tabulka 3 Počet zákazníků dle studií Gartner Magic Quadrant 2008-2012 ......................... 44
93
