Dimenzionálne modelovanie v sklade služby Microsoft Fabric: tabuľky dimenzií

Vzťahuje sa na:✅ koncový bod analýzy SQL a sklad v službe Microsoft Fabric

Poznámka

Tento článok je súčasťou série článkov o dimenzionálnom modelovaní . Táto séria sa zameriava na sprievodný materiál a najvhodnejšie postupy súvisiace s dimenzionálnym modelovaním v sklade služby Microsoft Fabric.

V tomto článku nájdete sprievodný materiál a najvhodnejšie postupy pri navrhovaní tabuliek dimenzií v dimenzionálnom modeli. Poskytuje praktické usmernenie pre sklad v službe Microsoft Fabric, čo je prostredie, ktoré podporuje množstvo funkcií T-SQL, ako napríklad vytváranie tabuliek a spravovanie údajov v tabuľkách. Takže máte úplnú kontrolu nad vytváraním tabuliek dimenzionálnych modelov a ich načítaním s údajmi.

Poznámka

V tomto článku termín sklad údajov odkazuje na podnikový sklad údajov, ktorý poskytuje komplexnú integráciu kritických údajov v rámci organizácie. Naopak samostatný pojem sklad odkazuje na sklad služby Fabric, čo je softvér ako relačná databáza SaaS (SaaS), ktorá ponúka relačnú databázu, ktorú môžete použiť na implementáciu skladu údajov. Pre objasnenie uvádzame v tomto článku názov skladu služby Fabric.

Prepitné

Ak ste neskúsení dimenzionálnym modelovaním, zvážte túto sériu článkov ako prvý krok. Jeho cieľom nie je poskytnúť kompletné informácie o návrhu dimenzionálneho modelovania. Ďalšie informácie nájdete priamo v široko prijatom publikovanom obsahu, napríklad : The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (Súprava nástrojov skladu údajov: Definitívny sprievodca dimenzionálnym modelovaním ) (3. vydanie, 2013), Ralph Kimball a ďalší.

V dimenzionálnom modeli tabuľka dimenzií popisuje entitu relevantnú pre vaše obchodné požiadavky a požiadavky na analýzu. Tabuľky dimenzií všeobecne predstavujú veci, ktoré modelujete. Môže ísť o produkty, ľudí, miesta alebo iný koncept vrátane dátumu a času. Ak chcete tabuľky dimenzií jednoducho identifikovať, zvyčajne ich predpony d_ k alebo Dim_.

Štruktúra tabuľky dimenzií

Ak chcete popísať štruktúru tabuľky dimenzií, zvážte nasledujúci príklad tabuľky dimenzií predajcu s názvom d_Salesperson. Tento príklad používa vhodné postupy návrhu. Každá zo skupín stĺpcov je popísaná v nasledujúcich sekciách.

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ý kľúč

Vzorová tabuľka dimenzií má náhradný kľúč s názvom Salesperson_SK. Náhradný kľúč je jedinečný identifikátor s jedným stĺpcom, ktorý sa vygeneruje a ukladá v tabuľke dimenzií. Ide o stĺpec primárneho kľúča , ktorý sa používa na vytvorenie vzťahov s inými tabuľkami v dimenzionálnom modeli.

Náhradné kľúče sa snažia izolovať sklad údajov od zmien v zdrojových údajoch. Prináša tiež mnoho ďalších výhod, ktoré vám umožnia:

• Zlúčenie viacerých zdrojov údajov (vyhnite sa konfliktu duplicitných identifikátorov).
• Zlúčenie prírodných kľúčov s viacerými stĺpcami do efektívnejšieho kľúča s jedným stĺpcom.
• Sledujte históriu dimenzií s pomaly sa meniacou dimenziou (SCD) typu 2.
• Obmedzte šírku tabuľky faktov na optimalizáciu úložiska (výberom najmenšieho možného celočíselného typu údajov).

Stĺpec náhradných kľúčov je odporúčaným postupom, aj keď sa prirodzený kľúč (ako je popísané ďalej) javí ako prijateľný kandidát. Mali by ste sa tiež vyhnúť významu kľúčových hodnôt (s výnimkou kľúčov dimenzie dátumu a času, ako je to popísané nižšie).

Prirodzené kľúče

Vzorová tabuľka dimenzií má tiež prirodzený kľúč s názvom EmployeeID. Prirodzeným kľúčom je kľúč uložený v zdrojovom systéme. Umožňuje vytvoriť vzťah medzi údajmi dimenzie a jej zdrojovým systémom, ktorý zvyčajne vykonáva proces extrahovania, načítania a transformácie (ETL) na načítanie tabuľky dimenzií. Niekedy sa prirodzený kľúč nazýva podnikový kľúč a jeho hodnoty môžu byť pre podnikových používateľov zmysluplné.

Dimenzie niekedy nemajú prirodzený kľúč. Môže to byť prípad vašej dimenzie dátumu alebo vyhľadávacích dimenzií alebo pri generovaní údajov dimenzie normalizáciou plochého súboru.

Atribúty dimenzie

