Moderní přístupy tvorby datových skladů

Mendelova univerzita v Brně Provozně ekonomická fakulta

Moderní přístupy tvorby datových skladů Diplomová práce

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

Bc. Luboš Bednář

Brno, 2010

Rád bych touto cestou poděkoval panu Ing. Janu Přichystalovi, Ph.D., za odborné vedení, cenné rady a připomínky, které mi poskytl při zpracování této diplomové práce. Dále patří podikování i panu Ing. Petru Kusému za odborné rady a mým blízkým za veškerou podporu během studia.

Prohlašuji, že jsem tuto diplomovou práci na téma „Moderní přístupy tvorby datových skladůÿ vypracoval samostatně s použitím literatury, kterou uvádím v seznamu.

V Brně dne 22. prosince 2010

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

4

Abstract Bednář, L. Modern approaches to data warehouse creation. Diploma thesis. 2010 This thesis deals with design of methodology ensuring configuration, logging, testing, deployment, and maintainance of data warehouses throught defined principles and creating appropriate tools. It describes the theory of this issue, analysis current methodology of data warehouse creation and proceed with implementation of own work expanding methodology with concrete principles and tools, which are applied in complex project creation. Key words: Business Intelligence, configuration, data warehouse, deployment, ETL, logging, maintainance, OLAP, SQL Server, testing.

Abstrakt Bednář, L. Moderní přístupy tvorby datových skladů. Diplomová práce. 2010 Práce se zabývá návrhem metodiky zajišťující konfiguraci, logování, testování, nasazení, údržbu a provoz datových skladů prostřednictvím definovaných principů a vytvořením odpovídajících nástrojů. Popisuje teorii týkající se dané problematiky, analyzuje současnou metodiku tvorby datových skladů a pokračuje v realizaci vlastní práce rozšiřující metodiku o konkrétní principy a nástroje, které jsou aplikovány při tvorbě komplexního projektu. Klíčová slova: Business Intelligence, datový sklad, ETL, konfigurace, logování, nasazení, OLAP, SQL Server, testování, údržba.

5

OBSAH

Obsah 1 Úvod a cíl práce 1.1 Úvod práce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 Cíl práce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Datové sklady a Business Intelligence 2.1 Definice pojmů . . . . . . . . . . . . . . . . . . . . . . 2.1.1 Business Intelligence . . . . . . . . . . . . . . . 2.1.2 Datový sklad . . . . . . . . . . . . . . . . . . . 2.2 Vývoj Business Intelligence . . . . . . . . . . . . . . . 2.3 Základní principy . . . . . . . . . . . . . . . . . . . . . 2.3.1 Multidimenzionální přístup . . . . . . . . . . . . 2.3.2 Rozdíly mezi OLTP a OLAP . . . . . . . . . . . 2.3.3 OLAP na úrovni relační databáze . . . . . . . . 2.3.4 Granularita . . . . . . . . . . . . . . . . . . . . 2.3.5 Agregace dat . . . . . . . . . . . . . . . . . . . 2.4 Hlavní komponenty datových skladů . . . . . . . . . . 2.4.1 Zdrojové systémy . . . . . . . . . . . . . . . . . 2.4.2 Dočasné úložiště dat . . . . . . . . . . . . . . . 2.4.3 ETL . . . . . . . . . . . . . . . . . . . . . . . . 2.4.4 Datový sklad a datové tržiště . . . . . . . . . . 2.4.5 OLAP databáze . . . . . . . . . . . . . . . . . . 2.4.6 Klientské aplikace (Reporting) . . . . . . . . . . 2.4.7 Dolování dat . . . . . . . . . . . . . . . . . . . 2.5 Přístupy k tvorbě datových skladů . . . . . . . . . . . 2.5.1 Jednorázové budování datového skladu . . . . . 2.5.2 Postupné (iterativní) budování datového skladu 2.6 Životní cyklus datového skladu . . . . . . . . . . . . . 2.7 Role v projektu datového skladu . . . . . . . . . . . . . 2.8 Užití datových skladů . . . . . . . . . . . . . . . . . . . 3 Metodika tvorby datového skladu 3.1 Projektový plán . . . . . . . . . . . . . . . . 3.2 Modelování datového skladu . . . . . . . . . 3.3 Modelování technologické platformy . . . . . 3.3.1 Návrh technologické architektury . . 3.3.2 Návrh fyzického modelu . . . . . . . 3.3.3 Návrh objemu datového skladu . . . 3.4 Implementace . . . . . . . . . . . . . . . . . 3.4.1 Předimplementační příprava . . . . . 3.4.2 Implementace dočasného úložiště dat 3.4.3 Implementace datového skladu . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

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

. . . . . . . . . .

8 8 8

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

10 10 10 11 11 12 12 12 14 16 17 17 17 18 19 19 20 20 21 22 22 22 22 23 24

. . . . . . . . . .

25 25 26 28 28 29 30 32 32 32 33

6

OBSAH

3.5

3.6

3.7

3.4.4 Implementace ETL . . . . . . . . 3.4.5 Implementace OLAP databází . . 3.4.6 Implementace klientských aplikací Testování . . . . . . . . . . . . . . . . . 3.5.1 Validace modelu datového skladu 3.5.2 Testování dočasného úložiště dat 3.5.3 Testování datového skladu . . . . 3.5.4 Testování OLAP databází . . . . 3.5.5 Testování reportů . . . . . . . . . Nasazení . . . . . . . . . . . . . . . . . . 3.6.1 Příprava plánu nasazení . . . . . 3.6.2 Zaškolení uživatelů . . . . . . . . 3.6.3 Nasazení . . . . . . . . . . . . . . Provoz, údržba a rozvoj . . . . . . . . . 3.7.1 Údržba datového skladu . . . . . 3.7.2 Rozvoj datového skladu . . . . .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

33 34 35 36 36 36 37 37 38 38 38 39 39 40 40 40

4 Metodická východiska práce 41 4.1 Použitá metodologie . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 4.2 Použité nástroje a technologie . . . . . . . . . . . . . . . . . . . . . . 41 5 Vlastní práce 5.1 Konfigurovatelnost ETL řešení . . . . . . . . . 5.1.1 Struktura konfiguračních tabulek . . . 5.1.2 Využití konfiguračních tabulek při ETL 5.2 Logování ETL . . . . . . . . . . . . . . . . . . 5.2.1 Struktura logovacích tabulek . . . . . . 5.2.2 Procedury a funkce . . . . . . . . . . . 5.2.3 Šablony ETL balíků . . . . . . . . . . 5.2.4 Reportovací nástroj . . . . . . . . . . . 5.3 Testování . . . . . . . . . . . . . . . . . . . . 5.3.1 Struktura testovacích tabulek . . . . . 5.3.2 Příprava konfiguračních dat . . . . . . 5.3.3 Realizace testovacího mechanismu . . . 5.3.4 Reportovací nástroj . . . . . . . . . . . 5.4 Nasazení a konfigurace . . . . . . . . . . . . . 5.4.1 Vytvoření struktur datového skladu . . 5.4.2 Konfigurace . . . . . . . . . . . . . . . 5.4.3 Zajištění automatizovaného provozu . . 5.5 Údržba a provoz . . . . . . . . . . . . . . . . . 5.5.1 Struktura tabulek pro údržbu . . . . . 5.5.2 Procedury pro naplnění tabulek . . . . 5.5.3 Reportovací nástroj . . . . . . . . . . .

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

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

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

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

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

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

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

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

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

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

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

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

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

43 43 44 45 47 48 50 51 55 58 58 59 60 63 66 66 66 68 69 69 70 71

7

OBSAH

5.6

Aplikace komponent při realizaci projektu 5.6.1 Projektová příprava . . . . . . . . . 5.6.2 Implementace . . . . . . . . . . . . 5.6.3 Nasazení . . . . . . . . . . . . . . . 5.6.4 Údržba a provoz . . . . . . . . . . 5.6.5 Interpretace a porovnání výsledků .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

75 75 76 79 79 79

6 Diskuse 6.1 Přínosy a užití vytvořených principů a nástrojů . . . . . . . . . . . . 6.2 Možnosti rozšíření . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.3 Srovnání s existujícími řešeními . . . . . . . . . . . . . . . . . . . . .

81 81 81 81

7 Závěr

83

8 Literatura

84

Přílohy

86

A Ukázka zdrojových kódů

87

B Skripty pro zajištění automatizovaného provozu

95

C Projektový plán a multidimenzionální analýza

96

1

ÚVOD A CÍL PRÁCE

1

8

Úvod a cíl práce

1.1

Úvod práce

Datové sklady a Business Intelligence je velmi dynamická oblast stojící na pomezí mezi informatikou, řízením podniku a statistikou. Velké množství firem po celém světě již využívá možnosti, které Business Intelligence nabízí v oblasti analýzy a prezentace informací. Trend nasazování těchto řešení je stále rostoucí a firmy, které efektivně nasazují Business Intelligence získávají konkurenční výhodu ve svém oboru. Tak, jako narůstá množství dat v podniku, ukazuje se potřeba tyto data efektivně zpracovat a přehledně prezentovat. Zavedení ERP1 , CRM2 a jiných informačních systémů bezesporu zlepšuje nejen efektivnost podnikových procesů, ale i centralizaci a snížení chybovosti dat. Nicméně neméně důležité je dále tato data využít. Business Intelligence dokáže z velkého množství uložených a velmi často dále nevyužitých dat efektivně získat znalosti a tím výrazně zkvalitnit rozhodování a řízení podniku. Podle výzkumu společnosti Gartner, jednoho z největších poskytovatelů IT řešení zabývající se mimo jiné i Business Intelligence, bude do roku 2012 více než 35 procent z 5000 největších celosvětových společností selhávat v rozhodování o podstatných změnách v jejich podnikání. Proto se předpokládá, že až 40 procent z rozpočtu firem bude od roku 2012 vynaloženo na analytické aplikace a služby (Gartner Inc., 2009). Z toho plyne, že oblast Business Intelligence a datových skladů má opravdu velký význam a to nejen pro velké firmy, ale i pro ty střední a menší, pro které je práce s informacemi na základě jejich dat neméně důležitá. V současné době si již firmy začínají uvědomovat, že uskutečněním správných rozhodnutí postavených na kvalitních analytických nástrojích dokáží nejen zvýšit obrat, ale i přinést úspory. Vývoj datových skladů a Business Inteligence řešení je specifický tím, že každá realizace se podstatně liší použitými technologiemi, zdrojovými systémy, požadavky apod. Ačkoliv již existuje celá řada řešení popisující realizaci datových skladů, jsou tato řešení obvykle velmi specifická a mohou sloužit většinou pouze k inspiraci při vývoji vlastního projektu. Definování principů, které lze obecně využít při tvorbě datových skladů, je prospěšnější než současný stav nabízející pouze návody na realizaci konkrétních projektů.

1.2

Cíl práce

Cílem diplomové práce je návrh obecné metodiky zajišťující konfiguraci, logování, testování, nasazení, údržbu a provoz datových skladů prostřednictvím definovaní principů a vytvořením odpovídajících nástrojů. Uvedené principy jsou totiž podmínkou pro realizaci správně fungujícího datového skladu. Pokud je na počátku 1 2

Enterprise Resource Planning Customer Relationship Management

1.2

Cíl práce

9

zvolen nesprávný přístup a zanedbány komponenty jako obecná konfigurace, logování a automatizované testování, pak je v praxi projekt většinou velmi nákladný a může být nejen složité jej dokončit, ale i spravovat . Na základě vytvořené metodiky je v poslední části provedena realizace datového skladu aplikací uvedených komponent.

2

DATOVÉ SKLADY A BUSINESS INTELLIGENCE

2

10

Datové sklady a Business Intelligence

2.1 2.1.1

Definice pojmů Business Intelligence

Business Intelligence (BI) představuje komplex přístupů a aplikací IS/ICT3 , které téměř výlučně podporují analytické a plánovací činnosti podniků a organizací a jsou postaveny na principu multidimenzionality, kterým zde rozumíme možnost pohlížet na realitu z několika možných úhlů (Novotný, Pour, Slánský, 2005, s. 1). V rámci širšího pojetí si lze pod termínem BI představit celkové řešení podporující analytické a rozhodovací procesy v organizaci. V užším pojetí, často používaném firmou Gartner, lze pod BI rozumět analytické platformy pracující nad databází datového skladu a poskytující přímou podporu analytickým a rozhodovacím procesům organizace (Pittner, 2007). Účelem Business Intelligence je konvertovat velké objemy dat na znalosti, které jsou potřebné pro koncové uživatele. Přeměnu dat na informace, informací na znalosti a budování „moudrostiÿ na základě znalostí můžeme zobrazit na hierarchické pyramidě informačních úrovní (obr. 1). Základem všeho jsou data. Data obsahují jen jednoduchá fakta, informace z nich získáme až tehdy, pokud přidáme k datům souvislosti. Když se kromě informací využije i tvořivá inteligence, získáme znalosti. Pokud tyto znalosti zobecníme, získáme „moudrostÿ, to znamená schopnost přesného zhodnocení znalostí a jejich následné uplatnění v reálné praxi (Lacko, 2006, s. 11).

Obr. 1: Hierarchie informačních úrovní (Lacko, 2006, s. 11) 3

Informační systémy a technologie

2.2

Vývoj Business Intelligence

2.1.2

11

Datový sklad

Podle nejznámější definice Billa Inmona je datový sklad podnikově strukturovaný depozitář subjektově orientovaných, integrovaných, časově proměnných, historických dat použitých na získávání informací a podporu rozhodování. V datovém skladu jsou uložena atomická a sumární data (Inmon, 2003, s. 31). Pro správné pochopení této definice je nutné rozumět následujícím pojmům: • Orientace na předmět (subjektová orientace) – Data se do datového skladu zapisují spíše podle předmětu zájmu, než podle aplikace, ve které byla vytvořena. Při orientaci na subjekt jsou data v datovém skladu kategorizována podle subjektu, kterým může být např. zákazník, dodavatel, zaměstnanec, výrobek apod. • Integrovanost – Datový sklad musí být jednotný a integrovaný. To znamená, že data týkající se konkrétního předmětu se do datového skladu ukládají jen jednou. Data přicházejí do datového skladu z nekonzistentního a neintegrovaného operačního prostředí. Proto musí být data v etapě přípravy a zavedení upravena, vyčištěna a sjednocena. Pokud data nejsou konzistentní a důvěryhodná, tak datový sklad ztrácí význam. • Časová variabilita – Data se ukládají do datového skladu jako série snímků, ze kterých každý reprezentuje určitý časový úsek. Na rozdíl od operačního prostředí, kde jsou data platná v okamžiku přístupu, v datových skladech jsou data platná vždy pro určitý časový moment, časový snímek. Typicky jsou v datovém skladu uložena data za delší časové období (několik let). • Neměnnost – Data v datovém skladu se obvykle nemění ani neodstraňují, jen se v pravidelných intervalech přidávají nová data (Lacko, 2006, s. 18-19).

2.2

Vývoj Business Intelligence

Řešení směřující k podpoře manažerských a analytických úloh v podnikovém řízení se začala objevovat již na konci sedmdesátých let minulého století v souvislosti s rozvojem on-line zpracování dat. Prvotní pokusy a aplikace jsou spojeny s americkou firmou Lockheed. V polovině osmdesátých let byly publikovány první významné práce k tomuto typu aplikací (prof. Rockart: „CEO Goes On-lineÿ a některé další). V druhé polovině osmdesátých let přišly na trh v USA první firmy s komerčními produkty, založenými na multidimenzionálním uložení a zpracování dat, označovanými jako EIS4 , a to firmy Comshare a Pilot. Trh s EIS produkty se pak velmi rychle rozvíjel a na začátku devadesátých let (od roku 1993) se tyto produkty začaly prosazovat i na českém IS/ICT trhu. 4

Executive Information System

2.3

Základní principy

12

Koncem osmdesátých a začátkem devadesátých let se v USA začal velmi silně prosazovat i další trend v multidimenzionálních technologiích, a to datové sklady (Data Warehouse) a datová tržiště (Data Marts). Za rozvojem těchto technologií stáli především Ralph Kimball a Bill Inmon. Větší uplatnění datových skladů a tržišť je na českém trhu patrně spíše až v druhé polovině devadesátých let. V souvislosti s datovými sklady a narůstajícím objemem dat v tomto prostředí se v průběhu devadesátých let začaly prosazovat i technologie a nástroje tzv. dolování dat (Data Mining) založené na vysoce sofistikovaných analýzách dat s pomocí nejrůznějších matematických a statistických metod (Novotný, Pour, Slánský, 2005, s. 17).

2.3 2.3.1

Základní principy Multidimenzionální přístup

Pro systémy pracujícími s analytickými informacemi se vžil v osmdesátých letech minulého století název OLAP (On-Line Analytical Processing). OLAP systémy jsou již optimalizované pro uložení a interaktivní využívání multidimenzionálních dat. Základním principem je několikadimenzionální tabulka umožňující velmi rychle a pružně měnit jednotlivé dimenze, a nabízet tak různé pohledy na modelovanou realitu. Jde tak v podstatě o princip „n-dimenzionální Rubikovy kostkyÿ (obr. 2) naplněné nejdůležitějšími podnikovými daty (Novotný, Pour, Slánský, 2005, s. 2122). Z obrázku vyplývá, že standardními dvěma dimenzemi jsou zvolené metriky (sledované ukazatele) a čas. Ostatní dimenze se definují dle potřeby, např. zákazník, produkt, organizační jednotka atd. Obsah dimenzí je tvořen prvky dimenzí, tj. konkrétními zákazníky, produkty apod. Promítnutí všech dimenzí do jednoho bodu tvoří prvek multidimenzionální databáze. Prvky dimenzí jsou většinou uspořádány v hierarchické struktuře, kde se rozdělují na skupiny prvků, podskupiny a jednotlivé prvky. Datové sklady pak zajišťují automatické agregace hodnot (metrik) podle definovaných hierarchických úrovní dimenzí. OLAP systémy lze obecně charakterizovat takto: • Zdrojem dat jsou transakční systémy (produkční databáze). • Data jsou uložena multidimenzionálně. • Obsahují různé úrovně agregace dat podle hierarchické struktury dimenzí. • Zachycují faktor času. 2.3.2

Rozdíly mezi OLTP a OLAP

Data jsou obvykle v podnicích ukládána do transakčních OLTP (On-Line Transaction Processing) databází. Ty jsou určeny pro vykonávání velkého množství online transakcí, například bankovních, obchodních a podobně. Takové databáze

2.3

Základní principy

13

