Dimensie bijwerken in een Datawarehouse

In een voorgaand artikel schreef ik over het voordeel van het afhandelen van wijzigingen binnen een dimensie volgens het Kimball Slowly Changing Dimension Type 2 principe, het aanmaken van een nieuwe rij binnen een dimensie. Er is uitgelegd wat de voordelen van deze methode zijn en hoe het werkt. Wat ik nog niet heb uitgelegd is hoe je dit kunt realiseren. In dit artikel bespreek ik de details van het bijwerken van een dimensie volgens type 2 met SQL scripts. Eerst behandel ik de standaard zaken die gecontroleerd moeten worden, vervolgens ga ik in op de keuze tussen een ETL-Tool zoals SSIS en tot slot behandel ik hoe je een dimensie bijwerkt. Het is vrij technisch en complex. Schroom niet om een te reageren of input te geven.

Wat controleer je?
Het bijwerken van een dimensie is saai. Negen van de tien keer doe je hetzelfde truukje opnieuw. Je pakt een datastroom uit een bronsysteem en een datastroom uit een dimensie. Deze houd je tegen elkaar aan en vervolgens controleer je altijd drie dingen:

• Zijn er wijzingen opgetreden;
• Zijn er deletes;
• Zijn er logische deletes.

Een wijziging is een verandering van een veld in een bronsysteem die impact heeft op een attribuut binnen een dimensie, bijvoorbeeld: de achternaam van een klant is veranderd doordat hij is getrouwd. Een delete is een record dat fysiek niet meer aanwezig is binnen een bronsysteem en een logische delete is een record dat met een markering als verwijderd is gekenmerkt.

Tool of SQL?
Voor het controleren van deze drie eigenschappen zijn verschillende methoden. Je kunt een ETL-tool gebruiken zoals SQL Server Integration Services of je kunt zelf een script bouwen / genereren die deze controle voor je doet. Ik ben altijd een voorstander geweest van tools. Tools zijn zelf-documenterend en nemen veel werk uit handen. Nu ik met een project bezig ben waarbij veel acties met SQL worden afgehandeld zonder tools zie ik steeds meer de voordelen van het links laten liggen van tools.

Voordelen:

• Voorkomt problemen met versieovergang
• Genereerbaar
• Werkt altijd
• Beheersbaarheid

Met de eerste bedoel ik de problemen die ontstaan wanneer de leverancier van een ETL-tool besluit een nieuwe versie op de markt te brengen die net iets beter is dan de vorige en dan na een tijdje besluit de oude versie niet meer te ondersteunen. Met SQL heb je dit niet. De basis van SQL blijft altijd hetzelfde, ook na een versieovergang. Met genereerbaar bedoel ik dat je met dynamische SQL objecten kunt genereren die het werk voor je doen. Denk aan een stored procedure. Wanneer ik SSIS (Integration Services) besluit in te zetten moet ik voor iedere dimensietabel een SSIS-package aanmaken (vanuit een template) en moet ik altijd handmatig dingen aanpassen. Een SSIS-package kun je NOOIT genereren! Daarnaast moet ik al die honderderen SSIS packages aanpassen als er iets veranderd (beheersbaarheid). Tot slot, SQL is betrouwbaar en werkt altijd, van SSIS is dat nog maar de vraag. Ik heb meerdere keren vage bugs en meldingen gezien die opeens ontstonden en opeens verdwenen!

Nadelen:

• Complexer en minder overzichtelijk
• Meer initieel ontwikkelwerk

ETL tools zijn zelf-documentered. Door gebruik te maken van tools zoals een Dataflowtask of een Derived Column zie je wat er gebeurd. Ook zijn tools sneller te begrijpen dan lappen code. Tot slot is het bouwen van je eigen ETL natuurlijk veel meer werk. Toch pluk je hier denk ik op langere termijn de vruchten van. Staat je oplossing eenmaal dan is het datawarehouse schaalbaarder en beter beheersbaar. Tot zover mijn mening over het gebruik van SQL vs TOOLS. Nu ben je natuurlijk nieuwsgierig geworden hoe je dimensies kunt bijwerken en welke standaard methode ik hiervoor gebruik.

