Updating a dimension in a data warehouse

In an earlier article I wrote about the benefit of handling changes in a dimension with the Kimball Slowly Changing Dimension Type 2 principle: creating a new row in a dimension. That article explained the benefits of this method and how it works. What I have not explained yet is how you build it. In this article I go through the details of updating a dimension as type 2 with SQL scripts. First I cover the standard things you must check. Then I look at whether to use an ETL tool such as SSIS, and finally I cover how you update a dimension. It is fairly technical and complex. Feel free to comment or give input.

What do you check?
Updating a dimension is boring. Nine times out of ten you do the same trick again. You take a data stream from a source system and a data stream from a dimension. You hold them against each other, and then you always check three things:

• Have there been changes;
• Are there deletes;
• Are there logical deletes.

A change is a change to a field in a source system that affects an attribute in a dimension. For example: the last name of a customer changed because he got married. A delete is a record that physically no longer exists in a source system, and a logical delete is a record that is flagged as deleted.

Tool or SQL?
There are different methods to check these three things. You can use an ETL tool such as SQL Server Integration Services, or you can build / generate a script yourself that does this check for you. I have always been in favour of tools. Tools document themselves and take a lot of work off your hands. Now that I work on a project where a lot of actions are handled with SQL and no tools, I see the benefits of leaving tools aside more and more.

Benefits:

• Prevents problems with version upgrades
• Can be generated
• Always works
• Manageability

By the first one I mean the problems that come up when the vendor of an ETL tool decides to bring out a new version that is just a bit better than the previous one, and after a while decides to stop supporting the old version. You do not have this problem with SQL. The basics of SQL always stay the same, even after a version upgrade. By “can be generated” I mean that you can use dynamic SQL to generate objects that do the work for you. Think of a stored procedure. When I decide to use SSIS (Integration Services), I have to create an SSIS package for every dimension table (from a template), and I always have to change things by hand. You can NEVER generate an SSIS package! On top of that, I have to change all those hundreds of SSIS packages when something changes (manageability). Finally, SQL is reliable and always works; with SSIS that remains to be seen. Several times I have seen odd bugs and messages that suddenly appeared and suddenly disappeared!

Downsides:

• More complex and harder to follow
• More development work up front

ETL tools document themselves. When you use tools such as a Dataflowtask or a Derived Column, you see what happens. Tools are also quicker to understand than pages of code. Finally, building your own ETL is of course a lot more work. Still, I think you reap the rewards of it in the long run. Once your solution is in place, the data warehouse scales better and is easier to manage. So much for my opinion about SQL vs TOOLS. By now you are of course curious how you can update dimensions and which standard method I use for it.

Updating the dimension

**
General**
To update a dimension, I work with an intermediate table for the records that are new and the records that are a new type 2 version. So I first fill an intermediate table before I update the dimension. To close old records, I use a so-called key table. The idea behind this method is that first all inserts/updates/deletes are detected and the intermediate table and the key table are filled. Then we can update the dimension in two steps. First we load all records from the intermediate table into the dimension, and then we update all expired/deleted records. Think of setting the end date correctly, updating the previous/nextid and updating MetaIsCurrent.

{module Easy Adsense Content}

Table structure

Dimensienaam_REG
This is the dimension table where all records end up. The structure of the table is always the same. The _ID is the surrogate key. The lookuphash is a binary field and is the converted source key (for performance). Veld1,2,3 are example attributes, BronLogicalDelete is a source field that shows whether the record is logically deleted, and the other fields are meta fields.

{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
This is the temporary table that is filled before the dimension is updated. The structure is exactly the same as the one above. Only the name of the table is Dimensienaam_TMP.

DimensieNaam_KEY
This is the table with keys that must be updated. UpdateType shows what kind of update it is. It can be a delete, a logical delete or an update of the metapreviousid; we store that here. ID is the surrogate key in the dimension that must be updated. SetID or UpdateDateTime hold the value to set. The latter is optional.

{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}

Finally, you also need a view that holds the SQL query that is the input for the dimension. You call it: vw_dim_DimensieNaam_REG.

Below I describe the content of a number of stored procedures that update a dimension. Note! I do not have to create these stored procedures by hand. They are generated for me as soon as I create a new dimension. I have several stored procedures that generate these stored procedures, and the other things needed to update my dimension, for me! In my own environment the steps below are even more extensive, because I have an HSA. I can loop through a set of dates to build a dimension from a historical layer.

**Step 0 - Initialise loading
**First some initial actions must be done before we can actually start loading. The _TEMP table and the _KEY table must be emptied, and the IDENT field must be initialised. In the temp table we want to number the surrogate key from the last known value in the dimension, otherwise we get gaps in the keys. The surrogate key in the _TEMP is an IDENT field, in the dimension it is not.

{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}

**Step 1 - Check for new records
**Here we compare the content of the dimension (dim_DimensieNaam_REG) with that of the dimension view (vw_dim_DimensieNaam_REG). We put new records in the _TEMP table. You have to get and fill in the @AuditID yourself. This is an identifier of the 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}

**Step 2 - Check for changes
**Here we check whether a field has changed. If so, we create a new record. The old one is closed later. At the bottom of the code are strange characters; you must change them to <>. These are the characters: <>

{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}

**Step 3 - Determine key updates MetaPreviousID and MetaNextID
**If all went well, we have now filled the temp table with all new records and all changed type 2 records (new version). Now that we have filled this table, we also know which old records must be closed. We do this by looking at the LookupHash. The LookupHash is a binary field that represents the source key. This is a tricky piece of code; look at it closely and hopefully you understand it 😆 .

{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}

**Step 5 - Determine Deletes
**The next step is to check which records are no longer in the source. These also go into the key table.

{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}

**Step 6 - Determine Logical Deletes
**Logic in the dimension view decides when a source record is logically deleted, for example when a certain field in the source system is set to TRUE.

{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}

**Step 7 - From intermediate table to Dimension
**At this point the key table and the intermediate table are updated. The intermediate table now holds all new records and all new type 2 records. The key table holds all dimension keys that must be updated: the deletes, logical deletes, metaprevious/next/current id’s. Now we can update the whole dimension in two steps. The first step is moving the records from the intermediate table to the dimension.

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

**Step 8 - From key table to Dimension
**The same for the keys. This step is a bit more complex and is split into several parts. The _KEY table holds an UpdateType. It decides what must be updated. For example, UpdateType 3 is a delete and UpdateType 1 is a 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}

**Conclusion
**These are the steps you go through for a complete type 2 load. It is a lot of information and complex SQL, but once it is implemented, it runs like clockwork without an ETL tool. In real life, the queries above are all stored procedures that are generated, so nothing is done by hand anymore! As soon as the dimension view is created, I have a procedure that ‘registers’ the view in my data warehouse. At that point all columns are known as well. This is needed, for example, to check which fields have changed. There are also several try-catch constructions for error handling (I left those out) and rowcounts to count the processed rows. There is auditing too, and finally a kind of master package is created for each dimension that kicks off all the procedures above. In short: a solid, scalable and fast solution!

Leave a message if you have questions about a certain construction or choice.