Slowly Changing Dimension (SCD) Type 6

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

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_NAME

PRODUCT_GROUP_CODE

PRODUCT_GROUP_NAME

1

11

PENCIL

1

WRITING SUPPLY

2

22

PEN

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPL

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_NAME

PRODUCT_GROUP_CODE

PRODUCT_GROUP_NAME

1

11

PENCIL

4

NON ELECTRONIC SUPPLY

2

22

PEN

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_NAME

PRODUCT_GROUP_CODE

PRODUCT_GROUP_NAME

EFFECTIVE_DATE

EXPIRY_DATE

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_NAME

PRODUCT_

GROUP

_CODE

PRODUCT_GROUP

_NAME

EFFECTIVE_DATE

EXPIRY_DATE

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Mar-10

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

5

11

PENCIL

4

NON ELECTRONIC SUPPLY

1-Apr-09

31-Dec-99

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_

NAME

PRODUCT_

GROUP_

CODE

PRODUCT_

GROUP_NAME

EFFECTIVE_

DATE

EXPIRY_

DATE

 

CURRENT_

PRODUCT_

GROUP_

CODE

CURRENT_

PRODUCT_

GROUP_NAME

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

 

1

WRITING SUPPLY

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

 

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

 

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

 

 

4

NON ELECTRONIC SUPPLY

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_

NAME

PRODUCT_

GROUP_CODE

PRODUCT_

GROUP_NAME

EFFECTIVE_

DATE

EXPIRY_

DATE

 

CURRENT_

PRODUCT_

GROUP_

CODE

CURRENT_

PRODUCT_

GROUP_

NAME

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Mar-10

 

 

4

NON ELECTRONIC SUPPLY

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

 

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

 

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

 

 

4

NON ELECTRONIC SUPPLY

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.

PRODUCT_SK

PRODUCT_CODE

PRODUCT_

NAME

PRODUCT_

GROUP_

CODE

PRODUCT_

GROUP_

NAME

EFFECTIVE_

DATE

EXPIRY_

DATE

 

CURRENT_

PRODUCT_

GROUP_

CODE

CURRENT_

PRODUCT_

GROUP_

NAME

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Mar-10

 

 

4

NON ELECTRONIC SUPPLY

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

 

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

 

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

 

 

4

NON ELECTRONIC SUPPLY

5

11

PENCIL

4

NON ELECTRONIC SUPPLY

1-Apr-10

31-Dec-99

 

 

4

NON ELECTRONIC SUPPLY

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

PRODUCT

_SK

PRODUCT

_CODE

PRODUCT

_NAME

PRODUCT_

GROUP_

CODE

PRODUCT_

GROUP

_NAME

EFFECTIVE_

DATE

EXPIRY_

DATE

 

CURRENT_

PRODUCT_

GROUP_

CODE

CURRENT_

PRODUCT_

GROUP_

NAME

1

11

PENCIL

1

WRITING SUPPLY

1-Jan-09

31-Mar-10

 

 

5

LEGACY SUPPLY

2

22

PEN

1

WRITING SUPPLY

1-Jan-09

31-Dec-99

 

1

WRITING SUPPLY

3

33

TONER

2

PRINTING SUPPLY

1-Jan-09

31-Dec-99

 

2

PRINTING SUPPLY

4

44

NOTEBOOK

4

NON ELECTRONIC SUPPLY

1-Jan-09

31-Dec-99

 

 

4

NON ELECTRONIC SUPPLY

5

11

PENCIL

4

NON ELECTRONIC SUPPLY

1-Apr-10

30-Jun-10

 

5

LEGACY SUPPLY

6

11

PENCIL

5

LEGACY SUPPLY

1-Jul-10

31-Dec-99

 

5

LEGACY SUPPLY

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).

Books to Consider

Building Websites with TYPO3
$ 12.00
TYPO3 Templates
$ 26.99
Instant Hyper-V Server Virtualization Starter
$ 19.99
comments powered by Disqus