Dimensionale modellering in Microsoft Fabric Warehouse: Dimensietabellen

Van toepassing op:✅ SQL Analytics-eindpunt en -magazijn in Microsoft Fabric

Notitie

Dit artikel maakt deel uit van de dimensionale modelleringsreeks van artikelen. Deze reeks is gericht op richtlijnen en aanbevolen procedures voor ontwerpen met betrekking tot dimensionale modellering in Microsoft Fabric Warehouse.

Dit artikel bevat richtlijnen en aanbevolen procedures voor het ontwerpen van dimensietabellen in een dimensional model. Het biedt praktische richtlijnen voor Warehouse in Microsoft Fabric. Dit is een ervaring die veel T-SQL-mogelijkheden ondersteunt, zoals het maken van tabellen en het beheren van gegevens in tabellen. U hebt dus volledige controle over het maken van uw dimensionale modeltabellen en het laden ervan met gegevens.

Notitie

In dit artikel verwijst de term datawarehouse naar een datawarehouse voor ondernemingen, dat een uitgebreide integratie van kritieke gegevens in de hele organisatie biedt. Het zelfstandige termenwarehouse verwijst daarentegen naar een Fabric Warehouse, een saaS-aanbieding (software as a service) voor relationele databases die u kunt gebruiken om een datawarehouse te implementeren. Voor de duidelijkheid wordt in dit artikel het laatste genoemd als Fabric Warehouse.

Tip

Als u onervaren bent met dimensionale modellering, kunt u deze reeks artikelen overwegen als eerste stap. Het is niet bedoeld om een volledige discussie te bieden over dimensionale modelleringsontwerpen. Raadpleeg voor meer informatie rechtstreeks gepubliceerde inhoud, zoals The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3e editie, 2013) door Ralph Kimball en anderen.

In een dimensional model beschrijft een dimensietabel een entiteit die relevant is voor uw bedrijfs- en analysevereisten. Dimensietabellen vertegenwoordigen in grote lijnen de dingen die u modelleert. Dingen kunnen producten, personen, plaatsen of een ander concept zijn, inclusief datum en tijd. Als u dimensietabellen gemakkelijk wilt identificeren, moet u hun namen meestal vooraf laten gaan door d_ of Dim_.

Dimensietabelstructuur

Als u de structuur van een dimensietabel wilt beschrijven, bekijkt u het volgende voorbeeld van een dimensietabel verkoopmedewerker met de naam d_Salesperson. In dit voorbeeld worden goede ontwerpprocedures toegepast. Elk van de groepen kolommen wordt beschreven in de volgende secties.

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

Surrogaatsleutel

De voorbeelddimensietabel heeft een surrogaatsleutel, die de naam Salesperson_SKheeft. Een surrogaatsleutel is een unieke id met één kolom die wordt gegenereerd en opgeslagen in de dimensietabel. Het is een primaire sleutelkolom die wordt gebruikt om te relateren aan andere tabellen in het dimensionale model.

Surrogaatsleutels streven ernaar om het datawarehouse te isoleren van wijzigingen in brongegevens. Ze bieden ook vele andere voordelen, zodat u het volgende kunt doen:

