Case Study 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

Database Table Schema Of The `PRODUCT_PRICE` Table
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.

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.

Note:

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

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

After the application process is run for the first time, the PRODUCT_PRICE table appears:

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.

After the application process is run again, the PRODUCT_PRICE table appears:

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.

After the application process is run again, the PRODUCT_PRICE table appears:

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.

After the application process is run again, the PRODUCT_PRICE table appears:

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