Dimenzionální modelování Profinit. All rights reserved

Dimenzionální modelování RNDr. Ondřej Zýka

© 2014 Profinit. All rights reserved.

[email protected]

Dimenzionální modelování o Ralph Kimball (1997) – Primárně modely pro datové sklady a analýzy – Silně denormalizovaný model

o Modely – Pochopitelné pro netechnicky orientované uživatele – Snadno rozšiřitelné – Orientované na analytické dotazy – Podporované datovými servery a analytickými nástroji (OLAP)

o Schopnost reportovat z extrémního objemu dat o Minimum update – pouze přidávání dat

o Požadavek neměnící se historie o Technologie „neumí“ současný update a select


2

Příklad


3

Standardní dotaz select SUM(qty) from F_SALES,D_TIME,D_TITLES,D_STORES,D_AUTHORS where F_SALES.TITLES_KEY = D_TITLES.TITLES_KEY and F_SALES.STORES_KEY = D_STORES.STORES_KEY and F_SALES.AUTHOR_KEY = D_AUTHOR.AUTHOR_KEY and F_SALES.DATE_KEY = D_DATE. DATE_KEY and podminky na D_TITLES and podminky na D_STORES and podminky na D_AUTHORS and podminky na D_DATE group by pozadovana granularita vysledku


4

Star schéma Dimenze Time Transaction Date Grocery Transaction Store Number Transaction Date Customer Product Quantity Amount Customer Customer From Date To Date First Name Last Name Address 1 Address 2 Address 3 City State Country Postal Code © 2014 Profinit. All rights reserved.

Fakta

Store Store Number Store Name City State Country Telephone

Product Product Description Category

5

Snowflake schéma Sales Period Period Identifier Sales Period From Date To Date

Region Region Description

Time Transaction Date

Grocery Transaction Store Number Transaction Date Customer Product Quantity Amount

Customer Category Category Customer Category


Customer Customer First Name Last Name Address 1 Address 2 Address 3 City State Country Postal Code Customer Category

Store Store Number Store Name City State Country Telephone Region

Product Product Description Category Product Category Product Category Description

6

Snowflake model o Výhody

o Nevýhody

– Minimální redundance dat v rámci dimenzí

– Složitější konstrukce dotazů, mnoho joinů

– Úspora místa v databázi

– Nižší výkonnost

– Větší flexibilita pro modelování

– Komplikovaný snowflake model může odradit uživatele od přímého přístupu k datům

– Užitečný pro dimenze se složitou strukturou

• uživatelské nástroje zpravidla zavádějí sémantickou vrstvu, která uživatele odstíní od datového modelu

– Možný konflikt s bitmapovými indexy – Úspora místa je většinou převážena nižší výkonností a složitější administrací


7

Constellation schéma


Product Inventory Product Warehouse Location Quantity On Hand Quantity Back Ordered

Warehouse Warehouse Address 1 Address 2 Address 3 City State Country Postal Code

Vendor Vendor Vendor Name Address 1 Address 2 Address 3 City State Country Postal Code

Time Transaction Date Product Purchases Product Purchase Date Supplying Vendor Purchase Order Unit Quantity Purchase Cost

Grocery Transaction Store Number Transaction Date Customer Product Purchase Quantity Amount Customer Customer First Name Last Name Address 1 Address 2 Address 3 City State Country Postal Code Customer Category © 2014 Profinit. All rights reserved.

Product Product Description Category Product Line

8

Snowstorm schéma Region Region Description

Sales Period Period Identifier Sales Period From Date To Date

Promotion Period Promotion Id Promotion From Date To Date


Product Inventory Product Warehouse Location Quantity On Hand Quantity Back Ordered

Warehouse Warehouse Address 1 Address 2 Address 3 City State Country Postal Code

Vendor Vendor Vendor Name Address 1 Address 2 Address 3 City State Country Postal Code

Time Transaction Date Product Purchases Product Purchase Date Supplying Vendor Purchase Order Unit Quantity Purchase Cost

Grocery Transaction Store Number Transaction Date Customer Product Purchase Quantity Amount

Customer Category Category Customer Category


Customer Customer First Name Last Name Address 1 Address 2 Address 3 City State Country Postal Code Customer Category

Product Product Description Category Product Line

Product Line Product Line ID Description

Product Category Product Category Description

9

Postup návrhu modelu

1.

Výběr sledovaných procesů

2.

Určení granularity

3.

Určení dimenzí

4.

Určení metrik

5.

Definice získávání dat (ETL)


10

Výběr sledovaných procesů

