Best practice for SCD2 start and end dates

View previous topic View next topic Go down

Best practice for SCD2 start and end dates

Post  nemo9123 on Mon Feb 16, 2015 4:23 pm

What is the best practice for setting the end date of the prior row?

1. Same as effective date for current row?
2. Day prior to the effective date for current now?
3. Day prior at 11:59.999?
4. Something else?

I've seen 1 & 2 in Kimball examples before, not sure which is the accepted best practice these days.

Thanks,
Nemo

nemo9123

Posts : 2
Join date : 2014-07-07

View user profile

Back to top Go down

Re: Best practice for SCD2 start and end dates

Post  ngalemmo on Mon Feb 16, 2015 8:59 pm

Generally the end timestamp should be set to some time prior to the new start timestamp. Such as subtracting a millisecond from the new start. This allows use of BETWEEN, which is an inclusive range, when filtering.

If you are using a DATE type which does not carry time of day (this varies by database) then the end date should be the day prior to the new start date.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Best practice for SCD2 start and end dates

Post  BoxesAndLines on Mon Feb 16, 2015 10:39 pm

I've worked places that use both of those solutions. Most of the companies set effective start equal to the previous row's end date or timestamp if you do multiple loads during the day. Either way works.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Best practice for SCD2 start and end dates

Post  nemo9123 on Tue Feb 17, 2015 9:58 am

Thanks guys, I've seen it done all different ways, wasn't sure what consensus was on "best". Thanks!

nemo9123

Posts : 2
Join date : 2014-07-07

View user profile

Back to top Go down

Re: Best practice for SCD2 start and end dates

Post  ron.dunn on Tue Feb 17, 2015 7:26 pm

I think there is a 'best', and it is the solution proposed by ngalemmo.

The use of BETWEEN should not be disregarded. Even if it is not part of your original plans, someone, somewhere, will use it in future.

Ron.

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Best practice for SCD2 start and end dates

Post  nick_white on Wed Feb 18, 2015 3:22 am

I agree.
If you set end_time = start_time on "sequential" Dim records then you have to introduce logic to use either ">= Start_time" or "<= end_time" when determining the applicable Dim record.
If your set end_date < start_date so that the Dim records don't overlap there can never be any confusion

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Best practice for SCD2 start and end dates

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