Vzorová tabuľka dimenzií má tiež atribúty dimenzie, ako napríklad FirstName stĺpec. Atribúty dimenzie poskytujú kontext číselným údajom uloženým v súvisiacich tabuľkách faktov. Zvyčajne ide o textové stĺpce, ktoré sa používajú v analytických dotazoch na filtrovanie a zoskupovanie (výseky a kocky), ale nie na samotnú agregáciu. Niektoré tabuľky dimenzií obsahujú niekoľko atribútov, iné obsahujú množstvo atribútov (toľko, koľko je potrebné na podporu požiadaviek dotazu dimenzionálneho modelu).

Prepitné

Dobrý spôsob, ako určiť, ktoré dimenzie a atribúty potrebujete, je nájsť správnych ľudí a klásť správne otázky. Konkrétne buďte upozornením na slovo podľa. Ak napríklad niekto hovorí, že potrebuje analyzovať predaj podľa obchodníka, podľa mesiaca a kategórie produktov, tak vám povie, že potrebuje dimenzie, ktoré majú tieto atribúty.

Ak plánujete vytvoriť sémantický model direct lake, mali by ste zahrnúť všetky možné stĺpce potrebné na filtrovanie a zoskupovanie ako atribúty dimenzie. Dôvodom je, že sémantické modely Direct Lake nepodporujú vypočítané stĺpce.

Cudzie kľúče

Vzorová tabuľka dimenzií má tiež cudzí kľúč s názvom SalesRegion_FK. Iné tabuľky dimenzií môžu odkazovať na cudzí kľúč a ich prítomnosť v tabuľke dimenzií je špeciálny prípad. Označuje, že tabuľka súvisí s inou tabuľkou dimenzií, čo znamená, že môže byť súčasťou dimenzie vločky alebo môže súvisieť s outrigger dimenziou.

Sklad služby Fabric podporuje obmedzenia cudzieho kľúča, ale nie je ich možné vynútiť. Preto je dôležité, aby vaše testy procesu ETL pri načítavaní údajov boli testované s integritou medzi súvisiacimi tabuľkami.

Je to stále dobrý nápad vytvoriť cudzie kľúče. Jedným z dobrých dôvodov na vytvorenie neefektívnych cudzích kľúčov je umožniť nástrojom modelovania, ako je napríklad Power BI Desktop, automaticky rozpoznať a vytvoriť vzťahy medzi tabuľkami v sémantickom modeli.

Atribúty historického sledovania

Vzorová tabuľka dimenzií má tiež rôzne atribúty historického sledovania. Atribúty historického sledovania sú voliteľné na základe vašej potreby sledovať konkrétne zmeny v zdrojovom systéme. Umožňujú ukladanie hodnôt na podporu primárnej roly skladu údajov, čo má presne opísať minulosť. Konkrétne tieto atribúty ukladajú historický kontext, keďže proces ETL načíta nové alebo zmenené údaje do dimenzie.

Ďalšie informácie nájdete v časti Spravovanie historických zmien ďalej v tomto článku.

Atribúty auditu

Vzorová tabuľka dimenzií má tiež rôzne atribúty auditu. Atribúty auditu sú voliteľné, ale odporúča sa. Umožňujú sledovať, kedy a ako sa záznamy dimenzií vytvorili alebo upravili, a môžu zahŕňať diagnostické informácie alebo informácie o riešení problémov, ktoré sa získali počas procesov ETL. Môžete napríklad sledovať, kto (alebo aký proces) aktualizoval riadok a kedy. Atribúty auditu môžu tiež pomôcť diagnostikovať náročný problém, ako keď sa proces ETL neočakávane zastaví. Môžu tiež označiť členov dimenzie ako chyby alebo odvodených členov.

Veľkosť tabuľky dimenzií

Najužitočnejšie a všestranné dimenzie v dimenzionálnom modeli sú často veľké a široké rozmery. Sú veľké z hľadiska riadkov (presahujúcich milióny) a široké, pokiaľ ide o počet atribútov dimenzií (potenciálne stovky). Veľkosť nie je taká dôležitá (aj keď by ste mali navrhnúť a optimalizovať pre najmenšiu možnú veľkosť). Dôležité je, že dimenzia podporuje požadované filtrovanie, zoskupenie a presnú historickú analýzu údajov faktov.

Veľké dimenzie môžu pochádzať z viacerých zdrojových systémov. V tomto prípade je spracovanie dimenzií potrebné skombinovať, zlúčiť, odstrániť a štandardizovať údaje. a priradiť náhradné kľúče.

Na porovnanie – niektoré dimenzie sú malé. Môžu predstavovať vyhľadávacie tabuľky, ktoré obsahujú iba niekoľko záznamov a atribútov. Tieto malé dimenzie často ukladajú hodnoty kategórií súvisiace s transakciami v tabuľkách faktov a implementujú sa ako dimenzie so náhradnými kľúčmi, aby sa vzťahovali na záznamy faktov.

Prepitné

Ak máte veľa malých dimenzií, zvážte ich konsolidáciu do nevyžiadanej dimenzie.

Koncepty návrhu dimenzie

Táto časť popisuje rôzne koncepty návrhu dimenzie.

Denormalizácia vs. normalizácia

