Een datawarehouseschema ontwerpen

Voltooid

Net als bij alle relationele databases bevat een datawarehouse tabellen waarin de gegevens die u wilt analyseren, worden opgeslagen. Meestal zijn deze tabellen ingedeeld in een schema dat is geoptimaliseerd voor multidimensionale modellering, waarbij numerieke metingen die zijn gekoppeld aan gebeurtenissen die bekend staan als feiten , kunnen worden samengevoegd door de kenmerken van gekoppelde entiteiten in meerdere dimensies. Metingen die zijn gekoppeld aan een verkooporder (zoals het betaalde bedrag of het aantal bestelde artikelen) kunnen bijvoorbeeld worden samengevoegd op kenmerken van de datum waarop de verkoop plaatsvond, de klant, de winkel, enzovoort.

Tabellen in een datawarehouse

Een veelvoorkomend patroon voor relationele datawarehouses is het definiëren van een schema met twee soorten tabellen: dimensietabellen en feitentabellen .

Dimensietabellen

Dimensietabellen beschrijven bedrijfsentiteiten, zoals producten, personen, plaatsen en datums. Dimensietabellen bevatten kolommen voor kenmerken van een entiteit. Een klantentiteit kan bijvoorbeeld een voornaam, een achternaam, een e-mailadres en een postadres hebben (die kunnen bestaan uit een adres, een plaats, een postcode en een land of regio). Naast kenmerkkolommen bevat een dimensietabel een unieke sleutelkolom die elke rij in de tabel uniek identificeert. In feite is het gebruikelijk dat een dimensietabel twee belangrijke kolommen bevat:

• een surrogaatsleutel die specifiek is voor het datawarehouse en elke rij in de dimensietabel in het datawarehouse uniek identificeert, meestal een oplopend geheel getal.
• Een alternatieve sleutel, vaak een natuurlijke of zakelijke sleutel die wordt gebruikt om een specifiek exemplaar van een entiteit in het transactionele bronsysteem te identificeren waaruit de entiteitsrecord afkomstig is, zoals een productcode of een klant-id.

Notitie

Waarom hebben twee sleutels? Er zijn enkele goede redenen:

• Het datawarehouse kan worden gevuld met gegevens uit meerdere bronsystemen, wat kan leiden tot het risico van dubbele of incompatibele bedrijfssleutels.
• Eenvoudige numerieke sleutels presteren over het algemeen beter in query's die veel tabellen samenvoegen: een gemeenschappelijk patroon in datawarehouses.
• Kenmerken van entiteiten kunnen na verloop van tijd veranderen, bijvoorbeeld dat een klant het adres kan wijzigen. Omdat het datawarehouse wordt gebruikt ter ondersteuning van historische rapportage, kunt u een record bewaren voor elk exemplaar van een entiteit op meerdere tijdstippen; zodat bijvoorbeeld verkooporders voor een specifieke klant worden geteld voor de plaats waar ze leefden op het moment dat de order werd geplaatst. In dit geval hebben meerdere klantrecords dezelfde zakelijke sleutel gekoppeld aan de klant, maar verschillende surrogaatsleutels voor elk discrete adres waar de klant op verschillende momenten woonde.

Een voorbeeld van een dimensietabel voor de klant kan de volgende gegevens bevatten:

CustomerKey	CustomerAltKey	Naam	E-mailen	Straat	City	PostalCode	CountryRegion
123	I-543	Navin Jones	navin1@contoso.com	1 Hoofd st.	Seattle	90.000	Verenigde Staten
124	R-589	Mary Smith	mary2@contoso.com	234 190th Ave	Buffalo	50001	Verenigde Staten
125	I-321	Jean-Jean-De-1	antoine1@contoso.com	2 Rue Jolie	Parijs	20098	Frankrijk
126	I-543	Navin Jones	navin1@contoso.com	24 125th Ave.	New York	50000	Verenigde Staten
...	...	...	...	...	...	...	...

Notitie

U ziet dat de tabel twee records voor Navin Jones bevat. Beide records gebruiken dezelfde alternatieve sleutel om deze persoon te identificeren (I-543), maar elke record heeft een andere surrogaatsleutel. Van daaruit kunt u zien dat de klant is verhuisd van Seattle naar New York. De verkopen die aan de klant zijn gedaan terwijl ze in Seattle wonen, zijn gekoppeld aan de sleutel 123, terwijl aankopen die zijn gedaan na de overstap naar New York, worden geregistreerd tegen record 126.

