Type-2 Dates as Date Data Type ?
3 posters
Page 1 of 1
Type-2 Dates as Date Data Type ?
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 ?
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
Re: Type-2 Dates as Date Data Type ?
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.
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
Re: Type-2 Dates as Date Data Type ?
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.
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
Re: Type-2 Dates as Date Data Type ?
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).
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
Similar topics
» scd2 effective date, end date data type
» SQL Server 2008 Date data type as dimension key
» From/To Dates and SCD Type 2 Process.
» SCD type 2 - Valid To Date Value? 12/31/9999 or Null?
» Is it a best practice that Data warehouse follows the source system data type?
» SQL Server 2008 Date data type as dimension key
» From/To Dates and SCD Type 2 Process.
» SCD type 2 - Valid To Date Value? 12/31/9999 or Null?
» Is it a best practice that Data warehouse follows the source system data type?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum