Dimenziómodellezés a Microsoft Fabric Warehouse-ban: Dimenziótáblák

A következőkre vonatkozik:✅ SQL Analytics-végpont és Warehouse a Microsoft Fabricben

Feljegyzés

Ez a cikk a dimenziómodellezési cikksorozat részét képezi. Ez a sorozat a Microsoft Fabric Warehouse-beli dimenziómodellezéssel kapcsolatos útmutatásra és tervezési ajánlott eljárásokra összpontosít.

Ez a cikk útmutatást és ajánlott eljárásokat tartalmaz dimenziótáblák dimenziómodellben való tervezéséhez. Gyakorlati útmutatást nyújt a Microsoft Fabric Warehouse-hoz, amely számos T-SQL-képességet támogat, például táblákat hozhat létre és adatokat kezelhet a táblákban. Így teljes mértékben szabályozhatja a dimenziómodell-táblák létrehozását és adatokkal való betöltését.

Feljegyzés

Ebben a cikkben az adattárház kifejezés egy vállalati adattárházra utal, amely a kritikus fontosságú adatok átfogó integrációját biztosítja a szervezetben. Ezzel szemben az önálló kifejezésraktár egy Fabric Warehouse-ra utal, amely egy szolgáltatott szoftver (SaaS) relációs adatbázis, amelyet az adattárház implementálásához használhat. Az egyértelműség kedvéért ebben a cikkben az utóbbit Fabric Warehouse-ként említik.

Tipp.

Ha tapasztalatlan a dimenziómodellezésben, első lépésként tekintse meg ezt a cikksorozatot. Nem célja a dimenziómodellezés tervezésének teljes körű megvitatása. További információkért tekintse meg közvetlenül a széles körben elfogadott közzétett tartalmakat, például Ralph Kimball és mások által kiadott The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3. kiadás, 2013).

A dimenziómodellekben a dimenziótáblák az üzleti és elemzési követelményeknek megfelelő entitást ismertetik. A dimenziótáblák általában a modellel kapcsolatos dolgokat jelölik. A dolgok lehetnek termékek, személyek, helyek vagy bármely más fogalom, beleértve a dátumot és az időt. A dimenziótáblák egyszerű azonosításához általában előtaggal kell előtagot adni a nevüknekd_.Dim_

Dimenziótábla felépítése

A dimenziótáblák szerkezetének leírásához tekintse meg a következő példát egy értékesítői dimenziótáblára.d_Salesperson Ez a példa a helyes tervezési eljárásokat alkalmazza. Az oszlopcsoportok mindegyikét a következő szakaszok ismertetik.

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
);

Helyettes kulcs

A minta dimenziótábla egy helyettesítő kulccsal rendelkezik, amelynek neve Salesperson_SK. A helyettesítő kulcs egy egyoszlopos egyedi azonosító, amely a dimenziótáblában van létrehozva és tárolva. Ez egy elsődleges kulcsoszlop , amely a dimenziómodell más tábláihoz kapcsolódik.

A helyettesítő kulcsok arra törekszenek, hogy elszigetelik az adattárházat a forrásadatok változásaitól. Emellett számos egyéb előnyt is biztosítanak, amelyek lehetővé teszik a következőket:

• Több adatforrás összevonása (elkerülve az ismétlődő azonosítók ütközését).
• Összevonja a többoszlopos természetes kulcsokat egy hatékonyabb, egyoszlopos kulcsba.
• A dimenzióelőzmények nyomon követése lassan változó 2. dimenziótípussal.
• A ténytábla szélességének korlátozása a tárolás optimalizálásához (a lehető legkisebb egész adattípus kiválasztásával).

A helyettesítő kulcs oszlop ajánlott gyakorlat, még akkor is, ha egy természetes kulcs (leírt következő) úgy tűnik, elfogadható jelölt. Ne adjon jelentést a kulcsértékek számára (kivéve a dátum- és idődimenzió-kulcsokat, a későbbiekben leírtak szerint).

Természetes kulcsok

A minta dimenziótáblája egy természetes kulccsal is rendelkezik, amelynek neve EmployeeID. A természetes kulcs a forrásrendszerben tárolt kulcs. Lehetővé teszi a dimenzióadatok forrásrendszerhez való összekapcsolásához, amelyet általában egy kinyerési, betöltési és átalakítási folyamat (ETL) végez a dimenziótábla betöltéséhez. A természetes kulcsokat néha üzleti kulcsnak nevezik, és értékei az üzleti felhasználók számára is hasznosak lehetnek.

Néha a dimenziók nem rendelkeznek természetes kulccsal. Ez lehet a dátum- vagy keresési dimenziók esetében, vagy ha egy lapos fájl normalizálásával hoz létre dimenzióadatokat.

Dimenzióattribútumok

A mintául szolgáló dimenziótáblák dimenzióattribútumokkal is rendelkeznek, például az FirstName oszlopmal. A dimenzióattribútumok kontextust biztosítanak a kapcsolódó ténytáblákban tárolt numerikus adatokhoz. Ezek általában szöveges oszlopok, amelyeket elemzési lekérdezésekben használnak szűrésre és csoportosításra (szeletek és kockák), de önmagukban nem összesíthetők. Egyes dimenziótáblák kevés attribútumot tartalmaznak, míg mások számos attribútumot tartalmaznak (a dimenziómodell lekérdezési követelményeinek támogatásához annyi, amennyi szükséges).