Obr. 2: Princip multidimenzionální databáze [http://www.math.cornell.edu/˜mec/Winter2009/Lipa/Puzzles/lesson2.html] jsou propojeny na IT systémy, jejichž cílem je automatizace každodenních činností, například skladové hospodářství, mzdy, nákup a prodej, případně řízení a monitorování technologických procesů v reálném čase. OLTP databáze jsou z důvodu jednoduchého dotazování a vyloučení redundance zpravidla normalizované, tzn. vyhovují pravidlům tzv. normálních forem. Výsledkem dotazování v transakčních databázích jsou databázové tabulky, souhrny získané pomocí agregačních funkcí, různé sestavy a podobně. Nicméně existuje několik zásadních nevýhod a problémů při snaze využítí OLTP systémů pro analýzu dat. Jsou jimi: • výpočetní výkon – OLTP systémy dosahují vysokých výkonů spíše při on-line transakcích než při složitých analýzách, které jsou velmi náročné na výpočetní kapacitu. • nehomogenní struktura dat – OLTP systémy nemají k dispozici integrovaný zdroj údajů ze všech operačních systémů v rámci podniku tak, aby umožnily tvorbu komplexních analýz. To znamená, že potřebná data jsou roztroušena v různých zpravidla heterogenních OLTP systémech. • uchovávání historických dat – OLTP systémy často neumožňují uchovávání dat po delší dobu (například při změně cen zboží, měnových kurzů), takže v mnoha případech chybí historická data potřebná na komplexní analýzu nebo predikci.

2.3

Základní principy

14

Obr. 3: Schéma hvězdy (Novotný, Pour, Slánský, 2005, s. 114) Komplexní analýza tedy vyžaduje jiné techniky návrhu databází, například použití multidimenzionálních schémat s tabulkami faktů, které obsahují měřitelné jednotky obchodování a vysoce denormalizované tabulky dimenzí. (Lacko, 2006, s. 1317) 2.3.3

OLAP na úrovni relační databáze

Datové modely transakčních systémů jsou velmi rozsáhlé a obsahují mnoho tabulek a jejich vazeb. Právě množství tabulek, které je pro vykonání komplexní analýzy nutné propojit, je největší zátěží systému. Proto byl pro účely tvorby datových skladů vytvořen relační dimenzionální model. Jde o zjednodušení ERD diagramu do typů schémat uvedených na obrázcích 3 a 4, tedy schéma hvězdy a schéma sněhové vločky. Obě tato schémata jsou založena na existenci tzv. tabulky faktů, která se nachází v samém středu schématu. Tabulka faktů obsahuje sledované ukazatele identifikovatelné klíčem složeným z klíčů tzv. dimenzionálních tabulek, v nichž jsou uloženy prvky jednotlivých dimenzí. Dimenzionální tabulky slouží jako úložiště textových informací o hodnotách uložených v tabulce faktů. Rozhodnutí, jestli má být pole zařazeno do faktové tabulky nebo tabulky dimenzionální je závislé na tom, je-li sledovaná veličina měřitelná a měnící se v čase. Pokud ano, patří do faktové tabulky. Pokud ne, a jedná se o hodnotu diskrétní, která vystupuje spíše jako konstanta, pak jde o položku z dimenzionální tabulky.

2.3

Základní principy

15

Obr. 4: Schéma sněhové vločky (Novotný, Pour, Slánský, 2005, s. 114) Rozdíl mezi uvedenými schématy: • Schéma hvězdy – je podstatně rychlejší v době odezvy pro poskytování výstupů, neboť odpadají operace spojování mezi tabulkami jednotlivých úrovní; – nabízí možnost využití bitmapových indexů, jelikož obsahuje sloupce s nízkou kardinalitou; – umožňuje jednodušší prohlížení dimenzí a zadávání filtrů pro všechny hierarchické úrovně dimenze; – je neefektivní při častých změnách v hierarchiích prvků dimenze; – neumožňuje tvořit agregace podle denormalizovaných atributů v rámci tabulky dimenze. • Schéma sněhové vločky – díky normalizaci dat je toto řešení výhodné při častých změnách v dimenzích a v hierarchické struktuře jejich prvků; – vede k úspoře místa v databázi datového skladu, což je ale v důsledku nízkého počtu dat v tabulkách dimenzí zanedbatelné;

2.3

Základní principy

16

– umožňuje využívat prostředky pro vynucení referenční integrity a kardinality; – její využití je nezbytné při tvorbě společné sběrnice dimenzí; – nabízí výhody pro efektivní tvorbu agregačních tabulek; – je méně přehledné ve srovnání se schematem hvězdy; – realizace spojení tabulek je velmi složitá a komplexní a je i v současných databázových systémech časově velmi náročná (Novotný, Pour, Slánský, 2005, s. 117–118). Hlavní rozdíl je tedy v tom, že schéma sněhové vločky je normalizované schéma hvězdy. Jeho hlavní výhodou je snadnější provádění úprav v normalizovaných tabulkách. Na druhou stranu více vazeb mezi tabulkami způsobuje prodloužení vykonávání dotazů, proto schéma sněhové vločky není vhodné pro rozsáhlejší datové sklady. 2.3.4

Granularita

Granularita je jedním z nejvýznamnějších aspektů při návrhu datového skladu. Granularita určuje úroveň podrobnosti údajů, tzn. faktů uložených ve faktové tabulce. Je také přímo závislá na úrovni podrobnosti dimenzí odpovídající příslušné tabulce faktů. Například je-li v časové dimenzi definovaná struktura až na jeden den, a v dimenzi Produkt na jeden dílčí produkt, pak každý záznam v tabulce faktů (zrno) je na úrovni jednoho dílčího produktu a jednoho dne. Tím je dána granularita tabulky faktů (Novotný, Pour, Slánský, 2005, s. 116). Čím více podrobných údajů se v datovém skladu ukládá, tím nižší je úroveň granularity. Například jedna transakce tvoří nízkou úroveň granularity. Na druhou stranu souhrn transakcí za měsíc tvoří vysokou úroveň granularity. Je tedy pochopitelné, že úroveň granularity výrazně ovlivňuje jak celkový objem dat, které se nachází v datovém skladu, tak i úroveň detailu, která může být dotazována. Při návrhu datového skladu je proto vždy nutné zvolit takovou úroveň granularity, která je nejvhodnější pro konkrétní potřeby. V některých případech může být ale obtížné rozhodnout, jaká úroveň granularity je nejvhodnější. Proto lze vytvořit tzv. dvojí úroveň granularity, která umožňuje aktuální data ukládat ve značných podrobnostech a poté po zvoleném časovém úseku tyto data sumarizovat. Vysoká úroveň detailu je obvykle vyžadována u aktuálních dat, například do jednoho měsíce v závislosti na typu dat. Zatímco u starších dat se dotazují většinou již data agregovaná. Takto lze efektivně nastavit granularitu tak, aby bylo uspokojeno největší množství dotazů při udržitelné zátěži na systém (Inmon, 2003, s. 49–52). Typy granularit: • Snímková granularita – Data vstupují do datového skladu ve stejných časových úsecích (snímcích). Volba časového úseku pak samozřejmě ovliv-

2.4

Hlavní komponenty datových skladů

17

ňuje nejen granularitu, ale i nárůst objemu dat v datovém skladu. V praxi je tento typ granularity nejpoužívanější. • Transakční granularita – Do datové skladu vstupují detailní informace vázány na jednotlivé transakce. Časový úsek tak není stejný, ale závisí na době výskytu jednotlivých transakcí. Transakce je vhodné rozdělit časovou dimenzí na čas jednotlivých transakcí a kombinovat se snímkovou granularitou. • Akumulovaná granularita (stavová) – Data vstupující do datového skladu jsou závislá na výskytu transakcí, ale jejich hodnoty se v čase postupně aktualizují. Například při postupném objednávání zboží na sklad se tak udržuje přehled o aktuálním stavu a vývoji dané objednávky (Novotný, Pour, Slánský, 2005). 2.3.5

Agregace dat

Již z principu datových skladů značná část dotazů směřuje na agregovaná data, například kolik hovorů uskutečnil zákazník za první měsíc v daném regionu. Pokud jsou data uložena v granularitě odpovídající dnům, pak pro získání výsledku je třeba sečíst (agregovat) všechny dny daného měsíce odpovídající zadaným kritériím. Nicméně všechny data nelze agregovat stejně. Z tohoto pohledu se data v tabulce faktů rozdělují na: • data plně aditivní, která lze téměř libovolně agregovat (například množství prodaných kusů, prodeje, apod.); • data neaditivní, kde agregace hodnot nedává smysl (například sazba marže v procentech); • data semiaditivní, kde agregované hodnoty mají smysl pouze podle určitých dimenzí. Například u položky Stav zasob Ks mají agregace smysl podle dimenze produktů, ale nikoli v čase. Tento typ agregovaných hodnot se nejčastěji využívá v případě skladů.

2.4

Hlavní komponenty datových skladů

Hlavní komponenty datových skladů jsou uvedeny na obrázku 5. Jsou jimi zdrojové databáze, dočasné úložiště dat, ETL transformace, datový sklad a datová tržiště, OLAP databáze, klientské aplikace a dolování dat. 2.4.1

Zdrojové systémy

Produkční (zdrojové) systémy jsou takové systémy podniku, ze kterých aplikace Business Intelligence získávají data a nepatří do skupiny BI aplikací. Většina těchto

2.4

Hlavní komponenty datových skladů

18

Obr. 5: Komponenty datového skladu (zdroj: http://www.daquas.cz/articles/379seznamte-se-s-bi) systémů je transakčních, podporující ukládání a modifikaci dat v reálném čase, nevhodných pro analytické úlohy. Příkladem těchto systémů mohou být ERP5 , SCM6 , CRM7 a další systémy. Dalším zdrojem pro datové sklady mohou být i externí systémy (výstupy statistického úřadu, telefonní seznamy, apod.). Produkční systémy jsou hlavním, a často i jediným vstupem pro datové sklady. V praxi je zpravidla zdrojových systémů několik, přičemž se jedná běžně o heterogenní systémy, datově i technologicky. Důležitým úkolem je proto tyto systémy důkladně analyzovat a vhodně vybrat relevantní data pro jejich integraci do datových skladů z hlediska budoucích výstupů (Novotný, Pour, Slánský, 2005, s. 28–29) 2.4.2

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

Dočasné úložiště dat, neboli DSA (Data Staging Area), je první komponentou datového skladu. Její úlohou je dočasně uchovávat netransformovaná data z produkčních systémů a podporovat jejich efektivní extrakci, před jejich zpracováním v datovém skladu. Důvodem existence dočasného úložiště je přenesení dat s minimálním dopadem na výkonost produkčních (zdrojových) systémů a konverze dat z heterogenního prostředí do stejného formátu. Vlastnosti dat uložených v DSA: • detailní – data nejsou agregovaná; • nekonzistentní – data nejsou kontrolována na jejich konzistenci; • neobsahující historii – pouze aktuální data se přenášejí ze zdrojových systémů; 5

Enterprise Resource Planning Supply Chain Management 7 Customer Relationship Management 6

2.4

Hlavní komponenty datových skladů

19

• měnící se – před každým snímkem jsou struktury prázdné a načítají se pouze data, která jsou nová nebo modifikovaná; • shodné struktury – data jsou v takové struktuře jako ve zdrojových systémech; • interní – k datům nemají přístup koncoví uživatelé. Dočasné úložiště dat není povinnou komponentou datového skladu, ale v mnoha případech je velmi vhodné jej využít (Novotný, Pour, Slánský, 2005, s. 30). 2.4.3

ETL

ETL (Extraction, Transformation, and Loading) je jednou z nejvýznamnějších komponent datového skladu. Běžně se tato komponenta označuje jako datová pumpa. Jejím úkolem je integrovat data z produkčních systémů do datového skladu. Tento proces se skládá ze tří kroků: • Extraction – získat (extrahovat) data z produkčních (zdrojových) systémů například do dočasného úložiště dat; • Transformation – upravit data do požadované formy a očistit; • Loading – nahrát data do předem definovaných, specifických struktur (schéma) datového skladu. Prostřednictvím ETL se získávají data ze všech zdrojových systémů libovolné struktury a typu. ETL komponenta pracuje v dávkovém režimu automatizovaně, data jsou tedy přenášena v určitém časovém intervalu, nejčastěji denně (Novotný, Pour, Slánský, 2005, s. 29). 2.4.4

Datový sklad a datové tržiště

Datový sklad je souhrn transformovaných, očištěných dat extrahovaných ze zdrojových systémů prostřednictvím komponenty ETL v souladu s definicí datového skladu (viz kapitola 2.1). Tzn. data se zde nachází integrovaná, subjektově orientovaná, stálá a časově rozlišitelná. Datové tržiště je podmnožina datového skladu dle požadavků určitých skupin uživatelů, jejíž vzájemnou integrací je utvářeno celopodnikové řešení. K vytváření datových tržišť se přistupuje z důvodu ekonomického efektu (zkrácení návratnosti investic, snížení nákladů, zmenšení rizika při zavádění) a psychologického efektu (fungující podmnožina datového skladu prohlubuje důvěru v úspěšnost a potřebnost celého datového skladu).

2.4

Hlavní komponenty datových skladů

2.4.5

20

OLAP databáze

OLAP databáze představují jednu nebo několik souvisejících OLAP kostek (analytických datových krychlí). Ty většinou, na rozdíl od datových skladů, již zahrnují předzpracované agregace dat podle definovaných hierarchických struktur dimenzí a jejich kombinací. Datová krychle se skládá z dimenzí, které jsou umístěny na osách, a metrik, nacházejících se v jejich průsečících, obsahující zkoumaná data. Technologie OLAP se realizuje v několika variantách: • MOLAP (Multidimensional OLAP) – data jsou uložena v multidimenzionálních (binárních) OLAP kostkách; • ROLAP (Relational OLAP) – data jsou uložena v relační databázi; • HOLAP (Hybrid OLAP) – kombinace předchozích přístupů, data jsou uložena v relační databázi a agregované hodnoty v binárních OLAP kostkách; • DOLAP (Desktop OLAP) – umožňuje připojit se k centrálnímu úložišti OLAP dat a stáhnout si potřebnou podmnožinu kostky na lokální počítač.Veškeré analytické operace jsou pak prováděny nad touto lokální kostkou, takže uživatel nemusí být připojen k serveru, což je výhodné pro mobilní aplikace a uživatele (Novotný, Pour, Slánský, 2005, s. 33). 2.4.6

Klientské aplikace (Reporting)

Reporting je komponenta datového skladu, která se nachází na jeho nejvyšší úrovni. Prostřednictvím reportingu se přistupuje (dotazuje) na data uložená v relačních i OLAP databázích datového skladu. Lze jej dále rozdělit na: • standardní reporting, u kterého jsou spouštěny předpřipravené dotazy ručně nebo v pravidelných časových intervalech; • ad hoc reporting, kde jsou na data většinou jednorázově formulovány specifické dotazy vytvořené uživatelem. Data jsou z databází dotazována prostřednictvím dotazovacích jazyků SQL a MDX. SQL SQL (Structured Query Language) je standardizovaný dotazovací jazyk určený pro práci s daty v relačních databázích. SQL příkazy se dělí na několik základních skupin: • příkazy pro definici datových struktur (DDL), • příkazy pro manipulaci s daty (DML), • příkazy pro řízení transakcí, • příkazy pro řízení přístupových práv (Hernandez – Viescas, 2004).

2.4

Hlavní komponenty datových skladů

21

MDX MDX (MultiDimensional eXpressions) je jazyk multidimenzionálních výrazů, který umožňuje definovat dotaz, na základě kterého jsou z kostky databáze OLAP vybrány požadované údaje (Spofford, 2001). 2.4.7

Dolování dat

Dolování dat (Data Mining) je další komponentou datového skladu umožňující pomocí algoritmů využívajících znalostí statistiky a umělé inteligence získávání netriviálních skrytých a potenciálně užitečných informací z dat. Je to analytická technika pevně spjatá s datovými sklady, jako s velmi kvalitním datovým zdrojem pro tyto analýzy. Dolování dat lze charakterizovat jako proces extrakce relevantních, předem neznámých nebo nedefinovaných informací z velmi rozsáhlých databází. Důležitou vlastností dolování dat je, že se jedná o analýzy odvozované z obsahu dat, nikoli předem specifikované. Jedná se tedy o odvozování prediktivních informací (Novotný, Pour, Slánský, 2005, s. 35). Dolování dat slouží k objevování nových skutečností, předvídání trendů, testování hypotéz, nebo i vyhledávání anomálií v datech využitím následujících technik: • Klasifikace je nejpoužívanější technikou při dolování dat. Jejím úkolem je přiřazení kategorie zkoumané charakteristice na základě společných znaků ze vstupních informací pro každý záznam. Zkoumaná vlastnost je tedy funkcí vstupních atributů. Známé algoritmy pro klasifikaci jsou rozhodovací stromy, neuronové sítě, nebo naivní Bayesovské klasifikátory. • Segmentace (Clustering) je metodou rozdělující data do skupin (clusterů) dle určitých kritérií. Tato kritéria nejsou předem obvykle známá a úlohou segmentace je jejich nalezení. • Asociace je problém tzv. analýzy nákupního koše. Jejím úkolem je nalezení kombinací, které se ve vstupních datech nejčastěji objevují společně. • Regrese je metodou velmi podobnou klasifikaci, rozdíl je v tom, že se ze vstupních informací nehledají společné znaky popisující kategorii, ale číselné hodnoty. Druhy regrese jsou lineární regrese a logistická regrese (binární). • Predikce na základě posloupnosti a charakteristice vstupních číselných hodnot zkoumá jejich budoucí vývoj. Algoritmy pro predikci jsou například ARIMA a lineární regrese založená na rozhodovacích stromech (MacLennan – Crivat – Tang, 2009, s. 6–8).

2.5

Přístupy k tvorbě datových skladů

2.5

Přístupy k tvorbě datových skladů

2.5.1

Jednorázové budování datového skladu

22

Budování datového skladu najednou není příliš obvyklé a je charakteristické pro velmi malé datové sklady. Mezi nejvýznamnější důvody proč není vhodné jednorázové budování datových skladů je velmi často se měnící požadavky, popř. technologie a neexistence možnosti postupného sledování návratnosti investic. 2.5.2

Postupné (iterativní) budování datového skladu

Princip budování datového skladu krok za krokem iterativně je pro oblast datových skladů přirozený. Jde o tzv. evoluční přístup. Produkční (zdrojová) data jsou do datového skladu zahrnována postupně. Tím jak jsou přinášena nová data se zároveň rozšiřuje i počet uživatelů datového skladu a možnosti jeho využití (Inmon, 2003, s. 41). Tvorba datových skladů iterativně je charakteristická těmito vlastnostmi: • budování datového skladu po malých částech (modulech) v krátkých intervalech, • možnost sledování návratnosti investic, • spolehlivě fungující menší celky zvyšují důvěru uživatelů v datový sklad, • pružné přizpůsobeni měnícím se požadavkům a technologiím.

2.6

Životní cyklus datového skladu

Životní cyklus datového skladu je podobný jako životní cyklus jiných softwarových projektů. Model životního cyklu, který se obvykle využívá při tvorbě datových skladů je specifický druh spirálovitého, tzv. prototypového modelu nazvaný rapid prototype and iterate model8 upraveného pro potřeby tvorby datových skladů založeného na principech R. Kimballa. Tento model je při tvorbě datových skladů charakteristický tím, že je na základě důkladné analýzy požadavků vytvořen první prototyp, který je nasazen a využíván určitou zpravidla úzkou skupinou uživatelů. Na základě zkušenosti uživatelů s prvním prototypem jsou definovány nové požadavky a datový sklad je dále rozšiřován pro další skupiny uživatelů, kteří definují nové potřeby a požadavky (Whherescape RED, 2006). Tento postup je iterační proces tvořen zpravidla následujícími částmi: • projektový plán • modelování datového skladu • modelování technologické platformy • implementace a vývoj 8

název se do češtiny nepřekládá

2.7

Role v projektu datového skladu

23

• testování • nasazení • provoz, údržba a rozvoj Více o jednotlivých částech životního cyklu je uvedeno dále v kapitole 3 (Metodika tvorby datového skladu).

2.7

Role v projektu datového skladu

Role při tvorbě datového skladu jsou chápány jako typové skupiny pracovníků, kteří mají v projektu obdobnou pozici a plní podobné úkoly. Jednotlivé role mají v projektu určité definované zodpovědnosti za jednotlivé funkce a procesy datového skladu. V závislosti na velikosti projektu mohou být určité role vykonávány více pracovníky, nebo naopak více rolí vykonáváno jedním pracovníkem. Role při tvorbě datových skladů jsou: • Sponsor – je to vlastník projektu a nese finanční odpovědnost za projekt. • Projektový manažer – je to osoba zodpovědná za chod projektu a koordinující všechny procesy projektu. Musí mít jak dostatečný analytický a technologický přehled, tak výborné komunikační a organizační schopnosti. • Vedoucí projektu ze strany podniku – je to osoba ze strany podniku, která zastupuje budoucí uživatele datového skladu. Zodpovídá za prosazování, specifikaci a kontrolu uživatelských požadavků. • Analytik – je zodpovědný za analýzu a realizaci uživatelských požadavků, za přípravu a řešení konceptuálních a logických modelů datového skladu a za návrh klientských aplikací. • Datový architekt – jeho úkolem je detailní analýza dat a vytvoření datových modelů. • Databázový specialista – je to osoba, která překládá datový model do fyzické struktury databázových tabulek. Dále vytváří indexovací strategie a další fyzické databázové charakteristiky. • Návrhář ETL – jeho úkolem je navržení a realizace datových pump, tzn. procedur pro extrakci dat z produkčních systémů, jejich transformaci a ukládání do datového skladu. • Vývojář koncových aplikací – je to osoba, která vytváří koncové aplikace datového skladu (sady reportů, apod.), rovněž se stará o jejich údržbu a rozvoj. • Školitel – jeho úkolem je kvalifikační příprava uživatelů datového skladu (Novotný, Pour, Slánský, 2005, s. 152–153).

2.8

2.8

Užití datových skladů

24

Užití datových skladů

Oblasti vhodné pro užití datových skladů a Business Intelligence řešení nejsou nijak výrazně omezeny. Jedinou podmínkou je existence zdrojových dat, na kterých mohou být tato řešení postavena. Oblasti, ve kterých se datové sklady využívají jsou zejména tato: • maloobchodní prodej • sklady • zásobování a logistika • systém objednávek • customer relationship management • účetnictví • řízení lidských zdrojů • finanční služby • telekomunikace • doprava • vzdělání • zdravotní péče • reklama • pojišťovnictví V každé z těchto oblastí jsou poptávána specifická data v různé formě, proto i způsob uložení a prezentace informací získaných z těchto dat se obvykle liší (Kimball – Ross, 2002, s. 10–13). Užití datových skladů lze dále rozdělit podle způsobu jejich užití. Mezi tyto způsoby patří: • Validace – Informace získané z datových skladů slouží pro validaci hypotéz jejich uživateli. • Taktické reportování – Uživatelé datových skladů využívají informace získané z datových skladů pro taktické rozhodování. • Zkoumání – Datový sklad slouží pro získávání předem neznámých znalostí z dat v něm uložených (Kelley, 2003).

3

METODIKA TVORBY DATOVÉHO SKLADU

3

25

Metodika tvorby datového skladu

3.1

Projektový plán

Prvním a klíčovým krokem při tvorbě datového skladu je vytvoření projektového plánu. Projektový plán je zaměřený na definici a rozsah projektu. Podstatnou podmínkou zpracování kvalitního projektového plánu je porozumění business pohledu zadavatele zhotovitelem. Důležitým prvkem je tedy v této fázi komunikace (Kimball, 1998, s. 33). Projektový plán obvykle obsahuje následující části: • Definování cílů – jasná a srozumitelná definice cílů je podstatným předpokladem úspěšného řešení. Zadavatel by měl být proto plně přesvědčen, že investuje čas a prostředky do řešení přinášející odpovídající výsledky. Formulace cílů by měla vycházet ze strategických cílů firmy. • Analýza zdrojů – kvalitní analýza zdrojových systémů a připravenosti firmy na tvorbu datového skladu zahrnuje hodnocení aktuálního stavu ICT9 , interních i externích datových bází, jejich validity vzhledem k požadavkům BI, dále technické, ekonomické a organizační dosažitelnosti. Výstupem je přehled existujících datových a technologických zdrojů, včetně požadavků na jejich úpravu s určením jejich další použitelnosti pro tvorbu datového skladu. • Analýza požadavků – vychází z potřeby klíčových uživatelů a podnikových procesů. Výsledkem je zmapování a definování požadavků na základě jejich specifikace zadavatelem, popřípadě jeho klíčovými uživateli. • Rozčlenění úkolů – rozdělení požadavků na moduly a modulů na jednotlivé úkoly je podstatné pro sledování průběhu celkového řešení. S přispěním modularity se rozdělí požadavky do několika částí, které budou zpracovány jeden po druhém v rámci postupného budování komplexního řešení. • Definování doby trvání úkolů – u jednotlivých modulů a jejich úkolů se definuje časová náročnost. • Sekvence úkolů – na základě priority požadavků se moduly a jejich úkoly seřazují podle důležitosti do posloupnosti (Novotný, Pour, Slánský, 2005, s. 63). Po vyhotovení projektového plánu je důležité jeho odsouhlasení zadavatelem, jelikož projektový plán bude v budoucnu sloužit pro ověření úspěšnosti projektu. 9

Informační a komunikační technologie

3.2

3.2

Modelování datového skladu

26

Modelování datového skladu

Modelování datového skladu navazuje na projektový plán a je jádrem celého řešení datového skladu. Při modelování se využívá multidimenzionální analýzy. Jeho účelem je navržení datového schéma (struktur datového skladu), které umožní přenos požadovaných dat z produkčních systémů do datového skladu dle principů multidimenzionality a podnikových požadavků definovaných v projektovém plánu. Multidimenzionální analýza obsahuje: • definování sledovaných ukazatelů (metrik) a jejich charakteristik; • definování jednotlivých dimenzí, jejich obsah, včetně vnitřní hierarchie prvků; • specifikaci vazeb mezi ukazateli a odpovídajícími dimenzemi. Při návrhu dimenzí se určují následující charakteristiky: • identifikace dimenze (např. Produkt) • prvky dimenze, • hierarchická struktura dimenze (např. Kategorie – Subkategorie – Produkt), • počty prvků v dimenzi na jednotlivých hierarchických úrovních, • zdroj dat pro vybrané prvky dimenzí (odpovídající prvky tabulek zdrojové databáze), • definice pro kalkulované prvky (např. spojení několika prvků dimenze [množství a měrné jednotky – 1 cm]). Obvyklé příklady dimenzí jsou čas, produkty, zákazníci, zaměstnanci, útvary, teritoria, dodavatelé, a další. V případě ukazatelů (metrik) se určují následující charakteristiky: • identifikace ukazatele (např. Prodej Kč ), • jednotka vyjádření ukazatele (Kč, kusy, procenta), • zdroj dat pro ukazatele z produkčních (zdrojových) systémů, • výpočty kalkulovaných ukazatelů (vzorce), • určení tzv. business pravidel (např. zvýraznit produkt, jehož prodej klesl o 10 a více procent)(Novotný, Pour, Slánský, 2005, s. 108–109). Pro multidimenzionální modelování je charakteristické, že úroveň detailu se může měnit. Proto obvykle při modelování dochází k tvorbě datových tržišť jako podmnožin datového skladu využívajících jen některé dimenze s různou úrovní detailu. Jednou z metod pro vytvoření modelu datového skladu sestávající se z několika

3.2

Modelování datového skladu

Obr. 6: The Data Warehouse Bus Architecture matrix http://blog.todmeansfox.com/wp-content/uploads/2008/05/aw bus.gif)

27

(zdroj:

datových tržišť je vytvoření matice nazvané The Data Warehouse Bus Architecture matrix10 (obr. 6), která je založena na identifikaci všech možných datových tržišť a na určení všech dimenzí, které se vyskytují v těchto tržištích. Tato tržiště potom tvoří matici, která slouží jako základ pro logický a fyzický návrh jednotlivých faktových a dimenzionálních tabulek (Kimball, 1998, s. 139–140). Dalším krokem metody The Data Warehouse Bus Architecture matrix je detailní návrh jednotlivých tabulek zahrnující tyto kroky: 1. Výběr datového tržiště – vychází z řádků matice datových tržišť a dimenzí. 2. Deklarace granularity – vyjadřuje úroveň detailu jednotlivého záznamu faktové tabulky. 3. Výběr dimenzí – vybrané dimenze musí odpovídat granularitě faktové tabulky z předchozího kroku. 4. Výběr faktů – určuje údaje (fakta), která lze nalézt v rámci deklarované granularity (např. objem denních prodejů v Kč )(Novotný, Pour, Slánský, 2005, s. 120). V další fázi modelování je na základě identifikovaných dimenzí a ukazatelů (faktů) vytvořeno logické schéma datového skladu na úrovni relační databáze při respektování principů multidimenzionálního přístupu, tedy schéma hvězdy nebo sněhové vločky (viz kapitola 2.3.3). Vytvořené logické schéma pak obsahuje pouze dva druhy tabulek, faktové a dimenzionální. Schémata se znázorňují prostřednictvím diagramů se slovním popisem. Návrh modelu je iterační, tzn. že každý krok je postupně přehodnocován, doplňován a upravován. Účelem je vytvořit model respektující základní cíle datového skladu i uživatelské požadavky definované v projektovém plánu. 10

název se do češtiny nepřekládá

3.3

Modelování technologické platformy

3.3

28

Modelování technologické platformy

Další částí při tvorbě datového skladu je návrh a modelování technologické platformy datového skladu. Tento proces se skládá z následujících fází: • návrh technologické architektury • návrh fyzického modelu • návrh objemu datového skladu 3.3.1

Návrh technologické architektury

Technologickou architekturu lze rozdělit na dvě části, a to back room a front room (Obr. 7). Jednoduše řečeno back room je ta část datového skladu, která je skrytá před uživateli. Odehrávají se zde všechny podstatné procesy k získání dat ze zdrojových systémů, jejich transformace, čištění a nahrání do požadovaných struktur dimenzionálního modelu datového skladu. Účelem návrhu technologické architektury této části je proto definování jednotlivých komponent pro zabezpečení tohoto procesu. Komponenty mohou být zvoleny na základě velikosti a potřeb řešení, jsou jimi zdrojové systémy, dočasná úložiště, operativní úložiště, datová tržiště a datový sklad. V rámci technologické architektury back roomu jsou rovněž definována metadata popisující obsah datového skladu. Ty udávají odkud jsou data získávána a dokumentují pravidla pro extrakci, transformaci, čištění a ukládání dat (Kimball, 1998, s. 335–336).

Obr. 7: Front room a back room rozdělení (Kimball, 1998) Front room je ta část datového skladu, která je uživateli přímo viditelná a přístupná. Pro front room je charakteristické, že uživatelé se jeho prostřednictvím dotazují na data uložená v datovém skladu. Komponentami této části jsou reportovací služby a OLAP databáze pro ad–hoc analýzy analytickými nástroji. V této části

3.3

Modelování technologické platformy

29

návrhu technologické architektury jsou také definováni uživatelé a skupiny uživatelů datového skladu, u kterých je určeno ke kterým datům a oblastem datového skladu mají nebo nemají přístup, a které operace mohou vykonávat. Dále jsou navrženy mechanismy pro sledování činnosti uživatelů a zaznamenávání jimi provedených dotazů s ohledem na výpočetní výkon a čas odezvy. Další součástí návrhu front roomu jsou metadata, která slouží jako slovník všech datových prvků přístupných uživatelům (Kimball, 1998, s. 373–375). Výsledkem návrhu technologické platformy je vytvoření dokumentu plánu technologické architektury, který je velmi podstatný, jelikož nesprávně navržené komponenty mohou významně zkomplikovat budoucí vývoj a implementaci datového skladu. V poslední fázi návrhu technologické platformy je na základě plánu technologické architektury vytvořen plán infrastruktury, ve které se definují jednotlivé servery, fyzické úložiště a další potřebná infrastruktura pro provoz datového skladu (Novotný, Pour, Slánský, 2005, s. 127–128). 3.3.2

Návrh fyzického modelu

Návrh fyzického modelu vychází z modelů na konceptuální a logické úrovni definovaných v předchozí kapitole ve fázi modelování datového skladu. Účelem je především doplnění logického návrhu tabulek o konkrétní i fyzické charakteristiky odpovídající danému databázovému prostředí, tedy charakteristiky jednotlivých položek (atributů). Cílem je dosažení co nejefektivnějšího uložení dat a omezení nároků na vstupně-výstupní operace, které představují významný podíl na době vykonávání jednotlivých dotazů. V praxi se tak jedná například o vytvoření indexů. V rámci návrhu fyzického modelu se tedy řeší následující fyzické charakteristiky datového skladu a jeho jednotlivých tabulek: • verifikace a případné úpravy návrhu granularity ukládaných dat, • návrh rozdělení tabulek (partitioning), • slučování tabulek (merging), • řešení datových polí, • využití redundance dat, • řešení odvozených dat, • předběžné úpravy (např. kalkulace), • indexování dat. Verifikace granularity ukládaných dat je důležitá s ohledem na výkon datového skladu. V této fázi se tedy ověřuje, zda-li pro zvolenou úroveň detailu získáme požadovaný výkon. Tzn. po zhodnocení disponibilních technologií a zvolené úrovně granularity může dojít k její modifikaci a zjednodušení.

3.3

Modelování technologické platformy

30

Dělení (partitioning) dat znamená rozdělení databázové tabulky na několik částí z důvodu pomalého procházení tabulky obsahující velké množství záznamů. Rozdělení tabulky na části podle určeného klíče, například času, zrychlí práci s těmito daty. Opačný způsob je slučování, tzv. merging, databázových tabulek. Optimálním slučováním tabulek se snižují přístupy do různých tabulek (jejich vzájemné spojování), a tak se může zkrátit celková doba zpracování dotazu. Vytváření datových polí je způsob, jak šetřit čas vstupních a výstupních (I/O) operací. Normalizovaná data a jejich sekvence jsou v tomto případě uspořádána fyzicky do řádků tak, jak za sebou logicky následují. Na jednotlivé řádky se pak vztahují jednotlivé I/O operace. Tento přístup je vhodný pouze v případech, kdy jsou předem pravděpodobná zpracování dat ve standardních sekvencích. Například u časových dimenzí. Uplatňování úmyslné redundance dat je cestou k denormalizaci dat. Opakování dat je vhodné pokud se jedná o data s minimální pravděpodobností jejich aktualizace. Využívá se pro úsporu I/O operací. Snížení počtu I/O přístupů může být efektivně dosaženo i zpracováním výpočtů, neboli kalkulovaných položek, již při ukládání do datového skladu. Potřeba indexování dat je shodná jako u jiných databázových systémů. Problémem je ale využití správných technik pro indexování datových skladů. U transakčních systémů je zatížení způsobeno typicky úzkou, předem definovanou množinou dotazů a velkým počtem uživatelů, které vedou ke zpracování obvykle pouze stovek záznamů. Oproti tomu se u datových skladů zpracovává velké množství předem neznámých dotazů relativně menším počtem uživatelů vedoucí ke zpracování až milionů záznamů. Z uvedených rozdílů vyplývá, že vhodné indexování pro transakční systémy je využití B-Tree stromů, zatímco pro datové sklady je vhodnější využití tzv. hashových a bitmapových indexů. S indexováním datových skladů souvisí i vytvoření tzv. kreativních indexů. To jsou indexy odpovídající předem definovaným pohledům a požadavkům uživatelů. Tyto indexy se vytvářejí současně při nahrávání dat do datového skladu a snižují dobu zpracování těchto dotazů. (Novotný, Pour, Slánský, 2005, s. 128–130) 3.3.3

Návrh objemu datového skladu

Velikost databáze datového skladu je vzhledem k průběžnému nárůstu dat díky existenci časové dimenze jednou z klíčových záležitostí při zajištění technické infrastruktury. Výpočty velikosti datového skladu jsou obecné a nezávislé na použité technologii. Obvykle se pro zjednodušení nezohledňují tabulky dimenzí jejichž objem nebývá zpravidla tak velký. Přesný výpočet hodnot lze získat také využitím CASE nástrojů, ale tento způsob se v praxi ukazuje jako zbytečně pracný (Novotný, Pour, Slánský, 2005, s. 130).

3.3

Modelování technologické platformy

31

Výpočet velikosti relační a multidimenzionální databáze datového skladu je uveden v tabulkách 1 a 2. Pro interpretaci byla zvolena obchodní firma s parametry uvedenými v těchto tabulkách. Tab. 1: Výpočet velikosti relační databáze pro datový sklad obsahující tříletou historii (Novotný, Pour, Slánský, 2005, s. 130) Parametr Vstup Výstup 1 Dimenze Čas 3 roky, počet dní 3*365 1095 dní 2 Dimenze Prodejny 250 prodejen, data předávají 250 prodejen denně 3 Počet objednávek cca 2300 na jednu prodejnu 2300 objednávek 4 Počet záznamů v ta- 1095 dní * 250 prodejen * 2300 629 625 000 zábulce faktů objednávek znamů 5 Délka záznamu, veli- 3 atributy * 4 B + 5 atributů 52 bytů kost atributů v ta- * 8 B bulce faktů 6 Velikost tabulky faktů 629 625 000 záznamů * 52 bytů cca 33 GB 7 Velikost indexů cca 200 % základu cca 65 GB 8 Celkem základ + indexy cca 98 GB

Tab. 2: Výpočet velikosti multidimenzionální databáze (MDDB) obsahující tříletou historii (Novotný, Pour, Slánský, 2005, s. 132) Parametr Vstup Výstup 1 Dimenze Čas (4 3 roky, počet dní 3*365 1095 dní úrovně hierarchie) 2 Dimenze Prodejny (6 250 prodejen, data předávají 250 prodejen úrovni hierarchií) denně 3 Počet objednávek cca 2300 na jednu prodejnu 2300 objednávek 4 Počet záznamů v ta- 1095 dní * 250 prodejen * 2300 629 625 000 zábulce faktů objednávek znamů 5 Velikost buňky 32 B 32 bytů 6 Základní velikost 629 625 000 záznamů * 32 bytů cca 20 GB MDDB 7 Velikost skutečné (((2 * počet všech úrovní) + cca 7,5 GB MDDB, např. v MS (4 * počet ukazatelů)) * poSSAS11 čet řádků) / 3, tj: ((2*(4+6) + 4 * 4) * 629 625 000)/3 8 Dočasné soubory cca 100 % základu cca 7,5 GB 9 Celkem základ + dočasné soubory cca 15 GB

3.4

Implementace

32

Značný rozdíl mezi virtuální (základní) a skutečnou multidimenzionální databází je způsoben kompresí řídké matice uložených dat, čímž dochází ke značnému snížení jejího objemu. Zásadní roli v případě velikosti skutečné multidimenzionální databáze (MDDB) také hraje počet hierarchií a jejich úrovní v jednotlivých dimenzích. Například jedna přidaná úroveň hierarchie v dimenzi Prodejny zvýší celkovou velikost multidimenzionální databáze v předchozím případě o 0,5 GB.

3.4

Implementace

Úlohou implementace řešení datového skladu je realizovat vše, co bylo v předchozích fázích projektu analyzováno a navrženo prostřednictvím zvolených nástrojů a metod. Oblast datových skladů je ve fázi implementace velmi specifická, na rozdíl od jiných oblastí, z důvodu výrazné odlišnosti použitých technik a nástrojů pro implementaci jednotlivých komponent datových skladů. Implementace se skládá z těchto kroků: • předimplementační příprava, • implementace dočasného úložiště dat, • implementace datového skladu, • implementace ETL, • implementace OLAP databází, • implementace klientských aplikací. 3.4.1

Předimplementační příprava

Před zahájením implementace je nutné uskutečnit některé důležité kroky, bez kterých by implementace nemohla začít. Jsou jimi například nastavení přístupových práv pro ETL aplikace do zdrojových (produkčních) systémů, připravení automatizovaného mechanismu pro zajištění zpřístupnění vždy aktuálních dat z nejrůznějších externích zdrojů (statistického úřadu, ČNB, apod.) pro využití ETL aplikacemi. Jakmile jsou všechny potřebné náležitosti splněny, je možné začít s vlastní implementací (Novotný, Pour, Slánský, 2005, s. 160). 3.4.2

Implementace dočasného úložiště dat

Implementace dočasného úložiště dat (DSA) je jednou z nejjednodušších úloh při tvorbě datového skladu. Skládá se z vytvoření databáze obsahující vybrané struktury (tabulky), shodné jako v produkčních systémech, pro přenesení kopie dat ze zdrojových systémů do databáze DSA. Určení těchto tabulek je již provedeno ve 11

SQL Server Analysis Services

3.4

Implementace

33

fázi modelování. Základním požadavkem dočasného úložiště dat je oddělení databáze DSA a databáze produkčních systémů pro snížení zátěže produkčních serverů, což je zároveň hlavním účelem vytvoření dočasného úložiště dat. Při implementaci je nutné zajistit, aby do dočasného úložiště dat měly přístup pouze aplikace ETL, které jako jediné budou tyto data dále využívat. 3.4.3

Implementace datového skladu

Implementace datového skladu se sestává z vytvoření databáze a v ní uložených tabulek podle principu multidimenzionálního přístupu. Při tvorbě struktur se tedy vychází z multidimenzionální analýzy modelování datového skladu (viz kapitola 3.2). Jedním z nejzákladnějších principů datového skladu je zabezpečení uchování pouze čistých dat. Ačkoliv k čištění dat dochází až při ETL, vhodnou implementací identifikátorů (např. primárních klíčů), nastavením referenční integrity (cizích klíčů) a doménové integrity (kontrola stanovených hodnot) lze rovněž výrazným způsobem přispět k uložení čistých a tedy kvalitních dat v datovém skladu. Další fázi implementace datového skladu je nastavení přístupových práv pro uživatele. Uživateli struktur datového skladu, tedy tabulek dimenzí a faktů, jsou OLAP aplikace nebo aplikace dolování dat. 3.4.4

Implementace ETL

ETL je nejdůležitější a zároveň nejsložitější část při implementaci datového skladu. Skládá se z následujících fází: • extrakce dat ze zdrojových systémů do DSA; • transformace, čištění a nahrání dat z DSA do datového skladu. Podmínkou pro realizaci ETL, tedy vytvoření tzv. datových pump, jsou správně nastavená přístupová práva aplikací ETL ke zdrojových systémům, strukturám dočasného úložiště dat a databázi datového skladu. Cílem implementace ETL je vytvoření procedur a mechanismů pro transformaci kvalitních dat do datového skladu. Extrakce dat ze zdrojových systémů do DSA Extrakce dat ze zdrojových systémů a jejich následné uložení do dočasného úložiště dat je první fází transformace dat. Principem této části je prostřednictvím procedur vytvořených v ETL aplikaci nakopírovaní záznamů ze zdrojových tabulek do tabulek DSA ve stejné struktuře beze změny. Před každým spuštěním tohoto procesu musí dojít k vyprázdnění tabulek DSA tak, aby v něm byla vždy pouze ta data, která se aktuálně nacházejí ve zdrojových systémech. Navíc v případě, že se do dočasného úložiště dat načítají pouze nová nebo modifikovaná data, tak pouze tato data. Transformace, čištění a nahrání dat z DSA do datového skladu

3.4

Implementace

34

Proces přenosu dat z dočasného úložiště do datového skladu je podstatně náročnější úkol. Prostřednictvím datových pump vytvořených v ETL aplikaci je nutné data při přenosu transformovat ze struktur obvykle transakčních systémů do struktur multidimenzionálních. Dále se tato data v této fázi čistí tak, aby byly v datovém skladu uloženy pouze kvalitní, úplná, konzistentní data se zachováním požadované datové integrity. Při vytváření datových pump je vhodné věnovat pozornost těmto doporučením: • optimalizovat průběh datových pump vzhledem k jejich výpočetní náročnosti na procesor a operační paměť, – v co nejvyšší míře používat SQL dotazy pro transformaci, – omezit používání kontrolních náhledů do jednotlivých tabulek, – na vstupu pracovat jen s daty relevantního snímku (pokud se do DSA neextrahují pouze nové a modifikované záznamy), • zavést kontrolu vstupních dat, • provádět čištění před uložením do tabulek datového skladu, • zavést automatické hlášení o chybách správci ETL, • zabránit porušování integritního omezení pokud jsou v datovém skladu implementována (tzn. nejdříve nahrávat tabulky dimenzí a poté faktové tabulky). Poté co jsou datové pumpy vytvořeny je v dalším kroku potřeba vytvořit scénář pro jejich spouštění. Scénář by měl obsahovat v jakém pořadí a kdy se jednotlivé ETL balíky (datové pumpy) budou vykonávat. (Novotný, Pour, Slánský, 2005, s. 169–170) 3.4.5

Implementace OLAP databází

Implementace OLAP databází vychází z provedené multidimenzionální analýzy (viz kapitola 3.2). Samotná implementace se velmi liší od implementace relačních databází, technologicky i použitými technikami. OLAP databáze je zpravidla složena z několika dimenzí, jedné nebo více OLAP kostek a přístupových práv k těmto kostkám a jejich datům. Proces tvorby OLAP databáze je posloupností následujících kroků: • vytvoření OLAP databáze, • připojení datových zdrojů k OLAP databázi, • vytvoření dimenzí (jejich prvků a jednotlivých hierarchií), • vytvoření OLAP kostek obsahující agregované ukazatele (metriky) sestávající se z: – definice tabulek faktů a vytvoření metrik,

3.4

Implementace

35

– výběru dimenzí pro sledování hodnot ukazatelů, – připojení metrik na jednotlivé dimenze prostřednictvím jejich vazeb, – definice kalkulovaných prvků, – definice a optimalizace způsobu uložení dat (MOLAP, ROLAP, HOLAP), • nastavení přístupových práv pro uživatele nebo skupiny uživatelů k jednotlivých OLAP kostkám a jejich datům. Jakmile je celá OLAP databáze vytvořena, musí dojít k její kompilaci. Při kompilaci je struktura databáze převedena do formátu OLAP databáze, naplněna již agregovanými daty z datového skladu a zpřístupněna uživatelům podle nastavených oprávnění. Náročnost tvorby OLAP databází vždy závisí na kvalitě provedené multidimenzionální analýzy a zejména na kvalitě dat, která jsou uložena v datovém skladu. (Novotný, Pour, Slánský, 2005, s. 171-184) 3.4.6

Implementace klientských aplikací

Implementace klientských aplikací, neboli reporting, je posledním krokem ve fázi vývoje datového skladu. Tato fáze je přímo závislá na zvolených technologiích. Příkladem těchto technologií (klientských aplikací) jsou SQL Server Reporting Services, MS Office Excel, Pro Clarity apod. Ačkoliv implementace v jednotlivých nástrojích se významně liší, principy zůstávají shodné. Reporting lze rozdělit na dvě části (viz kapitola 2.4.7), a to standardní reporting a ad-hoc reporting. Standardní reporting je tvořen prostřednictvím předem připravených sestav reportů vytvořených pomocí jazyků MDX a SQL. Prostřednictvím zvolených parametrů si uživatel může spouštět reporty ručně nebo automaticky generovat v pravidelných intervalech. Standardní reporting proto obvykle využívá větší počet uživatelů. Na druhou stranu ad-hoc reporting slouží pro tvorbu individuálních pohledů a analýz pomocí specializovaných softwarových nástrojů. Tyto analýzy ale vyžadují dobré porozumění s prací ve zvolených nástrojích. Proto je tato část využívána spíše užším počtem uživatelů. Pokud jde o vlastní implementaci v této fázi, rozumí se tím tvorba standardních reportů12 . Vytvoření reportu nebo sady reportů se skládá z následujících prvků: • návrh grafického designu • připojení reportu na multidimenzionální či relační data, • vytvoření parametrů reportu, • vytvoření pohledu (dotazu) na data prostřednictvím dotazovacího jazyka (MDX/SQL), 12 Implementace Ad–hoc reportingu je totiž samotná tvorba klientských softwarových produktů umožňující ad–hoc reporting, což je v tomto případě nad rámec tvorby datových skladů

3.5

Testování

36

• propojení parametrů a pohledu, • implementace dat do grafického designu. Příkladem reportů jsou nejrůznější kontingenční tabulky a grafy, které v co nejsrozumitelnější a nejpřehlednější formě interpretují data z datového skladu koncovému uživateli.

3.5

Testování

Testování je velmi podstatnou a časově náročnou fází každého kvalitního řešení datového skladu. Testování datového skladu je velmi specifické stejně jako jeho implementace, protože se skládá z testování různých komponent vytvořených v různých nástrojích. Při testování jsou validovány všechny části datového skladu. Testování probíhá za základě schváleného projektového plánu, který je porovnáván s výsledným datovým skladem. V některých fázích se testování účastní také koncoví uživatelé (viz Alfa a Beta fáze nasazení v kapitole 3.6.3) Testování je složeno z následujících fází: • validace modelu datového skladu, • testování dočasného úložiště dat, • testování datového skladu, • testování OLAP databází, • testování reportů. 3.5.1

Validace modelu datového skladu

Validace modelu datového skladu oproti požadavkům ve schváleném projektovém plánu je první fází testování datového skladu. Tato fáze je velmi podstatná, protože chybně navržený model je zpravidla příčinou dalších chyb v implementační fázi. Proto by se měla tato verifikace uskutečnit ještě před začátkem vývoje. Modelování datového skladu a jeho validace je iterační proces, který vede k zabezpečení kvalitního řešení a minimalizaci nákladů spojených s koncepčními a logickými chybami objevenými až při implementaci datového skladu. 3.5.2

Testování dočasného úložiště dat

Při testování dočasného úložiště dat se testují mechanismy a procedury ETL zabezpečující přenos kopie dat ze zdrojových systémů do dočasného úložiště. Pokud je dočasné úložiště koncipováno tak, že jsou do něj nahrávány pouze nová nebo modifikovaná data, je nutné to rovněž ověřit. Tato fáze testování je stejně tak jako jeho implementace nejjednodušší částí, ale chyby, které se zde mohou vyskytnout přímo ovlivňují data v datovém skladu. Proto verifikace dočasného úložiště dat je důležitá

3.5

Testování

37

pro zajištění, že data v této fázi jsou v pořádku a připravena pro transformaci do struktur datového skladu. 3.5.3

Testování datového skladu

Testování datového skladu je velmi důležitou fází celého testovacího procesu, protože data v datovém skladu jsou jeho podstatou. Chyby v datech uložených v datovém skladu vedou logicky k chybám v OLAP databázích a klientských aplikacích datového skladu, k nimž mají přístup uživatelé. A kvalita dat je v tomto případě zásadní, protože nekvalitní data mohou zapříčinit, že uživatelé nebudou mít důvěru v data z datového skladu, což vede k rychlému konci celého řešení datového skladu. Testování datového skladu lze rozdělit na několik částí: • testování struktur (tabulek) podle modelu dimenzionálního schéma (včetně zajištění datové integrity a indexování), • testování transformace, čištění a nahrání dat z dočasného úložiště do datového skladu, • testování změn v datech při pravidelném nahrávání nových nebo aktualizovaných dat. Testování se obvykle provádí nad relační databází datového skladu a databází dočasného úložiště dat prostřednictvím SQL dotazů, které pomocí definovaných pohledů porovnávají data mezi těmito zdroji. Zpravidla se vytváří tolik porovnání, kolik je v řešení datového skladu datových pump (ETL balíků). Obecně je vhodné vytvořené pohledy a mechanismy sloužící k porovnání dat uchovávat pro jejich pozdější použití, a to pro opětovné ověření kvality dat po určitém časovém období, případně pro otestování dat po provedení nějakých změn v datovém skladu. 3.5.4

Testování OLAP databází

Při testování OLAP databází se vychází z multidimenzionální analýzy. Nejprve se ověřuje struktura OLAP databází, tedy jednotlivé dimenze (verifikace prvků a hierarchií), OLAP kostky (verifikace definovaných metrik, kalkulovaných prvků apod.) a přístupová práva pro uživatele nebo skupiny uživatelů. Dále po ověření struktury OLAP databází následuje testování jejich obsahu. Na rozdíl od testování dat v datovém skladu se testování neuskutečňuje pouze prostřednictvím jazyka SQL nad relační databázi, ale také prostřednictvím ad–hoc nástrojů nad daty v jednotlivých OLAP kostkách. Vytvořené ad–hoc analýzy jsou poté porovnávány s daty v relační databázi datového skladu prostřednictvím dotazů jazyka SQL. Množství ad–hoc analýz je individuální a závisí na rozsahu OLAP kostky, tedy počtu dimenzí a metrik.

3.6

Nasazení

3.5.5

38

Testování reportů

V poslední fázi testování se ověřují vytvořené sady reportů nad OLAP kostkami, případně přímo daty v datovém skladu. Reporty obvykle vycházejí z požadavků uživatelů uvedených v projektovém plánu. Při testování se nejprve ověřuje struktura reportů, tedy zda-li report obsahuje všechny požadované parametry, ukazatele, apod. v definovaném formátu. Pokud jsou všechny tyto náležitosti splněny, ověřují se data zobrazované v reportu. Tato data jsou porovnávána oproti OLAP kostkám nebo relačním datům v datovém skladu v závislosti na typu reportu. Pokud data v reportu pocházejí z multidimenzionálních kostek, pak se obvykle pro testování využívají nástroje pro ad–hoc analýzy, které umožňují vytvářet prakticky stejné sestavy jako jsou v reportech. Ve výjimečných případech se může využít jazyk MDX. Pokud data v reportu vychází přímo z dat uložených v datovém skladu, pak se data v reportu testují prostřednictvím SQL dotazů nad datovým skladem. K testování jednotlivých komponent datového skladu by mělo zpravidla docházet jak po dokončení vývoje každé komponenty, tak po dokončení celého datového skladu. Vyhodnocení, že komponenta je v očekávané struktuře, obsahující očekávaná data před započetím vývoje další komponenty šetří čas i náklady strávené dohledáváním chyb v celém datovém skladu najednou a odlaďováním vzniklých chyb. Jak vychází z principu datových skladů, tedy nástroje pro podporu rozhodování, je kvalita dat v datovém skladu nejdůležitějším faktorem úspěšnosti celého projektu. Proto testování datového skladu zajišťuje užitečný a silný nástroj pro jeho dosažení.

3.6

Nasazení

Fázi nasazení, ačkoliv by se mohla zdát být poslední fází projektu, zpravidla nelze řešit jako poslední, tedy až po skončení fáze vývoje a testování. Důvodem je značné množství různorodých komponent, které společně s příslušným softwarem a hardwarem musí vytvořit kompaktní celek utvářející spolehlivě fungující řešení datového skladu. Fázi nasazení lze rozdělit do několika částí: • příprava plánu nasazení • zaškolení uživatelů • nasazení 3.6.1

Příprava plánu nasazení

Tato část fáze nasazení se obvykle řeší po skončení fáze modelování a analýzy, jakmile je zřejmá architektura i infrastruktura konečného řešení projektu. Při vytváření plánu nasazení se definuje posloupnost činností, které jsou nutné vykonat při nasazení jednotlivých komponent řešení. Obsahuje instalaci hardwaru i softwaru dle

3.6

Nasazení

39

požadované infrastruktury a zabezpečuje, že tato infrastruktura bude funkční a připravena pro nasazení celého projektu datového skladu. V další části plán obsahuje vytvoření rámce, například využitím skriptu, který zajistí automatizované provedení definovaných operací pro nasazení jednotlivých komponent řešení na připravené prostředí. Komponentami jsou všechny části datového skladu, tzn. dočasné úložiště dat, ETL procedury a mechanismy, struktury datového skladu, OLAP dimenze, uživatelská oprávnění a sady reportů. 3.6.2

Zaškolení uživatelů

Zaškolení uživatelů datového skladu je nezbytnou fází před nasazením projektu. Uživatelům jsou předávány informace o principu fungování datových skladů a práce s jejich nástroji. V závislosti na nástrojích, které uživatelé využívají a způsobu jakým k datům přistupují se tedy provádí školení zainteresovaných osob. 3.6.3

Nasazení

Nasazení probíhá vykonáním přesně definovaných činností podle vytvořeného plánu nasazení. Podobně ale jako při nasazení jiných softwarových projektů lze nasazení rozdělit do několika etap. Obvykle jsou to fáze Alfa, fáze Beta a fáze finálního řešení. Fáze alfa Fáze alfa je fáze prvotního testování komplexního řešení následující po jeho nasazení. V této fázi se testuje technická infrastruktura, provoz datového skladu (pravidelná extrakce a transformace dat), jednotlivé komponenty, sledování výkonu a kvalita dat. Tato fáze je založena na principech testování podobně jako popsáno v kapitole 3.5. Fáze alfa se účastní členové projektu zodpovědní za testování a kvalitu dat, analytici datového skladu a v některých případech i několik koncových uživatelů. Alfa fáze je iterativní fáze, která by měla mít stanovenu délku svého trvání. Při této fázi dochází k identifikování nalezených chyb a znovu nasazení opraveného řešení. Fáze beta Po skončení fáze alfa se zpravidla pokračuje k fázi beta. V této fázi se přiděluje přístup určitému množství koncových uživatelů, kteří provádí poslední fázi testování před finálním nasazením a zpřístupněním datového skladu celému spektru koncových uživatelů. Účelem této fáze je kontrola kvality dat zainteresovanými uživateli a odladění posledních funkčních nedostatků. Obvykle se zde provádí i nejrůznější zátěžové testy, sledování výkonu a připravují se plány pro zálohování a obnovu datového skladu. Finální fáze Finální fáze nasazení obsahuje poskytnutí přístupu ke všem částem datového skladu příslušným uživatelům a skupinám uživatelů. V závislosti na velikosti projektu lze tento proces rozdělit ještě do několika částí, tzv. vln. V takovém případě

3.7

Provoz, údržba a rozvoj

40

se přístup poskytuje postupně jednotlivým skupinám uživatelů tak, aby se vytvořil dostatečný prostor pro zaškolení a podporu těchto uživatelů ihned po nasazení. Zároveň lze vyřešit drobné chyby, které lze v dalších vlnách minimalizovat. Dále se v této fázi zpřístupňují dokumentace k projektu a dokončuje se školení všech zainteresovaných stran (Kimball, 1998, s. 691–710).

3.7

Provoz, údržba a rozvoj

Na rozdíl od některých jiných typů softwarových projektů není projekt datového skladu ukončen po jeho nasazení. Celý vývojový tým nelze přesunout na jiný projekt a provoz a údržbu systému okamžitě delegovat jiné skupině lidí. Vychází to z principu tvorby datových skladů, ve kterém nelze přesně definovat konec projektu. Obvykle se totiž po nasazení identifikují nové požadavky nebo řeší úpravy související se změnami v produkčních systémech. (Kimball, 1998, s. 715–716) 3.7.1

Údržba datového skladu

Údržba datového skladu je velmi důležitá součást projektu. Datový sklad v této fázi již využívají nejrůznější skupiny uživatelů pro vykonávání klíčových rozhodnutí. Proto je nutné zajistit, že data a informace, se kterými pracují jsou vždy správná a aktuální. Údržbu lze rozdělit na dvě části, údržbu a provoz technické infrastruktury (včetně optimalizace výkonu) a údržbu zajišťující kvalitu dat. V části údržby a provozu technické infrastruktury se řeší otázky distribuce serverů pro optimalizaci výkonu, zálohování a obnovy dat při selhání apod. Část údržby datového skladu pro zajištění kvality dat je zaměřena zejména na provádění nutných úprav a sledování kvality dat v souvislosti se změnami v produkčních systémech. Dalším neméně důležitým úkolem ve fázi údržby je zajistit technickou podporu a konzultaci koncovým uživatelům (Kimball, 1998, s. 716–717). 3.7.2

Rozvoj datového skladu

Projekt datového skladu je iterativní proces, ve kterém se stále objevují nové požadavky a potřeby uživatelů, které jsou řešeny v pravidelných cyklech. Požadavky na rozvoj datového skladu zpravidla znamenají úspěch projektu datového skladu jako celku, tzn. všech jeho předchozích fází a cyklů. Po specifikaci požadavků se nejprve vytváří nový (popřípadě rozšiřuje již existující projektový plán) a postupuje se podle popsané metodiky podobně jako v předchozím cyklu. Cykly jsou ale již obvykle podstatně menší než ten první, tzn. že i celkový čas implementace a nasazení nových požadavků je podstatně rychlejší. Významné riziko, které se vyskytuje při rozvoji datového skladu je, že jakákoli chyba může způsobit ztrátu důvěry v celý datový sklad, tedy i ve všechny jeho předchozí fungující části. Je tedy nutné brát na toto riziko ohled a nové požadavky vyvíjet s tímto vědomím. (Kimball, 1998, s. 727)

4

METODICKÁ VÝCHODISKA PRÁCE

4

41

Metodická východiska práce

Před zpracováním diplomové práce bylo nutné provést analýzu současného stavu této problematiky, tyto informace sumarizovat, a na základě toho navrhnout řešení, které rozšiřuje metodiku tvorby datových skladů. Pro získání potřebných teoretických znalostí byla použita literatura uvedena v kapitole 8. Realizace vlastní práce je založena na získaných teoretických poznatcích a praktických zkušenostech z této problematiky.

4.1

Použitá metodologie

Metodologie, která byla využita pro tvorbu první části vlastní práce, tedy realizace jednotlivých komponent na základě definovaných principů, je přírůstkový (inkrementální) přístup. Tento přístup přináší snadnější validaci jednotlivých částí a tím eliminování projektových rizik. Přírůstkový přístup je realizován prostřednictvím menších vodopádů, které znázorňují každou komponentu. Tyto vodopády se sestávají z následujících částí: • specifikace požadavků, • návrh, • implementace, • integrace, • validace, • nasazení, • údržba. Pro aplikaci jednotlivých komponent při realizaci datového skladu byl zvolen specifický druh spirálovitého, tzv. prototypového modelu nazvaný rapid prototype and iterate model13 popsaný v kapitole 2.6.

4.2

Použité nástroje a technologie

Technologie využité pro realizaci řešení vlastní práce, tedy navržených principů a celého projektu datového skladu, je MS SQL Server 2008. Tento produkt vyvinutý společností Microsoft je poslední verzí nejrozšířenějšího nástroje pro datovou platformu, který prostřednictvím jeho jednotlivých komponent, resp. technologií, umožňuje mimo jiné také tvorbu datových skladů. Mezi tyto komponenty patří: • Databázový stroj – realizuje systém řízení báze dat. 13

název se do češtiny nepřekládá

4.2

Použité nástroje a technologie

42

• Analytické služby – usnadňuje a umožňuje vytváření OLAP databází a datových krychlí. • Integrační služby – prostřednictvím ETL balíků umožňuje realizaci datových pump pro extrakci, transformaci a nahrání dat do datového skladu (ETL). • Reportovací služby – nabízí komplexní serverovou platformu navrženou pro podporu nejrůznějších potřeb generování sestav na základě dat z databázového stroje nebo analytických služeb. • Dolování dat – usnadňuje a umožňuje dolování dat prostřednictvím algoritmů integrovaných v jeho nástrojích. • SQL Agent – je to služba Microsoft Windows, která umožňuje automatizaci některých úloh spojených se správou. (Microsoft, 2010) Vývoj jednotlivých komponent byl realizován v nástrojích MS SQL Server 2008 Business Intelligence Developement Studio a MS SQL 2008 Management Studio.

5

43

VLASTNÍ PRÁCE

5

Vlastní práce

Vlastní část diplomové práce navazuje na metodiku tvorby datového skladu. Cílem první části je definovat pokročilé principy a vytvořit komplexní sadu vzájemně propojených nástrojů pro vývoj, testování, nasazení, údržbu a provoz datového skladu sestávající se z několika nezávislých komponent. Uvedené nástroje jsou implementované v prostředí SQL Server 2008, ale principy a filozofie využité pro jejich tvorbu jsou obecně využitelné i v jiných prostředích. V další části je pak realizován a stručně popsán kompletní projekt datového skladu aplikující uvedené principy a nástroje. Veškeré skripty, které nejsou uvedeny přímo v následující kapitole se nacházejí v příloze A.

5.1

Konfigurovatelnost ETL řešení

Konfigurovatelnost a nastavení řešení datového skladu je jeden z prvních předpokladů pro tvorbu kvalitního projektu. Dle základní charakteristiky datových skladů, podle které je datový sklad neustále upravován a rozšiřován na základě rychle se měnících požadavků, je možnost konfigurovatelnosti řešení základním klíčem k úspěchu. Jednou z nejdůležitějších a zároveň nejčastěji upravovaných částí datového skladu je ETL. Předem obvykle nelze přesně specifikovat kolik bude mít datový sklad zdrojových databází, tabulek, transformací atd. Proto by měla být konfigurovatelnost ETL základní podmínkou před samotnou implementací ETL balíků (datových pump). V této části jsou definovány principy pro konfiguraci ETL prostřednictvím databázových struktur, které zabezpečují konfigurovatelnost celého ETL řešení.

Obr. 8: Struktura konfiguračních tabulek

5.1

Konfigurovatelnost ETL řešení

5.1.1

44

Struktura konfiguračních tabulek

Struktura konfiguračních tabulek je sdružena do schématu CONFIG a je znázorněna na obrázku 8. Tabulka CONFIG.Databases (tab. 3) slouží ke konfiguraci zdrojových databází obsahující produkční data určená pro extrakci do datového skladu. Prostřednictvím této tabulky lze definovat přístup k jednotlivým databázím a dále využitím časových otisků přistupovat při extrakci pouze k novým nebo upraveným záznamům ve zdrojové databázi. Tab. 3: CONFIG.Databases tabulka

Název sloupce ID Database Name Database ConnectionString Active Last Timestamp

Popis Primární klíč tabulky. Název zdrojové databáze. Konfigurační řetězec pro přístup k datům při extrakci v ETL. Příznak, který určuje, zda-li je záznam (zdrojová databáze) aktivní a bude využit při ETL. Časový otisk záznamu ve zdrojové databázi, který byl vytvořen nebo upraven poslední. Slouží pro extrakci pouze nových nebo upravených záznamů ze zdrojové databáze, tzn. záznamů s časovým otiskem větším než je uveden zde. Po každé extrakci je hodnota nahrazena posledním aktuálním otiskem.

Tabulka CONFIG.Tables (tab. 4) je konfigurační tabulka sloužící pro uložení SQL dotazů k jednotlivým tabulkám zdrojových databází pro jejich extrakci. Primární klíč této tabulky je sdružený, složeny ze sloupců Table Name a Database Name. Důvodem je, že tabulka se stejným názvem může být ve více databázích. CONFIG.Packages (tab. 5) je tabulka zajišťující volání jednotlivých ETL balíků pro extrakci, transformaci a nahrání do požadovaných struktur datového skladu. Každý záznam nese informaci o tom, z jaké zdrojové tabulky se budou data extrahovat a do jaké tabulky se tyto data budou transformovat, v obou případech je uveden název příslušného ETL balíku pro obě fáze. Navíc je zde sloupec Order, který slouží k zajištění pořadí spuštění jednotlivých balíků při transformaci ze stage tabulek (tabulek dočasného úložiště dat) do store tabulek (tabulek datového skladu). Tabulka CONFIG.PackageConfig (tab. 6) slouží pro vzdálenou konfiguraci ETL balíků a jejich jednotlivých komponent v technologii SQL Server. Prostřednictvím této tabulky se například definují fyzické cesty v souborovém systému k uloženým ETL balíkům apod.

5.1

Konfigurovatelnost ETL řešení

45

Tab. 4: CONFIG.Tables tabulka

Název sloupce Table Name Database Name SQL String SQL Condition

Active Use Timestamp

Popis Název zdrojové tabulky (Primární klíč). Název zdrojové databáze (Primární klíč). SQL dotaz pro přístup k datům ve zdrojové databázi. Část SQL dotazu následující po klauzuli WHERE definující podmínku při spuštění dotazu ve zdrojové databázi. Příznak, který určuje, zda-li je záznam (tabulka) aktivní a bude využita při ETL. Příznak, který určuje, zda-li se mají využít časové otisky pro extrakci pouze nových nebo upravených záznamů. Například pokud tabulka tyto otisky neobsahuje apod.

Tab. 5: CONFIG.Packages tabulka

Název sloupce ID Source Table Name Stage Package Name Store Package Name Store Table Name Order

Popis Primární klíč. Název zdrojové tabulky. Název balíku pro extrakci ze zdroje do stage. Název balíku pro transformaci ze stage do store. Název store tabulky (tabulky datového skladu). Priorita spuštění balíku při transformaci do store.

Tab. 6: CONFIG.PackageConfig tabulka Název sloupce ConfigurationFilter ConfiguredValue PackagePath

ConfiguredValueType

5.1.2

Popis Název, který identifikuje záznam při konfiguraci v ETL balíku. Hodnota, která nese informaci (konfigurovanou hodnotu). Konfigurační řetězec, který přiřazuje hodnotu ConfiguredValue příslušnému objektu v ETL balíku (například proměnné apod.) Datový typ hodnoty v ConfiguredValue atributu (např. String)

Využití konfiguračních tabulek při ETL

V následující části je vytvořeno několik SQL dotazů využívající definované struktury (konfigurační tabulky), které lze využít k nezbytným činnostem zabezpečující správný chod ETL.

5.1

Konfigurovatelnost ETL řešení

46

Vyprázdnění stage tabulek Před každým procesem extrakce je nutné vyprázdnit stage tabulky (tabulky dočasného úložiště dat). Následující SQL dotaz využívá tabulku CONFIG.Tables pro sestavení souboru DML příkazů Truncate, které zajistí vyprázdnění struktur. SELECT DISTINCT ’TRUNCATE TABLE STAGE.’ + Table_Name AS TRUNCATE_QUERY FROM CONFIG.Tables WHERE SQL_String IS NOT NULL Získání informací o zdrojových databázích Při extrakci v ETL je nezbytné přistupovat přímo k jednotlivým zdrojovým databázím. Prostřednictvím tohoto dotazu lze získat všechny aktivní databáze, jejich poslední časový otisk a jejich připojovací řetězce pro využití v ETL balících. SELECT ID AS SOURCE_DATABASE_ID, Database_Name AS SOURCE_DATABASE_NAME, Database_ConnectionString AS SOURCE_DATABASE_CONNECTION_STRING, CAST (CAST (Last_Timestamp AS BIGINT) AS VARCHAR(40)) AS SOURCE_LAST_DATABASE_TIMESTAMP FROM CONFIG.[Databases] WHERE Active=1 ORDER BY ID S využitím připojovacího řetězce do zdrojové databáze získaného v předchozím dotazu lze poté spustit nad zdrojovou databází následující dotaz pro zjištění aktuálního časového otisku pro všechny aktivní databáze. Nově extrahované záznamy budou mít pak časové otisky mezi hodnotami SOURCE LAST DATABASE TIMESTAMP a SOURCE ACTUAL DATABASE TIMESTAMP SELECT CAST (CAST (@@DBTS AS BIGINT) AS VARCHAR(40)) AS SOURCE_ACTUAL_DATABASE_TIMESTAMP Získání informací pro extrakci jednotlivých tabulek do dočasného úložiště dat Propojením tabulek CONFIG.Tables a CONFIG.Packages lze získat informace důležité pro spuštění extrakce z jednotlivých tabulek pro všechny aktivní databáze. Jsou jimi SQL dotaz umožňující přistup k datům a název balíku pro extrakci jednotlivých tabulek do dočasného úložiště dat. SQL dotaz pro extrakci je v následujícím dotazu sestaven z několika částí v závislosti na tom, jsou-li v tabulce CONFIG.Tables využity pro jednotlivé záznamy sloupce SQL Condition a Use Timestamp. Podmínka v klauzuli WHERE pro časový otisk je sestavena pomocí informací získaných předešlými dotazy, jsou jimi hodnoty SOURCE LAST DATABASE TIMESTAMP a SOURCE ACTUAL DATABASE TIMESTAMP použité v proměnných následujícího dotazu.

5.2

Logování ETL

47

Další proměnnou uvedenou v dotazu je SOURCE DATABASE ID, která byla získána rovněž dotazem do tabulky CONFIG.Databases. SELECT T.Table_Name AS STAGE_TABLE_NAME, T.SQL_String + CASE WHEN T.SQL_Condition IS NOT NULL THEN ’ WHERE ’ + T.SQL_Condition + CASE WHEN T.Use_Timestamp = 1 THEN ’ AND ’ ELSE ’’ END ELSE CASE WHEN T.Use_Timestamp = 1 THEN ’ WHERE ’ ELSE ’’ END END + CASE WHEN T.Use_Timestamp = 1 THEN ’[upsize_ts] BETWEEN CAST (CAST (@SOURCE_ACTUAL_DATABASE_TIMESTAMP AS BIGINT) AS VARBINARY(8)) AND CAST (CAST (@SOURCE_LAST_DATABASE_TIMESTAMP AS BIGINT) AS VARBINARY(8))’ ELSE ’’ END AS SOURCE_SQL_STRING, P.Stage_Package_Name AS PACKAGE_NAME FROM CONFIG.[Tables] T INNER JOIN CONFIG.[Databases] D ON T.Database_Name = D.Database_Name INNER JOIN CONFIG.[Packages] P ON T.Table_Name = P.Table_Name WHERE D.ID = @SOURCE_DATABASE_ID and T.Active=1 and T.SQL_String IS NOT NULL Uložení nového časového otisku Po ukončení extrakce je nutné zapsat aktuální hodnotu časového otisku pro všechny databáze využité při extrakci jako poslední časový otisk ve sloupci Last Timestamp tabulky CONFIG.Databases, aby byla zajištěna extrakce pouze nových nebo upravených záznamů rovněž při příštím spuštění extrakce do dočasného úložiště dat. UPDATE CONFIG.Databases SET Last_Timestamp = CAST(CAST(@SOURCE_ACTUAL_DATABASE_TIMESTAMP AS BIGINT) AS VARBINARY(8)) WHERE ID = @SOURCE_DATABASE_ID

5.2

Logování ETL

Logování je důležitou součástí tvorby všech aplikací, programů a dalších nástrojů v informačních technologiích. Oblast datových skladů, ačkoliv je velmi specifická, není výjimkou. Při logování projektu datového skladu se zajišťuje uchovávání důležitých informací o fungování jednotlivých komponent usnadňující jejich ladění při odstraňování vzniklých problémů. Zejména tvorba ETL komponenty je velmi náročnou fází při tvorbě datového skladu, proto je vytvoření kvalitního logovacího mechanismu ETL nezbytné pro rychlé a účinné ladění při jeho vývoji a údržbě.

5.2

48

Logování ETL

Obr. 9: Struktura logovacích tabulek V následující kapitole je vytvořen nástroj zajišťující logování jednotlivých ETL balíků, poskytující podrobné informace o jejich průběhu. 5.2.1

Struktura logovacích tabulek

Struktura logovacích tabulek je sdružena do schématu LOG a je znázorněna na obrázku 9. Tabulka LOG.Database Timestamps (tab. 7) je určena pro logování časových otisků pro jednotlivé databáze. V tabulce CONFIG.Databases se tyto informace sice ukládají, ale pouze v aktuální podobě a tedy již nelze dohledat jakými hodnotami byly časové otisky v minulosti přepsány. Tab. 7: LOG.Database Timestamps tabulka

Název sloupce ID Database ID Database Name Database Timestamp Chrono Timestamp

Popis Primární klíč tabulky. ID záznamu v tabulce CONFIG.Databases (cizí klíč). Název databáze (business klíč). Časový otisk posledního záznamu ve zdrojové databázi. Datum a čas vytvoření záznamu.

Každý záznam tabulky LOG.LoadPackages (tab. 8) obsahuje informace o průběhu jednotlivých ETL balíků. Ukládá podrobné informace o tom, ze kterého nadřazeného balíku byl konkrétní balík spuštěn, kdo jej spustil, kolik záznamů se v něm extrahovalo do dočasného úložiště dat, popřípadě vložilo nebo upravilo z dočasného úložiště do datového skladu. Dále jsou zde uloženy informace o času spuštění a času dokončení běhu balíku, časovém otisku posledního záznamu v tabulce a především výsledný stav po ukončení běhu balíku.

5.2

49

Logování ETL

Tab. 8: LOG.LoadPackages tabulka

Název sloupce ID ParentPackage ID

PackageType Package TableName Operator Computer ExtractCount InsertCount UpdateCount StartTime EndTime ExecutionGUID Last Timestamp Status

Popis Primární klíč tabulky. Nese informaci o ID záznamu v tabulce LOG.LoadPackages, který vyvolal spuštění tohoto balíku (cizí klíč). Typ ETL balíku (stage, store, control). Název ETL balíku. Ukládá informaci o názvu cílové tabulky. Uživatelský účet, pod kterým byl balík spuštěn. Název počítače, na kterém byl balík spuštěn. Počet extrahovaných záznamů do struktur dočasného úložiště dat (pouze v případě typu balíku stage). Počet nových záznamů vložených do struktur datového skladu (pouze v případě typu balíku store). Počet upravených záznamů struktur datového skladu (pouze v případě typu balíku store). Datum a čas spuštění balíku. Datum a čas ukončení balíku. Jedinečný kód přiřazený při spuštění ETL balíku. Poslední časový otisk záznamu ve zdrojové tabulce (pouze v případě typu balíku stage). Výsledný stav běhu balíku (succeed, failed)

Tabulka LOG.LoadPackages Detail (tab. 9) obsahuje informace o chybách vyvolaných při běhu jednotlivých ETL balíků. Každý záznam obsahuje detailní informace o každé chybě s propojením na balík, který chybu vyvolal prostřednictvím tabulky LOG.LoadPackages. Dále jsou v této tabulce zobrazeny informace o tom, jaká komponenta ETL balíku chybu vyvolala, o jaký typ události se jedná apod. Tab. 9: LOG.LoadPackages Detail tabulka

Název sloupce ID Event Source ExecutionID Message

Popis Primární klíč tabulky. Typ události. Komponenta v ETL balíku, která chybu vyvolala. Klíč pro spojení s tabulkou LOG.LoadPackages prostřednictvím jeho sloupce ExecutionGUID. Chybová zpráva vyvolaná při běhu ETL balíku.

5.2

50

Logování ETL

5.2.2

Procedury a funkce

Logovací mechanismus je zajištěn souborem několika procedur a funkcí, jejíž prostřednictvím jsou naplňovány logovací tabulky v databázi. Spouštění jednotlivých procedur je uskutečňováno přímo v jednotlivých ETL balících. Zdrojové kódy uvedených procedur a funkcí jsou uvedeny v příloze A. První procedurou je LOG.sp InitLoadPackages (tab. 10), která zajišťuje inicializaci logovacího mechanismu. Při vykonání této procedury se ukládají do tabulky LOG.LoadPackages informace o názvu a typu spuštěného ETL balíku, datum a čas spuštění apod. Výstupem této procedury je ID nově vytvořeného záznamu z této tabulky. Tab. 10: Popis parametrů procedury LOG.sp InitLoadPackage

Parametr @PackageName @ExecutionGUID @Operator @Computer @PackageType @ParentPackage ID @TableName

Popis Název ETL balíku. Jedinečný kód ETL balíku. Uživatelský účet, pod kterým byl balík spuštěn. Název počítače, na kterém byl balík spuštěn. Typ ETL balíku (stage, store, control). ID záznamu tabulky LOG.LoadPackages, který vyvolal spuštění tohoto balíku. Název cílové tabulky.

Další procedurou je LOG.sp EndLoadPackages (tab. 11), která obstarává ukládání informací získaných při běhu jednotlivých ETL balíků. Těmito informacemi jsou počet zpracovaných záznamů (extrahovaných, transformovaných apod.), poslední časový otisk extrahovaného záznamu ze zdrojové databáze, datum a čas ukončení balíku a výsledný stav po skončení běhu balíku. Tab. 11: Popis parametrů procedury LOG.sp EndLoadPackage

Parametr @ExtractLogID @ExtractCount @InsertCount @UpdateCount @PackageType

Popis ID záznamu vytvořeného inicializační procedurou LOG.sp InitLoadPackages. Počet extrahovaných záznamů do dočasného úložiště dat (pouze v případě typu balíku stage) Počet nových záznamů vložených do struktur datového skladu (pouze v případě typu balíku store). Počet upravených záznamů struktur datového skladu (pouze v případě typu balíku store). Typ ETL balíku (stage, store, control).

Předchozí procedura je volána pouze v případě úspěšného dokončení běhu balíku. Pokud ale běh balíku selže při jeho zpracování je volána procedura

5.2

51

Logování ETL

LOG.sp OnError (tab. 12), která zajišťuje uložení informace o selhání balíku a datum a čas ukončení běhu balíku do tabulky LOG.LoadPackages. Tab. 12: Popis parametrů procedury LOG.sp OnError

Parametr @ExtractLogID

Popis ID záznamu vytvořeného inicializační procedurou LOG.sp InitLoadPackages.

První funkcí pro logování je LOG.sf LoadPackages(), která neobsahuje žádné vstupní parametry a vrací hodnoty z tabulky LOG.LoadPackages nově zobrazené ve stromové struktuře prostřednictvím sloupce Package v závislosti na volání jednotlivých ETL balíků doplněné o dobu vykonání každého ETL balíku. Příklad výstupu této funkce je zobrazen na obrázku 10.

Obr. 10: Příklad výstupu funkce LOG.sf LoadPackages() Funkce LOG.sf LoadPackages() využívá pro zajištění výstupu ve stromové struktuře funkci LOG.sf LoadPackages Tree(@logID), která je rekurzivní funkcí obsahující parametr @logID (ID záznamu tabulky LOG.LoadPackages). Výstupem této funkce jsou hodnoty z tabulky LOG.LoadPackages doplněné o sloupec Depth (úroveň spuštění balíku ve stromové struktuře), který je později využit ve funkci LOG.sf LoadPackages() pro odsazení hodnoty sloupce Package zobrazené na předchozím obrázku. 5.2.3

Šablony ETL balíků

Pro zajištění logování ETL je nejprve nutné vytvořit ETL procedury nebo balíky, které budou mimo extrakce, transformace a nahrání dat obstarávat také právě logování do definovaných struktur prostřednictvím logovacích procedur popsaných v předchozí části. Pro realizaci ETL procedur lze využít nejrůznější nástroje a technologie, které lze snadno propojit s popsaným logovacím mechanismem prostřednictvím volání vytvořených procedur s předáním příslušných parametrů.

5.2

Logování ETL

52

V této práci je praktická část realizace zaměřena na nejrozšířenější nástroj pro tvorbu datových skladů, kterým je SQL Server v aktuální verzi 2008. ETL je v SQL Serveru realizováno prostřednictvím komponenty Integrační služby (SSIS), která prostřednictvím ETL balíků umožňuje realizaci datových pump pro extrakci, transformaci a nahrání dat do datového skladu. Z funkčního hlediska lze ETL balíky rozdělit na dva druhy: balíky pro extrakci dat ze zdroje do dočasného úložiště dat a balíky pro transformaci, čištění a nahrání dat z dočasného úložiště do datového skladu. Z tohoto důvodu byly vytvořeny dvě šablony ETL balíků pro oba zmíněné druhy. Šablona ETL balíku pro extrakci dat První šablona obstarává extrakci dat do dočasného úložiště dat. Obvykle se vytváří tolik balíků pro extrakci, kolik je různých tabulek ve všech zdrojových databázích, a tedy kolik je tabulek v dočasném úložišti dat. Často jsou to desítky i více tabulek, resp. balíků. Proto je vhodné vytvořit šablonu, která usnadňuje vytváření těchto balíků, jenž již má v sobě zakomponován logovací mechanismus, který je pro všechny balíky pro extrakci dat shodný.

Obr. 11: Šablona ETL balíku pro extrakci dat (control flow) Na obrázku 11 je zobrazen control flow14 šablony pro extrakci dat do dočasného úložiště dat. Skládá se ze tří prvků, které obstarávají inicializaci logovacího mecha14

komponenta integračních služeb SQL Serveru zajišťující řízení běhu balíku.

5.2

Logování ETL

53

Obr. 12: Šablona ETL balíku pro extrakci dat (data flow) nismu (procedura LOG.sp InitLoadPackages), data flow15 provádějící extrakci dat a ukončení logovacího mechanismu (procedura LOG.sp EndLoadPackages). Obě procedury využívají k naplnění jejich parametrů uživatelské nebo systémové proměnné definované v balíku. Vytvořené data flow provádějící extrakci dat, které je v control flow zobrazeno jako druhý prvek, je dále dekomponováno na obrázku 12. Je složeno z těchto prvků: • načtení (extrakce) dat ze zdrojové databáze, • získání počtu extrahovaných záznamů, • získání DB ID při extrakci z více databází (nepovinné), • uložení dat do dočasného úložiště. Šablona ETL balíku pro transformaci dat Transformace dat z dočasného úložiště do struktur datového skladu je obvykle mnohem náročnější než extrakce dat. Nicméně logovací mechanismus je zde velmi podobný. Rozdíl je pouze v tom, že u logování při transformaci dat se neukládá časový otisk posledního záznamu tabulky zdrojové databáze a získává se namísto počtu extrahovaných záznamů, počet nových a upravených záznamů v tabulkách datových skladů. Podstatnější rozdíl je v konfiguraci balíku, kde již není nutné znát připojovací řetězec do zdrojových databází, protože data jsou získávána z dočasného 15

komponenta integračních služeb SQL Serveru zajišťující datový tok balíku (transformace záznamů apod.).

5.2

Logování ETL

54

Obr. 13: Šablona ETL balíku pro transformaci dat (control flow) úložiště dat. Control flow šablony pro transformaci dat je zobrazeno na obrázku 13. Data flow šablony (obr. 14) pro transformaci dat obsahuje tyto základní prvky: • načtení dat z dočasného úložiště; • získání ID záznamu vytvořeného inicializační procedurou LOG.sp InitLoadPackages v tabulce LOG.LoadPackages zapsané v uživatelské proměnné balíku. To slouží k uložení této informace ke každému záznamu ve všech tabulkách datového skladu, aby bylo možné dohledat, jaké spuštění ETL balíku realizovalo vložení nebo upravení konkretního záznamu v datovém skladu; • rozvětvení toku na nové a upravené záznamy (lze realizovat prostřednictvím komponenty Lookup (znázorněno na obrázku 14) nebo komponenty Slowly Changing Dimension; • získání počtu nových (resp. upravených) záznamů; • uložení do struktur datového skladu. Obvykle je ovšem schéma datového toku složitější v závislosti na konkrétních potřebách transformace, proto po načtení dat z tabulky dočasného úložiště dat může

5.2

Logování ETL

55

být vložen libovolný počet jiných prvků realizující pokročilé procesy transformace a čištění dat.

Obr. 14: Šablona ETL balíku pro transformaci dat (data flow) Šablony, které byly vytvořeny v této kapitole lze snadno zakomponovat do vlastních řídících ETL balíků, které realizují jejich volání a integrují celý proces ETL do jednoho celku (například využitím konfigurace popsané v kapitole 5.1). 5.2.4

Reportovací nástroj

Logování ETL bylo realizováno v předchozích kapitolách prostřednictvím databázových struktur (tabulek, procedur a funkcí) a šablon ETL balíků, které spouští procedury a obstarávají předávání jejich parametrů. Nicméně neméně důležité je také sledovat průběh ETL prostřednictvím přehledného nástroje. Základem pro prezentaci dat z logovací tabulky je funkce LOG.sf LoadPackages, která vrací záznamy s informacemi o spuštěných ETL balících v přehledné formě. Na následujícím SQL dotazu je zobrazen příklad použití této funkce, kterou je pak možné využít při realizaci vlastního reportovacího nástroje prostřednictvím libovolně zvolené technologie.

5.2

56

Logování ETL

DECLARE @Date datetime = CONVERT(datetime,CONVERT(VARCHAR(10),getdate(),103),103) SELECT * FROM LOG.sf_LoadPackages() WHERE CONVERT(datetime,CONVERT(VARCHAR(10),starttime,103),103) = @Date ORDER BY ID

Obr. 15: Load Packages Monitoring report

5.2

57

Logování ETL

Podrobnosti o chybách vyvolaných při vykonávání některého z ETL balíku je možné získat z tabulky LOG.LoadPackages Detail, kterou lze propojit s funkcí LOG.sf LoadPackages prostřednictvím sloupce ExecutionID (resp. ExecutionGUID). Příklad propojení a získání informací o chybách je znázorněno následujícím SQL dotazem. SELECT * FROM LOG.sf_LoadPackages() sf INNER JOIN LOG.LoadPackages_Detail LPD on LPD.ExecutionID=sf.ExecutionGUID Realizace reportovacího nástroje je uskutečněna opět prostřednictvím nástroje SQL Server 2008, konkrétně komponentou Reportovací služby (SSRS). Nástroj se skládá ze sady dvou reportů, které jsou vzájemně propojené. Report Load Packages Monitoring zobrazuje spuštěné ETL balíky s informacemi o jejich průběhu uspořádaných pod sebou ve stromové struktuře seřazených podle času spuštění. Díky zobrazení ve stromové struktuře lze snadno vidět, jaký balík byl spuštěn kterým rodičovským balíkem (viz obr. 15). Druhým reportem je Load Packages Monitoring Detail (obr. 16), do kterého se lze dostat prostřednictvím názvu balíku (sloupec Package Name) v reportu Load Packages Monitoring. Tento report zobrazuje informace o chybových hlášeních vyvolaných při běhu jednotlivých balíků. Díky tomu lze pak poměrně snadno ladit tyto ETL balíky při jejich vývoji, případně odstranit chyby vyskytnuté při provozu.

Obr. 16: Load Packages Monitoring Detail report

5.3

58

Testování

5.3

Testování

Testování datových skladů se výrazně liší od testování většiny softwarových projektů. Testování probíhá v několika fázích (viz kapitola 3.5) a zajištění kvalitního testování je velmi důležité pro úspěšnost celého projektu. Díky testování lze záhy identifikovat nejrůznější problémy. Ačkoliv je testování obvykle realizováno ručně, lze jej i částečně automatizovat a umožnit tak získání základního přehledu o funkčnosti jednotlivých prvků datového skladu při vývoji a monitorování stavu datového skladu při jeho údržbě a provozu. V následující části budou popsány struktury a mechanismy pro automatizované testování dat v datovém skladu. Lze definovat různé typy testů. Základními jsou test počtu záznamů v tabulkách datového skladu a test agregovaných hodnot pro zvolené metriky v kostkách OLAP databáze. V obou případech je realizováno porovnání získaných hodnot s hodnotami ve zdrojových databázích. 5.3.1

Struktura testovacích tabulek

CONFIG.Testing (tab. 13) tabulka slouží pro konfiguraci testovací komponenty, proto se nachází ve schématu CONFIG. Prostřednictvím této tabulky se definují názvy a typ položek, které se budou během testování ověřovat. Pro každou položku lze definovat dotaz pro získání hodnot ze zdrojových databází, které jsou později porovnávány s hodnotami v datovém skladu. Tab. 13: CONFIG.Testing tabulka

Název sloupce Name Type Query source Source DB Cube

Popis Název testovací položky (název tabulky, metriky, apod.). Typ testovací položky (metrika, tabulka, atd.). SQL dotaz do zdrojové databáze pro získání hodnoty pro test. ID databáze uvedené v tabulce CONFIG.Databases, ze které se bude hodnota dotazovat. Při testování metrik se vyplní název datové krychle.

Tabulka TEST.Values (tab. 14) je jediná tabulka nacházející se ve schématu TEST. Tato tabulka slouží pro ukládání hodnot získaných při testování.

5.3

59

Testování

Tab. 14: TEST.Values tabulka

Název sloupce ID Name Type Date Source Value Execution ID DB ID

5.3.2

Popis Primární klíč tabulky. Název testovací položky (název tabulky, metriky, apod.). Typ testovací položky (metrika, tabulka, atd.). Datum zapsání hodnoty. Zdroj, ze kterého byla hodnota získána (dwh, source). Hodnota získaná při testování. Identifikátor, který sdružuje testování více položek vyvolané společným podnětem. ID databáze uvedené v tabulce CONFIG.Databases, ze které se získala hodnota pro test ze zdrojové databáze (pouze v případě zdroje source).

Příprava konfiguračních dat

Je zřejmé, že techniky pro získání testovacích dat ze zdrojových databází, relačních tabulek datového skladu a OLAP databází se liší. Proto je nutné připravit konfigurační data zvlášť pro každou techniku. Konfigurace pro získání hodnot z tabulek datového skladu Příprava konfiguračních dat pro získání testovacích hodnot z tabulek datového skladu je velmi prostá. Prostřednictvím jednoduchého SQL dotazu se získají názvy tabulek datového skladu, které budou využity pro testování. SELECT DISTINCT Name FROM CONFIG.testing WHERE TYPE=’Table’ Konfigurace pro získání hodnot ze zdrojových databází Příprava konfiguračních dat pro získání testovacích hodnot ze zdrojových databází využívá mimo tabulky CONFIG.Testing také tabulku CONFIG.Databases pro získání připojovacího řetězce do zdrojové databáze, odkud se prostřednictvím SQL dotazu (sloupec Query source) získají testovací hodnoty. SELECT Name, Query_source, Type, Database_ConnectionString, Source_DB, CASE WHEN Cube IS NULL THEN ’Unknown’ ELSE Cube END FROM CONFIG.testing T INNER JOIN CONFIG.Databases D ON T.source_db=D.ID WHERE Query_source IS NOT NULL

5.3

Testování

60

Konfigurace pro získání hodnot z OLAP databází Příprava konfiguračních dat pro získání testovacích hodnot z OLAP databází je velmi specifická tím, že se zde prostřednictvím názvu metriky ve sloupci Name a názvu kostky ve sloupci Cube automaticky sestaví MDX dotaz pro získání agregované hodnoty pro jednotlivé metriky uvedené v tabulce CONFIG.Testing. SELECT Name, Type, Cube, ’WITH MEMBER Measures.[Hodnota] AS ’’[Measures].[’+[Name]+’]’’ SELECT Measures.[Hodnota] ON COLUMNS FROM [’+[Cube]+’]’ AS Query_olap FROM CONFIG.testing WHERE TYPE=’Measure’ AND Cube IS NOT NULL 5.3.3

Realizace testovacího mechanismu

Testovací mechanismus je založen na principu získání konfiguračních dat, provedení dotazů k získání testovacích hodnot a uložení těchto hodnot s dalšími podrobnostmi do tabulky TEST.Values. Realizace testovacího mechanismu je provedena v SQL Serveru 2008, prostřednictvím Integračních služeb. Celý mechanismus zajišťují dva vzájemně propojené ETL balíky, které je možné integrovat do celkového ETL řešení. Popis řešení Nejprve jsou do kolekcí načteny konfigurační data prostřednictvím dotazů popsaných v předchozí části a následně jsou pro každou kolekci vytvořeny kontejnery zajišťující průchod jednotlivých záznamů v kolekcích pro získání testovacích dat a uložení do tabulky TEST.Values. Control flow řešení je zobrazeno obrázku 17. První kontejner zajišťuje cyklus pro získání počtu záznamů v jednotlivých tabulkách datového skladu, které jsou definovány pro testování v tabulce CONFIG.Testing. Každý průchod v cyklu provádí následující SQL dotaz s různými hodnotami v parametrech podle informací extrahovaných z kolekce COLLECTION Store. Tento dotaz zajišťuje získání počtu záznamů z jednotlivých tabulek datového skladu. INSERT INTO [TEST].[Values] VALUES (@Name, ’Table’, getdate(), ’dwh’, ( SELECT sum(rows) AS rows FROM sysindexes i INNER JOIN sys.tables t ON i.id=t.object_id WHERE indid IN (0,1) AND SCHEMA_NAME(t.schema_id)=’STORE’ AND cast(object_name(id) as varchar(50))=@Name GROUP BY i.id ), @Execution_ID, NULL)

5.3

61

Testování

Obr. 17: ETL balík testovacího mechanismu (control flow) Druhý kontejner slouží jako cyklus pro volání dceřiného balíku, který zajišťuje získání požadované hodnoty ze zdrojové databáze. Důvod, proč je volán jiný balík je ten, že se do něj předává konfigurační hodnota připojovacího řetězce konkrétní zdrojové databáze, což nemůže být realizováno v jediném ETL balíku. Control flow i data flow tohoto balíku jsou zobrazeny na obrázcích 18 a 19.

Obr. 18: ETL balík pro získání hodnot ze zdrojové databáze (control flow)

5.3

62

Testování

Obr. 19: ETL balík pro získání hodnot ze zdrojové databáze (data flow) Poslední kontejner slouží jako cyklus pro získání agregovaných dat z metrik v OLAP databázích. Tento proces je realizován prostřednictvím data flow komponenty (obr. 20), která obstarává získání agregované hodnoty pro každou metriku prostřednictvím MDX dotazu sestaveného v kolekci COLLECTION Kostka.

Obr. 20: Získání hodnoty z kostky (data flow)

5.3

Testování

5.3.4

63

Reportovací nástroj

Výsledek testování je důležité přehledně prezentovat. Všechna data potřebná pro prezentaci lze získat z tabulky TEST.Values vytvořením patřičných SQL dotazů. Nástroj reportující výsledek testování, který bude v této části popsán se skládá ze dvou typů testů. Jedná se o test počtu záznamů v tabulkách datového skladu a test agregovaných hodnot pro zvolené metriky. V obou případech v závislosti na konfiguraci v tabulce CONFIG.Testing. Princip zobrazení výsledků testování je založen na porovnání hodnot z datového skladu s hodnotami ve zdrojových databázích. Tyto hodnoty by se měly shodovat. Příprava dat pro prezentaci Prezentaci výsledků testování lze umožňujit Vytvořením následujících SQL dotazů. Prvním typem testu, resp. SQL dotazem, je test počtu záznamů v tabulkách datového skladu ve srovnání s počtem záznamů ve všech zdrojových databázích sloužící jako vstup do tabulek datového skladu. SELECT Name, Date, Value AS Value_dwh, ( SELECT sum(value) FROM [TEST].[Values] WHERE Execution_ID=V.Execution_ID AND Name=V.Name AND source=’source’ ) AS Value_Source, Execution_ID, DB_ID FROM [TEST].[Values] V WHERE TYPE=’Table’ AND Source=’dwh’ AND Execution_ID = CASE WHEN @Execution_ID=0 THEN ( SELECT max(Execution_ID) FROM [TEST].[Values] WHERE TYPE=’Table’ AND Source=’dwh’ ) WHEN @Execution_ID!=0 THEN @Execution_ID END Druhým typem testu (SQL dotazem) je test agregovaných hodnot pro zvolené metriky ve srovnání s agregovanými hodnotami ze zdrojových databází. SELECT Name, Date, Value AS Value_dwh, ( SELECT sum(Value) FROM [TEST].[Values] WHERE Execution_ID=V.Execution_ID AND Name=V.Name AND Cube=V.Cube AND Source=’source’ ) AS Value_Source, Execution_ID, Cube

5.3

64

Testování

FROM [TEST].[Values] V WHERE TYPE=’Measure’ AND Source=’dwh’ AND Execution_ID = CASE WHEN @Execution_ID=0 then ( SELECT max(Execution_ID) FROM [TEST].[Values] WHERE TYPE=’Measure’ AND Source=’dwh’ ) WHEN @Execution_ID!=0 then @Execution_ID END Realizace nástroje pro prezentaci výsledků Pro prezentaci výsledků byl zvolen nástroj reportovacích služeb SQL Serveru 2008 podobně jako v případě logování v předchozí kapitole. Výsledek je tvořen sadou dvou reportů zabývajících se jednotlivými typy testů. Pro naplnění dat v reportech byly využity SQL dotazy popsány v předchozí části.

Obr. 21: Test Tables report

5.3

65

Testování

Prvním reportem je Test Tables report, který zobrazuje výsledek testu počtu záznamů v tabulkách datového skladu ve srovnání s počtem záznamů ve všech zdrojových databázích. Pokud se výsledky testů pro jednotlivé položky shodují, pak je záznam označen stavem Succeed (obr. 21). Druhým reportem je Test Measures report, který zobrazuje výsledek testu agregovaných hodnot pro jednotlivé metriky ve srovnání s agregovanými hodnotami ze všech souvisejících zdrojových databází. Ve srovnání s předchozím reportem je doplněn sloupec Difference, který zobrazuje rozdíl mezi hodnotou v datové krychli a hodnotou získanou ze zdrojových databází. Obvykle totiž dochází při transformaci hodnot z produkčních systémů do OLAP databází datového skladu k určitému nepatrnému zkreslení hodnot zaviněným zaokrouhlováním při transformaci. Ačkoliv jsou hodnoty zaokrouhlovány obvykle až na velmi podrobném desetinném místě, při množství dat do datového skladu to již může hrát roli. Proto je zde uveden právě sloupec Difference, který uvádí přesný rozdíl mezi těmito hodnotami. Hodnota tohoto sloupce je pak využita při vyhodnocení stavu testovací položky. Celková agregovaná hodnota v kostce je vynásobena koeficientem 0.00001 označující povolenou odchylku pro výsledek shody. Pokud je rozdíl menší než tato odchylka, pak je stav testovací položky označen hodnotou Succeed (Obr. 22).

Obr. 22: Test Measures report

5.4

Nasazení a konfigurace

5.4

66

Nasazení a konfigurace

Nasazení a konfigurace datového skladu je poslední fází při vývoji projektu, popřípadě jednoho cyklu projektu. Datový sklad se zpravidla vyvíjí na jiném prostředí než produkčním, obvykle testovacím, proto je zapotřebí postup nasazení a konfigurace kvalitně naplánovat a jednotlivé části automatizovat. Zajistí se tak bezproblémové nasazení a konfigurace datového skladu při prvním i dalším nasazení. Nasazení a konfigurace datového skladu a prostředí by se měla skládat z následujících kroků: • vytvoření struktur datového skladu, • konfigurace, • zajištění automatizovaného provozu. 5.4.1

Vytvoření struktur datového skladu

Struktury datového skladu jsou veškeré komponenty vytvořené při vývoji datového skladu nezbytné pro jeho provoz. Jsou jimi: • struktury relační databáze datového skladu (tabulky, procedury, funkce, apod.) • ETL balíky a procedury, • struktury OLAP (databáze, kostky, struktury pro dolování dat, uživatelské role, atd.), • reportovací nástroje (sady reportů). Struktury relačních a OLAP databází lze automatizovat vytvořením SQL a XMLA skriptů, které zajistí vytvoření potřebných struktur a jejich naplnění inicializačními daty. ETL balíky se obvykle pouze nakopírují na server, popřípadě pokud je ETL vykonáváno jiným způsobem, například prostřednictvím sady procedur, pak je pro ně vytvořen samostatný SQL skript. Reportovací nástroje se nasazují v závislosti na použité technologii, například vytvořením skriptů, které nakopírují sady reportů na webový server. 5.4.2

Konfigurace

Konfiguraci lze rozdělit na dvě části, konfiguraci prostředí (serveru) a konfiguraci datového skladu. Při konfiguraci prostředí se obvykle využívají proměnné prostředí, které definují připojení k databázím datového skladu. Tato připojení jsou využívána v jednotlivých ETL balících, kde při zpravidla velkém množství balíků je jejich společná konfigurace velmi důležitá. Příklad vytvoření záznamů v proměnných prostředí

5.4

Nasazení a konfigurace

67

pro využití v ETL balících vytvořených v SQL Serveru 2008 je zobrazeno na následujícím skriptu, který je možné uložit a poté snadno spustit v dávkovém souboru. Po vytvoření záznamů v proměnných prostředí je nutné restartovat stroj. REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\ Environment" /v~Framework_DB_ConnectionString /t REG_SZ /d "Data Source=localhost;Initial Catalog=Framework_DW_DB; Provider=SQLNCLI10.1;Integrated Security=SSPI;" /f REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\ Environment" /v~Framework_UDM_ConnectionString /t REG_SZ /d "Data Source=localhost;Initial Catalog=Framework_UDM; Provider=MSOLAP.4;Integrated Security=SSPI;Location=localhost; Format=Tabular;" /f Konfigurace datového skladu znamená naplnění konfiguračních tabulek vhodnými daty. To lze zahrnout již do SQL skriptu, který vytváří struktury datového skladu a naplňuje je inicializačními daty. V lepším případě lze ale vytvořit mechanismus pro snadnou konfiguraci všech potřebných údajů například prostřednictvím uložené procedury. Procedura, upravující konfigurační hodnoty dle struktur definovaných v kapitole 5.1 je znázorněna na následujícím skriptu. Parametr @Machine definuje název počítače a parametr @PackagePath fyzickou cestu k ETL balíkům. CREATE PROCEDURE [CONFIG].[usp_AdjustConfigurationValues] ( @Machine nvarchar(255), @PackagePath nvarchar(255) ) AS BEGIN IF @Machine IS NOT NULL UPDATE CONFIG.Databases SET Database_ConnectionString = ( SELECT REPLACE(D.Database_ConnectionString,’localhost’,@Machine) FROM CONFIG.Databases WHERE ID=D.ID ) FROM CONFIG.Databases D IF @PackagePath IS NOT NULL UPDATE CONFIG.PackageConfig SET ConfiguredValue=@PackagePath WHERE ConfigurationFilter=’PackagePath’ END

5.4

Nasazení a konfigurace

5.4.3

68

Zajištění automatizovaného provozu

Nasazení datového skladu nekončí pouze nasazením a konfigurací jeho komponent na produkčním prostředí. Nezbytné je také zajistit pravidelný běh ETL balíků, procesení kostek apod. K zajištění provozu slouží v SQL Serveru komponenta SQL Server Agent, která umožňuje vytvoření a konfiguraci úkolů (tzv. jobů), které prostřednictvím integrovaného plánovače dokáží vykonávat nejrůznější činnosti. Tímto způsobem lze automatizovat proces transformace nových a upravených dat do datového skladu. Pro vytvoření a konfiguraci úkolu je nezbytné provedení následujících kroků: • Vytvořit na serveru účet DW Service account (včetně hesla), • Nastavit spuštění služby SQL Server Agent a SQL Server Analytické služby pod účtem DW Service account (obr. 23),

Obr. 23: Konfigurace přihlášení ke službě SQL Server Agent • Nastavit přístup účtu ke strukturám DW Service account datového skladu (viz příloha B), • Spustit SQL dotaz pro vytvoření jobu (viz příloha B), • Naplánovat job.

5.5

69

Údržba a provoz

5.5

Údržba a provoz

Údržba a provoz datového skladu je vzhledem k obecné charakteristice datových skladů velmi specifická. Jeho úkolem je mimo implementaci drobných úprav dle změn v uživatelských požadavcích také sledovat stav datového skladu vzhledem k jeho objemu, kvalitě dat a celkovému provozu. Ralph Kimball uvádí, že práce na údržbě a provozu datového skladu pokrývají až padesát procent rozpočtu projektu (Kimball, 1998, s. 716). Vytvoření kvalitního nástroje pro sledování stavu datového skladu proto výrazně šetří náklady spojené s předvídáním budoucích problémů a analýzou příčin aktuálních problémů. 5.5.1

Struktura tabulek pro údržbu

Struktura tabulek pro údržbu je sdružena do schématu MAINTAINANCE a je znázorněna na obrázku 24.

Obr. 24: Struktura tabulek pro údržbu Tabulka MAINTAINANCE.Overview (tab. 15) slouží pro ukládání důležitých informací o databázi datového skladu a úložišti dat, kde se datový sklad nachází. Tyto informace lze využít pro sledování nárůstu objemu datového skladu a případného předvídání nedostatku místa na fyzických discích. Druhou tabulkou tohoto schématu je MAINTAINANCE.Records (tab. 16) tabulka, do které jsou zapisovány informace s vývoji záznamů v jednotlivých relačních tabulkách datového skladu.

5.5

70

Údržba a provoz

Tab. 15: MAINTAINANCE.Overview tabulka

Primární klíč tabulky. Název relační databáze datového skladu. Objem dat v databázi (v MB). Objem dat logu databáze (v MB). Objem skutečně uložených dat v databázi (v MB). Volný prostor v databázi, který je k dispozici (v MB). Celková velikost databáze (DataSize+LogSize, resp DBUsedSize+DBFreeSize) DBFilePath Cesta k souboru databáze na fyzickém disku. LogFilePath Cesta k souboru logu databáze na fyzickém disku. DataDrive Jednotka disku, kde se nachází data. LogDrive Jednotka disku, kde se nachází log. DataDiscCapacity Velikost oddílu, kde se nachází data. DataDiscUsedSpace Obsazené místo v oddílu, kde se nachází data. DataDiscFreeSpace Volné místo v oddílu, kde se nachází data. LogDiscCapacity Velikost oddílu, kde se nachází log. LogDiscUsedSpace Obsazené místo v oddílu, kde se nachází log. LogDiscFreeSpace Volné místo v oddílu, kde se nachází log. Date Datum a čas uložení informací. ID DBName DataSize LogSize DBUsedSize DBFreeSize TotalSize

Tab. 16: MAINTAINANCE.Records tabulka

ID Table Date Value Value Value Value 5.5.2

act prev new update

Primární klíč tabulky. Název tabulky datového skladu. Datum a čas uložení informací. Aktuální počet záznamů. Předchozí počet záznamů. Počet nových záznamů v tabulce. Počet aktualizovaných záznamů v tabulce.

Procedury pro naplnění tabulek

Základní procedurou je MAINTAINANCE.usp Overview, která zabezpečuje naplnění tabulky MAINTAINANCE.Overview. Tato procedura využívá pro získání informací o databázi a logovém souboru volně dostupnou proceduru dbo.sp SDS, vytvořenou Richardem Dingem, specialistou v oblasti administrace databází (Ding, 2008). V další části procedury MAINTAINANCE.usp Overview je pro získání informací o oddílu disku, kde se nachází databázové soubory logu a dat, využita assembly DiskSpace vytvořená v jazyku C# využívající Microsoft .NET Framework. Tato assembly používá třídu StoredProcedures vytvořenou pro technologii SQL Server společností Microsoft umožňující vytváření tzv. CLR uložených procedur v programovacích jazycích pracující s Microsoft .NET Framework. Kompletní zdrojový kód této assembly je uveden příloze X. Před využitím této assembly je ještě

5.5

Údržba a provoz

71

nutné spustit následující SQL dotaz, který ji integruje do databáze (s upřesněním cesty ke zkompilovanému souboru assembly DiskSpace.dll) a vytváří proceduru dbo.isp DiskSpace, prostřednictvím které je tato assembly volána v prostředí SQL Serveru. Tato procedura obsahuje jediný parametr, kterým je název serveru. Tento parametr je volitelný, pokud je vynechán, pak je použit název lokálního serveru. CREATE ASSEMBLY DiskSpace FROM ’\DiskSpace.dll’ WITH PERMISSION_SET = UNSAFE GO CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace GO Další procedurou je MAINTAINANCE.usp Records, která je volána uvnitř procedury MAINTAINANCE.usp Overview a obstarává naplnění tabulky MAINTAINANCE.Records aktuálními daty. 5.5.3

Reportovací nástroj

Data naplněná do tabulek uvedenými procedurami je nezbytné přehledným způsobem zobrazovat tak, aby odpovědné osoby v projektu měly okamžitý přístup ke všem těmto důležitým informacím. Reportovací nástroj pro údržbu a provoz datového skladu v sobě navíc integruje i sadu reportů vytvořených pro sledování testování a logování popsané v předchozích kapitolách. Tím se tento nástroj stává komplexním a umožňujícím sledovat na jednom místě veškeré důležité informace o provozu datového skladu. Realizace reportovacího nástroje je zajištěna opět prostřednictvím reportovacích služeb SQL Serveru 2008. Nástroj je složen z celkové sady deseti reportů, přičemž některé z nich byly již popsány v předchozích kapitolách. Základním reportem, do kterého jsou integrovány všechny důležité informace, je Maintainance Dashboard. Tento report (obr. 25) prostřednictvím jednoduchých SQL dotazů extrahuje z tabulek pro údržbu a provoz, logování a testování souhrnné informace o velikosti databáze, diskových oddílech, stavu pravidelného spouštění ETL a výsledku pravidelného testování. Prostřednictvím reportu Maintainance Dashboard lze volat další reporty, tzv. subreporty, které zobrazují podrobnější informace z jednotlivých sekcí tohoto reportu. Prvním z nich je Database Size Growth report, který zobrazuje vývoj velikosti databáze datového skladu v čase díky přehlednému grafu (obr. 26). Do tohoto reportu se lze dostat prostřednictvím hodnoty Total Database Size.

5.5

72

Údržba a provoz

Obr. 25: Maintainance Dashboard report

Obr. 26: Database Size Growth report Dalším subreportem složeným ze sady dvou reportů zobrazující rovněž informace o velikosti databáze, ale z pohledu počtu záznamů v jednotlivých tabulkách relační databáze datového skladu jsou reporty Tables Overview (obr. 27), do kterého se lze dostat přes hodnotu Rows count a Tables Overview Detail (obr. 28) přístupného z reportu Tables Overview prostřednictvím hodnoty ve sloupci Table. Report Tables Overview zobrazuje informace o vývoji záznamů ve všech

5.5

73

Údržba a provoz

tabulkách datového skladu přehledně pod sebou. Zatímco report Tables Overview Detail zobrazuje prostřednictvím grafu informace o vývoji záznamů ve zvolené tabulce (popřípadě všech tabulek skrze hodnotu Total).

Obr. 27: Tables Overview report Dalším subreportem přístupným z reportu Maintainance Dashboard je HDD Free Space Overview. Tento report zobrazuje vývoj volného místa na jednotlivých diskových oddílech, na kterých se nacházejí soubory dat a logu databáze datového skladu (obr. 29). Tyto informace lze využít pro předvídání možného nedostatku volného místa v jednotlivých oddílech a zabránění souvisejících problémů. Report lze zobrazit prostřednictvím hodnoty Free Space v sekci HDD Info. Report Load Duration Overview (obr. 30) je dalším subreportem reportu Maintainance Dashboard, přístupný přes hodnotu Duration v sekci ETL Load Info. Report zobrazuje v grafu vývoj doby trvání pravidelného spouštění ETL balíků. Následující subreporty vztahující se k logování a testování byly již podrobně popsány v předchozích kapitolách. Report Maintainance Dashboard je využívá a integruje tyto informace do společného celku. Prvním z těchto subreportů je Load Packages Monitor (obr. 15 v kapitole 5.2.4), který zobrazuje informace o posledním spuštěném ETL. Report je dostupný skrze hodnotu Status, která nabývá hodnot Succeed, resp. Failed, v závislosti na výsledném stavu posledního spuštění ETL. V sekci testování reportu Maintainance Dashboard jsou informace o posledním dokončeném testování a jejich výsledky. Do podrobností o každém z těchto testů se lze dostat skrze jejich výsledný stav. Subreporty takto dostupné jsou popsané v kapitole 5.3.4, jsou jimi Test Tables a Test Measures (obr. 21 a 22 na str. 64).

5.5

74

Údržba a provoz

Obr. 28: Tables Overview Detail report

Obr. 29: HDD Free Space Overview report

5.6

Aplikace komponent při realizaci projektu

75

Obr. 30: Load Duration Overview report

5.6

Aplikace komponent při realizaci projektu

Jak je uvedeno v kapitole 1.2 (Cíl práce), cílem této práce není podrobný popis realizace datového skladu od počátku do konce, ale realizace datového skladu aplikací jednotlivých nástrojů a principů popsaných v předchozích kapitolách pro konfiguraci, logování, testování, nasazení, údržbu a provoz datového skladu při tvorbě datového skladu. V této části je stručně popsána realizace datového skladu s využitím dat z databáze AdventureWorks2008. Při realizaci datového skladu byla využita technologie SQL Server 2008 a její jednotlivé komponenty: databázový stroj, integrační služby, analytické služby, reportovací služby a SQL Server Agent. 5.6.1

Projektová příprava

V první fázi byl vytvořen stručný projektový plán (viz příloha C), ve kterém byly uvedeny základní požadavky na datový sklad a jeho jednotlivé součásti. Projektový plán vymezuje cíle, kterými jsou vytvoření datového skladu ze zdrojové databáze AdventureWorks2008, který bude obsahovat prodeje z internetových nákupů v období let 2001–2004 a jehož výsledek bude srovnatelný s daty v datovém tržišti Internet Sales kostky Adventure Works OLAP databáze Adventure Works DW 2008. Dálé projektový plán také vymezuje dobu trvání a sekvenci jednotlivých úkolů projektu. V další části přípravy projektu byla provedena multidimenzionální analýza obsahující mapování zdrojů dat, definici metrik, dimenzí a kostek. Multidimenzionální analýza je uvedena rovněž v příloze C. Modelování technologické platformy bylo zaměřeno na návrh technologické architektury, ve které byly definovány jednotlivé komponenty datového skladu. Jsou

5.6

Aplikace komponent při realizaci projektu

76

jimi zdrojová databáze, dočasné úložiště dat a datový sklad v části back room, OLAP databáze a reportovací nástroje v části front room. 5.6.2

Implementace

Ve fázi implementace byly nejprve vytvořeny struktury datového skladu, kterými jsou jednotlivé tabulky dočasného úložiště dat a datového skladu. Mezi tyto struktury byly rovněž implementovány struktury konfiguračních, logovacích, testovacích a provozních tabulek. V dalším kroku byly naplněny konfigurační tabulky patřičnými daty dle požadavků projektu. Tzn. do tabulky CONFIG.Databases byl vložen jediný záznam pro zdrojovou databázi AdventureWorks2008. Do tabulky CONFIG.Tables bylo vloženo 13 záznamů specifikující tabulky a SQL dotazy, kterými se budou extrahovat data do dočasného úložiště dat. Dále tabulka CONFIG.Packages byla naplněna 13 záznamy definující názvy tabulek dočasného úložiště dat, do kterých se budou extrahovat data prostřednictvím specifikovaných ETL balíků pro extrakci. Tyto data se budou dále nahrávat do uvedených tabulek datového skladu prostřednictvím ETL balíků specifikovaných rovněž v této tabulce. V další fázi je provedena konfigurace ETL balíků prostřednictvím proměnných prostředí a procedury CONFIG.usp AdjustConfigurationValues uvedených v kapitole 5.2.4. Do poslední konfigurační tabulky CONFIG.Testing jsou vloženy záznamy pro testování. Po vytvoření potřebných struktur prostřednictvím tzv. create skriptu, což je soubor jednotlivých DDL16 a DML17 SQL dotazů a konfiguraci uvedených tabulek a proměnných prostředí se dále vytváří ETL řešení. To je postaveno na využití navržených šablon ETL balíků pro extrakci a transformaci s využitím řídících struktur prostřednictvím logiky uskutečněné dotazy uvedenými v kapitole 5.1.2 (Využití konfiguračních tabulek při ETL). Výsledná databáze datového skladu obsahující struktury definované v dimenzionální analýze a data po ETL transformaci se nachází v UIS (soubor Framework DW DB.bak). V další fázi vývoje projektu se přistupuje k vytvoření OLAP databáze a jejich jednotlivých součástí. Nejprve se definuje tzv. Data source view (obr. 31), který specifikuje tabulky a jejich vazby formující OLAP struktury databáze. Dále se definují jednotlivé dimenze, jejich prvky a hierarchie. Dimenzemi vytvořenými v tomto projektu jsou: • Product, • Time, • Geography, • Customer, • Sales Territory. 16 17

Data Definition Language Data Manipulation Language

5.6

Aplikace komponent při realizaci projektu

77

Obr. 31: Data source view OLAP databáze Po vytvoření všech dimenzí se přistupuje k vytvoření struktur kostky datového skladu. V kostce se nejprve definují skupiny metrik a v nich obsažené metriky z faktových tabulek uvedených v DSV (Data source view). Dále dimenze, které bude kostka využívat (Obr. 32) a následně je nutné definovat vazby, které spojují jednotlivé dimenze s fakty, resp. skupiny metrik s jednotlivými dimenzemi (viz obr. 33). Jakmile jsou všechny potřebné struktury OLAP databáze vytvořeny dle specifikace v projektovém plánu, pak je možné databázi uložit do formátu XMLA, který prostřednictvím skriptu vytváří všechny vytvořené struktury v analytických službách instance SQL Serveru, na které je skript spuštěn. V další fázi implementace je vytvořena sada reportů pro údržbu a provoz datového skladu uvedené v kapitole 5.5.3.

5.6

Aplikace komponent při realizaci projektu

Obr. 32: Struktura kostky

Obr. 33: Spojení faktů s dimenzemi (Dimension Usage)

78

5.6

Aplikace komponent při realizaci projektu

5.6.3

79

Nasazení

Nasazení projektu datového skladu probíhá podle postupu uvedeném v kapitole 5.4. Nejprve jsou na serveru vytvořeny struktury datového skladu složené z relační a OLAP databáze, ETL balíků a sady reportů prostřednictvím vytvořených skriptů, případně nakopírováním balíků na server, resp. reportů na webový server. Dále pokud se vývojový server liší od produkčního, pak je provedena konfigurace serveru a konfigurace datového skladu prostřednictvím dávkového souboru definující proměnné prostředí a procedury CONFIG.usp AdjustConfigurationValues. Jakmile jsou struktury nasazeny a konfigurace provedena je nutné automatizovat provoz datového skladu dle postupu v kapitole 5.4.3. Tzn. vytvoření účtu DW Service account na serveru, nastavení služeb Analytické služby a SQL Server Agent, aby byly spouštěny pod tímto účtem, a přidělení přístupu k strukturám datového skladu tomuto účtu. Následně je vytvořen job prostřednictvím komponenty SQL Server Agent a naplánováno jeho spouštění pro pravidelný běh ETL a procesení OLAP databáze. 5.6.4

Údržba a provoz

Údržba a provoz datového skladu jsou zajištěny sadou reportů, které zobrazují veškeré důležité informace o databázi datového skladu, diskových oddílech, na kterých se databáze nachází, spouštění ETL řešení a průběhu testování. 5.6.5

Interpretace a porovnání výsledků

Projekt datového skladu, který byl v této kapitole vytvořen na základě aplikace jednotlivých principů a nástrojů pro konfiguraci, logování, testování, nasazení, údržbu a provoz datového skladu, lze díky tomu, že již existuje vzorová výsledná OLAP databáze Adventure Works DW 2008, verifikovat z hlediska obsahu dat. Společnost Microsoft vytvořila vzorovou zdrojovou databázi AdventureWorks2008 a výslednou OLAP databázi Adventure Works DW 2008, které jsou součástí SQL Serveru 2008 bez mechanismů a struktur zajišťující proces transformace těchto dat pro umožnění realizace a následné verifikace vzorového projektu využívající vlastní principy a mechanismy. Na obrázcích 34 a 35 je vytvořena ad-hoc analýza porovnávající OLAP databázi Framework UDM a databáziAdventure Works DW 2008 z hlediska obsahu dat, aby bylo možné verifikovat výsledný projekt. Kompletní záloha výsledné OLAP databáze se nachází v UIS (soubor Framework UDM.abf).

5.6

Aplikace komponent při realizaci projektu

Obr. 34: Ad-hoc analýza OLAP databáze Framework UDM

Obr. 35: Ad-hoc analýza OLAP databáze Adventure Works DW 2008

80

6

DISKUSE

6

81

Diskuse

Po realizaci datového skladu s využitím aplikace principů a nástrojů rozšiřující metodiku tvorby datových skladů může dojít ke zhodnocení výsledků diplomové práce. Toto zhodnocení lze uvést rovněž na základě zkušeností s realizací datových skladů bez použití uvedených principů a nyní s jejich využitím.

6.1

Přínosy a užití vytvořených principů a nástrojů

Po teoretické stránce se lze v této práci ve srozumitelné formě seznámit s oblastí tvorby a údržby projektů datového skladu postavené na zdrojích, které jsou považovány za základní stavební kameny této problematiky. Přínos vlastní práce lze spatřit v podobě definování principů a vytvoření nástrojů, které lze obecně využít při tvorbě datových skladů. Užití této práce bylo již v praxi úspěšně realizováno při projektu tvorby datového skladu pro firmu zabývající se prodejem zboží na internetu. Tento projekt byl již několikátým projektem, na kterém jsem se podílel a zároveň prvním, u kterého byly využity principy a nástroje popsané v této práci. Na základě těchto zkušeností tedy lze uvést, že projekt tvorby datového skladu s využitím principů a nástrojů, které jsou obsahem této diplomové práce byl úspěšnější jak z hlediska času, tzn. nákladů, tak i kvality, tedy nutnosti provádění oprav při zkušebním provozu.

6.2

Možnosti rozšíření

Ačkoliv tato práce popisuje všechny nejdůležitější fáze podstatné při vývoji a údržbě datového skladu, které lze standardizovat a využít při jeho každé realizaci, další možnosti rozšíření této práce lze vidět například v definování principů pro údržbu uživatelských rolí a sledování chování uživatelů prostřednictvím logování jejich činnosti. Na základě toho je možné opět vytvořit nástroje realizující tyto principy v praxi využitím zvolené technologie. Uživatelé datového skladu jsou totiž velmi podstatnou oblastí při vývoji a údržbě datového skladu, kterou je vhodné se zabývat. Dále lze rozšířit již existující nástroje například o pokročilejší techniky využitelné pro predikci volného místa na disku prostřednictvím technik dolování dat apod. Předpokládám, že na základě dalších zkušeností s projekty datového skladu a zejména využití definovaných principů popsaných v této práci dojde v budoucnu k dalšímu rozšíření této práce.

6.3

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

Provést srovnání této práce s existujícími řešeními zabývající se problematikou tvorby datových skladů je poměrně obtížné. Tato práce se totiž nachází na hranici mezi obvyklými řešeními a zdroji popisujícími principy využitelné při vývoji

6.3

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

82

a údržbě projektů datových skladů. Na jedné straně jsou to velmi obecné principy, vytvořené zejména Ralphem Kimballem, které nepopisují vlastní metodiku a formu pro realizaci jednotlivých částí pro konfiguraci, logování, testování, nasazení, údržbu a provoz datového skladu popsané a realizované v této práci, ale pouze poměrně abstraktně přináší rady a principy pro vlastní realizaci těchto principů. Na druhé straně jsou to publikace a zdroje popisující realizaci konkrétního projektu, např. Microsoft Project Real (Microsoft, 2009), na kterém jsou popsány principy pro vytvoření a údržbu nejrůznějších komponent datového skladu. Tyto principy a nástroje ale nejsou obvykle obecně využitelné a slouží spíše jako inspirace při tvorbě vlastního projektu. Tato práce přináší kombinaci obou těchto přístupů, tzn. na základě konkretizace získaných teoretických poznatků z několika zdrojů a praktických zkušeností přináší obecné principy, které již definují samotnou strukturu a metodiku těchto nástrojů využitelných bez ohledu na použitou technologii.

7

7

ZÁVĚR

83

Závěr

Cílem této diplomové práce byl návrh metodiky a realizace modelového řešení zajišťující konfiguraci, logování, testování, nasazení, údržbu a provoz datových skladů sestávající se ze všech potřebných částí prostřednictvím definovaných principů a vytvořením odpovídajících nástrojů. V první části došlo k nastudování teorie problematiky tvorby datových skladů prostřednictvím zdrojů, které jsou uvedeny v přehledu literatury. Hlavními postavami z oblasti datových skladů a Business Intelligence, od kterých byly čerpány teoretické poznatky, byly W. Inmon a R. Kimball odbornou veřejností označování jako největší osobnosti z této oblasti. Na základě získaných znalostí a dosavadních praktických zkušeností byla navrhnuta metodika pro praktickou část diplomové práce, která popisuje jednotlivé kroky a nástroje pro dosažení definovaného cíle. Vlastní práce se zabývala obecným návrhem principů na základě využití uvedené metodiky umožňující realizaci datových skladů prostřednictvím nejrůznějších technologií. Následně byly v každé části navržené principy zobrazeny prostřednictvím nástrojů realizovaných v prostředí MS SQL Serveru 2008 a jeho souvisejících komponent. Na základě toho byl poté realizován projekt datového skladu aplikací vytvořených principů a nástrojů. V poslední části byly zhodnoceny výsledky a uvedeny přínosy této diplomové práce, možnosti rozšíření a srovnání s existujícími řešeními. Při zpracování diplomové práce bylo dosaženo požadovaného výsledku. Tím je přínos navržené metodiky rozšiřující současné principy pro obecnou tvorbu individuálních projektů. Během zpracování této práce se nevyskytly výraznější problémy, možná lze zmínit lehkou nestabilitu vývojového prostředí MS Visual Studio 2008 a nedostatek invence v literatuře, která by se odlišovala od principů a metod publikovaných W. Inmonem a R. Kimballem. Závěrem lze konstatovat, že cíl této diplomové práce byl splněn.

8

LITERATURA

8

84

Literatura

Ding R. Check SQL Server database and log file size with this stored procedure. 2008 [cit. 28-10-2010]. Dostupný na internetu: http://searchsqlserver.techtarget.com/tip/Check-SQL-Server-database-andlog-file-size-with-this-stored-procedure. Hernandez M. J. , Viescas J. L. Myslíme v jazyku SQL - Tvorba dotazů. Grada, 2004. 380 s. ISBN 80-247-0899-X. Inmon, W. H. Building the Data Warehouse (Third Edition). Wiley, 2003. 356 s. ISBN 0-471-08130-2. Kelley Ch. Best uses for a data warehouse. 2003 [cit. 12-10-2010]. Dostupný na internetu: http://www.itworld.com/nl/db mgr/11192003. Kimball R. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. Wiley, 1998. 800 s. ISBN 0-47125547-2. Kimball R., Ross M. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition. Wiley, 2002. 421 s. ISBN 0-471-20024-7. Lacko, Ľ. Business Intelligence v SQL Serveru 2005 : reportovací, analytické a další datové služby. Brno: Computer Press, 2006. 391 s. ISBN 80-251-1110-5. MacLennan J., Crivat B., Tang Z. Data mining with Microsoft SQL server 2008. Wiley, 2009. 672 s. ISBN 0-470-27774-2. Novotný – Pour – Slánský Business Intelligence: jak využít bohatství ve vašich datech. Praha: Grada, 2005. 254 s. ISBN 80-247-1094-3. Pittner K. Business intelligence: mějte pod čepicí. 2007 [cit. 20-10-2010]. Dostupný na internetu: http://businessworld.cz/aktuality/business-intelligencemejte-pod-cepici-2700. Spofford G. MDX Solutions With Microsoft SQL Server Analysis Services. Wiley, 2001. 432 s. ISBN 0-471-40046-7. Gartner Reveals Five Business Intelligence Predictions for 2009 and Beyond. 2009 [cit. 12-06-2010]. Dostupný na internetu: http://www.gartner.com/it/page.jsp?id=856714. Project REAL–Business Intelligence in Practice. 2009 [cit. 28-10-2010]. Dostupný na internetu: http://www.microsoft.com/sqlserver/2005/en/us/projectreal.aspx. Technologie. 2010 [cit. 4-11-2010]. Dostupný na internetu: http://www.microsoft.com/cze/sqlserver2008/produkt/technologie/default.aspx.

8

LITERATURA

85

Understanding The Data Warehouse Life Cycle. 2006 [cit. 4-11-2010]. Dostupný na internetu: http://www.wherescape.com/Downloads/wherescape dwlm%5B2%5D.pdf.

Přílohy

A

UKÁZKA ZDROJOVÝCH KÓDŮ

A

87

Ukázka zdrojových kódů

V následující části jsou uvedeny zdrojové kódy k vybraným procedurám, funkcím a assembly v jazyce C#. Kompletní sada všech skriptů pro vytvoření databázových struktur, které jsou popsány v části vlastní práce se nachází v UIS (dokument Skript.sql). Procedura LOG.sp InitLoadPackages CREATE PROCEDURE [LOG].[sp_InitLoadPackages] @PackageName NVARCHAR(100) , @ExecutionGUID uniqueidentifier , @Operator NVARCHAR(100) , @Computer NVARCHAR(100) , @PackageType NVARCHAR(100) , @ParentPackage_ID int = NULL , @TableName NVARCHAR(100) = NULL AS BEGIN IF @PackageType=’stage’ BEGIN DECLARE @Last_Timestamp varbinary(8) SET NOCOUNT ON; SELECT @Last_Timestamp = ISNULL(MAX(Last_Timestamp), 0x00000000) FROM [LOG].[LoadPackages] WHERE TableName = @TableName

A

UKÁZKA ZDROJOVÝCH KÓDŮ

END INSERT INTO [LOG].[LoadPackages] ( Package , StartTime , TableName , ExecutionGUID , Operator , Computer , PackageType , ParentPackage_ID ) VALUES ( @PackageName , GetDate() , @TableName , @ExecutionGUID , @Operator , @Computer , @PackageType , @ParentPackage_ID )

88

A

UKÁZKA ZDROJOVÝCH KÓDŮ

IF @PackageType=’stage’ SELECT CAST(Scope_Identity() AS INT) LOGGING_ExtractLogID , CAST (CAST (@Last_Timestamp AS BIGINT) AS VARCHAR(40)) LOGGING_LastTimestampString ELSE SELECT CAST(Scope_Identity() AS INT) LOGGING_ExtractLogID

SET NOCOUNT OFF; END Procedura LOG.sp EndLoadPackages CREATE PROCEDURE [LOG].[sp_EndLoadPackages] @ExtractLogID INT , @ExtractCount INT = NULL , @InsertCount INT = NULL , @UpdateCount INT = NULL , @PackageType NVARCHAR(100) AS BEGIN SET NOCOUNT ON;

89

A

UKÁZKA ZDROJOVÝCH KÓDŮ

90

IF @PackageType=’stage’ BEGIN DECLARE @Last_Timestamp varbinary(8), @SQL NVARCHAR(255) SELECT @SQL = N’SELECT @Last_Timestamp = ISNULL(MAX(upsize_ts), 0x00000000) FROM [Framework_DW_DB].[STAGE].’ + TableName FROM [LOG].[LoadPackages] WHERE ID = @ExtractLogID EXEC sp_executeSQL @SQL, N’@Last_Timestamp varbinary(8) OUTPUT’, @Last_Timestamp OUTPUT END UPDATE [LOG].[LoadPackages] SET EndTime = GetDate() , ExtractCount = @ExtractCount , InsertCount = @InsertCount , UpdateCount = @UpdateCount , Last_timestamp = case when

@PackageType=’stage’ then @Last_Timestamp

else NULL END , [Status] = ’Succeed’

A

UKÁZKA ZDROJOVÝCH KÓDŮ

WHERE ID = @ExtractLogID

SET NOCOUNT OFF; END Procedura [LOG].[sp OnError] CREATE PROCEDURE [LOG].[sp_OnError]

@Log_ID int

/* Procedura nastavi status zaznamu v~logu na Failed a~zapise cas ukonceni package

by Lubos */ AS

BEGIN UPDATE LOG.LoadPackages SET Status = ’Failed’ , EndTime = GETDATE() WHERE ID = @Log_ID

91

A

UKÁZKA ZDROJOVÝCH KÓDŮ

92

END Assembly isp DiskSpace using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Diagnostics; using Microsoft.SqlServer.Server;

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void isp_DiskSpace(string serverName) { if (serverName == "") { serverName = Environment.MachineName; } PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName); SqlDataRecord record = new SqlDataRecord( new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),

A

UKÁZKA ZDROJOVÝCH KÓDŮ

93

new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256), new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256), new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256), new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6)); SqlContext.Pipe.SendResultsStart(record); foreach (string instanceName in pcc.GetInstanceNames()) { PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "\% Free Space", instanceName, serverName); PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName); float percentfree = pcPercentFree.NextValue(); float freespace = pcFreeMbytes.NextValue(); float capacity = (freespace * 100) / percentfree; float usedspace = capacity - freespace; if (instanceName != "_Total") { record.SetSqlString(0, instanceName); record.SetSqlString(1, capacity.ToString());

A

UKÁZKA ZDROJOVÝCH KÓDŮ

record.SetSqlString(2, usedspace.ToString()); record.SetSqlString(3, freespace.ToString()); record.SetSqlString(4, percentfree.ToString()); SqlContext.Pipe.SendResultsRow(record); } } SqlContext.Pipe.SendResultsEnd(); } };