Takmer vždy sa zdá, že tabuľky dimenzií by sa mali denormalizovať. Zatiaľ čo normalizácia je pojem používaný na popis údajov uložených spôsobom, ktorý znižuje počet opakujúcich sa údajov, denormalizácia je výraz používaný na definovanie toho, kde existujú predkompilované nadbytočné údaje. Nadbytočné údaje existujú zvyčajne z dôvodu úložiska hierarchií (popísané neskôr), čo znamená, že hierarchie sú zjednodušené. Dimenzia produktu by napríklad mohla uchovávať podkategóriu (a jej súvisiace atribúty) a kategóriu (a jej súvisiace atribúty).

Keďže dimenzie sú vo všeobecnosti malé (v porovnaní s tabuľkami faktov), náklady na ukladanie nadbytočných údajov takmer vždy prevážia vylepšený výkon dotazov a použiteľnosť.

Vločkové dimenzie

Jednou z výnimiek denormalizácie je navrhnúť dimenziu v tvare snehovej vločky. Dimenzia vločky sa normalizuje a ukladá údaje dimenzie v rámci niekoľkých súvisiacich tabuliek.

Nasledujúci diagram znázorňuje dimenziu vločky, ktorá obsahuje tri súvisiace tabuľky dimenzií: Product, Subcategorya Category.

Zvážte implementáciu dimenzie v tvare vločky v týchto prípadoch:

• Dimenzia je mimoriadne veľká a náklady na ukladanie prevyšujú potrebu vysokého výkonu dotazov. (Pravidelne sa však prehodnocuje, že to tak ešte zostáva.)
• Na vytvorenie vzťahu dimenzie k faktom s vyššou granularnou potrebujete kľúče. Napríklad tabuľka faktov predaja ukladá riadky na úrovni produktu, ale tabuľka faktov Sales target ukladá riadky na úrovni podkategórie.
• Historické zmeny je potrebné sledovať na vyšších úrovniach granularity.

Poznámka

Majte na pamäti, že hierarchia v sémantickom modeli služby Power BI môže byť založená len na stĺpcoch z jednej tabuľky sémantických modelov. Preto by dimenzia vločky mala poskytovať denormalizovaný výsledok s použitím zobrazenia, ktoré spája tabuľky v snehovej vločke.

Hierarchie

Stĺpce dimenzií bežne vytvárajú hierarchie. Hierarchie umožňujú skúmanie údajov na rôznych úrovniach sumarizácie. Napríklad počiatočné zobrazenie vizuálu matice môže zobrazovať ročný predaj a spotrebiteľ zostavy sa môže rozhodnúť prejsť na detaily a zobraziť štvrťročný a mesačný predaj.

Existujú tri spôsoby uloženia hierarchie v dimenzii. Môžeš použiť:

• Stĺpce z jednej denormalizovanej dimenzie.
• Vločková dimenzia, ktorá obsahuje viacero súvisiacich tabuliek.
• Vzťah typu nadradený-podriadený (samostatne odkazujúci) v dimenzii.

Hierarchie môžu byť vyvážené alebo nevyvážené. Dôležité je tiež pochopiť, že niektoré hierarchie sú nepravidelné.

Vyvážené hierarchie

Vyvážené hierarchie sú najbežnejším typom hierarchie. Vyvážená hierarchia má rovnaký počet úrovní. Bežným príkladom vyváženej hierarchie je kalendárová hierarchia v dimenzii dátumov, ktorá obsahuje úrovne pre rok, štvrťrok, mesiac a dátum.

Nasledujúci diagram znázorňuje vyváženú hierarchiu oblastí predaja. Obsahuje dve úrovne, ktorými sú skupina oblasti predaja a oblasť predaja.

Úrovne vyváženej hierarchie sú buď založené na stĺpcoch z jednej, denormalizovanej dimenzie alebo z tabuliek, ktoré tvoria dimenziu v tvare snehovej vločky. Na základe jednej denormalizovanej dimenzie obsahujú stĺpce, ktoré predstavujú vyššie úrovne, nadbytočné údaje.

V prípade vyváženej hierarchie sa fakty vždy vzťahujú na jednu úroveň hierarchie, ktorá je zvyčajne najnižšou úrovňou. Týmto spôsobom je možné fakty agregovať (zhrnúť) na najvyššiu úroveň hierarchie. Fakty môžu súvisieť s akoukoľvek úrovňou, ktorá je určená granularnou tabuľky faktov. Napríklad tabuľka faktov predaja môže byť uložená na úrovni dátumu, zatiaľ čo tabuľka faktov cieľ predaja môže byť uložená na úrovni štvrťroka.

Nevyvážené hierarchie

Nevyvážené hierarchie sú menej bežným typom hierarchie. Nevyvážená hierarchia má úrovne založené na vzťahu nadradených a podriadených prvkov. Z tohto dôvodu je počet úrovní v nevyváženej hierarchii určený riadkami dimenzie a nie špecifickými stĺpcami tabuľky dimenzií.

Bežným príkladom nevyváženej hierarchie je hierarchia zamestnancov, v ktorej sa každý riadok v dimenzii zamestnanca vzťahuje na riadok manažéra vytvárania zostáv v tej istej tabuľke. V tomto prípade každý zamestnanec môže byť manažérom pri vykazovaní zamestnancov. Samozrejme, že niektoré vetvy hierarchie budú mať viac úrovní ako ostatné.