Tipp.

Jó módszer annak meghatározására, hogy mely dimenziókra és attribútumokra van szüksége, ha megkeresi a megfelelő személyeket, és felteheti a megfelelő kérdéseket. Pontosabban, maradjon éber a szó említése miatt. Ha például valaki azt mondja, hogy az értékesítéseket értékesítők, hónap és termékkategória szerint kell elemeznie, azt mondják, hogy olyan dimenziókra van szükségük, amelyek rendelkeznek ezekkel az attribútumokkal.

Ha Direct Lake szemantikai modellt szeretne létrehozni, a szűréshez és csoportosításhoz szükséges összes lehetséges oszlopot dimenzióattribútumként kell tartalmaznia. Ennek az az oka, hogy a Direct Lake szemantikai modelljei nem támogatják a számított oszlopokat.

Idegen kulcsok

A mintául szolgáló dimenziótábla egy idegen kulccsal is rendelkezik, amelynek neve SalesRegion_FK. Más dimenziótáblák hivatkozhatnak egy idegen kulcsra, és a dimenziótáblákban való jelenlétük különleges eset. Azt jelzi, hogy a tábla egy másik dimenziótáblához kapcsolódik, ami azt jelenti, hogy egy hópehely dimenzió részét képezheti, vagy egy kivándorló dimenzióhoz kapcsolódik.

A Fabric Warehouse támogatja a külső kulcsokra vonatkozó korlátozásokat , de nem kényszeríthetők ki. Ezért fontos, hogy az ETL-folyamat tesztelje a kapcsolódó táblák közötti integritást az adatok betöltésekor.

Még mindig jó ötlet idegen kulcsokat létrehozni. A nem kényszerített idegen kulcsok létrehozásának egyik jó oka, ha engedélyezi a modellezési eszközöknek, például a Power BI Desktopnak, hogy automatikusan észleljék és létrehozzák a szemantikai modell táblái közötti kapcsolatokat.

Előzménykövetési attribútumok

A minta dimenziótáblája különböző előzménykövetési attribútumokkal is rendelkezik. Az előzménykövetési attribútumok nem kötelezőek, mivel a forrásrendszerben bekövetkező konkrét változások nyomon követésére van szükség. Lehetővé teszik az értékek tárolását az adattárház elsődleges szerepének támogatásához, amely a múlt pontos leírása. Ezek az attribútumok az előzménykörnyezetet tárolják, mivel az ETL-folyamat betölti az új vagy módosított adatokat a dimenzióba.

További információ: Korábbi változások kezelése a cikk későbbi részében.

Naplózási attribútumok

A mintául szolgáló dimenziótábla különböző naplózási attribútumokkal is rendelkezik. A naplózási attribútumok nem kötelezőek, de ajánlottak. Lehetővé teszik a dimenziórekordok létrehozásának és módosításának nyomon követését, valamint az ETL-folyamatok során felmerülő diagnosztikai vagy hibaelhárítási információkat. Például nyomon kell követnie, hogy ki (vagy milyen folyamat) frissített egy sort, és mikor. A naplózási attribútumok segíthetnek diagnosztizálni egy kihívást jelentő problémát is, például ha egy ETL-folyamat váratlanul leáll. A dimenziótagokat hibaként vagy kikövetkezési tagként is megjelölhetik.

Dimenziótábla mérete

A dimenziómodellek leg hasznosabb és legsokoldalúbb dimenziói gyakran nagy, széles dimenziók. Nagyok a sorok (több milliót meghaladó) és a dimenzióattribútumok száma (akár több száz) szempontjából is. A méret nem annyira fontos (bár a lehető legkisebb méretre kell terveznie és optimalizálnia). A lényeg az, hogy a dimenzió támogatja a tényadatok szükséges szűrését, csoportosítását és pontos előzményelemzését.

A nagy méretek több forrásrendszerből is származhatnak. Ebben az esetben a dimenziófeldolgozásnak egyesítenie, egyesítenie, deduplikálnia és szabványosítania kell az adatokat; és helyettes kulcsokat rendelhet hozzá.

Összehasonlításképpen néhány dimenzió kicsi. Ezek olyan keresési táblákat jelölhetnek, amelyek csak több rekordot és attribútumot tartalmaznak. Ezek a kis dimenziók gyakran ténytáblákban tárolják a tranzakciókhoz kapcsolódó kategóriaértékeket, és a tényrekordokhoz kapcsolódó helyettesítő kulcsokkal rendelkező dimenziókként vannak implementálva.

Tipp.

Ha sok kis dimenzióval rendelkezik, érdemes lehet egy levélszemét dimenzióba egyesíteni őket.

Dimenziótervezési fogalmak

Ez a szakasz a dimenziótervezés különböző fogalmait ismerteti.

Denormalizálás és normalizálás