Bijwerken dimensie

Algemeen
Voor het bijwerken van een dimensie werk ik met een tussentabel voor de records die nieuw zijn en die van type 2 nieuw zijn (dus een nieuwe versie). Ik ga dus eerst een tussentabel vullen voordat ik dimensie bijwerk. Voor het afsluiten van oude records maak ik gebruik van een zogenaamde sleuteltabel. Het idee achter deze methode is dat eerst alle inserts/updates/deletes worden onderkend en dat de tussentabel en de sleuteltabel worden gevuld. Vervolgens kunnen we in twee stappen de dimensie bijwerken. Eerst laden we alle records van de tussentabel naar de dimensie en vervolgens werken we alle verlopen/verwijderde records bij. Hierbij moet je denken aan het goedzetten van de einddatum, het updaten van de previous/nextid en het updaten van MetaIsCurrent.

{module Easy Adsense Content}

Tabelopbouw

Dimensienaam_REG
Dit is de dimensietabel waarin alle records uiteindelijk komen te staan. De opbouw van de tabel is altijd hetzelfde. De _ID is de surrogaatsleutel, de lookuphash is een binair veld en is de omgetoverde bronsleutel (ivm performance). Veld1,2,3 zijn voorbeeld attributen,BronLogicalDelete is een bronveld dat aangeeft of het record logisch is verwijderd en de overige velden zijn metavelden.

{geshi xml:lang=”tsql” lines=”false”}
CREATE TABLE [dbo].[dim_DimensieNaam_REG](
[DimensieNaam_ID] [int] NOT NULL,
[LookupHash] [binary](20) NULL,
[Veld1] [varchar](32) NULL,
[Veld2] [varchar](32) NULL,
[Veld3] [varchar](32) NULL,
[BronLogicalDelete] [bit] NULL,
[MetaPreviousID] [int] NOT NULL,
[MetaCurrentID] [int] NOT NULL,
[MetaNextID] [int] NOT NULL,
[MetaAuditID] [int] NOT NULL,
[MetaStartDate] [datetime] NOT NULL,
[MetaEndDate] [datetime] NOT NULL,
[MetaRowDate] [datetime] NOT NULL,
[MetaIsActive] [bit] NOT NULL,
[MetaIsDeleted] [bit] NOT NULL,
[MetaIsLogicalDeleted] [bit] NOT NULL,
[MetaIsCurrent] [bit] NOT NULL,
[MetaIsInsert] [bit] NOT NULL,
[MetaIsUpdate] [bit] NOT NULL
) {/geshi}

DimensieNaam_TMP
Dit is de tijdelijke tabel die wordt gevuld voordat de dimensie wordt bijgewerkt. De opbouw is exact hetzelfde als bovenstaande, alleen de naam van de tabel is Dimensienaam_TMP.

DimensieNaam_KEY
Dit is de tabel met sleutels die moeten worden bijgewerkt. UpdateType geeft aan wat voor soort update het is. Zo kan het een delete zijn, een logische delete of een update van de metapreviousid, dit geven we hier aan. ID is de surrogaatsleutel in de dimensie die moet worden bijgewerkt. SetID of UpdateDateTime worden gevuld met de waarde zoals deze moet worden gevuld. Deze laatste is optioneel.

{geshi xml:lang=”tsql” lines=”false”}
CREATE TABLE [dbo].[dim_DimensieNaam_KEY](
[UpdateType] [int] NOT NULL,
[ID] [int] NOT NULL,
[SetID] [int] NOT NULL,
[UpdateDateTime] [datetime] NULL
){/geshi}

Tot slot heb je nog een view nodig waarin de SQL-querie zit die als input dient voor de dimensie. Deze noem je: vw_dim_DimensieNaam_REG.