Nasledujúci diagram znázorňuje nevyváženú hierarchiu. Obsahuje štyri úrovne a každý člen v hierarchii je obchodník. Všimnite si, že predajcovia majú v hierarchii rôzny počet predkov, podľa toho, komu sa uvádzajú.

Ďalšími bežnými príkladmi nevyvážených hierarchií sú faktúra materiálov, modely vlastníctva spoločnosti a financie.

V prípade nevyvážených hierarchií sa fakty vždy týkajú granuláru dimenzie. Napríklad fakty o predaji sa týkajú rôznych predajcov, ktorí majú rôzne štruktúry zostáv. Tabuľka dimenzií by mala náhradný kľúč (s názvom Salesperson_SK) a stĺpec cudzieho ReportsTo_Salesperson_FK kľúča, ktorý odkazuje na stĺpec primárneho kľúča. Každý predajca bez toho, aby ho niekto spravoval, nemusí byť nevyhnutne na najnižšej úrovni akejkoľvek vetvy hierarchie. Keď nie sú na najnižšej úrovni, predajca môže predávať produkty a vykazovať obchodníkov, ktorí tiež predávajú produkty. Takže súhrn údajov faktov musí brať do úvahy jednotlivých predajcov a všetkých jeho potomkov.

Dotazovanie hierarchií typu nadradený-podriadený môže byť zložité a pomalé, najmä pre veľké dimenzie. Zatiaľ čo zdrojový systém môže ukladať vzťahy ako nadradený-podriadený, odporúčame vám naturalizovať hierarchiu. V tejto inštancii naturalizácia znamená transformáciu a uloženie úrovní hierarchie v dimenzii ako stĺpce.

Prepitné

Ak sa rozhodnete ne naturalizovať hierarchiu, stále môžete vytvoriť hierarchiu založenú na vzťahu nadradených a podriadených prvkov v sémantickom modeli služby Power BI. Tento prístup sa však neodporúča pre veľké dimenzie. Ďalšie informácie nájdete v téme Základné informácie o funkciách pre hierarchie typu nadradený-podriadený v jazyku DAX.

Nepravidelné hierarchie

Niekedy je hierarchia nepravidelná , pretože nadradený prvok člena v hierarchii existuje na úrovni, ktorá nie je bezprostredne nad ňou. V týchto prípadoch chýbajúce hodnoty na úrovni opakujú hodnotu nadradeného prvku.

Zoberme si príklad vyváženej hierarchie geografie. Nepravidelná hierarchia existuje, keď krajina alebo oblasť nemá žiadne štáty alebo provincie. Napríklad Nový Zéland nemá žiadne štáty ani provincie. Po vložení riadka Nový Zéland by ste preto mali do StateProvince stĺpca uložiť aj hodnotu krajina alebo oblasť.

Nasledujúci diagram znázorňuje nepravidelnú hierarchiu geografických oblastí.

Spravovať historické zmeny

Ak je to potrebné, historické zmeny možno spravovať implementáciou pomaly sa meniacej dimenzie (SCD). SCD udržiava historický kontext ako nové alebo zmenené údaje, ktoré sa doň načítajú.

Toto sú najbežnejšie typy scd.

• 1. typ: Prepíšte existujúceho člena dimenzie.
• 2. typ: Vložte nový člen dimenzie založený na časovej verzii .
• 3. typ: Sledujte obmedzenú históriu s atribútmi.

Je možné, že dimenzia môže podporovať zmeny 1. typu SCD aj 2. typu SCD.

SCD typ 3 sa bežne nepoužíva, čiastočne z dôvodu skutočnosti, že je ťažké používať v sémantickom modeli. Dôkladne zvážte, či bude vhodnejší prístup 2. typu SCD.

Prepitné

Ak očakávate rýchlo sa meniacu dimenziu, čo je dimenzia s atribútom, ktorý sa často mení, zvážte namiesto toho pridanie tohto atribútu do tabuľky faktov . Ak je atribút číselný, napríklad cena produktu, môžete ho pridať ako mierku v tabuľke faktov. Ak je atribút textová hodnota, môžete vytvoriť dimenziu založenú na všetkých textových hodnotách a pridať kľúč jej dimenzie do tabuľky faktov.

SCD typ 1

Zmeny 1. typu SCD prepíšu existujúci riadok dimenzie, pretože nie je potrebné sledovať zmeny. Tento typ SCD možno použiť aj na opravu chýb. Ide o bežný typ scd a mal by sa používať pre väčšinu meniacich sa atribútov, ako je napríklad meno zákazníka, e-mailová adresa a ďalšie.

Nasledujúci diagram znázorňuje stav pred a za stav člena dimenzie obchodníka, v ktorom sa zmenilo telefónne číslo.

Tento typ SCD nezachová historickú perspektívu, pretože sa aktualizuje existujúci riadok. To znamená, že zmeny typu SCD 1 môžu mať za následok odlišné agregácie vyššej úrovne. Ak je napríklad predajca priradený k inej oblasti predaja, zmena 1. typu SCD by prepíše riadok dimenzie. Súhrn historických výsledkov predaja predajcov do oblasti by potom mal vytvoriť iný výsledok, pretože teraz používa novú aktuálnu oblasť predaja. Je to akoby tento predajca bol vždy priradený k novej oblasti predaja.