• Voeg meerdere gegevensbronnen samen (vermijd het conflict met dubbele id's).
• Voeg natuurlijke sleutels met meerdere kolommen samen tot een efficiëntere sleutel met één kolom.
• Houd de dimensiegeschiedenis bij met een langzaam veranderende dimensie (SCD) type 2.
• Beperk de breedte van feitentabellen voor opslagoptimalisatie (door het kleinste mogelijke gegevenstype voor gehele getallen te selecteren).

Een surrogaatsleutelkolom is een aanbevolen praktijk, zelfs als een natuurlijke sleutel (hierna beschreven) een acceptabele kandidaat lijkt. U moet ook geen betekenis geven aan de sleutelwaarden (met uitzondering van dimensiesleutels voor datum en tijd, zoals later wordt beschreven).

Natuurlijke sleutels

De voorbeelddimensietabel heeft ook een natuurlijke sleutel, die de naam EmployeeIDheeft. Een natuurlijke sleutel is de sleutel die is opgeslagen in het bronsysteem. Hiermee kunt u de dimensiegegevens koppelen aan het bronsysteem, dat doorgaans wordt uitgevoerd door een ETL-proces (Extract, Load en Transform) om de dimensietabel te laden. Soms wordt een natuurlijke sleutel een zakelijke sleutel genoemd en zijn de waarden mogelijk zinvol voor zakelijke gebruikers.

Soms hebben dimensies geen natuurlijke sleutel. Dit kan het geval zijn voor uw datumdimensie of opzoekdimensies, of wanneer u dimensiegegevens genereert door een plat bestand te normaliseren.

Dimensiekenmerken

Een voorbeelddimensietabel heeft ook dimensiekenmerken, zoals de FirstName kolom. Dimensiekenmerken bieden context voor de numerieke gegevens die zijn opgeslagen in gerelateerde feitentabellen. Dit zijn meestal tekstkolommen die worden gebruikt in analysequery's om te filteren en te groeperen (segment en dobbelstenen), maar niet om zelf te worden samengevoegd. Sommige dimensietabellen bevatten enkele kenmerken, terwijl andere veel kenmerken bevatten (zoveel als nodig is om de queryvereisten van het dimensionale model te ondersteunen).

Tip

Een goede manier om te bepalen welke dimensies en kenmerken u nodig hebt, is door de juiste mensen te vinden en de juiste vragen te stellen. Blijf in het bijzonder op de hoogte voor de vermelding van het woord door. Als iemand bijvoorbeeld zegt dat ze verkopen moeten analyseren per verkoper, per maand en per productcategorie, vertellen ze u dat ze dimensies nodig hebben die deze kenmerken hebben.

Als u van plan bent een semantisch Direct Lake-model te maken, moet u alle mogelijke kolommen opnemen die zijn vereist voor het filteren en groeperen als dimensiekenmerken. Dat komt doordat semantische Direct Lake-modellen geen berekende kolommen ondersteunen.

Refererende sleutels

De voorbeelddimensietabel heeft ook een refererende sleutel, die de naam SalesRegion_FKheeft. Andere dimensietabellen kunnen verwijzen naar een refererende sleutel en hun aanwezigheid in een dimensietabel is een speciaal geval. Hiermee wordt aangegeven dat de tabel is gerelateerd aan een andere dimensietabel, wat betekent dat deze mogelijk deel uitmaakt van een sneeuwvlokdimensie of die is gerelateerd aan een outriggerdimensie.

Fabric Warehouse ondersteunt beperkingen voor refererende sleutels, maar ze kunnen niet worden afgedwongen. Daarom is het belangrijk dat uw ETL-proces test op integriteit tussen gerelateerde tabellen wanneer gegevens worden geladen.

Het is nog steeds een goed idee om refererende sleutels te maken. Een goede reden om niet-afgedwongen refererende sleutels te maken, is het toestaan van modelleringshulpprogramma's, zoals Power BI Desktop, om automatisch relaties tussen tabellen in het semantische model te detecteren en te maken.

Historische traceringskenmerken

De voorbeelddimensietabel heeft ook verschillende historische traceringskenmerken. Historische traceringskenmerken zijn optioneel op basis van uw behoeften om specifieke wijzigingen bij te houden wanneer deze zich voordoen in het bronsysteem. Hiermee kunnen waarden worden opgeslagen ter ondersteuning van de primaire rol van een datawarehouse. Dit is om het verleden nauwkeurig te beschrijven. Deze kenmerken slaan met name historische context op als het ETL-proces nieuwe of gewijzigde gegevens in de dimensie laadt.

Zie Historische wijziging verderop in dit artikel beheren voor meer informatie.

Kenmerken controleren

De voorbeelddimensietabel heeft ook verschillende auditkenmerken. Controlekenmerken zijn optioneel, maar worden aanbevolen. Hiermee kunt u bijhouden wanneer en hoe dimensierecords zijn gemaakt of gewijzigd, en ze kunnen diagnostische of probleemoplossingsinformatie bevatten die tijdens ETL-processen wordt gegenereerd. U wilt bijvoorbeeld bijhouden wie (of welk proces) een rij heeft bijgewerkt en wanneer. Controlekenmerken kunnen ook helpen bij het diagnosticeren van een lastig probleem, bijvoorbeeld wanneer een ETL-proces onverwacht stopt. Ze kunnen dimensieleden ook markeren als fouten of afgeleide leden.

Grootte van dimensietabel

Vaak zijn de meest nuttige en veelzijdige dimensies in een dimensional model grote, brede dimensies. Ze zijn groot in termen van rijen (meer dan miljoenen) en breed in termen van het aantal dimensiekenmerken (mogelijk honderden). Grootte is niet zo belangrijk (hoewel u moet ontwerpen en optimaliseren voor de kleinste mogelijke grootte). Wat belangrijk is, is dat de dimensie ondersteuning biedt voor de vereiste filtering, groepering en nauwkeurige historische analyse van feitengegevens.

Grote dimensies kunnen afkomstig zijn van meerdere bronsystemen. In dit geval moet dimensieverwerking de gegevens combineren, samenvoegen, ontdubbelen en standaardiseren; en wijs surrogaatsleutels toe.

Ter vergelijking, sommige dimensies zijn klein. Ze kunnen opzoektabellen vertegenwoordigen die slechts verschillende records en kenmerken bevatten. Deze kleine dimensies slaan vaak categoriewaarden op die betrekking hebben op transacties in feitentabellen en ze worden geïmplementeerd als dimensies met surrogaatsleutels die betrekking hebben op de feitenrecords.

Tip

Als u veel kleine dimensies hebt, kunt u overwegen om ze samen te brengen in een ongewenste dimensie.

Concepten voor dimensieontwerp

In deze sectie worden verschillende dimensieontwerpconcepten beschreven.

Denormalisatie versus normalisatie

Het is bijna altijd het geval dat dimensietabellen moeten worden gedenormaliseerd. Hoewel normalisatie de term is die wordt gebruikt om gegevens te beschrijven die zijn opgeslagen op een manier die repetitious gegevens vermindert, is denormalisatie de term die wordt gebruikt om te definiëren waar vooraf ingevulde redundante gegevens bestaan. Redundante gegevens bestaan doorgaans vanwege de opslag van hiërarchieën (later besproken), wat betekent dat hiërarchieën worden afgevlakt. Een productdimensie kan bijvoorbeeld subcategorie (en de bijbehorende kenmerken) en categorie (en de bijbehorende kenmerken) opslaan.

Omdat dimensies over het algemeen klein zijn (vergeleken met feitentabellen), worden de kosten voor het opslaan van redundante gegevens bijna altijd opwegen tegen de verbeterde queryprestaties en bruikbaarheid.

Sneeuwvlokdimensies

Een uitzondering op de denormalisatie is het ontwerpen van een sneeuwvlokdimensie. Een snowflake-dimensie wordt genormaliseerd en slaat de dimensiegegevens op in verschillende gerelateerde tabellen.

In het volgende diagram ziet u een sneeuwvlokdimensie die uit drie gerelateerde dimensietabellen bestaat: Product, Subcategoryen Category.

Overweeg om een sneeuwvlokdimensie te implementeren wanneer:

• De dimensie is extreem groot en de opslagkosten wegen op tegen de noodzaak van hoge queryprestaties. (Er wordt echter regelmatig opnieuw beoordeeld of dit nog steeds het geval is.)
• U hebt sleutels nodig om de dimensie te relateren aan nauwkeurigere feiten. In de feitentabel verkoop worden bijvoorbeeld rijen op productniveau opgeslagen, maar in de feitentabel verkoopdoel worden rijen op subcategorieniveau opgeslagen.
• U moet historische wijzigingen bijhouden op hogere granulariteitsniveaus.

Notitie

Houd er rekening mee dat een hiërarchie in een semantisch Power BI-model alleen kan worden gebaseerd op kolommen uit één semantische modeltabel. Daarom moet een sneeuwvlokdimensie een gedenormaliseerd resultaat opleveren met behulp van een weergave die de sneeuwvloktabellen samenvoegt.

Hiërarchieën

Dimensiekolommen produceren gewoonlijk hiërarchieën. Hiërarchieën maken het mogelijk om gegevens te verkennen op verschillende niveaus van samenvatting. In de eerste weergave van een matrixvisual kunnen bijvoorbeeld jaarlijkse verkopen worden weergegeven en kan de rapportgebruiker inzoomen om kwartaal- en maandelijkse verkopen weer te geven.

Er zijn drie manieren om een hiërarchie in een dimensie op te slaan. U kunt gebruikmaken van:

• Kolommen van één gedenormaliseerde dimensie.
• Een sneeuwvlokdimensie, die uit meerdere gerelateerde tabellen bestaat.
• Een relatie tussen bovenliggend en onderliggend (zelfverwijzend) in een dimensie.

Hiërarchieën kunnen evenwichtig of onevenwichtig zijn. Het is ook belangrijk om te begrijpen dat sommige hiërarchieën onregelmatig zijn.

Evenwichtige hiërarchieën

Evenwichtige hiërarchieën zijn het meest voorkomende type hiërarchie. Een evenwichtige hiërarchie heeft hetzelfde aantal niveaus. Een veelvoorkomend voorbeeld van een evenwichtige hiërarchie is een kalenderhiërarchie in een datumdimensie die niveaus bevat voor jaar, kwartaal, maand en datum.

In het volgende diagram ziet u een evenwichtige hiërarchie van verkoopregio's. Het bestaat uit twee niveaus, namelijk de verkoopregiogroep en de verkoopregio.

Niveaus van een evenwichtige hiërarchie zijn gebaseerd op kolommen van één, gedenormaliseerde dimensie of van tabellen die een sneeuwvlokdimensie vormen. Wanneer ze zijn gebaseerd op één gedenormaliseerde dimensie, bevatten de kolommen die de hogere niveaus vertegenwoordigen redundante gegevens.

Voor evenwichtige hiërarchieën hebben feiten altijd betrekking op één niveau van de hiërarchie, meestal het laagste niveau. Op die manier kunnen de feiten worden samengevoegd (samengeteld) naar het hoogste niveau van de hiërarchie. Feiten kunnen betrekking hebben op elk niveau, dat wordt bepaald door de korrel van de feitentabel. De feitentabel verkoop kan bijvoorbeeld worden op datumniveau opgeslagen, terwijl de feitentabel verkoopdoel op kwartaalniveau kan worden opgeslagen.

Niet-gebalanceerde hiërarchieën

Onevenwichtige hiërarchieën zijn een minder gangbaar type hiërarchie. Een niet-evenwichtige hiërarchie heeft niveaus op basis van een relatie tussen bovenliggende en onderliggende elementen. Daarom wordt het aantal niveaus in een niet-verdeelde hiërarchie bepaald door de dimensierijen en niet door specifieke dimensietabelkolommen.

Een veelvoorkomend voorbeeld van een niet-evenwichtige hiërarchie is een werknemershiërarchie waarbij elke rij in een werknemerdimensie betrekking heeft op een rapportagemanagerrij in dezelfde tabel. In dit geval kan elke werknemer een manager zijn met rapportagemedewerkers. Sommige vertakkingen van de hiërarchie hebben natuurlijk meer niveaus dan andere.

In het volgende diagram ziet u een onevenwichtige hiërarchie. Het bestaat uit vier niveaus en elk lid in de hiërarchie is een verkoper. U ziet dat verkopers een ander aantal voorouders hebben in de hiërarchie, afhankelijk van aan wie ze rapporteren.

Andere veelvoorkomende voorbeelden van onevenwichtige hiërarchieën zijn het factureren van materialen, bedrijfseigendomsmodellen en grootboek.

Voor niet-evenwichtige hiërarchieën hebben feiten altijd betrekking op de dimensiekorrel. Verkoopcijfers hebben bijvoorbeeld betrekking op verschillende verkopers die verschillende rapportagestructuren hebben. De dimensietabel zou een surrogaatsleutel (benoemd Salesperson_SK) en een ReportsTo_Salesperson_FK refererende-sleutelkolom hebben, die verwijst naar de primaire-sleutelkolom. Elke verkoper zonder dat iemand moet beheren, is niet noodzakelijkerwijs op het laagste niveau van een vertakking van de hiërarchie. Wanneer ze zich niet op het laagste niveau bevinden, kan een verkoper producten verkopen en verkopers melden die ook producten verkopen. Daarom moet de samenteling van feitengegevens rekening houden met de afzonderlijke verkoper en al hun afstammelingen.

Het uitvoeren van query's op bovenliggende en onderliggende hiërarchieën kan complex en traag zijn, met name voor grote dimensies. Hoewel het bronsysteem relaties mogelijk opslaat als bovenliggend/onderliggend element, raden we u aan de hiërarchie te naturaliseren . In dit geval kunt u de hiërarchieniveaus in de dimensie als kolommen transformeren en opslaan.

Tip

Als u ervoor kiest om de hiërarchie niet te naturaliseren, kunt u nog steeds een hiërarchie maken op basis van een bovenliggende en onderliggende relatie in een semantisch Power BI-model. Deze benadering wordt echter niet aanbevolen voor grote dimensies. Zie Functies voor bovenliggende en onderliggende hiërarchieën in DAX voor meer informatie.

Onregelmatige hiërarchieën

Soms is een hiërarchie onregelmatig omdat het bovenliggende lid in de hiërarchie bestaat op een niveau dat er niet direct boven ligt. In deze gevallen herhalen ontbrekende niveauwaarden de waarde van het bovenliggende item.

Bekijk een voorbeeld van een evenwichtige geografiehiërarchie. Er bestaat een onregelmatige hiërarchie wanneer een land/regio geen staten of provincies heeft. Nieuw-Zeeland heeft bijvoorbeeld geen staten of provincies. Dus als u de rij Nieuw-Zeeland invoegt, moet u ook de land-/regiowaarde in de StateProvince kolom opslaan.

In het volgende diagram ziet u een onregelmatige hiërarchie van geografische regio's.

Historische wijziging beheren

Indien nodig kan historische wijziging worden beheerd door een langzaam veranderende dimensie (SCD) te implementeren. Een SCD onderhoudt historische context als nieuwe of gewijzigde gegevens, wordt erin geladen.

Hier volgen de meest voorkomende SCD-typen.

• Type 1: Het bestaande dimensielid overschrijven.
• Type 2: Voeg een nieuw op tijd gebaseerd dimensielid met versiebeheer in.
• Type 3: Beperkte geschiedenis bijhouden met kenmerken.

Het is mogelijk dat een dimensie zowel SCD-type 1- als SCD-type 2-wijzigingen kan ondersteunen.

SCD-type 3 wordt niet vaak gebruikt, deels vanwege het feit dat het moeilijk is om te gebruiken in een semantisch model. Overweeg zorgvuldig of een SCD type 2-benadering beter past.

Tip

Als u een snel veranderende dimensie verwacht, een dimensie die een kenmerk heeft dat vaak wordt gewijzigd, kunt u in plaats daarvan overwegen dat kenmerk toe te voegen aan de feitentabel. Als het kenmerk numeriek is, zoals de productprijs, kunt u het toevoegen als een meting in de feitentabel. Als het kenmerk een tekstwaarde is, kunt u een dimensie maken op basis van alle tekstwaarden en de bijbehorende dimensiesleutel toevoegen aan de feitentabel.

SCD type 1

SCD-type 1 overschrijft de bestaande dimensierij omdat u wijzigingen niet hoeft bij te houden. Dit SCD-type kan ook worden gebruikt om fouten te corrigeren. Het is een veelvoorkomend type SCD en moet worden gebruikt voor de meeste veranderende kenmerken, zoals de naam van de klant, het e-mailadres en andere.

In het volgende diagram ziet u de voor- en nastatus van een verkoperdimensielid waarin het telefoonnummer is gewijzigd.

Dit SCD-type behoudt geen historisch perspectief omdat de bestaande rij wordt bijgewerkt. Dat betekent dat SCD type 1 wijzigingen kunnen leiden tot verschillende aggregaties op een hoger niveau. Als een verkoper bijvoorbeeld is toegewezen aan een andere verkoopregio, wordt de dimensierij overschreven door een SCD-type 1. Het samenvouwen van verkoopmedewerkers van historische verkoopresultaten naar regio zou vervolgens een ander resultaat opleveren, omdat deze nu gebruikmaakt van de nieuwe huidige verkoopregio. Het is alsof die verkoper altijd is toegewezen aan de nieuwe verkoopregio.

SCD-type 2

SCD-type 2-wijzigingen resulteren in nieuwe rijen die een op tijd gebaseerde versie van een dimensielid vertegenwoordigen. Er is altijd een huidige versierij en deze weerspiegelt de status van het dimensielid in het bronsysteem. Historische traceringskenmerken in de dimensietabelopslagwaarden waarmee u de huidige versie (huidige vlag is TRUE) en de geldigheidsperiode kunt identificeren. Een surrogaatsleutel is vereist omdat er dubbele natuurlijke sleutels zijn wanneer meerdere versies worden opgeslagen.

Het is een gemeenschappelijk type SCD, maar moet worden gereserveerd voor kenmerken die historisch perspectief moeten behouden.

Als een verkoper bijvoorbeeld is toegewezen aan een andere verkoopregio, omvat een SCD-type 2-wijziging een updatebewerking en een invoegbewerking.

1. De updatebewerking overschrijft de huidige versie om de historische traceringskenmerken in te stellen. De kolom voor de geldigheid van het einde wordt ingesteld op de ETL-verwerkingsdatum (of een geschikte tijdstempel in het bronsysteem) en de huidige vlag is ingesteld FALSEop .
2. Met de invoegbewerking wordt een nieuwe, huidige versie toegevoegd, waarbij de beginwaarde van de geldigheidskolom wordt ingesteld op de waarde van de eindvalidatiekolom (gebruikt om de vorige versie bij te werken) en de huidige vlag op TRUE.

Het is belangrijk om te begrijpen dat de granulariteit van gerelateerde feitentabellen niet op het niveau van de verkoper is, maar in plaats van het versieniveau van de verkoper. Het samenvouwen van hun historische verkoopresultaten naar regio levert de juiste resultaten op, maar er zijn twee (of meer) versies van verkopers die moeten worden geanalyseerd.

In het volgende diagram ziet u de voor- en nastatus van een verkoperdimensielid waar de verkoopregio is gewijzigd. Omdat de organisatie de inspanningen van verkopers wil analyseren op basis van de regio waaraan ze zijn toegewezen, wordt een SCD-type 2-wijziging geactiveerd.

Tip

Wanneer een dimensietabel SCD-type 2 ondersteunt, moet u een labelkenmerk opnemen waarin het lid en de versie worden beschreven. Bekijk een voorbeeld wanneer de verkoper Lynn Tsoflias van Adventure Works de toewijzing van de Australische verkoopregio wijzigt in de verkoopregio van het Verenigd Koninkrijk. Het labelkenmerk voor de eerste versie kan "Lynn Tsoflias (Australië)" lezen en het labelkenmerk voor de nieuwe, huidige versie kan lezen "Lynn Tsoflias (Verenigd Koninkrijk)." Als dit handig is, kunt u ook de geldigheidsdatums in het label opnemen.

U moet de behoefte aan historische nauwkeurigheid en bruikbaarheid en efficiëntie in balans hebben. Probeer te voorkomen dat er te veel SCD-type 2-wijzigingen in een dimensietabel worden aangebracht, omdat dit kan leiden tot een overweldigend aantal versies waardoor analisten het lastig kunnen begrijpen.

Bovendien kunnen te veel versies erop wijzen dat een wijzigingskenmerk beter kan worden opgeslagen in de feitentabel. Als het eerdere voorbeeld wordt uitgebreid, kan de verkoopregio worden opgeslagen als dimensiesleutel in de feitentabel in plaats van een SCD-type 2 te implementeren.

Houd rekening met de volgende scd-type 2 historische traceringskenmerken.

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,

    <…>
);

