SQL Server: Slowly Changing Dimension Part 2

Slowly Changing Dimension Part 2

This is a follow up article describing the remaining types and transformations available in slowly changing dimension for dimension data management. The part one of the series covered the details from Type 0, Type 1, Type 2 and Type 3. The part one of the series can be found here .

In this follow up article, we will see Type 4, Type 6 and the Combining Types.

Before we move ahead, the table below will serve as a quick recap of the transformations available for the SCD’s.

Type 4

In type 4, we maintain a separate a ‘history table’ which will house all historical data or changes made to the dimension data in a similar table. I say similar and not identical SQL table for a reason. The history table is designed may/may not have all the columns as in the original dimension table.

For example: The business might just want few columns to be preserved in the dimension ‘history table’   

Specific scenario:

Dimension table

Suppose the ‘State’ column is managed using type 4 SCD, in that case

Case 1: All columns are preserved in the history table.

Case 2:  Selected columns are maintained in the history table.

Expert Comment: The advantage over SCD type 2 is that, the underlying dimension table is not burdened with a set of legacy data or history changes. Thus the size of dimension table can be restricted by maintaining a separate table for history data, as described above.

Type 6

This approach for data management of the dimension data encompasses Type 1 + Type 2 + Type 3. This can be illustrated as below.

Current:

Table structure after employee ID 1234 moves to some other state

This kind of exhaustive approach for dimension data manageability describes type 6. Let us now try to understand more what makes Type 6 by a short recap for Type 1, Type 2 and Type 3.

• When the employee changes the state for the first time, the value of ‘Current State’ is updated with the new value.
• A new tuple with a new version key (here surrogate key) is created with added time stamps.
• Also, the values both current and original are maintained in separate columns so as to provide a complete picture of the ‘State’ transition.
• Here, we have another support feature of a boolean identifier (value of ‘Current’) which indicates the current active value of the entity in concern, here in this case the value of ‘Current’.

 Combining Type:

With this we shall end our attempt to revisit the SCD’s.

The combining type as the name suggests can be implemented to facilitate dimension data manageability in way which best suits the business requirements.

We have learnt that Type 6 is nothing but a composition of Type 1, Type 2 and Type 3. On similar lines, a combination of above listed SCD transformations can be composed to suit the business need and implement a SCD transformation which falls in the category of ‘Combining Type’.

Feel free to let me know your views and any other aspect of Data Warehousing concept; you would like me to compose a write-up on.

Sincerely,

Raunak Jhawar

e: raunak.jhawar@gmail.com

t: @raunakjhawar

f: www.facebook.com/raunak.jhawar