Hieronder beschrijf ik de inhoud van een aantal stored procedures die een dimensie bijwerken. Let op! Deze stored procedures hoef ik niet met de hand aan te maken maar worden voor mij gegenereerd zodra ik een nieuwe dimensie ga aanmaken. Ik heb diverse stored procedures die deze stored procedures en andere zaken die nodig zijn voor het bijwerken van mijn dimensie voor mij genereren! In mijn eigen omgeving zijn onderstaand stappen nog uitgebreider omdat ik een HSA heb. Ik kan door een set van datums loopen om een dimensie op te bouwen vanuit een historische laag.

Stap 0 – Initieer laden
Eerst dienen er wat initiele acties te worden uitgevoerd voordat we daadwerkelijk kunnen gaan laden. Zo moeten de _TEMP tabel en de _KEY tabel worden geleegd en moet het IDENTveld worden geinitieerd.  In de temp-tabel willen we namelijk de surrogatkey gaan nummeren vanaf de laatst bekende waarde in de dimensie, anders krijgen we sleutelgaten. De surrogaatsleutel in de _TEMP is een IDENT-veld, in de dimensie niet.

{geshi xml:lang=”tsql” lines=”false”}
— BEPAAL AANTAL RIJEN IN DE REG TABEL
DECLARE @NOROWS INT
SET @NOROWS = 0
SELECT @NOROWS = t1.rows FROM sys.sysindexes t1
WHERE t1.id = OBJECT_ID(‘[dim_DimensieNaam_REG]’)
AND t1.indid in (0, 1, 255)

— TRUNCATE DE KEY TABEL
TRUNCATE TABLE dbo.[dim_DimensieNaam_KEY]

— TRUNCATE DE TMP TABEL
TRUNCATE TABLE dbo.[dim_DimensieNaam_TMP]

— ZET IDENTITY OP JUISTE START WAARDE
DBCC CHECKIDENT (
‘dbo.[dim_DimensieNaam_TMP]’
, RESEED
, @NOROWS
) WITH NO_INFOMSGS

END{/geshi}

Stap 1 – Controleer op nieuwe records
Hier vergelijken we de inhoud van de dimensie (dim_DimensieNaam_REG) met die van de dimensieview (vw_dim_DimensieNaam_REG). Nieuwe records zetten we in de _TEMP tabel. De @AuditID moet je zelf ophalen invullen. Dit is een kenmerk van de ETL-run.

{geshi xml:lang=”tsql” lines=”false”}
INSERT INTO dbo.[dim_DimensieNaam_TMP] ([LookupHash] ,[DimensieNaamCode] ,[Veld1] ,[Veld2] ,[Veld3] ,[BronLogicalDelete] ,    [MetaAuditID]           
,    [MetaStartDate]           
,    [MetaEndDate]               
,    [MetaRowDate]               
,    [MetaIsActive]               
,    [MetaIsDeleted]               
,    [MetaIsLogicalDeleted]       
,    [MetaIsCurrent]               
,    [MetaIsInsert]               
,    [MetaIsUpdate]    )           
SELECT
vw.*
,@AUDITID    AS    [MetaAuditID]
,GETDATE() AS [MetaStartDate]
,CASE WHEN vw.[BronLogicalDelete] = 1 THEN GETDATE() ELSE CAST(‘9999-01-01’ AS DATETIME) END    AS [MetaEndDate]
,GETDATE()    AS    [MetaRowDate] ,CASE WHEN vw.[BronLogicalDelete] = 1 THEN 0 ELSE 1 END    AS    [MetaIsActive]
,0        AS    [MetaIsDeleted]
,CASE WHEN vw.[BronLogicalDelete] = 1 THEN 1 ELSE 0 END    AS    [MetaIsLogicalDeleted] ,1        AS    [MetaIsCurrent]
,1        AS    [MetaIsInsert]
,0        AS    [MetaIsUpdate]