SCD typ 2

Výsledkom zmien 2. typu SCD sú nové riadky, ktoré predstavujú časovú verziu člena dimenzie. Vždy sa tu nachádza aktuálny riadok verzie, ktorý odráža stav člena dimenzie v zdrojovom systéme. Atribúty historického sledovania v tabuľke dimenzií ukladajú hodnoty, ktoré umožňujú identifikovať aktuálnu verziu (aktuálny príznak je TRUE) a jeho časové obdobie platnosti. Náhradný kľúč je povinný, pretože pri uložení viacerých verzií budú existovať duplicitné prirodzené kľúče.

Ide o bežný typ scd, ale mal by byť rezervovaný pre atribúty, ktoré musia zachovať historickú perspektívu.

Ak je napríklad predajca priradený k inej oblasti predaja, zmena 2. typu SCD zahŕňa operáciu aktualizácie a operáciu vloženia.

1. Operácia aktualizácie prepíše aktuálnu verziu, aby sa nastavili atribúty historického sledovania. Konkrétne je stĺpec koniec platnosti nastavený na dátum spracovania ETL (alebo primeranú časovú pečiatku v zdrojovom systéme) a aktuálny príznak je nastavený na FALSE.
2. Operácia vloženia pridá novú aktuálnu verziu, čím sa stĺpec start validity (doba platnosti) nastaví na hodnotu stĺpca End validity (používanú na aktualizáciu predchádzajúcej verzie) a aktuálny príznak na hodnotu TRUE.

Je dôležité vedieť, že granularita súvisiacich tabuliek faktov nie je na úrovni obchodníka, ale na úrovni verzie predajcu. Súhrn ich historických výsledkov predaja do oblasti bude mať správne výsledky, ale na analýzu budú existovať dve (alebo viaceré) verzie členov predajcov.

Nasledujúci diagram znázorňuje stav pred a za stav člena dimenzie obchodníka, v ktorom sa zmenila jeho oblasť predaja. Keďže organizácia chce analyzovať úsilie predajcov podľa oblasti, ku ktorej sú priradení, spustí sa zmena typu SCD 2.

Prepitné

Keď tabuľka dimenzií podporuje zmeny typu SCD 2, mali by ste zahrnúť atribút označenia, ktorý popisuje člena a verziu. Predstavte si príklad, keď predajca Lynn Tsoflias zo spoločnosti Adventure Works zmení priradenie z austrálskej oblasti predaja do oblasti predaja v Spojenom kráľovstve. Atribút označenia pre prvú verziu by mohol čítať "Lynn Tsoflias (Austrália)" a atribút označenia pre novú, aktuálnu verziu by mohol čítať "Lynn Tsoflias (Veľká Británia)." Ak je to užitočné, môžete do označenia zahrnúť aj dátumy platnosti.

Mali by ste vyvážiť potrebu historickej presnosti a použiteľnosti a efektivity. Snažte sa vyhnúť príliš veľkému počtu zmien SCD 2. typu v tabuľke dimenzií, pretože by to mohlo viesť k obrovskému počtu verzií, ktoré by mohli analytikom sťažiť pochopenie.

Príliš veľa verzií môže tiež znamenať, že meniaci sa atribút môže byť lepšie uložený v tabuľke faktov. Rozšírenie predchádzajúceho príkladu: ak boli časté zmeny oblasti predaja, oblasť predaja by sa mohla uložiť ako kľúč dimenzie v tabuľke faktov, a nie ako implementácia typu SCD 2.

Zvážte nasledujúce atribúty historického sledovania 2. typu SCD.

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,

    <…>
);

Tu sú účely atribútov historického sledovania.

• V stĺpci sa RecChangeDate_FK uloží dátum, kedy zmena nadobudla platnosť. Umožňuje dotazovať, kedy došlo k zmenám.
• Stĺpce RecValidFromKey a RecValidToKey ukladajú platné dátumy platnosti pre riadok. Zvážte uloženie najskoršieho dátumu nájdeného v dimenzii dátumu, RecValidFromKey aby reprezentoval počiatočnú verziu a uloženie 01/01/9999 pre RecValidToKey aktuálne verzie.
• RecReason Stĺpec je voliteľný. Umožňuje zdokumentovať dôvod vloženia verzie. Môže zakódovať, ktoré atribúty sa zmenili, alebo môže ísť o kód zo zdrojového systému, ktorý uvádza konkrétny obchodný dôvod.
• Stĺpec RecIsCurrent umožňuje načítať iba aktuálne verzie. Používa sa vtedy, keď proces ETL vyhľadá kľúče dimenzií pri načítavaní tabuliek faktov.

Poznámka

Niektoré zdrojové systémy neukladajú historické zmeny, preto je dôležité, aby sa dimenzia pravidelne spracovávala, aby sa zistili zmeny a implementovali nové verzie. Týmto spôsobom môžete zistiť zmeny krátko po ich výskyte a ich dátumy platnosti budú presné.

SCD typ 3

Zmeny 3\. typu SCD sledujú obmedzenú históriu s atribútmi. Tento prístup môže byť užitočný vtedy, keď je potrebné zaznamenať poslednú zmenu alebo počet najnovších zmien.