Dit zijn de doelen van de historische traceringskenmerken.

• In RecChangeDate_FK de kolom wordt de datum opgeslagen waarop de wijziging is doorgevoerd. Hiermee kunt u query's uitvoeren wanneer er wijzigingen zijn uitgevoerd.
• In RecValidFromKey de kolommen worden RecValidToKey de effectieve datums van geldigheid voor de rij opgeslagen. Overweeg de vroegste datum op te slaan die in de datumdimensie is gevonden om RecValidFromKey de oorspronkelijke versie weer te geven en op te slaan 01/01/9999 voor de RecValidToKey huidige versies.
• De RecReason kolom is optioneel. Hiermee kunt u de reden vastleggen waarom de versie is ingevoegd. Het kan coderen welke kenmerken zijn gewijzigd, of het kan een code zijn van het bronsysteem waarin een bepaalde bedrijfsreden wordt vermeld.
• De RecIsCurrent kolom maakt het mogelijk om alleen huidige versies op te halen. Het wordt gebruikt wanneer het ETL-proces dimensiesleutels opzoekt bij het laden van feitentabellen.

Notitie

Sommige bronsystemen slaan geen historische wijzigingen op, dus het is belangrijk dat de dimensie regelmatig wordt verwerkt om wijzigingen te detecteren en nieuwe versies te implementeren. Op die manier kunt u wijzigingen kort na de datums detecteren en de geldigheidsdatums nauwkeurig zijn.