Szinte mindig előfordul, hogy a dimenziótáblákat denormalizálni kell. Bár a normalizálás az ismétlődő adatok csökkentése érdekében tárolt adatok leírására használt kifejezés, a denormalizálás az előre lefordított redundáns adatok meglétének meghatározására használatos kifejezés. A redundáns adatok általában a (később tárgyalt) hierarchiák tárolása miatt léteznek, ami azt jelenti, hogy a hierarchiák összesimulnak. A termékdimenziók például alkategóriákat (és kapcsolódó attribútumokat) és kategóriát (és kapcsolódó attribútumait) tárolhatnak.

Mivel a dimenziók általában kicsik (a ténytáblákkal összehasonlítva), a redundáns adatok tárolásának költsége szinte mindig meghaladja a jobb lekérdezési teljesítményt és használhatóságot.

Hópehely méretei

A denormalizálás egyik kivétele a hópehely méretének megtervezése. A hópehely dimenzió normalizálva van, és több kapcsolódó táblában tárolja a dimenzióadatokat.

Az alábbi ábra egy hópehelydimenziót ábrázol, amely három kapcsolódó dimenziótáblából áll: Product, Subcategoryés Category.

Fontolja meg a hópehely dimenzió implementálását, ha:

• A méret rendkívül nagy, és a tárolási költségek meghaladják a nagy lekérdezési teljesítmény szükségességét. (Azonban rendszeresen újraértékeli, hogy ez továbbra is így marad.)
• Kulcsok szükségesek a dimenzió magasabb szintű tényekhez való kapcsolásához. Az értékesítési ténytábla például termékszinten tárolja a sorokat, de az értékesítési cél ténytáblája alkategóriás szinten tárolja a sorokat.
• A korábbi változásokat a részletesség magasabb szintjein kell nyomon követnie.

Feljegyzés

Ne feledje, hogy a Power BI szemantikai modell hierarchiája csak egyetlen szemantikai modelltáblából származó oszlopokon alapulhat. Ezért a hópehely dimenziónak denormalizált eredményt kell eredményeznie egy olyan nézet használatával, amely összekapcsolja a hópehelytáblákat.

Hierarchiák

A dimenzióoszlopok általában hierarchiát hoznak létre. A hierarchiák lehetővé teszik az adatok különböző összegzési szinteken történő feltárását. Egy mátrixvizualizáció kezdeti nézete például éves értékesítéseket jeleníthet meg, és a jelentésfelhasználó dönthet úgy, hogy részletezi a negyedéves és havi értékesítéseket.

A hierarchiák háromféleképpen tárolhatók egy dimenzióban. A következőket használhatja:

• Oszlopok egyetlen, denormalizált dimenzióból.
• Egy hópehely dimenzió, amely több kapcsolódó táblából áll.
• Szülő-gyermek (önhivatkozás) kapcsolat egy dimenzióban.

A hierarchiák kiegyensúlyozottak vagy kiegyensúlyozatlanok lehetnek. Fontos tisztában lenni azzal is, hogy egyes hierarchiák nincsenek raggedva.

Kiegyensúlyozott hierarchiák

A kiegyensúlyozott hierarchiák a leggyakoribb hierarchiatípusok. A kiegyensúlyozott hierarchia ugyanannyi szinttel rendelkezik. A kiegyensúlyozott hierarchia gyakori példája egy dátumdimenzió naptárhierarchiája, amely az év, a negyedév, a hónap és a dátum szintjeit tartalmazza.

Az alábbi ábra az értékesítési régiók kiegyensúlyozott hierarchiáját mutatja be. Ez két szintből áll, amelyek az értékesítési régió csoportja és az értékesítési régió.

A kiegyensúlyozott hierarchia szintjei vagy egyetlen, denormalizált dimenzióból származó oszlopokon, vagy hópehelydimenziót alkotó táblákon alapulnak. Egyetlen denormalizált dimenzió alapján a magasabb szinteket képviselő oszlopok redundáns adatokat tartalmaznak.

A kiegyensúlyozott hierarchiák esetében a tények mindig a hierarchia egyetlen szintjére vonatkoznak, amely általában a legalacsonyabb szint. Így a tények összesíthetők (összesíthetők) a hierarchia legmagasabb szintjére. A tények bármilyen szinthez kapcsolódhatnak, amelyet a ténytábla szemcséje határoz meg. Előfordulhat például, hogy az értékesítési ténytábla dátumszinten van tárolva, míg az értékesítési cél ténytáblája negyedéves szinten tárolható.

Kiegyensúlyozatlan hierarchiák

A kiegyensúlyozatlan hierarchiák kevésbé gyakori hierarchiatípusok. A kiegyensúlyozatlan hierarchia szintjei szülő-gyermek kapcsolaton alapulnak. Ezért a kiegyensúlyozatlan hierarchia szintjeinek számát a dimenziósorok határozzák meg, nem pedig a dimenziótábla adott oszlopai.

A kiegyensúlyozatlan hierarchia gyakori példája az alkalmazotti hierarchia, amelyben az alkalmazotti dimenzió minden sora ugyanabban a táblában lévő jelentéskezelő sorhoz kapcsolódik. Ebben az esetben bármely alkalmazott lehet a jelentéskészítő alkalmazottakkal rendelkező vezető. A hierarchia egyes ágai természetesen több szinttel rendelkeznek, mint mások.

