Návrh schématu datového skladu
Stejně jako všechny relační databáze obsahuje datový sklad tabulky, ve kterých jsou uložena data, která chcete analyzovat. Tyto tabulky jsou nejčastěji uspořádané do schématu, které je optimalizované pro multidimenzionální modelování, ve kterém se číselné míry přidružené k událostem známým jako fakta dají agregovat atributy přidružených entit ve více dimenzích. Například míry přidružené k prodejní objednávce (například zaplacená částka nebo množství objednaných položek) se dají agregovat podle atributů data, kdy došlo k prodeji, zákazníkovi, prodeji atd.
Tabulky v datovém skladu
Běžným vzorem pro relační datové sklady je definování schématu, které zahrnuje dva druhy tabulek: tabulky dimenzí a tabulky faktů .
Tabulky dimenzí
Tabulky dimenzí popisují obchodní entity, jako jsou produkty, lidé, místa a kalendářní data. Tabulky dimenzí obsahují sloupce pro atributy entity. Entita zákazníka může mít například křestní jméno, příjmení, e-mailovou adresu a poštovní adresu (která se může skládat z poštovní adresy, města, PSČ a země nebo oblasti). Kromě sloupců atributů tabulka dimenzí obsahuje jedinečný klíčový sloupec, který jednoznačně identifikuje každý řádek v tabulce. Ve skutečnosti je běžné, že tabulka dimenzí obsahuje dva klíčové sloupce:
- Náhradní klíč, který je specifický pro datový sklad, a jednoznačně identifikuje každý řádek tabulky dimenzí v datovém skladu – obvykle inkrementující celé číslo.
- Alternativní klíč, často přirozený nebo obchodní klíč, který slouží k identifikaci konkrétní instance entity v transakčním zdrojovém systému, ze kterého záznam entity pochází – například kód produktu nebo ID zákazníka.
Poznámka:
Proč máte dva klíče? Existuje několik dobrých důvodů:
- Datový sklad může být naplněn daty z více zdrojových systémů, což může vést k riziku duplicitních nebo nekompatibilních obchodních klíčů.
- Jednoduché číselné klíče obecně fungují lépe v dotazech, které spojují velké množství tabulek – běžný vzor v datových skladech.
- Atributy entit se můžou v průběhu času měnit – například zákazník může změnit svoji adresu. Vzhledem k tomu, že datový sklad se používá k podpoře historických sestav, můžete chtít zachovat záznam pro každou instanci entity v několika bodech v čase; aby se například prodejní objednávky pro konkrétního zákazníka počítaly pro město, ve kterém žil v době, kdy byla objednávka zadána. V tomto případě by několik záznamů zákazníků měl stejný obchodní klíč přidružený k zákazníkovi, ale různé náhradní klíče pro každou samostatnou adresu, ve které zákazník žil v různých časech.
Příklad tabulky dimenzí pro zákazníka může obsahovat následující data:
| CustomerKey | CustomerAltKey | Název | Ulice | City | PostalCode | CountryRegion | |
|---|---|---|---|---|---|---|---|
| 123 | I-543 | Navin Jones | navin1@contoso.com | 1 Hlavní st. | Seattle | 90000 | USA |
| 124 | R-589 | Mary Smith | mary2@contoso.com | 234 190th Ave | Buffalo | 50001 | USA |
| 125 | I-321 | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Paříž | 20098 | Francie |
| 126 | I-543 | Navin Jones | navin1@contoso.com | 24 125th Ave. | New York | 50000 | USA |
| ... | ... | ... | ... | ... | ... | ... | ... |
Poznámka:
Všimněte si, že tabulka obsahuje dva záznamy pro Navin Jones. Oba záznamy používají stejný alternativní klíč k identifikaci této osoby (I-543), ale každý záznam má jiný náhradní klíč. Z tohoto vyplývá, že zákazník přešel ze Seattlu do New Yorku. Prodeje provedené zákazníkem během života v Seattlu jsou spojené s klíčem 123, zatímco nákupy provedené po přechodu do New Yorku se zaznamenávají v záznamu 126.
Kromě tabulek dimenzí, které představují obchodní entity, je běžné, že datový sklad obsahuje tabulku dimenzí, která představuje čas. Tato tabulka umožňuje datovým analytikům agregovat data v časových intervalech. V závislosti na typu dat, která potřebujete analyzovat, může nejnižší členitost (označovaná jako agregace) časové dimenze představovat časy (na hodinu, sekundu, milisekundu, nanosekundu nebo ještě nižší) nebo data.
Příklad tabulky dimenzí času s agregačním intervalem na úrovni data může obsahovat následující data:
| DateKey | DateAltKey | DayOfWeek | DayOfMonth | Weekday | Month | NázevMěsíce | Čtvrtletí | Rok |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 0 | Pátek | 0 | Leden | 0 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 0 | Sobota | 0 | Leden | 0 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | Neděle | 0 | Leden | 0 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Úterý | 12 | Prosinec | 4 | 2030 |
Časový rozsah, na který se vztahují záznamy v tabulce, musí obsahovat nejstarší a nejnovější časové body pro všechny přidružené události zaznamenané v související tabulce faktů. Obvykle existuje záznam pro každý interval v příslušném intervalu mezi.
Tabulky faktů
Tabulky faktů ukládají podrobnosti o pozorováních nebo událostech; Například prodejní objednávky, zůstatky akcií, směnné kurzy nebo zaznamenané teploty. Tabulka faktů obsahuje sloupce pro číselné hodnoty, které lze agregovat podle dimenzí. Kromě číselných sloupců tabulka faktů obsahuje klíčové sloupce, které odkazují na jedinečné klíče v souvisejících tabulkách dimenzí.
Například tabulka faktů obsahující podrobnosti o prodejních objednávkách může obsahovat následující data:
| OrderDateKey | CustomerKey | StoreKey | ProductKey | OrderNo | LineItemNo | Množství | UnitPrice | Daň | ItemTotal |
|---|---|---|---|---|---|---|---|---|---|
| 20220101 | 123 | 5 | 701 | 1001 | 1 | 2 | 2,50 | 0,50 | 5.50 |
| 20220101 | 123 | 5 | 765 | 1001 | 2 | 0 | 2.00 | 0,20 | 2,20 |
| 20220102 | 125 | 2 | 723 | 1 002 | 1 | 1 | 4.99 | 0.49 | 5.48 |
| 20220103 | 126 | 0 | 823 | 1003 | 1 | 1 | 7.99 | 0.80 | 8.79 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Klíčové sloupce dimenzí tabulky faktů určují jeho agregační interval. Tabulka faktů prodejních objednávek například obsahuje klíče pro kalendářní data, zákazníky, obchody a produkty. Objednávka může obsahovat více produktů, takže agregační interval představuje řádkové položky pro jednotlivé produkty prodávané v obchodech zákazníkům v konkrétních dnech.
Návrhy schémat datového skladu
Ve většině transakčních databází, které se používají v obchodních aplikacích, se data normalizují , aby se snížila duplicita. V datovém skladu jsou však data dimenzí obecně denormalizována , aby se snížil počet spojení potřebných k dotazování dat.
Datový sklad je často uspořádaný jako hvězdicové schéma, ve kterém tabulka faktů přímo souvisí s tabulkami dimenzí, jak je znázorněno v tomto příkladu:
Atributy entity je možné použít k agregaci měr v tabulkách faktů v několika hierarchických úrovních – například k vyhledání celkových výnosů z prodeje podle země nebo oblasti, města, PSČ nebo jednotlivého zákazníka. Atributy pro každou úroveň mohou být uloženy ve stejné tabulce dimenzí. Pokud má ale entita velký počet hierarchických úrovní atributů nebo když je možné některé atributy sdílet několika dimenzemi (například zákazníci a obchody mají zeměpisnou adresu), může mít smysl použít určité normalizace u tabulek dimenzí a vytvořit schéma sněhové vločky , jak je znázorněno v následujícím příkladu:
V tomto případě byla tabulka DimProduct normalizována tak, aby vytvořila samostatné tabulky dimenzí pro kategorie produktů a dodavatele a byla přidána tabulka DimGeography, která představuje geografické atributy pro zákazníky i obchody. Každý řádek v tabulce DimProduct obsahuje klíčové hodnoty pro odpovídající řádky v tabulkách DimCategory a DimSupplier . Každý řádek v tabulkách DimCustomer a DimStore obsahuje hodnotu klíče pro odpovídající řádek v tabulce DimGeography .