SCD type 3

SCD-type 3-wijzigingen houden beperkte geschiedenis bij met kenmerken. Deze methode kan handig zijn wanneer de laatste wijziging moet worden vastgelegd of een aantal van de meest recente wijzigingen.

Dit SCD-type behoudt beperkte historische perspectieven. Dit kan handig zijn wanneer alleen de initiële en huidige waarden moeten worden opgeslagen. In dit geval zijn tussentijdse wijzigingen niet vereist.

Als een verkoper bijvoorbeeld is toegewezen aan een andere verkoopregio, overschrijft een SCD-type 3 de dimensierij. Een kolom waarin specifiek de vorige verkoopregio wordt opgeslagen, wordt ingesteld als de vorige verkoopregio en de nieuwe verkoopregio wordt ingesteld als de huidige verkoopregio.

In het volgende diagram ziet u de voor- en nastatus van een verkoperdimensielid waar de verkoopregio is gewijzigd. Omdat de organisatie een eerdere toewijzing van verkoopregio's wil bepalen, wordt een SCD-type 3-wijziging geactiveerd.

Speciale dimensieleden

U kunt rijen invoegen in een dimensie die ontbrekende, onbekende, N/B- of foutstatussen vertegenwoordigt. U kunt bijvoorbeeld de volgende surrogaatsleutelwaarden gebruiken.

Sleutelwaarde	Doel
0	Ontbreekt (niet beschikbaar in het bronsysteem)
-1	Onbekend (opzoekfout tijdens het laden van een feitentabel)
-2	N.b. (niet van toepassing)
-3	Error

Agenda en tijd

Vrijwel zonder uitzondering slaan feitentabellen metingen op specifieke tijdstippen op. Ter ondersteuning van analyse op datum (en mogelijk tijd) moeten er kalenderdimensies (datum en tijd) zijn.

Het is ongebruikelijk dat een bronsysteem kalenderdimensiegegevens zou hebben, dus deze moet worden gegenereerd in het datawarehouse. Deze wordt meestal eenmaal gegenereerd en als het een agendadimensie is, wordt deze uitgebreid met toekomstige datums wanneer dat nodig is.

Datumdimensie

De datumdimensie (of kalender) is de meest voorkomende dimensie die wordt gebruikt voor analyse. Er wordt één rij per datum opgeslagen en het ondersteunt de algemene vereiste om te filteren of te groeperen op specifieke perioden van datums, zoals jaren, kwartalen of maanden.

Belangrijk

Een datumdimensie mag geen korrel bevatten die zich uitbreidt tot het tijdstip van de dag. Als de tijd van de daganalyse is vereist, moet u zowel een datumdimensie als een tijddimensie hebben (hierna beschreven). Feitentabellen die het tijdstip van de dag opslaan, moeten twee refererende sleutels hebben, één voor elk van deze dimensies.