Tento typ SCD zachová obmedzenú historickú perspektívu. Môže to byť užitočné, keď by sa mali uložiť iba počiatočné a aktuálne hodnoty. V tejto inštancii nebudú potrebné priebežné zmeny.

Ak je napríklad predajca priradený k inej oblasti predaja, zmena 3. typu SCD prepíše riadok dimenzie. Stĺpec, ktorý konkrétne ukladá predchádzajúcu oblasť predaja, sa nastaví ako predchádzajúca oblasť predaja a nová oblasť predaja sa nastaví ako aktuálna oblasť predaja.

Nasledujúci diagram znázorňuje stav pred a za stav člena dimenzie obchodníka, v ktorom sa zmenila jeho oblasť predaja. Keďže organizácia chce určiť všetky predchádzajúce priradenia oblasti predaja, spustí zmenu typu SCD 3.

Špeciálni členovia dimenzie

Do dimenzie, ktorá predstavuje chýbajúce, neznáme, neuvádzané alebo chybové stavy, môžete vložiť riadky. Môžete napríklad použiť nasledujúce hodnoty náhradného kľúča.

Hodnota kľúča	Účel
0	Chýba (nie je k dispozícii v zdrojovom systéme)
-1	Neznámy (zlyhanie vyhľadávania počas načítavania tabuľky faktov)
-2	Neuvádza sa (nedá sa použiť)
-3	Chyba

Kalendár a čas

Tabuľky faktov takmer bez výnimky ukladajú mierky v konkrétnych časových bodoch. Ak chcete podporiť analýzu podľa dátumu (a prípadne času), musia existovať dimenzie kalendára (dátumu a času).

Je nezvyčajné, že zdrojový systém bude mať údaje dimenzie kalendára, takže ich bude potrebné vygenerovať v sklade údajov. Zvyčajne sa vygeneruje raz a ak ide o dimenziu kalendára, v prípade potreby sa rozšíri o budúce dátumy.

Dimenzia dátumu

Dimenzia dátumu (alebo kalendára) je najbežnejšou dimenziou použitou na analýzu. Ukladá jeden riadok na dátum a podporuje spoločnú požiadavku na filtrovanie alebo zoskupovanie podľa konkrétnych období dátumov, ako sú napríklad roky, štvrťroky alebo mesiace.

Dôležité

Dimenzia dátumu by nemala obsahovať vlákno, ktoré sa predlžuje na dennú dobu. Ak sa vyžaduje analýza času dňa, mali by ste mať dátumovú aj časovú dimenziu (popis nižšie). Tabuľky faktov, ktoré ukladajú fakty o čase dňa, by mali mať dva cudzie kľúče, jeden ku každej z týchto dimenzií.

Prirodzeným kľúčom dimenzie dátumu by mal byť typ údajov Dátum . Náhradný kľúč by mal ukladať dátum pomocou YYYYMMDD formátu a typu údajov int . Táto prijatá prax by mala byť jedinou výnimkou (spolu s časovou dimenziou), keď má hodnota náhradného kľúča význam a je čitateľná ľuďmi. Ukladanie YYYYMMDD ako typu int údajov je nielen efektívne a zoradené číselne, ale aj v súlade s jednoznačným formátom dátumov International Standards Organization (ISO) 8601.

Tu sú niektoré bežné atribúty, ktoré možno zahrnúť do dimenzie dátumu.

• Year, Quarter, Month, Day
• QuarterNumberInYear, MonthNumberInYear – ktorý môže byť potrebný na zoradenie textov.
• FiscalYear, FiscalQuarter – niektoré podnikové účtovné plány sa začínajú v polovici roka, takže začiatok/koniec kalendárneho roka a fiškálny rok sa líšia.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear – ktorý môže byť potrebný na zoradenie textov.
• WeekOfYear – existuje niekoľko spôsobov označenia týždňa v roku vrátane normy ISO, ktorá má buď 52, alebo 53 týždňov.
• IsHoliday– HolidayText ak vaša organizácia pracuje vo viacerých geografických oblastiach, mali by ste uchovávať viacero množín dovolenkových zoznamov, ktoré si každá geografia všimne ako samostatnú dimenziu alebo naturalizovanú vo viacerých atribútoch v dimenzii dátumov. Pridanie atribútu HolidayText by mohlo pomôcť identifikovať sviatky na vytváranie zostáv.
• IsWeekday – podobne, v niektorých geografických oblastiach bežný pracovný týždeň nie je od pondelka do piatka. Pracovný týždeň je napríklad od nedele do štvrtka v mnohých oblastiach Blízkeho východu, zatiaľ čo iné oblasti zamestnávajú štvordňový alebo šesťdňový pracovný týždeň.
• LastDayOfMonth
• RelativeYearOffset, RelativeQuarterOffset, RelativeMonthOffset, RelativeDayOffset – ktoré môže byť potrebné na podporu filtrovania relatívnych dátumov (napríklad za predchádzajúci mesiac). Aktuálne obdobia používajú posun nuly (0); predchádzajúce obdobia ukladajú posuny -1, -2, -3...; budúce obdobia uchovávajte posuny od 1, 2, 3....

