In a dimensional model, you have a fact table and multiple dimension tables related to the fact table. Slowly Changing Dimension (SCD) is the technique to maintain the history of data in dimension tables.

The basic SCD has three types, called Type 1, 2, and 3. SCD Type 6 combines the three basic techniques. After introducing these three basic types, this article by Djoni Darmawikarta, shows by example what SCD Type 6 is and how it works.

The Example

We will apply SCD’s to maintain the history of Product dimension, specifically the history of changes of Product's Product Group. The PRODUCT_SK column is the surrogate key of the Product dimension table.

SCD Type 1

We will apply SCD Type 1 to the PENCIL product in the Product dimension table.

Let’s say PENCIL changes its product group into 4. Effecting this change by applying SCD Type 1 just updates the existing row of PENCIL on its product group. We do not have record of its previous product group; in other words, we do not maintain its product group history.

The updated PENCIL’s product group is shown highlighted in blue.

SCD Type 2

SCD Type 2 is essentially the opposite of Type 1. When we apply SCD Type 2, we never update or delete any existing product group.

To apply SCD Type 2 we need an effective date and an expiry date. Effective date 31-Dec-99 means the row is not expired. It is the most current version of the product.

Assuming the product group change of PENCIL is effective 1 April 2010, we update the expiry date of its existing row to 31 March 2010, one day before the effective date of the effective date of the change, and insert a new row that represents its new, current version.

SCD Type 3

With SCD Type 3 we maintain history but in one record only. We have one column for each version of the product group. You need to have as many columns as the number of versions you want to keep.

One of the most common SCD Type 3 applications is to maintain two versions of product group: the original version and the current version. When there is no product group change yet, the current product group is the same as the original product group.

When the pencil’s product group changes, let’s say on 1 April 2010, we expire its original product group by changing the expiry date to a day earlier (30 March 2010), and replace its current product group to the new product group.

When its product group changes again in the future, we will replace just the current product group with the new product group. The expiry date does not change. It gets updated once only the first time its product group changes.

SCD Type 6

SCD Type 6 combines the three basic types. Type 6 is particularly applicable if you want to maintain complete history and would also like have an easy way to effect on current version. Let’s apply Type 6 instead of Type 3 only.

We have applied Type 3 by having two versions of product group. When the pencil’s product group changes we update its existing current product group (that is Type 1 update). We also apply Type 2 by adding a new row.

On the next pencil’s product group change (1 July 2010), we will again apply all three SCD types.

QUERY

Let’s next see how our Type 6 in the Product dimension works on a sales fact. (In a real sales fact data you will have some other dimensions, meaning the fact table will have more surrogate key columns than just the product surrogate key)

If our interest is in the current version, our SQL query will use the current product group column. An example SQL query will look like:

SELECT current_product_group_name, SUM(sales_amt)
FROM sales_fact s, product_dim p
WHERE
s.product_sk = p.product_sk
AND product_name = ‘PENCIL’
GROUP BY current_product_group_code

The output of the query will be:

The reason of applying SCD Type 2 is to have a complete history that tracks changes. SQL queries that take into account dimension history use the product group column:

SELECT product_group_name, SUM(sales_amt)
FROM sales_fact s, product_dim p, date_dim d
WHERE
s.product_sk = p.product_sk
AND product_name = ‘PENCIL’
GROUP BY product_group_code

The output of the query will be:

SUMMARY

This article discusses what SCD Type 6 is, when to apply it, and how it works. The name Type 6 comes from the ‘sum’ of the three basic SCD types (6 = 1 + 2 + 3).

Oracle Database 11g – Underground Advice for Database Administrators

A real-world DBA survival guide for Oracle 11g database implementations with this Oracle book and eBook

Published: April 2010
eBook Price: £18.99
Book Price: £30.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

About the Author :

Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.

Books From Packt