Slowly Changing Dimension (SCD) Type 6

Exclusive offer: get 50% off this eBook here
Oracle Database 11g – Underground Advice for Database Administrators

Oracle Database 11g – Underground Advice for Database Administrators — Save 50%

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

£18.99    £9.50
by Djoni Darmawikarta | March 2010 | Oracle

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.

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

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
Select your format and quantity:

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

The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11.1
The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11.1

Oracle 11g Streams Implementer's Guide
Oracle 11g Streams Implementer's Guide

IBM Cognos 8 Planning
IBM Cognos 8 Planning

SAP Business ONE Implementation
SAP Business ONE Implementation

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

RESTful PHP Web Services
RESTful PHP Web Services

SOA Cookbook
SOA Cookbook

SOA Governance
SOA Governance

 

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software