De natuurlijke sleutel van de datumdimensie moet het gegevenstype Datum gebruiken. De surrogaatsleutel moet de datum opslaan met behulp van YYYYMMDD de notatie en het int-gegevenstype . Deze geaccepteerde praktijk moet de enige uitzondering zijn (naast de tijddimensie) wanneer de waarde van de surrogaatsleutel betekenis heeft en door mensen kan worden gelezen. Opslaan YYYYMMDD als een gegevenstype int is niet alleen efficiënt en numeriek gesorteerd, maar voldoet ook aan de ondubbelzinnige ISO-datumnotatie (International Standards Organization) 8601.

Hier volgen enkele algemene kenmerken die moeten worden opgenomen in een datumdimensie.

• Year, , , QuarterMonthDay
• QuarterNumberInYear, MonthNumberInYear – die mogelijk vereist is om tekstlabels te sorteren.
• FiscalYear, FiscalQuarter – sommige bedrijfsboekhoudingsschema's beginnen halverwege het jaar, zodat het begin/einde van het kalenderjaar en het fiscale jaar verschillen.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear – die mogelijk vereist is om tekstlabels te sorteren.
• WeekOfYear – er zijn meerdere manieren om de week van het jaar te labelen, inclusief een ISO-norm die 52 of 53 weken heeft.
• IsHoliday, – HolidayText als uw organisatie in meerdere geografische gebieden werkt, moet u meerdere sets vakantielijsten onderhouden die elke geografie als een afzonderlijke dimensie of genatuurd in meerdere kenmerken in de datumdimensie bekijkt. Het toevoegen van een HolidayText kenmerk kan helpen bij het identificeren van feestdagen voor rapportage.
• IsWeekday – in sommige geografische gebieden is de standaardwerkweek niet maandag tot en met vrijdag. De werkweek is bijvoorbeeld zondag tot en met donderdag in veel regio's in het Midden-Oosten, terwijl andere regio's een werkweek van vier dagen of zes dagen gebruiken.
• LastDayOfMonth
• RelativeYearOffset, RelativeQuarterOffset, RelativeMonthOffset, RelativeDayOffset – die mogelijk vereist is om relatieve datumfiltering te ondersteunen (bijvoorbeeld vorige maand). Huidige perioden gebruiken een verschuiving van nul (0); vorige perioden slaan verschuivingen van -1, -2, -3...; toekomstige perioden slaan verschuivingen van 1, 2, 3....

