Curr_Indic column in future dated dimensions ?

In my company there are people against adding current_Record_flag to the Dimension tables.

They are bringing up a point about future dated records in the system. In otherwords there will be records which is valid in the future but will exist now itself.

Example:


Natural Key Start Date End Date Status
------------- ----------- ---------- --------
100 07/01/2011 07/01/2012 Vested
100 07/01/2012 12/31/9999 Terminated


Both these records may come at sametime from OLTP so when we put a curr_rec_flag it will look like this.


Surrogate Key Natural Key Start Date End Date Status Curr_Rec_Flag
------------- ----------- ---------- -------- ------ -------------
101 100 07/01/2011 07/01/2012 Vested N
102 100 07/01/2012 12/31/9999 Terminated Y


But in reality currently valid record is 101 not 102.

I can think of two solutions :

1. Have a after load process to correct the field.
2. Do not add this field in such tables.

I am sure there are other people who would have faced the same scenario. Can you please share your thoughts ?

Your two solutions are down the right track. However the following post will give you more detailed answer:
http://forum.kimballgroup.com/t836-loading-future-dated-rows-to-dimensions#3575

The decision to include/exclude usually is a business driven one .... for example, I've seen future dated employee dimension records in an HR data mart (e.g. Future Hires) .

As for the flag .... keep in mind it can contain any value. I typically use 3 values (N,Y,F).

Regarding ETL (Fact surrogate key pipeline) - I tend to use the effective dates for the lookup rather than the current flag .... that way I don't need a separate lookup routine for early or late-arriving facts.

Hope this helps.

Guys

Thanks for the reply. I think we have to go with wither dropping this field in future dated dimensions or we have to write a post load process to update this field. I think it will cause problems if we use F as also a value for this column as users may not find any records if they use Y.

Thanks

there should always be a "Y" record for every business/natural key. Its the one that is "current" as of the last ETL process.

In your example, SK=101 would have a curr_rec_flag='Y'. SK=102 would have a curr_rec_flag='F'.