Az alábbi ábra egy kiegyensúlyozatlan hierarchiát ábrázol. Négy szintből áll, és a hierarchia minden tagja értékesítő. Figyelje meg, hogy az értékesítőknek eltérő számú elődjük van a hierarchiában attól függően, hogy kinek jelentenek.

A kiegyensúlyozatlan hierarchiákra más gyakori példák az anyagjegyzék, a vállalati tulajdonjogi modellek és a főkönyv.

A kiegyensúlyozatlan hierarchiák esetében a tények mindig a dimenzió szemcséihez kapcsolódnak. Az értékesítési tények például különböző értékesítőkre vonatkoznak, akik különböző jelentési struktúrával rendelkeznek. A dimenziótábla egy helyettesítő kulccsal (névvel Salesperson_SK) és egy ReportsTo_Salesperson_FK idegenkulcs-oszloptal rendelkezik, amely az elsődleges kulcs oszlopára hivatkozik. A hierarchia egyik ágának legalsó szintjén sem feltétlenül minden üzletkötőt kezelhet anélkül, hogy bárkit kezelhet. Ha nem a legalacsonyabb szinten vannak, előfordulhat, hogy egy értékesítő termékeket ad el, és jelentéskészítési értékesítővel rendelkezik, akik szintén termékeket árulnak. Tehát a tényadatok összesítésének figyelembe kell vennie az egyes értékesítőket és az összes leszármazottjukat.

A szülő-gyermek hierarchiák lekérdezése összetett és lassú lehet, különösen nagy méretek esetén. Bár a forrásrendszer szülő-gyermek kapcsolatként tárolhatja a kapcsolatokat, javasoljuk, hogy honosítsa a hierarchiát . Ebben a példában a honosítás azt jelenti, hogy a hierarchiaszinteket oszlopként alakítja át és tárolja a dimenzióban.

Tipp.

Ha úgy dönt, hogy nem honosítja a hierarchiát, akkor is létrehozhat egy hierarchiát szülő-gyermek kapcsolat alapján egy Power BI szemantikai modellben. Ez a megközelítés azonban nem ajánlott nagy méretek esetén. További információ: A szülő-gyermek hierarchiák függvényeinek ismertetése a DAX-ban.

Hézagos hierarchiák

A hierarchia néha azért van felrázva , mert a hierarchia egy tagjának szülője olyan szinten létezik, amely nem közvetlenül felette van. Ezekben az esetekben a hiányzó szintértékek megismétlik a szülő értékét.

Vegyük példaként a kiegyensúlyozott földrajzi hierarchiát. A ragged hierarchia akkor létezik, ha egy ország/régió nem rendelkezik államokkal vagy tartományokkal. Új-Zélandnak például nincs sem állama, sem tartománya. Az új-zélandi sor beszúrásakor tehát az ország/régió értékét is az oszlopban StateProvince kell tárolnia.

Az alábbi ábra a földrajzi régiók ragged hierarchiáját ábrázolja.

Előzménymódosítás kezelése

Szükség esetén az előzménymódosítás egy lassan változó dimenzió (SCD) implementálásával kezelhető. Az SCD új vagy módosított adatokként tartja karban az előzménykörnyezetet.

Íme a leggyakoribb SCD-típusok.

• 1. típus: Írja felül a meglévő dimenziótagot.
• 2. típus: Új, időalapú verziójú dimenziótag beszúrása.
• 3. típus: Korlátozott előzmények nyomon követése attribútumokkal.

Lehetséges, hogy egy dimenzió támogatja az SCD 1. és a 2. típusú SCD-módosításokat is.

A 3. SCD-típust nem gyakran használják, részben azért, mert szemantikai modellben nehéz használni. Alaposan gondolja át, hogy az SCD 2-es típusú megközelítése jobb-e.

Tipp.

Ha egy gyorsan változó dimenzióra számít, amely egy olyan dimenzió, amelynek attribútuma gyakran változik, vegye fel ezt az attribútumot a ténytáblába. Ha az attribútum numerikus, például a termékárat, mértékként is hozzáadhatja a ténytáblában. Ha az attribútum szöveges érték, létrehozhat egy dimenziót az összes szöveges érték alapján, és hozzáadhatja annak dimenziókulcsát a ténytáblához.

1. SCD-típus

Az 1. típusú SCD-módosítások felülírják a meglévő dimenziósort, mert nincs szükség a változások nyomon követésére. Ez az SCD-típus a hibák kijavítására is használható. Ez az SCD egy gyakori típusa, és a legtöbb változó attribútumhoz, például az ügyfélnévhez, az e-mail-címhez és másokhoz kell használni.

Az alábbi ábra egy értékesítő dimenziótag előtti és utáni állapotát ábrázolja, ahol a telefonszámuk megváltozott.

