[SOLVED] SCD2 Dimension and Fact Table

Hi there,

The context of my problem is the following :

I have a SCD2 dimension that represents the purchasers:

Code:


SK | Natural Key | Name | Entity | Last
---------------------------------------
1  | 1           | 'xx' | 'E1'   | 'N'
2  | 1           | 'xx' | 'E2'   | 'Y'


My fact table represents invoice outstanding, let's say we recorded two payment events for the invoice #1:

Code:


Date       | Invoice Key | Purchaser | Outstanding
--------------------------------------------------
01/03/2014 | 1           | 1         | 100
01/25/2014 | 1           | 2         | 50


Let's say the purchaser entity changed on 01/12/2014.

If I want to report the total outstanding for entity E2 on Jan. 13, the reality would be 100. But the fact table designed as it is will return a total outstanding of 0 on Jan. 13 for E2, which is not true.

What would be the right modeling to address my problem ?

Thanks!

Thibault

Why would it not be true?

Your model is fine, its the question you are trying to answer that needs clarity. Do you want the balance as the of the Jan 12th state of the dimension, the state at the time of the transaction, or the current state?

Typically a type 2 will contain a current flag and an effective period. Joining on the type 2 key will give you the state at the time of the transaction. A further self-join on the dimension (based on the natural key) will allow you to locate the current row or a row in effect at a particular time.

All three are correct results depending on the question being asked.

Thank you ngalemmo,

Thanks to reply I realize that I was missing two things :
1. The effective period in my design
2. The self join on the dimension table on my report query

All is crystal clear for me now !!

Thank you again !!