dimensional data model data warehouse tutorial with examples
Tento výukový program vysvětluje výhody a mýty dimenzionálního datového modelu v datovém skladu. Další informace o tabulkách dimenzí a tabulkách faktů s příklady:
Testování datového skladu bylo vysvětleno v našem předchozím tutoriálu, v tomto Série školení pro datový sklad pro všechny .
Obrovská data jsou organizována v Data Warehouse (DW) s technikami dimenzionálního datového modelování. Tyto techniky dimenzionálního datového modelování usnadňují koncovým uživatelům dotaz na obchodní data. Tento výukový program vysvětluje vše o dimenzionálních datových modelech v DW.
Cílová skupina
- Vývojáři a testeři datového skladu / ETL.
- Databázoví profesionálové se základními znalostmi databázových konceptů.
- Správci databází / odborníci na velká data, kteří chtějí porozumět konceptům datového skladu / ETL.
- Absolventi / osvěžovači vysokých škol, kteří hledají práci v datovém skladu.
Co se naučíte:
Dimenzionální datové modely
Dimenzionální datové modely jsou datové struktury, které mají koncoví uživatelé v toku ETL k dispozici k dotazování a analýze dat. Proces ETL končí načtením dat do cílových dimenzionálních datových modelů. Každý dimenzionální datový model je sestaven z tabulky faktů obklopené několika tabulkami dimenzí.
Kroky, které je třeba dodržet při návrhu dimenzionálního datového modelu:
Výhody modelování dimenzionálních dat
Níže jsou uvedeny různé výhody dimenzionálního datového modelování.
- Jsou zabezpečeni k používání neustále se měnících prostředí DW.
- Obrovská data lze snadno vytvořit pomocí dimenzionálních datových modelů.
- Data z dimenzionálních datových modelů jsou snadno srozumitelná a analyzovatelná.
- Jsou rychle přístupní koncovým uživatelům pro dotazování s vysokým výkonem.
- Dimenzionální datové modely nám umožňují hierarchicky procházet (nebo) srolovat data.
ER modelování vs. dimenzionální datové modelování
- Modelování ER je vhodné pro operační systémy, zatímco rozměrové modelování je vhodné pro datový sklad.
- Modelování ER udržuje podrobné aktuální transakční údaje, zatímco dimenzionální modelování udržuje souhrn aktuálních i historických transakčních dat.
- ER modelování má normalizovaná data, zatímco dimenzionální modelování má normalizovaná data.
- ER modelování používá více spojení během načítání dotazu, zatímco dimenzionální modelování používá menší počet spojení, proto je výkon dotazu v dimenzionálním modelování rychlejší.
Mýty o dimenzionálním datovém modelování
Níže uvádíme některé ze stávajících mýtů o modelování dimenzionálních dat.
- Dimenzionální datové modely se používají pouze k reprezentaci souhrnu dat.
- Jsou v organizaci specifické pro dané oddělení.
- Nepodporují škálovatelnost.
- Jsou navrženy tak, aby sloužily účelu zpráv a dotazů koncových uživatelů.
- Dimenzionální datové modely nemůžeme integrovat.
Rozměrové tabulky
Tabulky dimenzí hrají v systému DW klíčovou roli tím, že ukládají všechny analyzované metrické hodnoty. Tyto hodnoty jsou uloženy pod snadno volitelnými dimenzionálními atributy (sloupci) v tabulce. Kvalita systému DW většinou závisí na hloubce atributů dimenze.
Proto bychom se měli pokusit poskytnout mnoho atributů spolu s jejich příslušnými hodnotami v tabulkách dimenzí.
Prozkoumejme strukturu kótovacích tabulek !!
# 1) Klíč tabulky dimenzí: Každá tabulka dimenzí bude mít jeden ze svých atributů dimenzí jako primární klíč k jedinečné identifikaci každého řádku. Z tohoto důvodu mohou odlišné číselné hodnoty tohoto atributu fungovat jako primární klíče.
Pokud hodnoty atributů nejsou v žádném případě jedinečné, můžete za primární klíče považovat sekvenčně generovaná čísla systémů. Ty se také nazývají jako náhradní klíče.
Dimenzionální datové modely musí mít omezení referenční integrity pro každý klíč mezi dimenzemi a fakty. Tabulky faktů tedy budou mít odkaz na cizí klíč pro každý primární / náhradní klíč v tabulce dimenzí, aby byla zachována referenční integrita.
Pokud se to nepodaří, nelze pro danou dimenzi klíč načíst příslušná data tabulky faktů.
# 2) Tabulka je široká: Můžeme říci, že tabulky dimenzí jsou široké, protože do tabulky dimenzí můžeme v libovolném bodě cyklu DW přidat libovolný počet atributů. DW architekt požádá tým ETL o přidání příslušných nových atributů do schématu.
Ve scénářích v reálném čase můžete vidět tabulky dimenzí s 50 (nebo) více atributy.
# 3) Textové atributy: Dimenzionální atributy mohou být jakéhokoli typu, nejlépe textové (nebo) číselné. Textové atributy budou mít spíše skutečná obchodní slova než kódy. Dimenzionální tabulky nejsou určeny pro výpočty, proto se číselné hodnoty pro dimenzionální atributy používají jen zřídka.
# 4) Atributy nemusí přímo souviset: Všechny atributy v tabulce dimenzí nemusí navzájem souviset.
# 5) Není normalizováno: Normalizace tabulky dimenzí přináší do obrazu více zprostředkujících tabulek, což není efektivní. Tabulky dimenzí tedy nejsou normalizovány.
Dimenzionální atributy mohou fungovat jako zdroj omezení v dotazech a lze je také zobrazit jako popisky v sestavách. Dotazy budou fungovat efektivně, pokud přímo vyberete atribut z tabulky dimenzí a odkazujete přímo na příslušnou tabulku faktů, aniž byste se dotkli jiných zprostředkujících tabulek.
# 6) Vrtání a vyhrnutí: Atributy dimenze mají schopnost procházet (nebo) srolovat data, kdykoli je to potřeba.
# 7) Více hierarchií: Tabulka jedné dimenze s více hierarchiemi je velmi běžná. Tabulka dimenzí bude mít jednoduchou hierarchii, pokud existuje pouze jedna cesta od spodní úrovně nahoru. Podobně bude mít více hierarchií, pokud existuje více cest k dosažení od spodní úrovně nahoru.
# 8) Několik záznamů: Dimenzionální tabulky budou mít menší počet záznamů (ve stovkách) než tabulky faktů (v milionech). I když jsou menší než fakta, poskytují všechny vstupy do tabulek faktů.
Zde je příklad tabulky dimenzí zákazníků:
Pochopením výše uvedených konceptů můžete rozhodnout, zda datové pole může fungovat jako atribut dimenze (nebo) nikoli při extrahování dat ze samotného zdroje.
Základní plán zatížení pro dimenzi
Dimenze lze vytvořit dvěma způsoby, tj. Extrahováním dat dimenze z externích zdrojových systémů (nebo) Systém ETL může vytvořit dimenze z fázování bez zapojení jakýchkoli externích zdrojů. Systém ETL bez jakéhokoli externího zpracování je však vhodnější k vytváření kótovacích tabulek.
Níže jsou uvedeny kroky tohoto procesu:
jak přidat maven v zatmění
- Čištění dat: Data se před načtením do tabulky dimenzí vyčistí, ověří a použijí se obchodní pravidla, aby se zachovala konzistence.
- Shoda s údaji: Data z jiných částí datového skladu by měla být správně agregována jako jedna hodnota s ohledem na každé pole tabulky dimenzí.
- Sdílejte stejné domény: Jakmile jsou data potvrzena, jsou znovu uložena do fázovacích tabulek.
- Dodání dat: Nakonec se načtou všechny hodnoty dimenzionálních atributů s přiřazenými primárními / náhradními klíči.
Typy rozměrů
Níže jsou uvedeny různé typy dimenzí pro vaši potřebu.
Začněme!!
# 1) Malé rozměry
Malé rozměry v datovém skladu fungují jako vyhledávací tabulky s menším počtem řádků a sloupců. Data do malých rozměrů lze snadno načíst z tabulek. V případě potřeby lze malé rozměry kombinovat jako super dimenzi.
# 2) Shoda dimenze
Konformovaná dimenze je dimenze, na kterou lze odkazovat stejným způsobem s každou tabulkou faktů, s níž souvisí.
Dimenze data je nejlepším příkladem přizpůsobené dimenze, protože atributy dimenze data, jako je rok, měsíc, týden, dny atd., Komunikují stejná data stejným způsobem napříč libovolným počtem faktů.
Příklad přizpůsobené dimenze.
# 3) Junk Dimension
Několik atributů v tabulce faktů, jako jsou příznaky a indikátory, lze přesunout do samostatné tabulky dimenzí nevyžádané pošty. Tyto atributy také nepatří k žádným jiným existujícím tabulkám dimenzí. Obecně jsou hodnoty těchto atributů jednoduše „ano / ne“ (nebo) „pravda / nepravda“.
Vytvoření nové dimenze pro každý jednotlivý atribut příznaku je složitější vytvořením většího počtu cizích klíčů do tabulky faktů. Současné zachování všech těchto příznaků a informací o indikátorech v tabulkách faktů také zvyšuje množství dat uložených ve faktech, čímž se zhoršuje výkon.
Nejlepším řešením je proto vytvoření jediné nevyžádané dimenze, protože nevyžádaná dimenze je schopná pojmout libovolný počet indikátorů „ano / ne“ nebo „pravda / nepravda“. Nevyžádané dimenze však ukládají popisné hodnoty pro tyto indikátory (ano / ne (nebo) pravda / nepravda), například aktivní a čekající atd.
Na základě složitosti tabulky faktů a jejích indikátorů může mít tabulka faktů jednu nebo více nevyžádaných dimenzí.
Příklad Junk Dimension.
# 4) Dimenze hraní rolí
Jedna dimenze, kterou lze v tabulce faktů označit pro více účelů, se nazývá dimenze hraní rolí.
Nejlepším příkladem pro dimenzi hraní rolí je opět tabulka dimenzí data, protože stejný atribut data v dimenzi lze použít pro různé účely ve skutečnosti, jako je datum objednávky, datum dodání, datum transakce, datum zrušení, atd.
V případě potřeby můžete vytvořit čtyři různé pohledy na tabulku dimenzí data s ohledem na čtyři různé atributy data tabulky faktů.
Příklad dimenze hraní rolí.
# 5) Degenerujte rozměry
Může existovat několik atributů, které mohou být dimenze (metriky) ani fakta (míry), ale je třeba je analyzovat. Všechny tyto atributy lze přesunout do degenerovaných dimenzí.
Například, číslo objednávky, číslo faktury atd. můžete považovat za zdegenerované atributy dimenze.
Příklad degenerované dimenze.
# 6) Pomalu se měnící rozměry
Pomalu se měnící dimenze je druh, kdy se data mohou měnit pomalu kdykoli, spíše než v pravidelných pravidelných intervalech. S upravenými daty v tabulkách dimenzí lze zacházet různými způsoby, jak je vysvětleno níže.
Můžete vybrat typ SCD, který bude reagovat na změnu jednotlivě pro každý atribut v dimenzionální tabulce.
i) SCD typu 1
- V typu 1, když dojde ke změně hodnot dimenzionálních atributů, jsou stávající hodnoty přepsány nově upravenými hodnotami, což není nic jiného než aktualizace.
- Stará data se neudržují pro historickou referenci.
- Předchozí zprávy nelze obnovit, protože neexistují stará data.
- Snadná údržba.
- Dopad na tabulky faktů je větší.
Příklad SCD typu 1:
(II) SCD typu 2
- V typu 2, když dojde ke změně hodnot dimenzionálních atributů, bude vložen nový řádek s upravenými hodnotami beze změny dat starého řádku.
- Pokud v některé z tabulek faktů existuje nějaký odkaz na cizí klíč, který existuje ke starému záznamu, pak se starý náhradní klíč automaticky aktualizuje s novým náhradním klíčem.
- Dopad na změny tabulky faktů je s výše uvedeným krokem mnohem menší.
- Stará data se po změnách nikde neuvažují.
- V typu 2 můžeme sledovat všechny změny, ke kterým dochází u dimenzionálních atributů.
- Ukládání historických dat není nijak omezeno.
- V typu 2 je přidání několika atributů do každého řádku, jako je datum změny, datum účinnosti, datum ukončení, důvod změny a aktuální příznak volitelný. To je však významné, pokud chce podnik znát počet změn provedených během určitého časového období.
Příklad SCD typu 2:
(III) Typ 3 SCD
- V typu 3, když dojde ke změně hodnot dimenzionálních atributů, jsou aktualizovány nové hodnoty, ale staré hodnoty stále zůstávají v platnosti jako druhá možnost.
- Místo přidání nového řádku pro každou změnu bude přidán nový sloupec, pokud dříve neexistuje.
- Staré hodnoty jsou umístěny do výše přidaných atributů a data primárního atributu jsou přepsána změněnou hodnotou jako u typu 1.
- Ukládání historických dat je omezené.
- Dopad na tabulky faktů je větší.
Příklad SCD typu 3:
(iv) SCD typu 4
- U typu 4 jsou aktuální data uložena v jedné tabulce.
- Všechna historická data jsou uchovávána v jiné tabulce.
Příklad SCD typu 4:
(v) Typ 6 SCD
- Dimenzionální tabulka může mít také kombinaci všech tří typů SCD 1, 2 a 3, která je známá jako pomalu se měnící dimenze typu 6 (nebo) hybridní.
Tabulky faktů
Tabulky faktů ukládají množinu kvantitativně naměřených hodnot, které se používají pro výpočty. Hodnoty tabulky faktů se zobrazí v obchodních zprávách. Na rozdíl od textových datových typů dimenzionálních tabulek je datový typ tabulek faktů výrazně číselný.
Tabulky faktů jsou hluboké, zatímco tabulky dimenzí jsou široké, protože tabulky faktů budou mít vyšší počet řádků a menší počet sloupců. Primárním klíčem definovaným v tabulce faktů je především identifikace každého řádku zvlášť. Primární klíč se také nazývá tabulka složeného klíče ve skutečnosti.
Pokud v tabulce faktů chybí složený klíč a pokud nějaké dva záznamy mají stejná data, je velmi těžké rozlišovat mezi daty a odkazovat na data v tabulkách dimenzí.
Pokud tedy existuje vlastní jedinečný klíč jako složený klíč, je dobré vygenerovat pořadové číslo pro každý záznam tabulky faktů. Další alternativou je vytvoření zřetězeného primárního klíče. To se vygeneruje zřetězením všech odkazovaných primárních klíčů dimenzionálních tabulek po řádcích.
Jedna tabulka faktů může být obklopena několika tabulkami dimenzí. Pomocí cizích klíčů, které existují v tabulkách faktů, lze v dimenzionálních tabulkách odkazovat na příslušný kontext (podrobná data) měřených hodnot. S pomocí dotazů budou uživatelé efektivně provádět rozbalení a shrnutí.
Nejnižší úroveň dat, která lze uložit do tabulky faktů, se nazývá Granularity. Počet tabulek dimenzí přidružených k tabulce faktů je nepřímo úměrný granularitě dat této tabulky faktů. tj. nejmenší naměřená hodnota vyžaduje odkaz na více dimenzionálních tabulek.
V dimenzionálním modelu udržují tabulky faktů relaci mnoho k mnoha s dimenzionálními tabulkami.
Příklad tabulky faktů o prodeji:
Plán zatížení pro tabulky faktů
Data tabulky faktů můžete načíst efektivně zvážením následujících ukazatelů:
# 1) Vyřazení a obnovení indexů
Indexy ve skutečnosti tabulky jsou dobré boostery výkonu při dotazování na data, ale demolici výkonu při načítání dat. Před načtením jakýchkoli obrovských dat do tabulek faktů proto primárně zrušte všechny indexy v této tabulce, načtěte data a obnovte indexy.
# 2) Oddělte přílohy od aktualizací
Při načítání do tabulky faktů neslučujte vložené a aktualizované záznamy. Pokud je počet aktualizací menší, zpracovávejte přílohy a aktualizace samostatně. Pokud je počet aktualizací větší, je vhodné zkrátit a znovu načíst tabulku faktů pro rychlé výsledky.
# 3) Rozdělení
Proveďte rozdělení fyzicky na tabulce faktů do mini tabulek pro lepší výkon dotazů na data tabulky hromadných faktů. S výjimkou DBA a týmu ETL nikdo nebude vědět o oddílech na faktech.
Jako příklad , můžete rozdělit tabulku podle měsíce, čtvrtletí, podle roku atd. Při dotazování se místo skenování celé tabulky berou v úvahu pouze rozdělená data.
# 4) Načíst paralelně
jaký je nejlepší software pro kopírování DVD
Nyní jsme získali představu o oddílech na tabulkách faktů. Oddíly na faktech jsou také užitečné při načítání obrovských dat do faktů. Chcete-li to provést, nejprve logicky rozdělte data do různých datových souborů a spusťte úlohy ETL a načtěte všechny tyto logické části dat paralelně.
# 5) Nástroj pro hromadné načtení
Na rozdíl od jiných systémů RDBMS nemusí systém ETL explicitně udržovat protokoly o vrácení pro selhání uprostřed transakce. Zde dochází k „hromadnému načtení“ do faktů místo k „vložení SQL“ k načtení obrovských dat. Pokud v případě, že jedno načtení selže, pak lze všechna data snadno znovu načíst (nebo), může pokračovat, odkud je u hromadného načtení přerušeno.
# 6) Odstranění záznamu faktu
K vymazání záznamu tabulky faktů dojde pouze v případě, že podnik chce výslovně. Pokud existují nějaká data tabulky faktů, která již ve zdrojových systémech neexistují, mohou být příslušná data odstraněna buď fyzicky (nebo) logicky.
- Fyzické mazání: Nežádoucí záznamy jsou trvale odstraněny z tabulky faktů.
- Logické mazání: Do tabulky faktů bude přidán nový sloupec, například „odstraněn“ bitového (nebo) booleovského typu. Toto funguje jako příznak představující odstraněné záznamy. Musíte se ujistit, že při dotazování na data tabulky faktů nevyberete odstraněné záznamy.
# 7) Pořadí pro aktualizace a mazání v tabulce faktů
Pokud jsou k dispozici nějaká data, která se mají aktualizovat, měly by se nejprve aktualizovat tabulky dimenzí a poté podle potřeby aktualizovat náhradní klíče ve vyhledávací tabulce a poté se aktualizuje příslušná tabulka faktů. Odstranění proběhne obráceně, protože smazáním všech nežádoucích dat z tabulek faktů lze snadno odstranit propojená nevyžádaná data z dimenzionálních tabulek.
V obou případech bychom měli postupovat podle výše uvedené posloupnosti, protože tabulky dimenzí a tabulky faktů neustále udržují referenční integritu.
Druhy faktů
Na základě chování dat tabulek faktů jsou kategorizovány jako tabulky faktů transakcí, tabulky faktů snímků a akumulované tabulky faktů tabulek. Všechny tyto tři typy sledují různé funkce s různými strategiemi načítání dat.
# 1) Tabulky transakčních faktů
Jak název označuje tabulky faktů transakcí, ukládejte data na úrovni transakcí pro každou událost, která se stane. Takový druh dat je snadné analyzovat na samotné úrovni tabulky faktů. Ale pro další analýzu můžete také odkázat na související dimenze.
Například, každý prodej (nebo) nákup z marketingového webu by měl být načten do tabulky faktů o transakcích.
Níže je uveden příklad tabulky transakčních faktů.
# 2) Tabulky pravidelných snímků
Jak název naznačuje, data v tabulce pravidelných snímků se ukládají ve formě snímků (obrázků) v pravidelných intervalech, například pro každý den, týden, měsíc, čtvrtletí atd. V závislosti na obchodních potřebách.
Je tedy jasné, že se jedná o agregaci dat po celou dobu. Fakta o snímku jsou tedy ve srovnání s tabulkami faktů o transakcích složitější. Například, jakákoli data přehledů výnosů z výkonu mohou být uložena v tabulkách faktů pro snadnou orientaci.
Níže je uveden příklad tabulky faktů o periodickém snímku.
# 3) Shromažďování tabulek faktů
Akumulační tabulky faktů umožňují ukládat data do tabulek po celou dobu životnosti produktu. Funguje to jako kombinace výše uvedených dvou typů, kde lze data vložit jakoukoli událostí kdykoli jako snímek.
V tomto typu se aktualizují další sloupce data a data pro každý řádek s každým milníkem daného produktu.
Příklad tabulky faktů akumulačního snímku.
Kromě výše uvedených tří typů je zde několik dalších typů tabulek faktů:
# 4) Tabulky faktických faktů: Faktem je soubor opatření, zatímco fakt méně zachycuje pouze události (nebo) podmínky, které neobsahují žádná opatření. Tabulka faktů bez faktů se používá hlavně ke sledování systému. Údaje v těchto tabulkách lze analyzovat a použít pro vykazování.
Například, můžete hledat podrobnosti o zaměstnanci, který si vzal dovolenou, a typ dovolené za rok atd. Pokud zahrnete všechny tyto nejasné podrobnosti skutečnosti, tabulka určitě zvětší velikost faktů.
Níže je uveden příklad tabulky Fact Fact Fact Table.
# 5) Tabulky shody faktů: Konformní skutečnost je skutečnost, na kterou lze odkazovat stejným způsobem s každým datovým trhem, s nímž souvisí.
Specifikace tabulky faktů
Níže jsou uvedeny specifikace tabulky faktů.
- Název faktu: Jedná se o řetězec, který stručně popisuje funkce tabulky faktů.
- Obchodní proces: Tato tabulka faktů musí splňovat rozhovory o podnikání.
- Dotazy: Uvádí seznam obchodních otázek, na které odpoví tabulka faktů.
- Obilí: Označuje nejnižší úroveň podrobností spojených s údaji z tabulky faktů.
- Rozměry: Seznam všech tabulek dimenzí přidružených k této tabulce faktů.
- Opatření: Vypočítané hodnoty uložené v tabulce faktů.
- Frekvence zatížení Představuje časové intervaly pro načtení dat do tabulky faktů.
- Počáteční řádky: Poprvé se podívejte na počáteční data vyplněná v tabulce faktů.
Příklad modelování rozměrových dat
Můžete si udělat představu o tom, jak lze dimenzionální tabulky a tabulky faktů pro systém navrhnout, když se podíváte na níže uvedený diagram modelování dimenzionálních dat pro prodej a objednávky.
Závěr
Nyní byste měli získat vynikající znalosti o technikách dimenzionálního datového modelování, jejich výhodách, mýtech, dimenzionálních tabulkách, tabulkách faktů, spolu s jejich typy a procesy.
Podívejte se na náš nadcházející výukový program a dozvíte se více o schématech Data Warehouse !!
=> Chcete-li se dozvědět více o skladování dat od začátku, přejděte sem.
Doporučené čtení
- Výukový program pro testování datových skladů s příklady | Průvodce testováním ETL
- Příklady dolování dat: Nejběžnější aplikace dolování dat 2021
- Výukový program Python DateTime s příklady
- Základy datového skladu: Průvodce s příklady
- Výukový program pro testování hlasitosti: Příklady a nástroje pro testování hlasitosti
- Top 10 populárních nástrojů datového skladu a testovacích technologií
- Těžba dat: Proces, techniky a hlavní problémy v analýze dat
- Jak provádět testování řízené daty v SoapUI Pro - SoapUI Tutorial # 14