Ez az SCD-típus nem őrzi meg a korábbi perspektívát, mert a meglévő sor frissül. Ez azt jelenti, hogy az 1. típusú SCD-módosítások különböző magasabb szintű összesítéseket eredményezhetnek. Ha például egy értékesítő egy másik értékesítési régióhoz van rendelve, egy 1. típusú SCD-módosítás felülírná a dimenziósort. Az értékesítők korábbi értékesítési eredményeinek összesítése a régióban más eredményt eredményezne, mivel most az új aktuális értékesítési régiót használja. Mintha az értékesítő mindig az új értékesítési régióhoz lett volna rendelve.

SCD 2-es típus

A 2. típusú SCD-módosítások olyan új sorokat eredményeznek, amelyek egy dimenziótag időalapú verzióját jelölik. Mindig van egy aktuális verziós sor, amely a forrásrendszer dimenziótagjának állapotát tükrözi. A dimenziótábla-tároló értékeinek előzménykövetési attribútumai lehetővé teszik az aktuális verzió (jelenlegi jelölő TRUE) és érvényességi időtartamának azonosítását. Helyettesítő kulcsra van szükség, mert több verzió tárolásakor ismétlődő természetes kulcsok lesznek.

Ez az SCD egy gyakori típusa, de olyan attribútumok számára kell fenntartani, amelyeknek meg kell őriznie a történeti perspektívát.

Ha például egy értékesítő egy másik értékesítési régióhoz van rendelve, az SCD 2. típusú módosítása frissítési művelettel és beszúrási művelettel jár.

1. A frissítési művelet felülírja az aktuális verziót az előzménykövetési attribútumok beállításához. Pontosabban a záró érvényesség oszlop az ETL feldolgozási dátumra (vagy a forrásrendszer megfelelő időbélyegére) van állítva FALSE, és az aktuális jelző értéke .
2. A beszúrási művelet egy új, aktuális verziót ad hozzá, amely a kezdő érvényességi oszlopot a záró érvényességi oszlop értékére állítja (a korábbi verzió frissítésére szolgál), és a jelenlegi jelölőt a következőre TRUEállítja.

Fontos tisztában lenni azzal, hogy a kapcsolódó ténytáblák részletessége nem az üzletkötő szintjén, hanem az értékesítői verzió szintjén van. A régióra vonatkozó korábbi értékesítési eredmények összesítése helyes eredményeket fog eredményezni, de két (vagy több) értékesítői tagverziót kell elemezni.

Az alábbi ábra egy értékesítő dimenziótag előtti és utáni állapotát mutatja be, ahol az értékesítési régiójuk megváltozott. Mivel a szervezet elemezni szeretné az értékesítők azon régiója által tett erőfeszítéseket, amelyhez hozzá vannak rendelve, egy 2. típusú SCD-módosítást indít el.

Tipp.

Ha egy dimenziótábla támogatja a 2. típusú SCD-módosításokat, a tagot és a verziót leíró címkeattribútumot kell tartalmaznia. Vegyünk egy példát, amikor Lynn Tsoflias, az Adventure Works értékesítője az ausztrál értékesítési régióból az Egyesült Királyság értékesítési régiójába módosítja a hozzárendelést. Az első verzió címkeattribútuma a "Lynn Tsoflias (Ausztrália)" szöveget, az új, jelenlegi verzió címkeattribútumát pedig a "Lynn Tsoflias (Egyesült Királyság)" szöveget olvashatja. Ha hasznos, az érvényességi dátumokat is belefoglalhatja a címkébe.

Ki kell egyensúlyoznia a történelmi pontosság és a használhatóság és a hatékonyság iránti igényt. Próbálja meg elkerülni, hogy túl sok 2. típusú SCD-módosítás legyen egy dimenziótáblán, mert az túl sok verziót eredményezhet, ami megnehezítheti az elemzők számára a megértést.

A túl sok verzió azt is jelezheti, hogy egy változó attribútum jobban tárolható a ténytáblában. A korábbi példa kiterjesztése, ha az értékesítési régió változásai gyakoriak voltak, az értékesítési régió dimenziókulcsként tárolható a ténytáblában ahelyett, hogy 2. típusú SCD-típust implementálna.

Vegye figyelembe a következő 2. típusú SCD előzménykövetési attribútumokat.

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,

    <…>
);

Az előzménykövetési attribútumok céljai az alábbiak.

• Az RecChangeDate_FK oszlop tárolja a módosítás érvénybe lépésének dátumát. Ez lehetővé teszi, hogy lekérdezhesse a módosítások megtörténtét.
• Az RecValidFromKey oszlopok a RecValidToKey sor érvényes érvényességi dátumát tárolják. Fontolja meg a dátum dimenzióban található legkorábbi dátum tárolását a kezdeti verzió megjelenítéséhezRecValidFromKey, valamint az RecValidToKey aktuális verziók tárolásához01/01/9999.
• Az RecReason oszlop nem kötelező. Lehetővé teszi a verzió beszúrásának okának dokumentálását. Kódolhatja, hogy mely attribútumok változtak meg, vagy a forrásrendszerből származó kód, amely egy adott üzleti okot jelez.
• Az RecIsCurrent oszlop lehetővé teszi, hogy csak az aktuális verziókat kérje le. Ez akkor használatos, ha az ETL-folyamat dimenziókulcsokat keres a ténytáblák betöltésekor.

Feljegyzés