Naast dimensietabellen die bedrijfsentiteiten vertegenwoordigen, is het gebruikelijk dat een datawarehouse een dimensietabel bevat die de tijd vertegenwoordigt. Met deze tabel kunnen gegevensanalisten gegevens met tijdelijke intervallen aggregeren. Afhankelijk van het type gegevens dat u moet analyseren, kan de laagste granulariteit (aangeduid als de korrel) van een tijddimensie tijdsdimensie vertegenwoordigen (tot het uur, seconde, milliseconden, nanoseconden of zelfs lager) of datums.

Een voorbeeld van een tijddimensietabel met een korrel op het datumniveau kan de volgende gegevens bevatten:

DateKey	DateAltKey	DayOfWeek	DayOfMonth	Weekday	Month	MonthName	Kwartaal	Jaar
19990101	01-01-1999	6	1	Vrijdag	1	januari	1	1999
...	...	...	...	...	...	...	...	...
20220101	01-01-2022	7	1	Zaterdag	1	januari	1	2022
20220102	02-01-2022	1	2	Zondag	1	januari	1	2022
...	...	...	...	...	...	...	...	...
20301231	31-12-2030	3	31	Dinsdag	12	December	4	2030

De tijdsperiode die wordt gedekt door de records in de tabel, moet het vroegste en laatste tijdstip bevatten voor alle gekoppelde gebeurtenissen die zijn vastgelegd in een gerelateerde feitentabel. Meestal is er een record voor elk interval tussen de juiste korrels.

Feitentabellen

Feitentabellen bevatten details van waarnemingen of gebeurtenissen; Bijvoorbeeld verkooporders, aandelensaldi, wisselkoersen of geregistreerde temperaturen. Een feitentabel bevat kolommen voor numerieke waarden die kunnen worden geaggregeerd op dimensies. Naast de numerieke kolommen bevat een feitentabel belangrijke kolommen die verwijzen naar unieke sleutels in gerelateerde dimensietabellen.

Een feitentabel met details van verkooporders kan bijvoorbeeld de volgende gegevens bevatten:

OrderDateKey	CustomerKey	StoreKey	ProductKey	OrderNo	LineItemNo	Hoeveelheid	UnitPrice	Belasting	ItemTotal
20220101	123	5	701	1001	1	2	2,50	0,50	5.50
20220101	123	5	765	1001	2	1	2.00	0.20	2,20
20220102	125	2	723	1002	1	1	4,99	0,49	5.48
20220103	126	1	823	1003	1	1	7.99	0.80	8.79
...	...	...	...	...	...	...	...	...	...

De dimensiesleutelkolommen van een feitentabel bepalen het graan. De feitentabel verkooporders bevat bijvoorbeeld sleutels voor datums, klanten, winkels en producten. Een bestelling kan meerdere producten bevatten, dus het graan vertegenwoordigt regelitems voor afzonderlijke producten die op specifieke dagen aan klanten worden verkocht.

Schemaontwerpen voor datawarehouses

In de meeste transactionele databases die worden gebruikt in zakelijke toepassingen, worden de gegevens genormaliseerd om duplicatie te verminderen. In een datawarehouse worden de dimensiegegevens over het algemeen gedenormaliseerd om het aantal joins te verminderen dat nodig is om een query uit te voeren op de gegevens.

Vaak is een datawarehouse geordend als een stervormig schema, waarin een feitentabel rechtstreeks is gerelateerd aan de dimensietabellen, zoals wordt weergegeven in dit voorbeeld:

De kenmerken van een entiteit kunnen worden gebruikt voor het aggregeren van metingen in feitentabellen over meerdere hiërarchische niveaus, bijvoorbeeld om de totale omzet per land of regio, plaats, postcode of afzonderlijke klant te vinden. De kenmerken voor elk niveau kunnen worden opgeslagen in dezelfde dimensietabel. Wanneer een entiteit echter een groot aantal hiërarchische kenmerkniveaus heeft, of wanneer sommige kenmerken kunnen worden gedeeld door meerdere dimensies (bijvoorbeeld klanten en winkels hebben een geografisch adres), kan het zinvol zijn om enige normalisatie toe te passen op de dimensietabellen en een sneeuwvlokschema te maken, zoals wordt weergegeven in het volgende voorbeeld:

In dit geval is de tabel DimProduct genormaliseerd om afzonderlijke dimensietabellen te maken voor productcategorieën en leveranciers, en er is een DimGeography-tabel toegevoegd om geografische kenmerken voor zowel klanten als winkels weer te geven. Elke rij in de tabel DimProduct bevat sleutelwaarden voor de bijbehorende rijen in de tabellen DimCategory en DimSupplier . Elke rij in de tabellen DimCustomer en DimStore bevat een sleutelwaarde voor de bijbehorende rij in de tabel DimGeography .