Net als bij elke dimensie is het belangrijk dat deze kenmerken bevat die ondersteuning bieden voor de bekende vereisten voor filteren, groeperen en hiërarchie. Er kunnen ook kenmerken zijn waarmee vertalingen van labels in andere talen worden opgeslagen.

Wanneer de dimensie wordt gebruikt om verband te houden met feiten met een hogere korrel, kan de feitentabel de eerste datum van de datumperiode gebruiken. Een feitentabel met verkoopdoelen waarin de doelen van verkopers per kwartaal worden opgeslagen, slaat bijvoorbeeld de eerste datum van het kwartaal op in de datumdimensie. Een alternatieve methode is het maken van belangrijke kolommen in de datumtabel. Een kwartsleutel kan bijvoorbeeld de kwartaalsleutel opslaan met behulp van YYYYQ de notatie en het gegevenstype smallint .

De dimensie moet worden gevuld met het bekende datumbereik dat door alle feitentabellen wordt gebruikt. Het moet ook toekomstige datums bevatten wanneer het datawarehouse feiten over doelen, budgetten of prognoses opslaat. Net als bij andere dimensies kunt u rijen opnemen die ontbrekende, onbekende, N/B- of foutsituaties vertegenwoordigen.

Tip

Zoek op internet naar 'datumdimensiegenerator' om scripts en spreadsheets te zoeken die datumgegevens genereren.

Normaal gesproken moet het ETL-proces aan het begin van het volgende jaar de datumdimensierijen uitbreiden naar een bepaald aantal jaren vooruit. Wanneer de dimensie relatieve offsetkenmerken bevat, moet het ETL-proces dagelijks worden uitgevoerd om offsetkenmerkwaarden bij te werken op basis van de huidige datum (vandaag).

Tijddimensie

Soms moeten feiten op een bepaald tijdstip (zoals op het tijdstip van de dag) worden opgeslagen. In dit geval maakt u een tijddimensie (of klok). Het kan een korrel van minuten hebben (24 x 60 = 1.440 rijen) of zelfs seconden (24 x 60 x 60 = 86.400 rijen). Andere mogelijke korrels zijn een half uur of uur.

De natuurlijke sleutel van een tijddimensie moet het tijdgegevenstype gebruiken. De surrogaatsleutel kan een geschikte indeling gebruiken en waarden opslaan die betekenis hebben en die door mensen kunnen worden gelezen, bijvoorbeeld met behulp van de HHMM of HHMMSS indeling.

Hier volgen enkele algemene kenmerken die moeten worden opgenomen in een tijddimensie.

• Hour, , , HalfHourQuarterHourMinute
• Tijdsperiodelabels (ochtend, middag, avond, nacht)
• Namen van werkdiensten
• Piek- of dalvlagmen

Conforme dimensies

Sommige dimensies kunnen voldoen aan dimensies. Conforme dimensies hebben betrekking op veel feitentabellen en worden dus gedeeld door meerdere sterren in een dimensional model. Ze leveren consistentie en kunnen u helpen bij het verminderen van doorlopende ontwikkeling en onderhoud.

Het is bijvoorbeeld gebruikelijk dat feitentabellen ten minste één datumdimensiesleutel opslaan (omdat activiteit bijna altijd wordt vastgelegd op datum en/of tijd). Daarom is een datumdimensie een gemeenschappelijke conforme dimensie. Zorg er daarom voor dat uw datumdimensie kenmerken bevat die relevant zijn voor de analyse van alle feitentabellen.

In het volgende diagram ziet u de Sales feitentabel en de Inventory feitentabel. Elke feitentabel heeft betrekking op de Date dimensie en Product dimensie, die voldoen aan dimensies.

In een ander voorbeeld kunnen uw werknemers en gebruikers dezelfde set personen zijn. In dit geval kan het zinvol zijn om de kenmerken van elke entiteit te combineren om één conforme dimensie te produceren.

Rollenspeldimensies

Wanneer in een feitentabel meerdere keren naar een dimensie wordt verwezen, wordt deze een rolspeldimensie genoemd.

Wanneer een feitentabel bijvoorbeeld orderdatum, verzenddatum en leveringsdatum heeft, is de datumdimensie op drie manieren gerelateerd. Elke manier vertegenwoordigt een afzonderlijke rol, maar er is slechts één fysieke datumdimensie.

In het volgende diagram ziet u een Flight feitentabel. De Airport dimensie is een rollenspeldimensie omdat deze twee keer is gerelateerd aan de feitentabel als de Departure Airport dimensie en de Arrival Airport dimensie.

Dimensies voor ongewenste e-mail

