Type 2 SCD Fact Implementation

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.

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.

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.