Egyes forrásrendszerek nem tárolják az előzménymódosításokat, ezért fontos, hogy a dimenzió rendszeresen feldolgozva legyen a módosítások észleléséhez és az új verziók implementálásához. Így a módosítások röviddel a bekövetkezésük után észlelhetők, és az érvényességi dátumok pontosak lesznek.

SCD type 3

A 3. SCD-típus változásai a korlátozott előzményeket követik attribútumokkal. Ez a módszer akkor lehet hasznos, ha az utolsó módosítást vagy a legutóbbi módosításokat kell rögzíteni.

Ez az SCD-típus megőrzi a korlátozott történeti perspektívát. Hasznos lehet, ha csak a kezdeti és az aktuális értékeket kell tárolni. Ebben az esetben nem lenne szükség köztes módosításokra.

Ha például egy értékesítő egy másik értékesítési régióhoz van rendelve, egy 3. típusú SCD-módosítás felülírja a dimenziósort. Az előző értékesítési régiót kifejezetten tároló oszlop az előző értékesítési régióként van beállítva, az új értékesítési régió pedig az aktuális értékesítési régióként van beállítva.

Az alábbi ábra egy értékesítő dimenziótag előtti és utáni állapotát mutatja be, ahol az értékesítési régiójuk megváltozott. Mivel a szervezet minden korábbi értékesítési régió-hozzárendelést meg szeretne határozni, az SCD 3-as típusú módosítást vált ki.

Speciális dimenziótagok

Sorokat szúrhat be egy olyan dimenzióba, amely hiányzó, ismeretlen, hiányzik vagy hibaállapotokat jelöl. Használhatja például a következő helyettesítő kulcsértékeket.

Kulcsérték	Célja
0	Hiányzik (a forrásrendszerben nem érhető el)
-1	Ismeretlen (keresési hiba egy ténytábla betöltése során)
-2	N/A (nem alkalmazható)
-3	Hiba

Naptár és idő

A ténytáblák szinte kivétel nélkül meghatározott időpontokban tárolják a mértékeket. A dátum (és esetleg idő) szerinti elemzés támogatásához a naptár (dátum és idő) dimenzióinak kell lenniük.

Nem gyakori, hogy egy forrásrendszer naptárdimenzió-adatokkal rendelkezne, ezért az adattárházban kell létrehozni. Általában egyszer jön létre, és ha naptárdimenzióról van szó, szükség esetén a jövőbeli dátumokkal is kiterjeszthető.

Dátumdimenzió

A dátum (vagy naptár) dimenzió az elemzéshez leggyakrabban használt dimenzió. Dátumonként egy sort tárol, és támogatja azt a gyakori követelményt, hogy bizonyos dátumok, például évek, negyedévek vagy hónapok szerint szűrjön vagy csoportosítsa azokat.

Fontos

A dátumdimenziók nem tartalmazhatnak olyan szemcsét, amely a nap idejére terjed ki. Ha a nap időelemzésére van szükség, dátum- és idődimenzióval is rendelkeznie kell (ezt a következő lépésben ismertetjük). A ténytábláknak, amelyek a napi adatokat tárolják, két idegen kulcsnak kell rendelkezniük, egy-egy ilyen dimenzióval.

A dátumdimenzió természetes kulcsának a dátum adattípusát kell használnia. A helyettesítő kulcsnak formátum és int adattípus használatával YYYYMMDD kell tárolnia a dátumot. Ez az elfogadott gyakorlat lehet az egyetlen kivétel (az idő dimenziója mellett), ha a helyettesítő kulcs értéke jelentéssel bír, és emberileg olvasható. Az adattípusként való YYYYMMDD tárolás nem csak hatékony és numerikusan rendezhető, hanem megfelel a Nemzetközi Szabványügyi Szervezet (ISO) 8601 dátumformátumának is.

Íme néhány gyakori attribútum, amelyeket bele kell foglalni egy dátumdimenzióba.

• Year, Quarter, MonthDay
• QuarterNumberInYear, MonthNumberInYear – amely a szövegfeliratok rendezéséhez szükséges lehet.
• FiscalYear, FiscalQuarter – egyes vállalati könyvelési ütemtervek az év közepén kezdődnek, így a naptári év és a pénzügyi év kezdete/vége eltérő.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear – amely a szövegfeliratok rendezéséhez szükséges lehet.
• WeekOfYear – az év hetét többféleképpen is megjelölheti, beleértve az 52 vagy 53 hetes ISO-szabványt is.
• IsHoliday, HolidayText – ha a szervezet több földrajzi helyen is működik, akkor több olyan ünnepi listát kell fenntartania, amelyet az egyes földrajzi területek külön dimenzióként vagy a dátumdimenzió több attribútumában honosítanak. Egy HolidayText attribútum hozzáadása segíthet azonosítani a jelentéskészítés ünnepnapját.
• IsWeekday – Hasonlóképpen, egyes földrajzi helyeken a szokásos munkahét nem hétfőtől péntekig tart. A munkahét például több közel-keleti régióban vasárnaptól csütörtökig tart, míg más régiókban négy- vagy hatnapos munkahét van.
• LastDayOfMonth
• RelativeYearOffset, RelativeQuarterOffset, RelativeMonthOffset– RelativeDayOffset amely a relatív dátumszűrés támogatásához szükséges lehet (például az előző hónap). Az aktuális időszakok nulla (0) eltolást használnak; az előző időszakok -1, -2, -3 eltolásokat tárolnak; a jövőbeli időszakok 1, 2, 3 eltolásokat tárolnak....