FROM dbo.[DimensieNaam_REG] AS vw
LEFT OUTER JOIN dbo.[vw_dim_DimensieNaam_REG] dim
ON vw.[LookupHash] = dim.[LookupHash] WHERE dim.[LookupHash] IS NULL{/geshi}

 

Stap 2 – Controleer op wijzigingen
Hier kijken we of er een veld is gewijzigd. Zoja dan maken we een nieuw record aan. Het oude wordt later afgesloten. Onderaan de code staan vreemde tekens, die moet je veranderen in <>. Het zijn deze tekens: &lt;&gt;

{geshi xml:lang=”tsql” lines=”false”}
INSERT INTO dbo.[dim_DimensieNaam_TMP]([LookupHash],[Veld1],[Veld2],[Veld3],[BronLogicalDelete],    [MetaAuditID]          
,    [MetaStartDate]          
,    [MetaEndDate]              
,    [MetaRowDate]              
,    [MetaIsActive]              
,    [MetaIsDeleted]              
,    [MetaIsLogicalDeleted]      
,    [MetaIsCurrent]              
,    [MetaIsInsert]              
,    [MetaIsUpdate]    )          
SELECT
vw.*
,@AUDITID    AS    [MetaAuditID],GETDATE() AS [MetaStartDate],’9999-01-01′    AS [MetaEndDate],GETDATE()    AS    [MetaRowDate],1        AS    [MetaIsActive],0        AS    [MetaIsDeleted],0        AS    [MetaIsLogicalDeleted],1        AS    [MetaIsCurrent],0        AS    [MetaIsInsert],1        AS    [MetaIsUpdate]

FROM dbo.[vw_dim_DimensieNaam_REG] AS vw
INNER join dbo.[dim_DimensieNaam_REG] dim
ON (vw.[LookupHash] = dim.[LookupHash]AND dim.[MetaIsCurrent] = 1)
— check of er een wijziging heeft opgetreden

WHERE
CASE
WHEN vw.[LookupHash] <> dim.[LookupHash] THEN 1
WHEN vw.[Veld1] <> dim.[Veld1] THEN 1

WHEN vw.[Veld2] <> dim.[Veld2] THEN 1
WHEN vw.[Veld3] <> dim.[Veld3] THEN 1
WHEN vw.[BronLogicalDelete] <> dim.[BronLogicalDelete] THEN 1      
ELSE 0 END = 1{/geshi}

 

Stap 3 – Sleutelupdates bepalen MetaPreviousID en MetaNextID
Nu hebben we als het goed is de temptabel gevuld met alle nieuwe records en alle gewijzigde type 2 records (nieuwe versie). Nu we deze tabel hebben gevuld weten we dus ook welke oude records afgesloten moeten worden, dit doen we door te kijken naar de LookupHash. De LookupHash is een binair veld dat de bronsleutel vertegenwoordigd. Dit is een lastig stukje code, kijk er maar goed naar en hopelijk begrijp je het .

{module Easy Adsense Content}

{geshi xml:lang=”tsql” lines=”false”}
— STAP 1: VUL DE SLEUTELTABEL MET DE RECORDS WAARVAN DE METACURRENTID
— MOET WORDEN BIJGEWERKT

INSERT INTO dbo.[dim_DimensieNaam_KEY] (ID, UpdateType, SetID, UpdateDateTime)
(
SELECT  T3.ID,
1 AS UpdateType,
T1.LastID AS SetID,
T2.MetaStartDate AS UpdateDateTime
FROM    ( SELECT    [LookupHash],
MAX([DimensieNaam_ID]) AS LastID
FROM      dbo.[dim_DimensieNaam_TMP] AS IT
WHERE     MetaIsUpdate = 1
GROUP BY  [LookupHash] ) AS T1
INNER JOIN dbo.[dim_DimensieNaam_TMP] AS T2
ON T1.LastID = T2.[DimensieNaam_ID]
INNER JOIN ( SELECT [DimensieNaam_ID] AS ID,
[LookupHash] FROM   dbo.[dim_DimensieNaam_TMP] UNION ALL
SELECT [DimensieNaam_ID] AS ID,
[LookupHash] FROM   dbo.[dim_DimensieNaam_REG] ) AS T3
ON ( T1.[LookupHash] = T3.[LookupHash] AND T3.ID < T1.LastID)
)   