Een ongewenste dimensie is handig wanneer er veel onafhankelijke dimensies zijn, met name wanneer ze een paar kenmerken bevatten (misschien één) en wanneer deze kenmerken een lage kardinaliteit hebben (weinig waarden). Het doel van een ongewenste dimensie is om veel kleine dimensies in één dimensie samen te voegen. Deze ontwerpbenadering kan het aantal dimensies verminderen en het aantal feitentabelsleutels en dus de opslaggrootte van feitentabellen verminderen. Ze helpen ook om het gegevensvenster overzichtelijker te maken, omdat ze minder tabellen aan gebruikers presenteren.

In een tabel met ongewenste dimensies wordt meestal het Cartesische product van alle dimensiekenmerkwaarden opgeslagen, met een kenmerk surrogaatsleutel.

Goede kandidaten zijn vlaggen en indicatoren, orderstatus en demografische status van klanten (geslacht, leeftijdsgroep en andere).

In het volgende diagram ziet u een ongewenste dimensie die Sales Status orderstatuswaarden en leveringsstatuswaarden combineert.

Gedegenereerde dimensies

Een degenereerde dimensie kan optreden wanneer de dimensie gelijk is aan de gerelateerde feiten. Een veelvoorkomend voorbeeld van een degenereerde dimensie is een dimensie voor verkoopordernummers die betrekking heeft op een feitentabel verkoop. Normaal gesproken is het factuurnummer één niet-hiërarchisch kenmerk in de feitentabel. Het is dus een geaccepteerde procedure om deze gegevens niet te kopiëren om een afzonderlijke dimensietabel te maken.

In het volgende diagram ziet u een Sales Order dimensie die een degenereerde dimensie is op basis van de SalesOrderNumber kolom in een feitentabel verkoop. Deze dimensie wordt geïmplementeerd als een weergave waarmee de unieke waarden voor het verkoopordernummer worden opgehaald.

Tip

Het is mogelijk om een weergave te maken in een fabricwarehouse waarin de ontaarde dimensie wordt weergegeven als dimensie voor het uitvoeren van query's.

Vanuit een semantisch power BI-modelleringsperspectief kan een degenererende dimensie worden gemaakt als een afzonderlijke tabel met behulp van Power Query. Op die manier voldoet het semantische model aan de best practice dat velden die worden gebruikt voor het filteren of groeperen afkomstig zijn van dimensietabellen en velden die worden gebruikt om feiten samen te vatten, afkomstig zijn van feitentabellen.

Outriggerdimensies

Wanneer een dimensietabel betrekking heeft op andere dimensietabellen, wordt deze een outriggerdimensie genoemd. Een outriggerdimensie kan helpen definities in het dimensionale model te conformeren en opnieuw te gebruiken.

U kunt bijvoorbeeld een geografiedimensie maken waarin geografische locaties voor elke postcode worden opgeslagen. Deze dimensie kan vervolgens worden verwezen door uw klantdimensie en verkoperdimensie, waarmee de surrogaatsleutel van de geografiedimensie wordt opgeslagen. Op die manier kunnen klanten en verkopers vervolgens worden geanalyseerd met behulp van consistente geografische locaties.

In het volgende diagram ziet u een Geography dimensie die een outriggerdimensie is. Het heeft niet rechtstreeks betrekking op de Sales feitentabel. In plaats daarvan is het indirect gerelateerd via de Customer dimensie en de Salesperson dimensie.

Houd er rekening mee dat de datumdimensie kan worden gebruikt als een outriggerdimensie wanneer andere dimensietabelkenmerken datums opslaan. De geboortedatum in een klantdimensie kan bijvoorbeeld worden opgeslagen met behulp van de surrogaatsleutel van de datumdimensietabel.

Dimensies met meerdere waarden

Wanneer een dimensiekenmerk meerdere waarden moet opslaan, moet u een dimensie met meerdere waarden ontwerpen. U implementeert een dimensie met meerdere waarden door een brugtabel te maken (ook wel een jointabel genoemd). In een brugtabel wordt een veel-op-veel-relatie tussen entiteiten opgeslagen.

Denk bijvoorbeeld aan een verkoopmedewerkerdimensie en dat elke verkoper is toegewezen aan een of meer verkoopregio's. In dit geval is het zinvol om een dimensie voor een verkoopregio te maken. Deze dimensie slaat elke verkoopregio slechts één keer op. In een afzonderlijke tabel, ook wel de brugtabel genoemd, wordt een rij opgeslagen voor elke verkoper en verkoopregiorelatie. Fysiek is er een een-op-veel-relatie van de verkoopmedewerkerdimensie tot de brugtabel en een andere een-op-veel-relatie van de dimensie verkoopregio tot de brugtabel. Logisch is er een veel-op-veel-relatie tussen verkopers en verkoopregio's.

In het volgende diagram heeft de Account dimensietabel betrekking op de Transaction feitentabel. Omdat klanten meerdere accounts en accounts kunnen hebben kunnen meerdere klanten hebben, is de Customer dimensietabel gerelateerd via de Customer Account brugtabel.

Gerelateerde inhoud

In het volgende artikel in deze reeks vindt u informatie over richtlijnen en aanbevolen procedures voor het ontwerpen van feitentabellen.