Type 2 SCD Fact Implementation

View previous topic View next topic Go down

Type 2 SCD Fact Implementation

Post  baskaran.s on Tue Nov 22, 2011 5:59 am

Hi,


I have been creating HR data mart for employee head count report,

My employee dimension is in SCD2,


Employeetranskey Employeeid .. Start date End date
1 100 2001-10-10 2002-12-31
2 100 2003-01-01 null or 9999-12-31


Fact table as follows,

Fact id Employeetranskey DateID
1 1 2002-12-31
2 2 null or 9999-12-31

Is it correct design..


my question is,

1.How do I relate employee dimension Start data and end date with Fact table

2.Do I need to create separate start data and end date attributes in fact table(to related Dimension table start date and end date) instead of one Dateid.
for instance

Fact table structure is,

Fact id Employeetranskey Start date End date
1 1 2001-10-10 2002-12-31
2 2 2003-01-01 null or 9999-12-31



pls advice me and if possible pls give sample fact table structure to handle SCD2 with start data and end date.

need help ,pls advice me

Thanks,
Baskaran.




baskaran.s

Posts : 5
Join date : 2011-10-20

View user profile

Back to top Go down

SCD 2 Fact implementation

Post  baskaran.s on Tue Nov 22, 2011 10:09 pm

How question is,

IN SCD2 Scenario how to relate dimension start data and End date filed to fact table.

How would be the fact table structure? Will fact also have start data and end date which are related to SCD 2 dimension stat data and end date

Need Help pls Advice any one.

Thanks in advance,

Baskaran.

baskaran.s

Posts : 5
Join date : 2011-10-20

View user profile

Back to top Go down

Re: Type 2 SCD Fact Implementation

Post  ngalemmo on Wed Nov 23, 2011 2:33 am

The association of a fact to a dimension occurs when the fact row is loaded. Normally with type 2 dimensions, you simply assocate the fact with the most current version of a dimension row (for that natural key) at the time of load... that is why you include a current flag as an attribute in the dimension, to ease locating the row.

There are other situations where you would locate the appropriate row using the transaction date of the fact against the effective dates in the dimension. This is usually done when there is normally a significant delay (days) in receiving data going to the fact table.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Type 2 SCD Fact Implementation

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