— STAP 2: VUL DE SLEUTELTABEL MET DE RECORDS WAARVAN DE METANEXTID EN METAPREVIOUSID
— MOET WORDEN BIJGEWERKT

INSERT INTO dbo.[dim_DimensieNaam_KEY] (ID, UpdateType, SetID, UpdateDateTime)
(
SELECT T3.ID,
2 AS UpdateType,
T3.PreviousID AS SetID,
T4.MetaStartDate AS UpdateDateTime
FROM   ( SELECT    T1.[LookupHash],
T1.ID ,
MAX(T2.ID) AS PreviousID
FROM      ( SELECT [DimensieNaam_ID] AS ID,
[LookupHash] FROM   dbo.[dim_DimensieNaam_TMP] WHERE     MetaIsUpdate = 1
) T1
INNER JOIN ( SELECT [DimensieNaam_ID] AS ID,
[LookupHash] FROM   dbo.[dim_DimensieNaam_TMP] UNION ALL
SELECT [DimensieNaam_ID] AS ID,
[LookupHash] FROM   dbo.[dim_DimensieNaam_REG] ) T2
ON (T1.[LookupHash] = T2.[LookupHash] AND T2.ID < T1.ID)
GROUP BY  T1.[LookupHash],
T1.ID
) T3
INNER JOIN dbo.[dim_DimensieNaam_TMP] AS T4
ON T3.ID = T4.[DimensieNaam_ID] ){/geshi}

 

Stap 5 – Bepalen Deletes
De volgende stap is te kijken welke records niet meer in de bron staan. Deze worden ook in de sleuteltabel geplaatst.

{geshi xml:lang=”tsql” lines=”false”}
INSERT INTO dbo.[dim_DimensieNaam_KEY]  (UpdateType , ID, SetID, UpdateDateTime)
SELECT 3
, dim.[DimensieNaam_ID] , 0
, GETDATE()
FROM dbo.[dim_DimensieNaam_REG] dim
left outer join dbo.[vw_dim_DimensieNaam_REG] AS vw
ON (vw.[LookupHash] = dim.[LookupHash])
WHERE vw.[LookupHash] IS NULL
AND dim.[DimensieNaam_ID] > 0
AND dim.[MetaIsCurrent] = 1
AND dim.[MetaIsActive] = 1{/geshi}

 

Stap 6 – Bepalen Logische Deletes
Logica in de dimensieview bepaald wanneer een bronrecord logisch is verwijderd, bijvoorbeeld als een bepaald veld in het bronsysteem op TRUE staat.

{geshi xml:lang=”tsql” lines=”false”}
INSERT INTO dbo.[dim_DimensieNaam_KEY]  (UpdateType , ID, SetID, UpdateDateTime)
SELECT 4
, dim.[DimensieNaam_ID] , 0
, GETDATE()
FROM dbo.[dim_DimensieNaam_REG] dim
join dbo.[vw_dim_DimensieNaam_REG] AS vw
ON (vw.[LookupHash] = dim.[LookupHash])
WHERE vw.[BronLogicalDelete] = 1
AND dim.[DimensieNaam_ID] > 0
AND dim.[MetaIsCurrent] = 1
AND dim.[MetaIsActive] = 1{/geshi}

 

Stap 7 – Van tussentabel naar Dimensie
Op dit moment zijn de sleuteltabel en de tussentabel bijgewerkt. In de tussentabel staan nu alle nieuwe records en alle type 2 nieuwe records. In de sleuteltabel staan alle dimensiesleutels die moeten worden bijgewerkt, de deletes, logische deletes, metaprevious/next/current id’s. Nu kunnen we in twee stappen de hele dimensie bijwerken. De eerste stap is het verplaatsen van de records vanuit de tussentabel naar de dimensie.