Rovnako ako v prípade každej dimenzie, dôležité je to, že obsahuje atribúty, ktoré podporujú známe požiadavky na filtrovanie, zoskupenie a hierarchiu. Môžu existovať aj atribúty, ktoré ukladajú preklady označení do iných jazykov.

Keď sa dimenzia použije na vytvorenie vzťahu so faktami s vyššou granularnou, tabuľka faktov môže použiť prvý dátum dátumového obdobia. Napríklad tabuľka faktov s cieľom predaja, ktorá ukladá ciele štvrťročných predajcov, by ukladala prvý dátum v štvrťroku v dimenzii dátumu. Alternatívnym prístupom je vytvorenie kľúčových stĺpcov v tabuľke dátumov. Kľúč štvrťroka môže napríklad uložiť kľúč štvrťroka pomocou YYYYQ formátu a menšieho typu údajov.

Dimenzia by mala byť vyplnená známym rozsahom dátumov, ktoré používajú všetky tabuľky faktov. Mala by obsahovať aj budúce dátumy, keď sklad údajov uchováva fakty o cieľoch, rozpočtoch alebo prognózach. Rovnako ako v prípade iných dimenzií, môžete zahrnúť riadky, ktoré predstavujú chýbajúce, neznáme, neuvádzané alebo chybové situácie.

Prepitné

Vyhľadajte na internete položku "date dimension generator" (generátor dimenzií dátumu) a vyhľadajte skripty a tabuľkové hárky, ktoré generujú údaje dátumu.

Proces ETL by mal zvyčajne na začiatku budúceho roka predĺžiť riadky dimenzie dátumu na konkrétny počet rokov dopredu. Keď dimenzia obsahuje atribúty relatívneho posunu, proces ETL musí byť spustený denne, aby sa aktualizovali hodnoty atribútov posunu na základe aktuálneho dátumu (dnes).

Časová dimenzia

Niekedy je potrebné fakty uchovávať v určitom časovom okamihu (napríklad v čase dňa). V tomto prípade vytvorte dimenziu času (alebo hodín). Môže mať množstvo minút (24 x 60 = 1 440 riadkov) alebo dokonca sekundy (24 x 60 x 60 = 86 400 riadkov). Ostatné možné zrná zahŕňajú pol hodiny alebo hodiny.

Prirodzeným kľúčom časovej dimenzie by mal byť typ časových údajov. Náhradný kľúč by mohol používať vhodný formát a ukladať hodnoty, ktoré majú význam a sú čitateľné pre ľudí, napríklad pomocou formátu HHMM alebo HHMMSS .

Tu sú niektoré bežné atribúty, ktoré treba zahrnúť do časovej dimenzie.

• Hour, HalfHour, QuarterHour, Minute
• Označenia časovej periódy (ráno, popoludní, večer, noc)
• Pracovné názvy zmien
• Vlajky na vrchole alebo mimo neho

Vyhovujúce dimenzie

Niektoré dimenzie môžu byť v súlade s rozmermi. Vyhovujúce dimenzie sa vzťahujú k mnohým tabuľkám faktov, a preto sú zdieľané viacerými hviezdami v dimenzionálnom modeli. Poskytujú konzistenciu a môžu vám pomôcť znížiť priebežný vývoj a údržbu.

Je napríklad typické, že tabuľky faktov ukladajú aspoň jeden kľúč dimenzie dátumu (pretože aktivita sa takmer vždy zaznamenáva podľa dátumu a/alebo času). Z tohto dôvodu je dimenzia dátumu bežnou zodpovedajúcou dimenziou. Preto by ste mali zabezpečiť, aby dimenzia dátumov obsahuje atribúty relevantné pre analýzu všetkých tabuliek faktov.

Nasledujúci diagram zobrazuje tabuľku faktov Sales a tabuľku faktov Inventory . Každá tabuľka faktov sa vzťahuje na dimenziu a Product dimenziuDate, ktoré sú v súlade dimenziami.

Ďalším príkladom je to, že zamestnanec a používatelia môžu byť v rovnakej množine ľudí. V tomto prípade môže mať zmysel skombinovať atribúty každej entity tak, aby vytvorili jednu zodpovedajúcu dimenziu.

Dimenzie zohrávajúce roly

Keď sa na dimenziu v tabuľke faktov odkazuje viackrát, označuje sa ako dimenzia, ktorá má funkciu roly.

Ak napríklad tabuľka faktov predaja obsahuje dátum objednávky, dátum odoslania a kľúče dimenzie dátumu doručenia, dimenzia dátumu sa vzťahuje tromi spôsobmi. Každý spôsob predstavuje jedinečnú rolu, no existuje len jedna dimenzia fyzického dátumu.

Nasledujúci diagram znázorňuje tabuľku Flight faktov. Dimenzia Airport má funkciu roly, pretože dvakrát súvisí s tabuľkou faktov ako dimenziou Departure Airport a dimenziou Arrival Airport .

Nevyžiadané dimenzie

