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:

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:

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

  2. If the record exists in the PRODUCT_PRICE table, but nothing has changed, do not insert a new record or update an existing one.

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

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

NoteA 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, a 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

The PRODUCT table is updated again on 28 July 2008, to remove the mouse as an available product.

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