{geshi xml:lang=”tsql” lines=”false”}
INSERT INTO dbo.[dim_DimensieNaam_REG] SELECT * FROM dbo.[dim_DimensieNaam_TMP]{/geshi}

 

Stap 8 – Van sleuteltabel naar Dimensie
Idemdito voor de sleutels. Deze stap is wat complexer en is opgesplitst in meerdere delen. In de _KEY tabel staat een UpdateType. Deze bepaalt wat er moet worden bijgewerkt. Zo is UpdateType 3 een delete en UpdateType 1 een MetaCurrentID-update.

{geshi xml:lang=”tsql” lines=”false”}
— STAP 1 – PROCESS METACURRENTID
UPDATE DR
SET  MetaCurrentID = ST.SetID,
MetaIsCurrent = 0   
FROM dbo.[dim_DimensieNaam_REG] AS DR
INNER JOIN dbo.[dim_DimensieNaam_KEY] AS ST
ON DR.[DimensieNaam_ID] = ST.ID
AND ST.UpdateType = 1

— STAP 2 – PROCESS METAPREVIOUSID

UPDATE DR
SET  MetaPreviousID = ST.SetID
FROM dbo.[dim_DimensieNaam_REG] AS DR
INNER JOIN dbo.[dim_DimensieNaam_KEY] AS ST
ON DR.[DimensieNaam_ID] = ST.ID
AND ST.UpdateType = 2

— STAP 3 – PROCESS METANEXTID

Update DR
Set MetaNextID = ST.ID, 
MetaEndDate = ST.UpdateDateTime,
MetaIsCurrent = 0   
FROM dbo.[dim_DimensieNaam_REG] AS DR
INNER JOIN dbo.[dim_DimensieNaam_KEY] AS ST
ON DR.[DimensieNaam_ID] = ST.SetID
AND ST.UpdateType = 2

— STAP 4 – PROCESS DELETES

Update DR
Set MetaIsActive = 0, 
MetaEndDate = ST.UpdateDateTime,
MetaIsDeleted = 1,
MetaIsCurrent = 0   
FROM dbo.[dim_DimensieNaam_REG] AS DR
INNER JOIN dbo.[dim_DimensieNaam_KEY] AS ST
ON DR.[DimensieNaam_ID] = ST.ID
AND ST.UpdateType = 3

— STAP 5 – PROCESS LOGICALDELETES

Update DR
Set MetaIsActive = 0, 
MetaEndDate =  ST.UpdateDateTime,
MetaIsLogicalDeleted = 1,   
MetaIsCurrent = 0   
FROM dbo.[dim_DimensieNaam_REG] AS DR
INNER JOIN dbo.[dim_DimensieNaam_KEY] AS ST
ON DR.[DimensieNaam_ID] = ST.ID
AND ST.UpdateType = 4{/geshi}

Conclusie
Dit zijn de stappen die doorlopen moeten worden voor een volledige type 2 verwerking. Het is erg veel informatie en complexe SQL maar als het eenmaal geimplementeerd is draait het als een zonnetje zonder gebruik te maken van een ETL-tool. Bovenstaande queries zijn in het echt allemaal stored procedures die worden gegenereerd, niets meer met de hand doen dus! Zodra de dimensieview is aangemaakt heb ik een procedure die de view ‘registreert’ in mijn datawarehouse. Op dat moment zijn ook alle kolommen bekend. Dit is bijvoorbeeld nodig om te kijken welke velden gewijzigd zijn. Daarnaast zitten er diverse try-catch constructies in voor error-afhandeling (die heb ik weggelaten) en rowcounts om de verwerkte rijen te tellen. Ook vind er auditing plaats en tot slot wordt er voor een dimensie een soort masterpackage aangemaakt die alle bovenstaande procedures aanschopt. Kortom, een robuuste, schaalbare en snelle oplossing!

Laat een bericht achter als je vragen hebt over een bepaalde constructie of keuze.