o Seznam procesů, které chceme analyzovat – Od jednodušších ke složitějším

o Bus matrix – Matice: Business procesy x Dimenze

o Často odpovídá jeden business proces ≈ jeden datamart


11

× Service Orders × Trouble Reports × Yellow Page Ads × Customer Inquiries × Promotion × Billing Call Detail × Network Call Detail × Customer Inventory × Network Inventory × Real eastate × Labor & Payroll × Computer Charges × Purchase Orders × Supplier Deliverables © 2014 Profinit. All rights reserved. × Custommer Billing

× × × × × × × × ×

×

× × × × × × × × ×

×

×

× × × × × ×

× × × × × × × ×

×

× × × × × ×

× × ×

× ×

× × × × ×

×

× × × × × × × × × × × × × ×

× × × × ×

× × × × × ×

× × × × × × × × × × × × × × ×

× ×

×

×

× × ×

× × × × × ×

× × × × ×

× × × × ×

× × ×

× × ×

× × ×

× ×

Account status

Weather

Item Shipped

Supplier

Equipment Type

Location

Employee

Internal Organization

Long Dist Provider

Called Party

Calling Party

Local Svc Provider

Rate Category

Service

Customer

Date

Bus matrix

× × × × × × × × ×

12

Buss matrix


13

Bus architektura a schéma - příklad


14

Určení granularity o Všechny řádky musí mít stejnou granularitu o Granularita malá – Jeden řádek ≈ jedno měření – Velký objem dat

o Granularita velká – Malé databáze

– Omezená možnost analýz

o Hodnoty odpovídají průniku všech dimenzí o Někdy potřeba realokace dat na několik řádek

o Řádky s hodnotou nula se nazapisují


15

Fact tables o Transaction - co řádek to transakce (například obchody) – Proces může obsahovat více typů transakcí, rozhodnutí zda jedna nebo více tabulek není jednoduché

o Snapshots - každý den se udělá celý snímek – State model – celé denní snímky – Event model – každý den pouze změněné záznamy – Možnost dopočítání dalších hodnot ke každému snímku

o Akumulujíce se shapshoty (sklad) – Id výrobku jako primární klíč a doplňují/updatují se hodnoty pro události popisující životní cyklus – Do daného řádku se doplní datum expedice, fakturace, dodání, vyúčtování, …

– Pozor - update v tabulce faktů

o (Fact tables bez faktů – slouží jako n:n vazba mezi dimenzemi)


16

Fact tables o Fakta – aditivní - počet, cena v transakčních fact tabulkách • Význam pro všaechny dimenze • Nejlépe se s nimi pracuje • Cílem je převést na aditivní fakta maximum •

Discount -> ceníková cena, prodejní cena

– semiaditivní - počet cena v snapshot tabulkách • součet za produkty má význam, za čas nemá význam • Obecně význam pouze pro některé dimenze

– nonadditive - procentuální profit • Často text • Někdy možné přenést do dimenzí (degenerované dimenze)

o Factless fact table – pouze cizí klíče, žádná fakta – Příznak existence (účast v kampani)


17

Určení dimenzí o Konformní dimenze – Jedna nejpodrobnější dimenze, ostatní jsou jejich agregací – Jednotné dimenze pro všechny business procesy

o Struktura – Jeden sloupec primárního klíče – Hodně sloupců popisů, často přes 30, čím více tím lépe – Atributy spíše textové (srozumitelnost) – Hierarchie pro analýzy

o Časová dimenze o Degenerovaná dimenze – nemá popis (číslo faktury)

o Dimenze jsou denormalizované (jedna široká tabulka) – Normalizace – vločkové schéma

o Řádek s hodnotu „Not applicable“, „Uknown“ © 2014 Profinit. All rights reserved.

18

Časová dimenze o V každém datovém skladu o Často mnoho hierarchií – Provozní rok – Fiskální rok – Kalendářní rok

o Mnoho sloupců – Textová informace – Číselná informace – Konce a začátky období – …


19

Časová dimenze


20

Dimensions o Schéma a instance dimenze lokace

o Použití srozumitelných dat, texty o Často odvozeno z jiných zdrojů (i externích) o Redundance dat je pouze v dimenzích (nikoliv ve faktových tabulkách) o Umožňuje vybírat a agregovat data po úrovních

o Hierarchie by měli mít konstatní hloubku – (nedoplňovat regiony jenom někde)

o Hierarchie jsou obsaženy v metadatech o dimenzích


21

