[SOLVED] SCD2 Dimension and Fact Table

View previous topic View next topic Go down

[SOLVED] SCD2 Dimension and Fact Table

Post  wawanco on Tue Jul 01, 2014 3:55 am

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


Last edited by wawanco on Tue Jul 01, 2014 6:40 am; edited 1 time in total

wawanco

Posts : 6
Join date : 2014-05-28
Location : Paris

View user profile

Back to top Go down

Re: [SOLVED] SCD2 Dimension and Fact Table

Post  ngalemmo on Tue Jul 01, 2014 4:26 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: [SOLVED] SCD2 Dimension and Fact Table

Post  wawanco on Tue Jul 01, 2014 5:00 am

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 !!


wawanco

Posts : 6
Join date : 2014-05-28
Location : Paris

View user profile

Back to top Go down

Re: [SOLVED] SCD2 Dimension and Fact Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum