Datové sklady a BI aplikace

1. Datové sklady a BI aplikace MFF – část 2 Říjen 2004 Ing. David Pirkl

2. 3. Přednáška

3. Téma • Dimenzionální modelování

4. Agenda BDLC • Plán projektu a projektový management • Business požadavky • Dimenzionální modelování • Architektura • Fyzický design • ETL • Uživatelské aplikace • Nasazení • Správa a růst DW

5. Dimenzionální modelování • Dimenzionální modelování je rozdílné od klasického ERD datového modelování • ERD • Normalizace • Odstranění redundantnosti • Málo srozumitelné člověku • Optimalizován na vkládáni dat a update • Dimensionální modelování • Důraz na srozumitelnost pro uživatele • Dosaženo standardní strukturou – fakta a dimenze • Základní přístup – denormalizace, redundance • Optimalizován na vyhledávání dat a složité analýzy • Základy položeny v 60. tých letech (General Mills and Dartmount University, Nielson Marketing Research)

6. Dimenzionální modelování • Výhody dimenzionálního modelu • Standardní – navazují na to OLAP aplikace, tvůrci reportingových aplikací, … • Dobře rozšiřitelný (bez dopadu na aplikace) • Přidání nových fakt se stejnou granualitou • Přidání dimenze • Přidání atributu dimenze • Standardní způsoby modelování reálných problémů • SCD • Sledování událostí (Factless fact table) • Heterogenní produkty • …

7. Dimenzionální modelování • Základní přístup k modelování dat v datovém skladě • Oproti klasickému relačnímu modelu dochází k denormalizaci • Proč dimenzionální modelování: • Přehledné, uživatelsky pochopitelné • Důraz na obchodní logiku • Denormalizace • Menší počet tabulek, spojení • Rychlejší odezva • Většina údajů v jedné tabulce • Indexy

8. Region Prodej Granualita Rok Kategorie produktu Kvartál Typ produktu Produkt 3 dimenze Dimenzionální modelování • Základní myšlenka multidimenzionálního modelování

9. Základní typy tabulek • V datovém skladě se vyskytují dva hlavní typy tabulek: • Faktové tabulky • Dimenzionální tabulky

10. Dimensionální modelování • Nejužitečnější fakta – jsou často numerická a aditivní • Dimenze tvoří vstupní bod do DW • Omezení v dotazech • Hlavičky řádků v reportech

11. Dimensionální tabulky • Dimensionální tabulky zachycují úhel pohledu na sledované ukazatele • Představují vlastně „číselníky“ • Typické dimenze jsou: • Čas • Zákazník • Produkt • Prodejna • Smlouva

12. Dimensionální tabulky • Atributy v dimenzi: • Textové hodnoty (nebo se chovají jako textové) • Diskrétní • Slouží pro definici omezení a agregace v výstupech • K výstupům lze využít všechny atributy dimenzí • Jednoduché SQL (Select …group by…order by) Drag Drag Grouping column Additive facts

13. Dimenzionální tabulky • Každá dimenzionální tabulka obsahuje jeden jednoznačný identifikátor a popisné atributy • Dimenzionální tabulka je denormalizovaná

14. Dimensionální tabulky • Atributy v dimenzi: • Hierarchické (kategorie – subkategorie – produkt) • Nehierarchické (barva_produktu) • Výstupy většinou kombinují oba typy atributů • Muže existovat i více hierarchií Hierarchie 1 Hierarchie 2 Atributy v žádné hierarchii

15. Hierarchie v dimenzi • Dimenzionální tabulka v sobě nese různé hierarchie – vztahy 1:M mezi atributy dimenze • Hierarchie mají vždy stromovou strukturu • Příklady hierarchie: • Rok -> Kvartál -> Měsíc -> Den • Rok -> Týden -> Den • Země -> Kraj -> Okres -> Obec • Kategorie produktu -> Sub kategorie -> Produkt

16. Kalendářní rok Fiskální rok Produkt kategorie Kalendářní kvartál Fiskální týden Produkt skupina Kalendářní měsíc Víkend Typ produktu Prázdniny Produkt Den v týdnu Den Hierarchie – zápis • Způsob zápisu hierarchie v dimenzi:

17. Dimensionální modelování • Dimensionální atributy jsou důležité (vstupní brána do DW) • Mají být • Popisné • Nepoužívat kódy • Bez chybějících (null) hodnot – nahradit např. Neuvedeno • Zajištění kvality (bez překlepů, nemožných hodnot, zastaralé, sirotci…) • Standardizace (např. standardní zápis adresy)

18. Výběr dimenzí • Počet dimenzí by se měl pohybovat okolo 5 až 15 • Méně dimenzí – něco chybí, zda nelze dodat: • Kauzální dimenzi (promoce, kontrakt, počasí, podmínky obchodu, …) • Další časové dimenze (hlavně u faktových tabulek zachycující položky celku (např. objednávka -> objednané produkty) • Dimenzi ve více rolích (např. místo odkud se volalo, kam se volalo) • Status dimenzi – označující současný status dimenze nebo snímku (např. nový zákazník) • Auditní dimenzi • Degenerovanou dimenzi • Junk dimenzi • Přidání dimenzí často nezmění granualitu původní faktové tabulky • Lze přidat i do provozujícího datového skladu

19. Výběr dimenzí • Více jak 20 až 30 dimenzí ukazuje na možnost jejich spojení • Nejsou nezávislé – spojit do jedné • Obchodně patří k sobě (např. značka, kategorie, oddělení) • Korelace (viz –Junk dimenze)

20. Faktové tabulky • Faktové tabulky slouží k uchování informací o sledovaných ukazatelích • Mezi typické ukazatele lze zařadit: • Počet prodaných kusů • Hodnotu prodaného zboží (v Kč) • Počet zákazníků • Počet smluv • Výše škody • Délka hovoru • Z hlediska datového se většinou jedná o číselné údaje, které lze agregovat

21. Faktové tabulky • Granualita faktové tabulky: míra podrobnosti sledovaných ukazatelů, jejich přesný význam • Např.: počet prodaných kusů za den daného zboží v dané prodejně • Přiřazený dimenzí: • Popisy, které nabývají jedné hodnoty pro jeden záznam ve faktové tabulce (s danou granualitou) • Při více hodnotách (M:N vztah) řešit přes pomocné tabulky

22. Typy ukazatelů • Ukazatele máme tří typů: • Aditivní – agregovatelné (sčítáním) přes všechny dimenze (např. prodej v ks) • Semiaditivní – agregovatelné (sčítáním) jen přes některé dimenze (např. stav zásob v ks) • Nutné agregovat jinými agregačními funkcemi např. průměr • POZOR nelze vždy použít funkci AVG v SQL – problém prázdných období (kdy nebyla transakce) • Neaditivní – neagregovatelné (např. některá textová fakta – počasí při nehodě)

23. Faktová tabulka • Z datového hlediska: • Faktová tabulka obsahuje cizí klíče dimenzionálních tabulek • Tyto cizí klíče tvoří primární klíč faktové tabulky • Navíc obsahuje faktová tabulka jednotlivé ukazatele • Kimball’s law: Každý vztah M:N je faktová tabulka, z definice

24. Ukazatele v faktové tabulce • Existují dva způsoby zápisu ukazatelů do faktové tabulky:

25. Typy faktových tabulek • Transakční • Zachycuje jednotlivé transakce, jednotlivé akce v daný časový okamžik • Zachycuje jen transakce jež se udály (jestliže zákazník nic nekoupil nebude v faktové tabulce) • Obvyklý fakt: Množství (v dané transakci) • Obvykle se po naplnění dále neprovádí update • Ukazuje chování, vývoj v čase • Snímková • Zachycuje stav k určitému časovému okamžiku (periodicky) • Většinou měsíční • Obvykle existuje jeden záznam pro všechny kombinace významných dimenzí • Sledovaná fakta – často složité výpočty, někdy vhodné přebírat z OLTP systémů (již ověřená čísla) • Umožňuje efektivně generovat výstupní reporty s často složitě vypočitatelnými ukazateli • Není efektivní je generovat přímo z transakcí

26. Typy faktových tabulek • Akumulovaná • Zachycuje stav v daný okamžik • Většinou obsahuje několik časových dimenzí (kdy byl záznam naposledy updatován, datum jednotlivých sledovaných fází) • Řada obsahuje „null“ hodnoty, které jsou postupně vyplňovány • Potřeba umělých klíčů v časové dimenzi na hodnotu „Dosud neznámo“ • Dochází k update v faktové tabulce při změně stavu • Pro sledovanou událost jeden záznam ve faktové tabulce, který je postupně updatován • Vhodná tam kde sledovaná událost má daný čas trvání

27. Typy faktových tabulek • Vhodné začít s snímkovou nebo akumulovanou fakt tabulkou • Postupně lze přidat i transakční • Pro pokročilé analýzy chování

28. Dimensionální modelování • Čtyři kroky tvorby faktové tabulky • Výběr datového tržiště • Jeden datový zdroj vs. Více • Začít s řešením kde jeden datový zdroj • Určení granuality dat • Měla by být co nejdetailnější • Potřeba přesně vydefinovat, určuje co bude v fakt tabulce uloženo • Určení typy faktové tabulky • Výběr dimenzí • Vychází z určení granuality plus další dimenze vyhovující navržené granualitě • Granualita dimenze nemůže být nižší než granualita faktové tabulky • Určení faktů (ukazatelů) • Vychází z granuality a typu faktové tabulky • Ukazatele s rozdílnou granualitou vytvořené např. z důvodu urychlení výpočtu je třeba uložit do zvláštní faktové tabulky (např. součty pro výpočet procent z detailních ukazatelů, …)

29. Různá granualita fakt • Fakta s různou granualitou musí být uloženy v různých faktových tabulkách • Často je potřeba alokovat fakta s vyšší granualitou na nižší pro možnost srovnaní • Např. alokovat náklady objednávky na jednotlivé produkty pro porovnání s výnosy • Viz dále Parent-child modelování

30. Uspořádání tabulek • Existují dvě základní schémata uspořádání faktových a dimenzionálních tabulek: • Hvězda • Sněhová vločka

31. Hvězda

32. Vločka

33. Výhody/Nevýhody uspořádání • Hvězda • Preferované uspořádání • Přehlednější uspořádání z hlediska uživatele • Snadněji udržovatelé • Méně spojení než u vločky • Vločka • Méně přehledné • Náročnější na údržbu • Nutné pro napojitelnost dalších faktových tabulek (BUS architektura)

34. Dimensionální modelování • Doporučuje se používat star schéma • Snowflake – není tak srozumitelné uživateli • Na druhou stranu je někdy vhodné při napojení jiných faktových tabulek s rozdílnou granualitou • Použijeme-li fyzicky snowflake – je vhodné odstínit uživatele pomocí views • Snowflake ušetří trochu místa ale většinou zanedbatelné (hlavní velikost je ve faktech) • Existují výjimky – extra velké dimenze (např. zakaznici)

35. Kdy Snowflakes • Před uživatelem je možné Snowflakes skrýt za pomoci views • Fyzický design – řízen rychlostí, efektivností • Logický design – řízen uživatelskou přívětivostí, jednoduchostí • Např. dimenze zákazník • Klasický zákazník (20%) • Návštěvník www (80%) • Společné atributy: • Shopper surrogate key • Shopper ID (fixed ID for each physical shopper) • Recency • Frequency • Klasický zákazník • Five name attributes • 10 location attributes • 10 behavior attributes • 25 demographic attributes.

36. Kdy Snowflakes • Příklad:

37. Kdy Snowflakes • Finanční produkty • Mnoho produktů, některé atributy společné, jiné rozdílné • Jedna dimenze – mnoho null hodnot v nevyplněných atributech • Snowflake key lze nahradit umělým klíčem společným přes všechny tabulky (viz unity dimension) • Uživatele lze odstínit pomocí views

38. Kdy Snowflakes • Časová dimenze – různé kalendáře v různých organizacích • Pozor: • Subdimenze má větší kardinality než dimenze • Primární klíč subdimenze je snowflake_key a Organization • Potřeba specifikovat organizaci při spojení tabulek (pak 1:1)

39. Pojmy Drill-down, …. • Drill-down: ukaž mi větší detail • Přidání sloupce z dimenze do výstupu • Drill-up: ukaž mi agregaci • Odebrání sloupce z výstupu • Drill-across: spojení dvou a více faktových tabulek se stejnou granualitou • Drill-around: podobné jako drill-across, ale pro nelineární uspořádání • Slice-dice: řez multidimenzionální kostkou, omezení výběru • Slice – výběr dimenze (zákazník, produkt, čas) • Dice – výběr hodnoty v dimenzi (za rok = 2004 a produkt = chleba)

40. Pojmy Drill-down, …. • Příklad drill-across: obchodní řetězec • Zásoba, objednávka, dodávka, prodej • Samostatné fact tabulky spojené časovou a produktovou dimenzí (dimenze jsou „conformed“ vůči faktovým tabulkám) • Výsledný dotaz využívá outer-join

41. Pojmy Drill-down, …. • Drill-around: příklad zdravotnictví, conformed dimenze pacient Patient

42. 4. Přednáška

43. Dimensionální modelování • BUS architektura • Zajišťuje napojení jednotlivých etap (datových tržišť) navzájem a tak zajišťuje vytvoření celistvého DW • Datová tržiště by měla obsahovat co nejvíce podrobná atomická data • Vytvořena za pomoci Conformed dimensions a standard fact • Conformed dimensions • Dimenze, která znamená to samé ve všech faktových tabulkách na které může být připojena • Je stejná ve všech datových tržištích • Klasickým příkladem: Zákazník, Produkt, Region, Čas • Potřeba identifikovat tyto dimenze, udržovat je, publikovat a dodržovat • Data pro conformed dimenzi často z více zdrojů • Fyzicky jsou implementovány jednou tabulkou napojitelnou na všechny relevantní faktové tabulky • Dovoluje tedy operaci drill across • Návrh většinou na co nejdetailnější úrovni • Využívat umělé klíče • Větší flexibilita • Nezávislost na OLTP • SCD

44. Dimensionální modelování • Conformed standard fact • Aby zaručilo bezchybné drill across • Stejná fakta musí být stejně uložena ve všech datových tržištích (např. příjem) • Stejné měrné jednotky (ne jednou v jednotkách, jednou v krabicích) • Lze ztěží porovnat v reportech • Uložit oboje • Označuje-li jiné věci, pojmenovat rozdílně • Není-li třeba provázanosti, je někdy možné vytvářet nezávislé datové tržiště (např. prodáváme-li rychlé občerstvení a traktory, nemusí mít cenu zákazníky sdružovat do jedné dimenze)

45. Dimensionální modelování • Praktické zkušenosti: • Obvykle 4 – 15 dimenzí na faktovou tabulku • Fakt – je něco co není známo většinou předem, co pozorujeme, co vychází z chování trhu, … • Dimenze • Často záleží na rozhodnutí designera • Lze dimenze produkt a obchod • Prodávají-li se produkty ve všech obchodech (nezávislé) • Nebo jednu dimenzi obchod-produkt • Prodávají-li se určité produkty v určitém obchodě (závislé) • Taková dimenze je výhodnější – nese informaci o tom kde se co prodává – lze tuto informaci získat přímo prohlížením (browsing) dimenze

46. Praktický příklad 3 • Vytvořte multidimenzionální model 1. vrstvy datového skladu: • Uživatelé chtějí sledovat: • Prodej zboží v korunách a kusech • Prodeje v jednotlivých dnech • Prodeje podle produktů, jejich kategorií a subkategorií • Analyzovat využití dopravců – počet přepraveného zboží • Analyzovat prodejce • Analyzovat prodeje podle jednotlivých zákazníků a skupin zákazníků (dle geografického umístění)

47. Praktický příklad 3

48. Dimenzionální modelování • Modelovací řešení • Slowly Changing Dimensions • Rychle se měnící dimenze (RCD) • Surrogate Keys • Pomocné tabulky (vztahy M:N) • Složité hierarchie v dimenzi • Degenerovaná dimenze • Junk dimension • Heterogenní produkty • Spojení fakt tabulek • Transakční dimenze • Audit dimenze • Časová dimenze • Mnohonásobné měrné jednotky • Různě měny • Intervalový reporting • Změny v dimenzi • Velké dimenze • Causal dimension • Role • Many Alternate Realities • Unity dimension • Faktová tabulka bez faktů • Vztahy parent-child • On-line DW • Sledování zákazníka

49. Slowly Changing Dimensions • Problém uchování historie v datech • OLTP systémy často neřeší • Přepsání hodnot • Odmazání historických dat • Příklad: Změna názvu produktu, ID produktu se nemění • Tři možnosti řešení • Přepsání • Vytvoření nového záznamu • Vytvoření atributu s aktuální hodnotou

50. SCD – Typ 1 • Nejednoduší a nejrychlejší • Neudržuje historii • Stará hodnota pro nás není významná • Např. byla špatně zadaná