Nevyžiadaná dimenzia je užitočná v prípade množstva nezávislých dimenzií, najmä ak obsahujú niekoľko atribútov (možno jeden), a keď majú tieto atribúty nízku kardinalitu (niekoľko hodnôt). Cieľom nevyžiadanej dimenzie je zlúčiť mnohé malé dimenzie do jednej dimenzie. Tento prístup k návrhu môže znížiť počet dimenzií a znížiť počet kľúčov tabuľky faktov, a tým aj veľkosť úložiska tabuľky faktov. Pomáhajú tiež zmenšiť nepotrebné položky na table Údaje, pretože prezentujú menej tabuliek používateľom.

Tabuľka nevyžiadanej dimenzie zvyčajne ukladá kartézsky súčin všetkých hodnôt atribútu dimenzie s atribútom náhradného kľúča.

Dobrými kandidátmi sú vlajky a ukazovatele, stav objednávok a demografické stavy zákazníkov (pohlavie, veková skupina a ďalšie).

Nasledujúci diagram znázorňuje nevyžiadanú dimenziu s názvom Sales Status , ktorá kombinuje hodnoty stavu objednávky a hodnoty stavu doručenia.

Dimenzie faktov

Dimenzia faktov sa môže vyskytnúť, keď je dimenzia na rovnakom množstve ako súvisiace fakty. Bežným príkladom dimenzie faktov je dimenzia čísla predajnej objednávky, ktorá sa týka tabuľky faktov predaja. Číslo faktúry je zvyčajne jedným ne hierarchickým atribútom v tabuľke faktov. V akceptovanom postupe teda nie je možné tieto údaje skopírovať a vytvoriť tak samostatnú tabuľku dimenzií.

Nasledujúci diagram znázorňuje dimenziu Sales Order , ktorá je degenerovanou dimenziou založenou na SalesOrderNumber stĺpci v tabuľke faktov predaja. Táto dimenzia sa implementuje ako zobrazenie, ktoré načíta rozdielne hodnoty číselných predajných objednávok.

Prepitné

V sklade tkaniny je možné vytvoriť zobrazenie, ktoré prezentuje dimenziu faktov ako dimenziu na účely dotazovania.

Z perspektívy sémantického modelovania služby Power BI je možné vytvoriť zdegenerovanú dimenziu ako samostatnú tabuľku pomocou doplnku Power Query. Týmto spôsobom je sémantický model v súlade s osvedčenými postupmi, že polia používané na filtrovanie alebo zoskupovanie pochádzajú z tabuliek dimenzií a polia používané na sumarizáciu faktov pochádzajú z tabuliek faktov.

Outrigger dimenzie

Keď sa tabuľka dimenzií vzťahuje na iné tabuľky dimenzií, označuje sa ako outrigger dimenzia. Outrigger dimenzia môže pomôcť v súlade a opätovne použiť definície v dimenzionálnom modeli.

Môžete napríklad vytvoriť dimenziu geografie, ktorá ukladá geografické miesta pre každé PSČ. Na túto dimenziu by potom mohla odkazovať dimenzia zákazníka a dimenzia obchodníka, v ktorej by sa ukladal náhradný kľúč dimenzie geografie. Týmto spôsobom budú môcť byť zákazníci a predajcovia analyzovaní pomocou konzistentných geografických miest.

Nasledujúci diagram znázorňuje dimenziu Geography , ktorá je outrigger dimenziou. Nevzťahuje sa priamo na tabuľku Sales faktov. Namiesto toho nepriamo súvisí prostredníctvom dimenzie Customer a dimenzie Salesperson .

Zoberme si, že dimenziu dátumu možno použiť ako outrigger dimenziu, keď iné atribúty tabuľky dimenzií ukladajú dátumy. Napríklad dátum narodenia v dimenzii zákazníka možno uložiť pomocou náhradného kľúča tabuľky dimenzií dátumu.

Multihodnotové dimenzie

Keď atribút dimenzie musí uchovávať viacero hodnôt, musíte navrhnúť dimenziu s viacerými hodnotami. Dimenziu s viacerými hodnotami implementujete vytvorením tabuľky mosta (niekedy nazývanej tabuľka spojenia). Tabuľka mosta ukladá vzťah typu many-to-many medzi entitami.

Predpokladajme napríklad, že existuje dimenzia predajcov a každý predajca je priradený k jednej alebo aj viacerým oblastiam predaja. V tomto prípade má zmysel vytvoriť dimenziu oblasti predaja. Táto dimenzia ukladá každú oblasť predaja iba raz. Samostatná tabuľka, známa ako tabuľka mosta, ukladá riadok pre každý vzťah predajcu a oblasti predaja. Fyzicky existuje vzťah one-to-many od dimenzie obchodníka po tabuľku mosta a ďalší vzťah typu one-to-many z dimenzie oblasti predaja po tabuľku mosta. Logicky existuje vzťah typu many-to-many medzi predajcami a oblasťami predaja.

V nasledujúcom diagrame Account sa tabuľka dimenzií vzťahuje na tabuľku Transaction faktov. Keďže zákazníci môžu mať viacero kont a kont môžu mať viacerých zákazníkov, tabuľka dimenzií Customer je súvisiaca prostredníctvom tabuľky mosta Customer Account .

Súvisiaci obsah

V nasledujúcom článku v tejto sérii získate informácie o sprievodnom materiáli a najvhodnejších postupoch pri navrhovaní tabuliek faktov.