94

B

B

SKRIPTY PRO ZAJIŠTĚNÍ AUTOMATIZOVANÉHO PROVOZU

95

Skripty pro zajištění automatizovaného provozu

Sada následujících skriptů se stará o přidělení odpovídajících práv účtu DW Service account a vytvoření jobu pro pravidelný běh ETL balíků, procesení kostek apod. CREATE LOGIN [localhost\DW_Service_account] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO

CREATE USER [DW_Service_account] FOR LOGIN [localhost\DW_Service_account] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N’db_owner’, N’DW_Service_account’ EXEC sp_addrolemember N’db_datareader’ , N’NT AUTHORITY\NETWORK SERVICE’ Ukázka skriptu pro vytvoření jobu v SQL Serveru se nachází v UIS (dokument Vytvoreni jobu.pdf).

C

C

PROJEKTOVÝ PLÁN A MULTIDIMENZIONÁLNÍ ANALÝZA

96

Projektový plán a multidimenzionální analýza

V této příloze se nachází kompletní projektový plán k realizaci datového skladu popsaného v kapitole 5.6. Definování cílů Cílem projektu je realizace datového skladu ze zdrojové databáze AdventureWorks2008 aplikací komponent vytvořených v diplomové práci. Data v datovém skladu bude možné srovnat s daty v databázi AdventureWorksDW2008. Analýza zdrojů Analýza zdrojů provedená nad zdrojovou databází AdventureWorks2008 obsahuje informace o využitelných tabulkách a jejich sloupcích pro extrakci do datového skladu. Obsahuje také tzv. mapování zdrojů do struktur datového skladu. Všechny tyto informace jsou součástí multidimenzionální analýzy. Analýza požadavků Primárním požadavkem byla realizace datového skladu podle struktur v databázi AdventureWorksDW2008 aplikací principů a nástrojů vytvořených v diplomové práci. Dalším požadavkem bylo, že výsledný datový sklad obsahuje shodná výsledná data jako databáze AdventureWorksDW2008. Rozčlenění úkolů Realizace datového skladu byla rozčleněna do následujících úkolů: • Projektový plán • Multidimenzionální analýza • Návrh fyzického modelu • Implementace databázových struktur • Implementace ETL řešení • Implementace OLAP databáze • Implementace reportů • Testování • Nasazení Definování doby trvání úkolů Celková časová náročnost byla odhadnuta na 140 hodin.

C

PROJEKTOVÝ PLÁN A MULTIDIMENZIONÁLNÍ ANALÝZA

Název úkolu Projektový plán Multidimenzionální analýza Návrh fyzického modelu Implementace databázových struktur Implementace ETL řešení Implementace OLAP databáze Implementace reportů Testování Nasazení

97

Časová náročnost 4 hodiny 16 hodin 10 hodin 12 hodin 30 hodin 12 hodin 26 hodin 24 hodin 6 hodin

Sekvence úkolů Pro návrh a prezentaci sekvence úkolů byl využit Ganttův diagram. Výsledek Ganttova diagramu lze vidět na následujícím obrázku. Pracovní čas byl zvolen 8 hodin denně, 40 hodin týdně v období od 12.10.2010. Podle diagramu by měl být projekt dokončen 4.11.2010. V rozsáhlejších projektech se obvykle jednotlivé úkoly dále dekomponují na menší celky, čímž nabývá tento přístup na využitelnosti.

Multidimenzionální analýza Multidimenzionální analýza obsahující mapování zdroje dat, definici metrik, dimenzí a kostek se nachází v UIS (dokument Multidimenzionálni analyza.xls). Ukázka z multidimenzionální analýzy je zobrazena na následujícím obrázku.