Dimenzionální modelování v Microsoft Fabric Warehouse: Tabulky dimenzí
Platí pro:✅ Koncový bod sql Analytics a sklad v Microsoft Fabric
Poznámka:
Tento článek je součástí řady dimenzionálních modelů článků. Tato série se zaměřuje na pokyny a osvědčené postupy návrhu související s dimenzionálním modelováním ve službě Microsoft Fabric Warehouse.
Tento článek obsahuje pokyny a osvědčené postupy pro navrhování tabulek dimenzí v dimenzionálním modelu. Poskytuje praktické pokyny pro Warehouse v Microsoft Fabric, což je prostředí, které podporuje mnoho funkcí T-SQL, jako je vytváření tabulek a správa dat v tabulkách. Takže máte úplnou kontrolu nad vytvářením dimenzionálních tabulek modelu a jejich načítáním s daty.
Poznámka:
V tomto článku se termín datový sklad týká podnikového datového skladu, který poskytuje komplexní integraci důležitých dat v rámci organizace. Naproti tomu samostatný termínový sklad odkazuje na sklad Infrastruktury, což je nabídka relační databáze SaaS (software jako služba), kterou můžete použít k implementaci datového skladu. Pro přehlednost se v tomto článku uvádí jako Fabric Warehouse.
Tip
Pokud jste nezkušení s dimenzionálním modelováním, zvažte tuto řadu článků, které jste v prvním kroku. Účelem není poskytnout kompletní diskuzi o návrhu dimenzionálního modelování. Další informace najdete přímo na široce přijímaný publikovaný obsah, například The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3. vydání, 2013) od Ralph Kimball a dalších.
V dimenzionálním modelu tabulka dimenzí popisuje entitu, která je relevantní pro vaše obchodní a analytické požadavky. Tabulky dimenzí obecně představují věci , které modelujete. Můžou to být produkty, lidé, místa nebo jakýkoliv jiný koncept, včetně data a času. Chcete-li snadno identifikovat tabulky dimenzí, obvykle předponu jejich názvů pomocí d_
nebo Dim_
.
Struktura tabulky dimenzí
Chcete-li popsat strukturu tabulky dimenzí, zvažte následující příklad tabulky dimenzí prodejce s názvem d_Salesperson
. Tento příklad používá osvědčené postupy návrhu. Jednotlivé skupiny sloupců jsou popsány v následujících částech.
CREATE TABLE d_Salesperson
(
--Surrogate key
Salesperson_SK INT NOT NULL,
--Natural key(s)
EmployeeID VARCHAR(20) NOT NULL,
--Dimension attributes
FirstName VARCHAR(20) NOT NULL,
<…>
--Foreign key(s) to other dimensions
SalesRegion_FK INT NOT NULL,
<…>
--Historical tracking attributes (SCD type 2)
RecChangeDate_FK INT NOT NULL,
RecValidFromKey INT NOT NULL,
RecValidToKey INT NOT NULL,
RecReason VARCHAR(15) NOT NULL,
RecIsCurrent BIT NOT NULL,
--Audit attributes
AuditMissing BIT NOT NULL,
AuditIsInferred BIT NOT NULL,
AuditCreatedDate DATE NOT NULL,
AuditCreatedBy VARCHAR(15) NOT NULL,
AuditLastModifiedDate DATE NOT NULL,
AuditLastModifiedBy VARCHAR(15) NOT NULL
);
Náhradní klíč
Tabulka ukázkových dimenzí má náhradní klíč, který má název Salesperson_SK
. Náhradní klíč je jedinečný identifikátor s jedním sloupcem, který se vygeneruje a uloží v tabulce dimenzí. Jedná se o sloupec primárního klíče , který slouží k propojení s jinými tabulkami v dimenzionálním modelu.
Náhradní klíče se snaží izolovat datový sklad před změnami zdrojových dat. Poskytují také mnoho dalších výhod, které vám umožní:
- Sloučení více zdrojů dat (zabránění kolidování duplicitních identifikátorů)
- Sloučení přirozených klíčů s více sloupci do efektivnějšího klíče s jedním sloupcem
- Sledujte historii dimenzí pomocí pomalu se měnící dimenze typu 2.
- Omezte šířku tabulky faktů pro optimalizaci úložiště (výběrem nejmenšího možného datového typu integer).
Náhradní klíčový sloupec je doporučený postup, i když se přirozený klíč (popsaný dále) jeví jako přijatelný kandidát. Měli byste se také vyhnout významu hodnot klíčů (s výjimkou klíčů dimenzí data a času, jak je popsáno později).
Přirozené klíče
Tabulka ukázkových dimenzí má také přirozený klíč, který má název EmployeeID
. Přirozeným klíčem je klíč uložený ve zdrojovém systému. Umožňuje přiložit data dimenze ke svému zdrojovému systému, což obvykle provádí proces extrakce, načtení a transformace (ETL) k načtení tabulky dimenzí. Někdy se přirozenému klíči říká obchodní klíč a jeho hodnoty můžou být pro firemní uživatele smysluplné.
Někdy dimenze nemají přirozený klíč. To může být případ dimenze kalendářního data nebo vyhledávacích dimenzí nebo při generování dat dimenze normalizací plochého souboru.
Atributy dimenze
Tabulka ukázkových dimenzí má také atributy dimenzí, jako je FirstName
sloupec. Atributy dimenze poskytují kontext číselných dat uložených v souvisejících tabulkách faktů. Obvykle se jedná o textové sloupce, které se používají v analytických dotazech k filtrování a seskupování (řez a kostky), ale ne k agregaci. Některé tabulkydimenzíchm systémům obsahují několik atributů, zatímco jiné obsahují mnoho atributů (tolik, kolik je potřeba k podpoře požadavků na dotazy dimenzionálního modelu).
Tip
Dobrým způsobem, jak určit, které dimenze a atributy potřebujete, je najít správné lidi a klást správné otázky. Konkrétně buďte upozorňováni na zmínku o slově. Když například někdo řekne, že potřebuje analyzovat prodej podle prodejce, podle měsíce a podle kategorie produktů, říká vám, že potřebuje dimenze, které mají tyto atributy.
Pokud plánujete vytvořit sémantický model Direct Lake, měli byste zahrnout všechny možné sloupce potřebné pro filtrování a seskupení jako atributy dimenze. Je to proto, že sémantické modely Direct Lake nepodporují počítané sloupce.
Cizí klíče
Tabulka ukázkových dimenzí má také cizí klíč, který má název SalesRegion_FK
. Jiné tabulky dimenzí můžou odkazovat na cizí klíč a jejich přítomnost v tabulce dimenzí je zvláštní případ. Označuje, že tabulka souvisí s jinou tabulkou dimenzí, což znamená, že může být součástí dimenze sněhové vločky nebo souvisí s rozměrem outriggeru.
Fabric Warehouse podporuje omezení cizího klíče, ale nejde je vynutit. Proto je důležité, aby proces ETL testuje integritu mezi souvisejícími tabulkami při načtení dat.
Stále je vhodné vytvořit cizí klíče. Jedním z dobrých důvodů, proč vytvořit nevynucené cizí klíče, je umožnit nástrojům modelování, jako je Power BI Desktop, automaticky zjišťovat a vytvářet relace mezi tabulkami v sémantickém modelu.
Historické atributy sledování
Tabulka ukázkových dimenzí má také různé historické atributy sledování. Historické atributy sledování jsou volitelné na základě potřeby sledovat konkrétní změny, ke kterým dochází ve zdrojovém systému. Umožňují ukládání hodnot pro podporu primární role datového skladu, což je přesné popisování minulosti. Konkrétně tyto atributy ukládají historický kontext, protože proces ETL načítá nová nebo změněná data do dimenze.
Další informace naleznete v tématu Správa historických změn dále v tomto článku.
Atributy auditu
Tabulka ukázkových dimenzí má také různé atributy auditu. Atributy auditu jsou volitelné, ale doporučuje se. Umožňují sledovat, kdy a jak se záznamy dimenzí vytvořily nebo upravily, a mohou zahrnovat diagnostické informace nebo informace o řešení potíží vyvolané během procesů ETL. Budete například chtít sledovat, kdo (nebo jaký proces) aktualizoval řádek a kdy. Atributy auditu můžou také pomoct diagnostikovat náročný problém, například když se proces ETL neočekávaně zastaví. Mohou také označit členy dimenze jako chyby nebo odvozené členy.
Velikost tabulky dimenzí
Nejužitečnější a všestranné rozměry v dimenzionálním modelu jsou často velké a široké rozměry. Jsou velké z hlediska řádků (nad miliony) a široké z hlediska počtu atributů dimenzí (potenciálně stovky). Velikost není tak důležitá (i když byste měli navrhnout a optimalizovat nejmenší možnou velikost). Důležité je, že dimenze podporuje požadované filtrování, seskupování a přesnou historickou analýzu dat faktů.
Velké dimenze můžou být zdrojové z více zdrojových systémů. V tomto případě musí zpracování dimenzí kombinovat, sloučit, odstranění duplicitních dat a standardizovat data; a přiřaďte náhradní klíče.
Ve srovnání jsou některé dimenze malé. Můžou představovat vyhledávací tabulky, které obsahují pouze několik záznamů a atributů. Tyto malé dimenze často ukládají hodnoty kategorií související s transakcemi v tabulkách faktů a implementují se jako dimenze s náhradními klíči, které se týkají záznamů faktů.
Tip
Pokud máte mnoho malých dimenzí, zvažte jejich sloučení do nevyžádané dimenze.
Koncepty návrhu dimenzí
Tato část popisuje různé koncepty návrhu dimenzí.
Denormalizace vs. normalizace
Je to téměř vždy případ, kdy by se tabulky dimenzí měly denormalizovat. I když normalizace je termín používaný k popisu dat uložených způsobem, který repetitní data snižuje, denormalizace je termín, který slouží k definování, kde existují předpočítané redundantní data. Redundantní data existují obvykle kvůli úložišti hierarchií (probíraných později), což znamená, že hierarchie jsou zploštěny. Dimenze produktu může například ukládat podkategorie (a související atributy) a kategorii (a související atributy).
Vzhledem k tomu, že dimenze jsou obecně malé (ve srovnání s tabulkami faktů), náklady na ukládání redundantních dat téměř vždy převáží vyšší výkon a použitelnost dotazů.
Sněhové vločkové rozměry
Jednou z výjimek denormalizace je návrh dimenze sněhové vločky. Dimenze sněhové vločky se normalizuje a ukládá data dimenzí do několika souvisejících tabulek.
Následující diagram znázorňuje sněhovou vločkovou dimenzi, která se skládá ze tří souvisejících tabulek dimenzí: Product
, Subcategory
a Category
.
Zvažte implementaci dimenze sněhové vločky, pokud:
- Dimenze je extrémně velká a náklady na úložiště převáží nad potřebou vysokého výkonu dotazů. (Pravidelně však znovu vyhodnocuje, že tento případ stále zůstává.)
- Potřebujete klíče pro vztah dimenze k faktům vyšší úrovně. Například tabulka faktů prodeje ukládá řádky na úrovni produktu, ale tabulka faktů prodeje ukládá řádky na úrovni podkategorie.
- Je potřeba sledovat historické změny na vyšších úrovních členitosti.
Poznámka:
Mějte na paměti, že hierarchie v sémantickém modelu Power BI může být založená pouze na sloupcích z jedné tabulky sémantického modelu. Proto by dimenze sněhové vločky měla přinést denormalizovaný výsledek pomocí zobrazení, které spojuje sněhové vločkové tabulky dohromady.
Hierarchie
Sloupce dimenzí obvykle vytvářejí hierarchie. Hierarchie umožňují zkoumat data na různých úrovních souhrnů. Například počáteční zobrazení maticového vizuálu může zobrazovat roční prodeje a spotřebitel sestavy se může rozhodnout přejít k podrobnostem a zobrazit čtvrtletní a měsíční prodeje.
Existují tři způsoby, jak hierarchii uložit do dimenze. Můžete použít:
- Sloupce z jedné denormalizované dimenze
- Dimenze sněhové vločky, která se skládá z více souvisejících tabulek.
- Vztah nadřazený-podřízený (odkazující na sebe) v dimenzi.
Hierarchie mohou být vyváženy nebo nevyvážené. Je také důležité pochopit, že některé hierarchie jsou nepravidelné.
Vyvážené hierarchie
Vyvážené hierarchie jsou nejběžnějším typem hierarchie. Vyvážená hierarchie má stejný počet úrovní. Běžným příkladem vyvážené hierarchie je hierarchie kalendáře v dimenzi kalendářního data, která zahrnuje úrovně pro rok, čtvrtletí, měsíc a datum.
Následující diagram znázorňuje vyváženou hierarchii prodejních oblastí. Skládá se ze dvou úrovní, což jsou skupina prodejních oblastí a prodejní oblast.
Úrovně vyvážené hierarchie jsou založené buď na sloupcích z jedné, denormalizované dimenze, nebo z tabulek, které tvoří sněhovou vločkovou dimenzi. Pokud je založená na jedné denormalizované dimenzi, sloupce, které představují vyšší úrovně, obsahují redundantní data.
U vyvážených hierarchií se fakta vždy vztahují k jedné úrovni hierarchie, což je obvykle nejnižší úroveň. Tímto způsobem lze fakta agregovat (zahrnovat) na nejvyšší úroveň hierarchie. Fakta mohou souviset s libovolnou úrovní, která je určena odstupňovanou tabulkou faktů. Například tabulka faktů o prodeji může být uložena na úrovni data, zatímco tabulka faktů cíle prodeje může být uložena na úrovni čtvrtletí.
Nevyvážené hierarchie
Nevyvážené hierarchie jsou méně běžným typem hierarchie. Nevyvážená hierarchie má úrovně založené na vztahu nadřazeného a podřízeného objektu. Z tohoto důvodu je počet úrovní v nevyvážené hierarchii určen řádky dimenzí, nikoli konkrétní sloupce tabulky dimenzí.
Běžným příkladem nevyvážené hierarchie je hierarchie zaměstnanců, kde každý řádek v dimenzi zaměstnance souvisí s řádkem správce sestav ve stejné tabulce. V tomto případě může být jakýkoli zaměstnanec manažerem se zaměstnanci, kteří hlásí. Samozřejmě, že některé větve hierarchie budou mít více úrovní než jiné.
Následující diagram znázorňuje nevyváženou hierarchii. Skládá se ze čtyř úrovní a každý člen v hierarchii je prodejcem. Všimněte si, že prodejci mají v hierarchii jiný počet předků podle toho, komu se hlásí.
Mezi další běžné příklady nevyvážených hierarchií patří faktura materiálů, modely vlastnictví společnosti a hlavní kniha.
V případě nevyvážených hierarchií se fakta vždy vztahují k agregačnímu intervalu dimenze. Například fakta o prodeji se vztahují k různým prodejcům, kteří mají různé struktury vytváření sestav. Tabulka dimenzí by měla náhradní klíč (pojmenovanýSalesperson_SK
ReportsTo_Salesperson_FK
) a sloupec cizího klíče, který odkazuje na sloupec primárního klíče. Každý prodejce bez nutnosti spravovat nemusí nutně na nejnižší úrovni žádné větve hierarchie. Pokud nejsou na nejnižší úrovni, prodejce může prodávat produkty a hlásit prodejce, kteří také prodávají produkty. Souhrn dat faktů proto musí vzít v úvahu jednotlivého prodejce a všechny jejich potomky.
Dotazování hierarchií nadřazených a podřízených objektů může být složité a pomalé, zejména pro velké dimenze. I když zdrojový systém může ukládat vztahy jako nadřazený-podřízený, doporučujeme, abyste hierarchii naturalizovali . V tomto případě naturalizace znamená transformovat a ukládat úrovně hierarchie v dimenzi jako sloupce.
Tip
Pokud se rozhodnete hierarchii nepřirozenit, můžete v sémantickém modelu Power BI vytvořit hierarchii založenou na vztahu nadřazeného a podřízeného objektu. Tento přístup se ale nedoporučuje pro velké rozměry. Další informace naleznete v tématu Principy funkcí pro hierarchie nadřazený-podřízený v jazyce DAX.
Nepravidelné hierarchie
Někdy je hierarchie nepravidelná , protože nadřazený prvek člena v hierarchii existuje na úrovni, která není bezprostředně nad ní. V těchto případech chybějící hodnoty úrovně opakují hodnotu nadřazeného objektu.
Představte si příklad vyvážené geografické hierarchie. Nepravidelná hierarchie existuje, když země/oblast nemá žádné státy nebo provincie. Například Nový Zéland nemá žádné státy ani provincie. Pokud tedy vložíte řádek Nový Zéland, měli byste do sloupce uložit také hodnotu StateProvince
země/oblasti.
Následující diagram znázorňuje nepravidelnou hierarchii geografických oblastí.
Správa historických změn
V případě potřeby je možné historickou změnu spravovat implementací pomalu se měnící dimenze (SCD). ScD udržuje historický kontext jako nová nebo změněná data, která se do něj načtou.
Tady jsou nejběžnější typy SCD.
- Typ 1: Přepište existující člen dimenze.
- Typ 2: Vložte nového člena dimenze s časovou verzí.
- Typ 3: Sledování omezené historie pomocí atributů
Je možné, že dimenze může podporovat změny typu SCD 1 i SCD typu 2.
Typ SCD 3 se často nepoužívá, protože je obtížné ho použít v sémantickém modelu. Pečlivě zvažte, zda by přístup typu 2 typu SCD byl vhodnější.
Tip
Pokud očekáváte rychle se měnící dimenzi, což je dimenze s atributem, který se často mění, zvažte přidání atributu do tabulky faktů. Pokud je atribut číselný, například cena produktu, můžete ho přidat jako míru v tabulce faktů. Pokud je atribut textovou hodnotou, můžete vytvořit dimenzi na základě všech textových hodnot a přidat její klíč dimenze do tabulky faktů.
SCD – typ 1
Změna typu 1 typu SCD přepíše existující řádek dimenze, protože není nutné sledovat změny. Tento typ SCD lze také použít k opravě chyb. Jedná se o běžný typ SCD a měl by se používat pro většinu měnících se atributů, jako je jméno zákazníka, e-mailová adresa a další.
Následující diagram znázorňuje před a po stavu člena dimenze prodejce, kde se změnilo telefonní číslo.
Tento typ SCD nezachová historickou perspektivu, protože existující řádek se aktualizuje. To znamená, že změny typu 1 typu SCD můžou vést k různým agregacím vyšší úrovně. Pokud je například prodejce přiřazen k jiné prodejní oblasti, změna typu SCD 1 přepíše řádek dimenze. Souhrn historických výsledků prodeje prodejců do oblasti by pak vytvořil jiný výsledek, protože teď používá novou aktuální prodejní oblast. Je to jako kdyby byl prodejce vždy přiřazený k nové prodejní oblasti.
SCD – typ 2
Změna typu SCD 2 způsobí nové řádky, které představují časovou verzi člena dimenze. Vždy existuje řádek aktuální verze a odráží stav člena dimenze ve zdrojovém systému. Historické atributy sledování v tabulce dimenzí ukládají hodnoty, které umožňují identifikovat aktuální verzi (aktuální příznak je TRUE
) a časové období platnosti. Náhradní klíč je povinný, protože při uložení více verzí bude existovat duplicitní přirozené klíče.
Jedná se o běžný typ SCD, ale měl by být vyhrazený pro atributy, které musí zachovat historickou perspektivu.
Pokud je například prodejce přiřazen k jiné prodejní oblasti, změna typu SCD 2 zahrnuje operaci aktualizace a operaci vložení.
- Operace aktualizace přepíše aktuální verzi, aby se nastavily historické atributy sledování. Konkrétně je sloupec koncové platnosti nastaven na datum zpracování ETL (nebo vhodné časové razítko ve zdrojovém systému) a aktuální příznak je nastaven na
FALSE
. - Operace vložení přidá novou, aktuální verzi, nastaví sloupec počáteční platnosti na hodnotu sloupce koncové platnosti (používá se k aktualizaci předchozí verze) a aktuální příznak na
TRUE
.
Je důležité si uvědomit, že členitost souvisejících tabulek faktů není na úrovni prodejce, ale spíše na úrovni verze prodejce. Souhrn jejich historických výsledků prodeje do oblasti bude mít správné výsledky, ale budou k dispozici dvě (nebo více) verzí členů prodejce, které se mají analyzovat.
Následující diagram znázorňuje před a po stavu člena dimenze prodejce, kde se jejich prodejní oblast změnila. Vzhledem k tomu, že organizace chce analyzovat úsilí prodejců podle oblasti, ke které jsou přiřazené, aktivuje změnu typu SCD 2.
Tip
Pokud tabulka dimenzí podporuje změny typu SCD 2, měli byste zahrnout atribut popisku, který popisuje člena a verzi. Představte si příklad, když prodejce Lynn Tsoflias z Adventure Works změní přiřazení z australské prodejní oblasti do prodejní oblasti Spojeného království. Atribut label pro první verzi by mohl číst "Lynn Tsoflias (Austrálie)" a atribut popisku pro novou, aktuální verzi by mohl přečíst "Lynn Tsoflias (Spojené království)." Pokud je to užitečné, můžete do popisku zahrnout také data platnosti.
Měli byste vyvážit potřebu historické přesnosti a použitelnosti a efektivity. Snažte se vyhnout příliš mnoha změnám typu SCD typu 2 v tabulce dimenzí, protože může vést k zahlcení počtu verzí, které by mohly analytikům ztížit pochopení.
Příliš mnoho verzí může také znamenat, že by se v tabulce faktů mohlo lépe uložit měnící se atribut. Rozšíření předchozího příkladu, pokud byly změny prodejní oblasti časté, může být prodejní oblast uložena jako klíč dimenze v tabulce faktů místo implementace typu SCD 2.
Zvažte následující atributy sledování typu SCD typu 2.
CREATE TABLE d_Salesperson
(
<…>
--Historical tracking attributes (SCD type 2)
RecChangeDate_FK INT NOT NULL,
RecValidFromKey INT NOT NULL,
RecValidToKey INT NOT NULL,
RecReason VARCHAR(15) NOT NULL,
RecIsCurrent BIT NOT NULL,
<…>
);
Tady jsou účely historických atributů sledování.
- Sloupec
RecChangeDate_FK
ukládá datum, kdy se změna projevila. Umožňuje dotazovat se, kdy došlo ke změnám. - Sloupce
RecValidFromKey
ukládajíRecValidToKey
platná data platnosti řádku. Zvažte uložení nejstaršího data nalezeného v dimenziRecValidFromKey
data, aby představovala počáteční verzi, a uložení01/01/9999
aktuálníchRecValidToKey
verzí. - Sloupec
RecReason
je nepovinný. Umožňuje zdokumentovat důvod vložení verze. Může kódovat, které atributy se změnily, nebo by to mohl být kód ze zdrojového systému, který uvádí konkrétní obchodní důvod. - Sloupec
RecIsCurrent
umožňuje načíst pouze aktuální verze. Používá se, když proces ETL vyhledá klíče dimenzí při načítání tabulek faktů.
Poznámka:
Některé zdrojové systémy neukládají historické změny, proto je důležité, aby dimenze byla pravidelně zpracována za účelem zjištění změn a implementace nových verzí. Díky tomu můžete krátce po jejich výskytu zjistit změny a jejich data platnosti budou přesná.
SCD – typ 3
Změny typu SCD 3 sledují omezenou historii pomocí atributů. Tento přístup může být užitečný v případě, že je potřeba zaznamenat poslední změnu nebo řadu nejnovějších změn.
Tento typ SCD zachovává omezenou historickou perspektivu. Může být užitečné, když by se měly ukládat jenom počáteční a aktuální hodnoty. V tomto případě by se dočasné změny nemusely vyžadovat.
Pokud je například prodejce přiřazen k jiné prodejní oblasti, změna typu SCD 3 přepíše řádek dimenze. Sloupec, který konkrétně ukládá předchozí prodejní oblast, se nastaví jako předchozí prodejní oblast a nová prodejní oblast se nastaví jako aktuální prodejní oblast.
Následující diagram znázorňuje před a po stavu člena dimenze prodejce, kde se jejich prodejní oblast změnila. Vzhledem k tomu, že organizace chce určit jakékoli předchozí přiřazení prodejní oblasti, aktivuje změnu typu SCD 3.
Speciální členy dimenze
Řádky můžete vložit do dimenze, která představuje chybějící, neznámé, N/A nebo chybové stavy. Můžete například použít následující náhradní hodnoty klíče.
Hodnota klíče | Účel |
---|---|
0 | Chybí (není k dispozici ve zdrojovém systému) |
-1 | Neznámé (selhání vyhledávání při načítání tabulky faktů) |
-2 | Není k dispozici (není k dispozici) |
-3 | Chyba |
Kalendář a čas
Téměř bez výjimky tabulky faktů ukládají míry v konkrétních bodech v čase. Aby bylo možné podporovat analýzu podle data (a případně času), musí existovat dimenze kalendáře (data a času).
Je neobvyklé, že zdrojový systém by měl data dimenzí kalendáře, takže se musí vygenerovat v datovém skladu. Obvykle se vygeneruje jednou a pokud se jedná o dimenzi kalendáře, prodlouží se v případě potřeby o budoucí data.
Dimenze data
Dimenze data (nebo kalendáře) je nejběžnější dimenzí, která se používá k analýze. Ukládá jeden řádek na datum a podporuje společný požadavek na filtrování nebo seskupení podle konkrétních období kalendářních dat, jako jsou roky, čtvrtletí nebo měsíce.
Důležité
Dimenze data by neměla obsahovat agregační interval, který se prodlužuje na denní dobu. Pokud je vyžadována denní analýza, měli byste mít dimenzi data i časovou dimenzi (popsanou dále). Tabulky faktů, které ukládají denní fakta, by měly mít dva cizí klíče, jeden pro každou z těchto dimenzí.
Přirozený klíč dimenze data by měl používat datový typ kalendářního data . Náhradní klíč by měl uchovávat datum pomocí YYYYMMDD
formátu a datového typu int . Tato akceptovaná praxe by měla být jedinou výjimkou (vedle časového rozměru), pokud má náhradní hodnota klíče význam a je čitelná člověkem. YYYYMMDD
Ukládání jako datového typu typu int je nejen efektivní a seřazené číselně, ale také odpovídá jednoznačnému formátu data organizace ISO (International Standards Organization) 8601.
Tady jsou některé běžné atributy, které se mají zahrnout do dimenze data.
Year
,Quarter
, ,Month
Day
QuarterNumberInYear
,MonthNumberInYear
– což může být nutné k řazení textových popisků.FiscalYear
,FiscalQuarter
– některé podnikové účetní plány začínají uprostřed roku, takže začátek/konec kalendářního roku a fiskálního roku se liší.FiscalQuarterNumberInYear
,FiscalMonthNumberInYear
– což může být nutné k řazení textových popisků.WeekOfYear
– existuje několik způsobů, jak označit týden v roce, včetně normy ISO, která má buď 52, nebo 53 týdnů.IsHoliday
–HolidayText
pokud vaše organizace pracuje v několika zeměpisných oblastech, měli byste udržovat několik sad seznamů svátků, které každá zeměpis sleduje jako samostatnou dimenzi nebo naturalizovaná v několika atributech v dimenzi data. Přidání atributuHolidayText
může pomoct s identifikací svátků pro vytváření sestav.IsWeekday
– podobně v některých zeměpisných oblastech není standardní pracovní týden od pondělí do pátku. Například pracovní týden je neděle až čtvrtek v mnoha oblastech Blízkého východu, zatímco jiné oblasti využívají čtyřdenní nebo šestidenní pracovní týden.LastDayOfMonth
RelativeYearOffset
,RelativeQuarterOffset
,RelativeMonthOffset
–RelativeDayOffset
což může být nutné k podpoře filtrování relativního data (například předchozí měsíc). Aktuální období používají posun nuly (0); předchozí období ukládají posuny -1, -2, -3...; budoucí období ukládají posuny 1, 2, 3....
Stejně jako u každé dimenze je důležité, že obsahuje atributy, které podporují známé požadavky na filtrování, seskupení a hierarchii. Mohou existovat i atributy, které ukládají překlady popisků do jiných jazyků.
Pokud se dimenze používá k relaci s fakty vyšší úrovně, tabulka faktů může použít první datum období kalendářního data. Například tabulka faktů cíle prodeje, která ukládá cíle čtvrtletních prodejců, by ukládaly první datum čtvrtletí v dimenzi kalendářního data. Alternativním přístupem je vytvoření klíčových sloupců v tabulce kalendářních dat. Například klíč čtvrtletí může uložit klíč čtvrtletí pomocí YYYYQ
formátu a malého datového typu.
Dimenze by měla být naplněna známým rozsahem kalendářních dat používaných všemi tabulkami faktů. Měla by také zahrnovat budoucí data, kdy datový sklad ukládá fakta o cílech, rozpočtech nebo prognózách. Stejně jako u jiných dimenzí můžete zahrnout řádky, které představují chybějící, neznámé, N/A nebo chybové situace.
Tip
Vyhledejte na internetu "generátor dimenzí kalendářních dat" a vyhledejte skripty a tabulky, které generují data kalendářních dat.
Proces ETL by měl na začátku příštího roku obvykle rozšířit řádky dimenzí kalendářních dat na určitý počet let dopředu. Pokud dimenze obsahuje relativní atributy posunu, proces ETL musí být spuštěn denně, aby se aktualizovaly hodnoty atributu posunu na základě aktuálního data (dnes).
Časová dimenze
V některých případech musí být fakta uložena v určitém časovém okamžiku (stejně jako v denním čase). V tomto případě vytvořte dimenzi času (nebo hodin). Může mít interval minut (24 x 60 = 1 440 řádků) nebo dokonce sekundy (24 x 60 x 60 = 86 400 řádků). Mezi další možné zrní patří půlhodina nebo hodina.
Přirozený klíč časové dimenze by měl používat datový typ času . Náhradní klíč může použít vhodný formát a uložit hodnoty, které mají význam a jsou čitelné, například pomocí HHMM
nebo HHMMSS
formátu.
Tady jsou některé běžné atributy, které se mají zahrnout do časové dimenze.
Hour
,HalfHour
, ,QuarterHour
Minute
- Popisky časových období (ráno, odpoledne, večer, noc)
- Názvy pracovních směn
- Příznaky špičky nebo mimo špičku
Odpovídající rozměry
Některé dimenze můžou být v souladu s dimenzemi. Odpovídající dimenze se vztahují k mnoha tabulkám faktů, takže je sdílí několik hvězdiček v dimenzionálním modelu. Poskytují konzistenci a můžou vám pomoct snížit průběžný vývoj a údržbu.
Je například typické, že tabulky faktů ukládají alespoň jeden klíč dimenze kalendářního data (protože aktivita se téměř vždy zaznamenává podle data a času). Z tohoto důvodu je dimenze kalendářního data společnou odpovídající dimenzí. Proto byste měli zajistit, aby dimenze kalendářních dat obsahovala atributy relevantní pro analýzu všech tabulek faktů.
Následující diagram znázorňuje Sales
tabulku faktů a tabulku faktů Inventory
. Každá tabulka faktů souvisí s Date
dimenzí a Product
dimenzí, které jsou v souladu s dimenzemi.
Jako další příklad může být váš zaměstnanec a uživatelé stejnou sadou lidí. V tomto případě může být vhodné zkombinovat atributy každé entity a vytvořit tak jednu odpovídající dimenzi.
Dimenze role
Pokud je dimenze odkazována vícekrát v tabulce faktů, označuje se jako dimenze role.
Pokud má například tabulka faktů prodeje datum objednávky, datum expedice a klíče dimenzí data doručení, dimenze data se vztahuje třemi způsoby. Každý způsob představuje odlišnou roli, ale existuje pouze jedna fyzická dimenze kalendářních dat.
Následující diagram znázorňuje Flight
tabulku faktů. Dimenze Airport
je dimenze role, protože souvisí dvakrát s tabulkou faktů jako Departure Airport
dimenze a Arrival Airport
dimenze.
Rozměry nevyžádané pošty
Nevyžádaná dimenze je užitečná, pokud existuje mnoho nezávislých dimenzí, zejména pokud tvoří několik atributů (třeba jeden) a pokud mají tyto atributy nízkou kardinalitu (několik hodnot). Cílem nevyžádané dimenze je konsolidovat mnoho malých dimenzí do jedné dimenze. Tento přístup k návrhu může snížit počet dimenzí a snížit počet klíčů tabulky faktů a tím i velikost úložiště tabulek faktů. Pomáhají také omezit nepotřebné podokno dat, protože uživatelům zobrazují méně tabulek.
Tabulka nevyžádaných dimenzí obvykle ukládá kartézský součin všech hodnot atributů dimenze s náhradním atributem klíče.
Mezi vhodné kandidáty patří příznaky a indikátory, stav objednávky a demografické stavy zákazníků (pohlaví, věková skupina a další).
Následující diagram znázorňuje dimenzi nevyžádané pošty, Sales Status
která kombinuje hodnoty stavu objednávky a hodnoty stavu doručení.
Degenerované dimenze
Degenerovaná dimenze může nastat, když je dimenze ve stejném agregačním intervalu jako související fakta. Běžným příkladem degenerovaných dimenzí je dimenze čísla prodejní objednávky, která souvisí s tabulkou faktů prodeje. Číslo faktury je obvykle jeden, ne hierarchický atribut v tabulce faktů. Je to tedy uznávaný postup, kdy se tato data nekopírují, aby se vytvořila samostatná tabulka dimenzí.
Následující diagram znázorňuje Sales Order
dimenzi, která je degenerovat dimenze na SalesOrderNumber
základě sloupce v tabulce faktů prodeje. Tato dimenze se implementuje jako zobrazení, které načte jedinečné hodnoty čísel prodejních objednávek.
Tip
V skladu fabric je možné vytvořit zobrazení, které představuje degenerující dimenzi jako dimenzi pro účely dotazování.
Z hlediska sémantického modelování Power BI je možné pomocí Power Query vytvořit degenerované dimenze jako samostatnou tabulku. Sémantický model tak odpovídá osvědčeným postupům, které pole použitá k filtrování nebo seskupování pocházejí z tabulek dimenzí a pole použitá k shrnutí faktů pocházejí z tabulek faktů.
Rozměry outriggeru
Pokud tabulka dimenzí souvisí s jinými tabulkami dimenzí, označuje se jako dimenze outriggeru. Dimenze outriggeru může pomoct splňovat a opakovaně používat definice v dimenzionálním modelu.
Můžete například vytvořit geografickou dimenzi, která ukládá zeměpisné polohy pro každé PSČ. Na danou dimenzi pak může odkazovat dimenze zákazníka a dimenze prodejce, která by uložila náhradní klíč zeměpisné dimenze. Zákazníci a prodejci tak můžou být pak analyzováni pomocí konzistentních geografických lokalit.
Následující diagram znázorňuje Geography
dimenzi, která je dimenze outriggeru. Nesouvisí přímo s tabulkou Sales
faktů. Místo toho to souvisí nepřímo prostřednictvím Customer
dimenze a Salesperson
dimenze.
Vezměte v úvahu, že dimenze kalendářních dat se dá použít jako dimenze outriggeru, když jiné atributy tabulky dimenzí ukládají kalendářní data. Například datum narození v dimenzi zákazníka může být uloženo pomocí náhradního klíče tabulky dimenzí kalendářních dat.
Vícehodnotové dimenze
Pokud atribut dimenze musí obsahovat více hodnot, musíte navrhnout vícehodnotovou dimenzi. Implementujete vícehodnotovou dimenzi vytvořením tabulky mostu (někdy označovanou jako join table). Tabulka mostu ukládá mezi entitami relaci M:N.
Představte si například dimenzi prodejce a že je každý prodejce přiřazený k jedné nebo více prodejním oblastem. V tomto případě je vhodné vytvořit dimenzi prodejní oblasti. Tato dimenze ukládá každou prodejní oblast pouze jednou. Samostatná tabulka, která se označuje jako tabulka mostu, uchovává řádek pro každý prodejce a relaci prodejní oblasti. Fyzicky existuje relace 1:N z dimenze prodejce do tabulky mostu a druhá relace 1:N z dimenze prodejní oblasti do tabulky mostu. Logicky existuje vztah M:N mezi prodejci a prodejními oblastmi.
V následujícím diagramu Account
se tabulka dimenzí vztahuje k Transaction
tabulce faktů. Vzhledem k tomu, že zákazníci můžou mít více účtů a účty můžou mít více zákazníků, Customer
tabulka dimenzí souvisí prostřednictvím tabulky mostu Customer Account
.
Související obsah
V dalším článku této série se seznámíte s pokyny a osvědčenými postupy návrhu pro tabulky faktů.