A dimenziókhoz hasonlóan az is fontos, hogy olyan attribútumokat tartalmaz, amelyek támogatják az ismert szűrési, csoportosítási és hierarchiakövetelményeket. Lehetnek olyan attribútumok is, amelyek más nyelvekre tárolják a címkék fordítását.

Ha a dimenzió a magasabb szintű tényekhez kapcsolódik, a ténytábla a dátumidőszak első dátumát használhatja. Például egy értékesítési cél ténytáblája, amely negyedéves értékesítési célokat tárol, a negyedév első dátumát a dátumdimenzióban tárolná. Egy másik módszer a kulcsoszlopok létrehozása a dátumtáblában. Egy negyedéves kulcs például a formátum és a kis adattípus használatával YYYYQ tárolhatja a negyedkulcsot.

A dimenziót fel kell tölteni az összes ténytábla által használt ismert dátumtartománysal. Tartalmaznia kell a jövőbeli dátumokat is, amikor az adattárház adatokat tárol a célokról, a költségvetésekről vagy az előrejelzésekről. Más dimenziókhoz hasonlóan előfordulhat, hogy hiányzó, ismeretlen, hiányzik vagy hibahelyzeteket ábrázoló sorokat is tartalmaz.

Tipp.

Keressen rá az interneten a "dátumdimenzió-generátor" kifejezésre, és keresse meg a dátumadatokat létrehozó szkripteket és számolótáblákat.

Az ETL-folyamatnak általában a következő év elején ki kell terjesztenie a dátumdimenzió sorait egy meghatározott számú évre. Ha a dimenzió relatív eltolási attribútumokat tartalmaz, az ETL-folyamatot naponta kell futtatni az eltolás attribútumértékeinek frissítéséhez az aktuális dátum (ma) alapján.

Idődimenzió

Néha a tényeket egy adott időpontban kell tárolni (mint a nap folyamán). Ebben az esetben hozzon létre egy idő (vagy óra) dimenziót. Lehet percek (24 x 60 = 1440 sor) vagy akár másodperc (24 x 60 x 60 = 86 400 sor). Más lehetséges szemcsék közé tartozik a fél óra vagy óra.

Az idődimenzió természetes kulcsának az idő adattípusát kell használnia. A helyettesítő kulcs megfelelő formátumot használhat, és olyan értékeket tárolhat, amelyek jelentéssel rendelkeznek, és emberi olvashatók, például a formátum vagy HHMMSS a HHMM formátum használatával.

Íme néhány gyakori attribútum, amelyeket bele kell foglalni egy idődimenzióba.

• Hour, HalfHour, QuarterHourMinute
• Időszak címkéi (reggel, délután, este, éjszaka)
• Műszakok nevei
• Csúcs vagy csúcson kívüli jelzők

Megfelelő méretek

Egyes dimenziók lehetnek megfelelő dimenziók. A megfelelő dimenziók számos ténytáblához kapcsolódnak, ezért több csillag is megosztja őket egy dimenziómodellben. Konzisztenciát biztosítanak, és segítenek csökkenteni a folyamatos fejlesztést és karbantartást.

Jellemző például, hogy a ténytáblák legalább egy dátum dimenziókulcsot tárolnak (mivel a tevékenység szinte mindig dátum és/vagy idő szerint van rögzítve). Ezért a dátumdimenziók közösen konformált dimenziók. Ezért gondoskodnia kell arról, hogy a dátumdimenzió tartalmazza az összes ténytábla elemzéséhez szükséges attribútumokat.

Az alábbi ábrán a Sales ténytábla és a Inventory ténytábla látható. Minden ténytábla a dimenzióra és Product a Date dimenzióra vonatkozik, amelyek megfeleltetendő dimenziók.

Egy másik példaként az alkalmazott és a felhasználók azonos személyek lehetnek. Ebben az esetben érdemes lehet egyesíteni az egyes entitások attribútumait, hogy egy megfelelő dimenziót állítsunk elő.

Többszerepű dimenziók

Ha egy dimenzióra többször hivatkozik egy ténytáblában, szerepkör-játék dimenziónak nevezzük.

Ha például egy értékesítési ténytábla rendelési dátummal, szállítási dátummal és szállítási dátum dimenziókulcsokkal rendelkezik, a dátum dimenzió három módon kapcsolódik. Minden módszer egy különálló szerepkört jelöl, de csak egy fizikai dátum dimenziója van.

Az alábbi diagram egy ténytáblát Flight ábrázol. A Airport dimenzió egy szerepjáték dimenzió, mert kétszer kapcsolódik a ténytáblához dimenzióként és Arrival Airport dimenziókéntDeparture Airport.

Levélszemétdimenziók

