Case study scenario

This section provides a case study scenario for type 2 SCD and describes how to create transformation projects in Sybase ETL to implement this scenario.

Case description

You have two tables:

PRODUCT in the operational or source database.
PRODUCT_PRICE in the data warehouse or target database. This table tracks modification of products in the source table (PRODUCT) over time, such as:
- Change in price of existing products
- Newly added products
- Deleted products

The database table schema of the PRODUCT table looks like:

Column	Description
Key	Unique ID of product.
Name	Name of the product.
Price	Price of the product.

The database table schema of the PRODUCT_PRICE table looks like:

Column	Description
Key	Source key identifier in the source table (PRODUCT).
Name	Name of the product.
Price	Price of the product.
Valid From	Date of insertion of new records.
Valid To	End of validity of records. A record becomes invalid when a new record with the same source key is inserted in the PRODUCT_PRICE table.

Rules

The rules to transfer dimensions from the PRODUCT table to the PRODUCT_PRICE table are:

If the record does not exist in the PRODUCT_PRICE table, create it with the same column values as the PRODUCT table. Set the Valid From date to the record insertion date, and the Valid To date to 9999-12-31.
If the record exists in the PRODUCT_PRICE table, but nothing has changed, do not insert a new record or update an existing one.
If the record exists in the PRODUCT_PRICE table, and the price of the record has changed:
- Set the Valid To date for the record with the old price to yesterday.
- Create a new record. Set the Valid From date to the record insertion date, and the Valid To date to 9999-12-31.
If the record exists in the PRODUCT_PRICE table but has been deleted from the PRODUCT table, set the Valid To date of the product in the PRODUCT_PRICE table to yesterday.

A running history of dimension changes, based on these rules, is maintained in the PRODUCT_PRICE table.

How it works

After initial load on 01 January 2008, the PRODUCT table displays:

Key	Name	Price
1	Notebook	1200
2	Monitor	1000
3	Mouse	500

After the ETL process is run for the first time, the PRODUCT_PRICE table displays:

Key	Name	Price	Valid From	Valid To
1	Notebook	1200	2008-01-01	9999-12-31
2	Monitor	1000	2008-01-01	9999-12-31
3	Mouse	500	2008-01-01	9999-12-31

On 15 January 2008, the PRODUCT table is updated when the price of the monitor is modified.

Key	Name	Price
1	Notebook	1200
2	Monitor	1400
3	Mouse	500

After the ETL process is run again, the PRODUCT_PRICE table displays:

Key	Name	Price	Valid From	Valid To
1	Notebook	1200	2008-01-01	9999-12-31
2	Monitor	1000	2008-01-01	2008-01-14
3	Mouse	500	2008-01-01	9999-12-31
2	Monitor	1400	2008-01-15	9999-12-31

On 22 January 2008, the PRODUCT table is updated again when a new product, hard disk, is added.

Key	Name	Price
1	Notebook	1200
2	Monitor	1400
3	Mouse	500
4	Hard Disk	1000

After the ETL process is run again, the PRODUCT_PRICE table displays:

Key	Name	Price	Valid From	Valid To
1	Notebook	1200	2008-01-01	9999-12-31
2	Monitor	1000	2008-01-01	2008-01-14
3	Mouse	500	2008-01-01	9999-12-31
2	Monitor	1400	2008-01-15	9999-12-31
4	Hard Disk	1000	2008-01-22	9999-12-31

PRODUCT table is updated again on 28 July 2008:

A product is removed from the table – Mouse.

Key	Name	Price
1	Notebook	1200
2	Monitor	1400
4	Hard Disk	1000

After the ETL process is run again, the PRODUCT_PRICE table displays:

Key	Name	Price	Valid From	Valid To
1	Notebook	1200	2008-01-01	9999-12-31
2	Monitor	1000	2008-01-01	2008-01-14
3	Mouse	500	2008-01-01	2008-07-27
2	Monitor	1400	2008-01-15	9999-12-31
4	Hard Disk	1000	2008-01-22	9999-12-31