Design databáze DW

Design databáze DW Ing. Jan Přichystal, Ph.D.

Úvod • Design databáze popisuje přepis logického modelu na fyzický • Implementace se v různých DB systémech liší • Design vychází a je dán možnostmi DB a dalších nástrojů • Design potvrzuje správný návrh ETL procesů a obráceně

Definice standardů Při návrhu DW je nutné definovat množinu standardů pro jednotlivé části To umožní jednotlivým koncovým uživatelům orientovat se ve struktuře DW Lze vycházet z již definovaných, ty ovšem mohou respektovat odlišnou filozofii (OLTP)

Konvence názvů Tabulky a atributy by měly mít shodné pojmenování jako v logickém modelu Názvy by měly být konzistentní, popisné a orientované na koncového uživatele Vhodné např.: [Customer Name] Pozor na problémy s mezerami, velikostí písmen a zvyky vývojářů

Použití NULL V dimenzionálních tabulkách je vhodné se vyhnout NULL hodnotám Zbytečně matou koncové uživatele Vhodnější je např.: „N/A“ nebo „Unknown“ Problém může nastat v dim. Datum Lze využít speciální hodnotu přes klíč Namapovat NULL na nepoužívané datum

STAGE tabulky ETL proces obvykle vyžaduje použití STAGE tabulek. Jejich množství závisí i na typu DB systémů, ze kterých získáváme data STAGE tab. je vhodné umístit do zvláštní instance databáze Usnadní to správu i zvýší flexibilitu při případném přesunu na jiný server

Umístění souborů Je třeba definovat umístění souborů zdrojových kódů, skriptů a DB souborů Vhodné je využívat systém správy verzí a týmové spolupráce DB soubory by měly být umístěny na zvláštní diskové oddíly Logové soubory je vhodné umístit také zvlášť

Použití synonym a pohledů Zjednodušují přístup k datům i správu Synonyma jsou dalším pojmenováním tabulek i atributů View se z pohledu koncového uživatele chová jako běžná tabulka Lze ho využít i pro odfiltrování nepotřebných, provozních atributů Umožňuje i předpočítávání hodnot Lze jím denormalizovat schéma vločky pro koncové uživatele Není vhodné pro přímý přístup k zdroj. datům

Primární klíče Je vhodné definovat politiku tvorby PK Poměrně snadné je to u dim. tabulek: Oracle – SEQUENCE, MSSQL – IDENTITY Datový typ použít vhodný pro joinování, záleží na DB, někdy se hodí i malý CHAR U faktových tabulek je složený z FK, může být i sekvence, záleží to na situaci:

Primární klíče Interní pravidla organizace umožňují více záznamů v FT se stejným ID Některé techniky updatování záznamů ve FT to vyžadují Sekvenční hodnota umožní odhalit, kde nastal problém v ETL Snadnější debugování – „podívej se na záznam 11535“ M:N vazby mezi faktovými tabulkami

Cizí klíče Otázkou je jestli řešit provázanost pomocí FK Výhodou je referenční integrita Nevýhodou nižší výkon Největší nebezpečí vyplývá z vymazání záznamu v dim tabulce, což není obvyklé Pokud DB systém podporuje optimalizace založené na CONSTRAINTS, použijte je

Fyzický datový model Vychází z logického datového modelu Doplňuje např. STAGE, provozní tabulky, atd. Liší se podle typu DB systému Je potřeba ověřit: jména tabulek a atributů korespondují s firemní politikou datové typy korespondují se zdrojovými daty Doplňuje provozní sloupce Použijte modelovací nástroj (konzistence, dokumentace, …)

Hvězda nebo vločka Logický datový model je obvykle hvězda – intuitivnější, vyšší výkon Některé BI nástroje preferují strukturu vločka, pak je vhodné dodržet logické přirozené členění Koncoví uživatelé mohou používat VIEW

Odhad velikosti DW Roli hrají především faktové tabulky a indexy Odhad velikosti zahrnuje: velikosti jednoho záznamu na základě datových typů, cca. 100B započítaní i historických dat indexy odhadneme až následně započítání prostoru pro dočasné tabulky a logy Započítání velikosti analytické krychle, cca. 40 až 300% vstupních dat Celkově DW zahrnuje asi 3x větší objem než vstupní data

Provozní tabulky Určeny pro běh systému: STAGE – určeny pro uchovávání denního loadu i pro jednotlivé postupné fáze ETL AUDIT – audit provedených operací ERROR – zaznamenání chyb Monitorovací tabulky – sledují přístup uživatelů Security – omezení dotazů na určité záznamy

Audit tabulky • Vytvořeny pro každou fact tabulku jako speciální dimenze • Obsahuje metadata o okamžiku, kdy vzniká každý nový záznam • Data prezentována formou reportu informujícího o průběhu plnění DW

Error tabulky • Cílem je zachytit všechny chyby, především v ETL • Středem je fact tabulka s granularitou na chyby • Dimenze tvoří čas (id pro sumarizaci všech chyb), dávka (proces spuštění) a chyba (popis chyby) • Může být propojena na Error detail tabulku

Indexy Závisí na konkrétním DB systému Typy a použití: b-tree – stromová struktura, sloupce s vysokou kardinalitou (order_number, customer_key, …) clustered – sloučený, jeden na tabulku, vysoká kardinalita, data jsou fyzicky seřazena bitmap indexy – nízká kardinalita, denormalizované tabulky, typicky pro flagy a yes/no hodnoty Pokud indexace zdrží ETL o více než 20% vyplatí se indexy zrušit a znovu vytvořit

Indexace dimenzí Primární klíč sestává z jednoho sloupce Na něj se vytváří index Pokud jsou podporovány bitmap indexy, používají se na sloupce, podle kterých se často filtruje B-tree indexy se používají na dimenzionální atributy Indexujte sloupce pro joiny, filtry i seskupování

Indexace fakt tabulek Především b-tree nebo clustered index na primární klíč Klíč časové dimenze by měl být první, protože se podle něj často dotazuje Indexují se i jednotlivé FK do dimenzí Fakta se nevyplatí indexovat, jen v případě, že jsou častou součástí dotazu: „plat > 20 000“

Testování kvality dat • Nevyplácí se podceňovat • Spočívá ve spuštění dotazu pracujícím se zdrojovými daty, který kopíruje logiku DW • Získané výsledky se srovnávají s těmi v DW • Srovnávají se SUM a COUNT hodnoty • Neexistuje testovací nástroj

Další testy • ETL – testování, zda ETL se spouští kdy má, probíhá předem odhadnutý čas, jednotlivé fáze jsou včas, množství souhlasí • Výkonostní – hardware, OS, DB systém, ladění výkonu (indexy) • Použitelnost – spolupráce s uživateli

Děkuji za pozornostDotazy?

Design databáze DW