A levélszemétdimenziók akkor hasznosak, ha sok független dimenzió létezik, különösen akkor, ha néhány attribútumot (esetleg egyet) alkotnak, és ha ezek az attribútumok alacsony számossággal rendelkeznek (kevés érték). A levélszemét dimenzió célja, hogy számos kis dimenziót egyetlen dimenzióba egyesítsünk. Ez a kialakítási módszer csökkentheti a dimenziók számát, és csökkentheti a ténytáblakulcsok és így a ténytábla tárhelyének méretét. Az Adatpanel zsúfoltságának csökkentésében is segítenek, mivel kevesebb táblát mutatnak be a felhasználók számára.

A levélszemét dimenziótáblák általában az összes dimenzióattribútum értékének Cartesian-termékét tárolják helyettesítő kulcsattribútummal.

A jó jelöltek közé tartoznak a jelzők és a mutatók, a rendelési állapot és az ügyfél demográfiai állapota (nem, korcsoport és egyéb).

Az alábbi diagram egy levélszemétdimenziót Sales Status ábrázol, amely a rendelési állapot értékeit és a kézbesítési állapot értékeit egyesíti.

Dimenziók degenerálása

Degenerált dimenzió akkor fordulhat elő, ha a dimenzió megegyezik a kapcsolódó tényekkel. A degenerált dimenziók gyakori példája egy értékesítési rendelésszám dimenzió, amely egy értékesítési ténytáblához kapcsolódik. A számlaszám általában egyetlen, nem hierarchikus attribútum a ténytáblában. Ezért elfogadott gyakorlat, hogy ezeket az adatokat nem lehet másolni külön dimenziótáblák létrehozásához.

Az alábbi diagram egy olyan dimenziót Sales Order ábrázol, amely egy értékesítési ténytábla oszlopa alapján SalesOrderNumber degenerált dimenzió. Ez a dimenzió olyan nézetként van implementálva, amely lekéri a különböző értékesítési rendelésszámértékeket.

Tipp.

Létrehozhat egy olyan nézetet egy Fabric Warehouse-ban, amely a degenerált dimenziót dimenzióként jeleníti meg lekérdezési célokra.

Power BI szemantikai modellezési szempontból a degenerált dimenziók külön táblaként hozhatók létre a Power Query használatával. Így a szemantikai modell megfelel annak az ajánlott eljárásnak, hogy a szűréshez vagy csoportosításhoz használt mezők dimenziótáblákból származnak, a tényösszegző mezők pedig ténytáblákból származnak.

Kivándoroló dimenziók

Ha egy dimenziótábla más dimenziótáblákhoz kapcsolódik, az úgynevezett kirigger dimenzió. A kivándoroló dimenziók segíthetnek a definícióknak a dimenziómodellben való megfelelésében és újrafelhasználásában.

Létrehozhat például egy földrajzi dimenziót, amely minden irányítószámhoz földrajzi helyeket tárol. Erre a dimenzióra hivatkozhat az ügyfél- és üzletkötői dimenzió, amely a földrajzi dimenzió helyettesítő kulcsát tárolja. Így az ügyfelek és az értékesítők ezután egységes földrajzi helyek használatával elemezhetők.

Az alábbi ábrán egy Geography kivándoroló dimenziót ábrázoló dimenzió látható. Nem kapcsolódik közvetlenül a Sales ténytáblához. Ehelyett közvetetten kapcsolódik a dimenzión és a Customer Salesperson dimenzión keresztül.

Vegye figyelembe, hogy a dátumdimenzió használható kimenő dimenzióként, ha más dimenziótábla-attribútumok dátumokat tárolnak. Egy ügyféldimenzió születési dátuma például a dátum dimenziótáblájának helyettesítő kulcsával tárolható.

Többértékű dimenziók

Ha egy dimenzióattribútumnak több értéket kell tárolnia, többértékű dimenziót kell megterveznie. Többértékű dimenziót egy hídtábla (más néven illesztőtábla) létrehozásával valósíthat meg. A hídtáblák több-a-többhöz kapcsolatot tárolnak az entitások között.

Tegyük fel például, hogy van egy értékesítői dimenzió, és hogy az egyes értékesítők egy vagy akár több értékesítési régióhoz vannak rendelve. Ebben az esetben érdemes létrehozni egy értékesítési régió dimenziót. Ez a dimenzió csak egyszer tárolja az egyes értékesítési régiókat. Egy külön tábla, más néven hídtábla tárolja az egyes értékesítők és értékesítési régiók közötti kapcsolatokat. Fizikailag egy-a-többhöz kapcsolat van az üzletkötő dimenzió és a hídtábla között, valamint egy egy-a-többhöz kapcsolat az értékesítési régió dimenziójától a hídtábláig. Logikailag több-a-többhöz kapcsolat van az értékesítők és az értékesítési régiók között.

Az alábbi ábrán a Account dimenziótábla a Transaction ténytáblához kapcsolódik. Mivel az ügyfelek több fiókkal is rendelkezhetnek, és több ügyfél is lehet, a Customer dimenziótábla a Customer Account hídtáblán keresztül kapcsolódik.

Kapcsolódó tartalom

A sorozat következő cikkében megismerheti a ténytáblák útmutatását és tervezési ajánlott eljárásait.