Curr_Indic column in future dated dimensions ?

View previous topic View next topic Go down

Curr_Indic column in future dated dimensions ?

Post  VTK on Fri Jul 15, 2011 5:16 pm

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 ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Curr_Indic column in future dated dimensions ?

Post  hang on Fri Jul 15, 2011 5:23 pm

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Curr_Indic column in future dated dimensions ?

Post  LAndrews on Mon Jul 18, 2011 12:23 pm


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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Curr_Indic column in future dated dimensions ?

Post  VTK on Mon Jul 18, 2011 7:10 pm

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

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Curr_Indic column in future dated dimensions ?

Post  LAndrews on Mon Jul 18, 2011 7:16 pm


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'.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Curr_Indic column in future dated dimensions ?

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