Type-2 Dates as Date Data Type ?

View previous topic View next topic Go down

Type-2 Dates as Date Data Type ?

Post  VTK on Fri Jan 06, 2012 8:42 pm

When we have the type-2 ETL Housekeeping columns like Record Effective From Date and Record Effective To Dates as DATE datatypes
and the record is changing next day what would we use to close the existing record.

1. If we use (Row_Eff_Dt of the new record - 1 day) to close the existing record then the Row_Eff_Dt and Row_End_Dt will be same ?
Is that standard practice ?
2. If we use Row_Eff_Dt of the new record to close the existing one then we will get two records when between operator is used.

Is there any other way ?

Code:

On 1/1/2011:

Prod_Dim_Key   Prod_ID(Natural Key)   Prod_Name   Prod_Color   Prod_Group   Row_Eff_DT   Row_End_Dt


P1      100         abc      Blue      xyz      1/1/2011   1/2/2011

On 1/2/2011 :

P2      100         abc      Blue      xyz1      1/2/2011   12/31/9999

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Type-2 Dates as Date Data Type ?

Post  hang on Fri Jan 06, 2012 10:03 pm

Instead of using between, use this where clause: TrasactionDate >= Row_Eff_Dt and TransactionDate < Row_End_Dt.

Or alternatively as in some MS server sample DW, you may expire the SCD 2 record one day earlier to be able to use between without producing duplicate. Both options will work as the logic is just used in ETL process, and the dimension users should not have to worry about it, although I prefer the same date approach as it does not give you impression that there is one day gap.

hang

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

View user profile

Back to top Go down

Re: Type-2 Dates as Date Data Type ?

Post  Vishy on Sat Mar 10, 2012 3:10 pm

Hang,
dont you think that end date of previous record and start date of new record should have difference of 1 day otherwise logically seeing records it appearas 2 records were active on a given day.

I have seen both these approaches i.e having 1 day difference and having no diference but I always preferred former one.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Type-2 Dates as Date Data Type ?

Post  hang on Wed Mar 21, 2012 8:44 am

Vishy,
If you realise 1/2/2011 really indicates 1/2/2011 00:00:00, you would know what I meant by 'one day gap'. So you may have to think of SCD end date as 1/2/2011 23:59:59 and start date 2/2/2011 as 2/2/2011 00:00:00 to close the gap. However, the same day approach is very close to that when you use datetime data type, ie. 2/2/2011=1/2/2011 23:59:59 (previous end)=2/2/2011 00:00:00(next start).


hang

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

View user profile

Back to top Go down

Re: Type-2 Dates as Date Data Type ?

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