Why not just Type 2?

In a Slowly Changing Dimension scenario you normally decide per field whether it is a type 1, type 2 or type 3 field. With a type 1 the record is overwritten with the new value, with a type 2 a new record is created, and with a type 3 the old value is kept as an extra column in the dimension. But why not treat everything as type 2?

The type 2 approach
All changes that have an impact on the dimension are handled as a type 2 change. To meet every possible reporting requirement, this approach has a lot of advantages. Because you handle all changes as a type 2, you can easily find everything and the full history stays available.

The dimension gets a few extra meta fields to meet the reporting requirements:

• MetaPreviousID
• MetaNextID
• MetaCurrentID

The example below makes this clearer:

CompanyID	CompanyCode	Naam	Adres	MetaPreviousID	MetaNextID	MetaCurrentID	MetaStartDate	MetaEndDate	IsCurrent
1	100	KPN	AAA	1	2	3	2009-01-01	2009-01-05	0
2	100	KPN	BBB	1	3	3	2009-01-06	2009-01-12	0
3	100	KPN	CCC	2		3	2009-01-13	9999-12-31	1

For the Company with code 100, changes were made to the address over time. As a result, two new records were created. When the dimension is updated, the previousid, nextid and currentid are updated as well. Because you keep track of this, you can find the newest version of a record at any moment.

{module Easy Adsense Content}

Say a fact record is created on 2 January 2009. The matching dimension record is the record with ID = 1. The fact is now filled with ID = 1, so there is a relation between the fact and the dimension. Maybe the report users are not interested at all in the old address “AAA” of this Company. Normally this field would be a Type 1 field, so the address is overwritten every time and the current address is reported. We now handle this with a recursive join on the dimension. With the MetaCurrentID you can easily find the active dimension row. The SQL would look roughly like this:

{geshi xml:lang=“tsql” lines=“false”}
SELECT    DC.Adres AS  ToenGeldigeAdres,
DC2.Adres AS CurrentAdres,
DC3.Adres AS VorigeAdres
FROM FactVerkopen AS FV
INNER JOIN DimCompany AS DC ON FV.CompanyID = DC.CompanyID
INNER JOIN DimCompany AS DC2 ON DC.MetaCurrentID = DC2.CompanyID
INNER JOIN DimCompany AS DC3 ON DC.MetaPreviousID = DC3.CompanyID{/geshi}

As you can see, this gives you every possible combination. You can find the address that was valid at the moment the fact came in, the current address and the previous address (type 3).

Most people probably use a data mart on top of the data warehouse that only holds the data that is interesting to report on. The ETL that fills the data marts probably takes Views as its input. You build the views that feed your data mart in such a way that you only offer the information that is interesting for the end user. In the example above, if the users are not interested in the old address of the company, you only show the CurrentID!

Advantages:

• Full history of a record available
• Simple ETL, only inserts
• No need to think about history yet during ETL development, that comes later
• Type 1, 2 and 3 support
• What is my currently valid dimension record
• What was my previous valid dimension record
• What is my next valid dimension record

Disadvantages

• Extra storage
• Extra join to find the current or previous record
• CurrentID, NextID have to be updated every time.

The advantages outweigh the disadvantages. With a clustered index on the surrogate key the joins are fast, and the old type 2 records can be updated quickly (for example updating the currentID).

Comments welcome 😎

{module Easy Adsense Content}