Hierarchie o Popis jak agregovat hodnoty jedné dimenze o Může existovat několik nezávislých hierarchií na jedné dimenzi o Drill-down podle dimenzí

o Dimenze času o Nejmenší granularita – den o 6 nezávislých dimenzí © 2014 Profinit. All rights reserved.

Typy dimenzí o Konformní – Pro celý podnik – Ostatní dimenze jako podimenze konformních dimenzí

o Minidimenze a sběrné dimenze – Číselníky – Stavové a textové atributy – Možné sloučit do sběrných dimenzí

o Degenerované dimenze – Přímo v tabulce faktů (číslo objednávky)


23

Typy dimenzí z pohledu změn o Statické – Žádné ošetření změn – V případě změny se přepíše starý záznam – Žádná historie

o Rostoucí dimenze – Přidávají se nové záznamy – V případě změny se přepíše starý záznam – Žádná historie

o Rychle rostoucí diimenze – Nutné speciální řešení – Oddělení rychle se měnících atributů do vlastní dimenze

– (Jako Slowly changed dimension Type 2)

o Slowly changing dimenze


24

Slowly changing dimension o Typ 1 – přepis hodnot – Žádná historie

o Typ 2 – přidávání řádků, vždy jeden platný – Přidané pole“ Begin date, End date, Eff date key, Change reason text, Current flag – Kompletní historie

o Typ 3 – alternativní realita vice možností v jeden čas – Přidání nových záznamů uchování současné a předchozí hodnoty v případě změny

o Redundance nebývá problém – Dimenze zabírají cca 5% místa v DWH


25

Dimensionální model ve zkratce

o Fact tables – fakta (metriky) a cizí klíče z dimenzí

o Dimension tables – jeden sloupec primárního klíče a mnoho popisných sloupců o Star schéma o Další speciální tabulky


26

Příklad o Fakta – Počet prodaných knih – Cena za prodané knihy

o Dimenze – Knihy – Obchody – Čas

o Hierarchie – Knihy • Kniha – typy knih – vše

– Čas • Den – kalendářní měsíc – kalendářní kvartál – kalendářní rok – celá historie

– Obchody • Obchod – hierarchická struktura podle ústředí – vše


27

Další témata o Údržba modelu o Přidělování klíčů

o Vazba mezi identifikací ve vstupních datech a primárními klíči o Datová kvalita o Agregace o Vstupní data – snapshots nebo events


28

OLAP technologie o Uložení a zpracování dat podporující určité druhy analýz – parameterized static reporting – slicing and dicing with drill down – ‘what if?’ analysis – goal seeking models

o Způsob uložení předpočítaných hodnot (denormalizace) – Uložení agregovaných hodnot vyžadovaných analýzami podle zadaných • Metrik • Dimenzí • Hierarchií na dimenzích


29

OLAP technologie o Uložení a zpracování dat podporující určité druhy analýz – parameterized static reporting – slicing and dicing with drill down – ‘what if?’ analysis – goal seeking models

o Způsob uložení předpočítaných hodnot (denormalizace) – Uložení agregovaných hodnot vyžadovaných analýzami podle zadaných • Metrik • Dimenzí • Hierarchií na dimenzích


Multidimenzionální databáze o Nutné rozlišit – Princip • Práce s dimenzemi • Práce s hierarchiemi

– Skutečný způsob uložení dat • Relační model • Speciální úložiště se speciálními indexy

o Kategorizace dle místa uložení dat a agregací – MOLAP– veškerá data uložená v multidimenzionální databázi – ROLAP – veškerá data uložená v relační – HOLAP – hybrid

o Další typy – RTOLAP – real time, data pouze v RAM – DOLAP – desktop OLAP, data uložená na klientském počítači


31

Příklady dodavatelů OLAP serverů o MS Analysis Services o IBM Cognos

o Oracle OLAP option o Hyperion Essbase o Business Objects o MicroStrategy o SAS


32

Co si zapamatovat o K čemu slouží dimenzionální datové modely o Jaké jsou hlavní rozdíly relačního a dimenzionálního modelování

o Jaké jsou rozdíly mezi modely typu hvězda, souhvězdí, vločka nebo sněhová bouře o Jaký je doporučený postup při návrhu dimenzionálního modelu

o Co to je Buss Matrix, k čemu slouží o Jaké typy faktových tabulek se používají o Co to je aditivní, semiaditivní a neaditivní metrika o Jaké typy dimenzí se používají o Co to je "Slowly changing dimension of type 2" o Co to je OLAP databáze


33

Diskuse


Dimenzionální modelování Profinit. All rights reserved

Recommend Documents