Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD2 and RowStartDate, RowEndDate stamping?

2 posters

Go down

SCD2 and RowStartDate, RowEndDate stamping? Empty SCD2 and RowStartDate, RowEndDate stamping?

Post  pzajkowski Mon Mar 29, 2010 2:43 pm

When maintaining a SCD2, Kimball recommends that the expiration (expiry) date (aka RowEndDate) of a given row should equal the RowStartDate of the newly inserted row.

I'm confused by this idea. For example, if someone's region has changed from "North" to "South" , effective April 1, why would the RowEndDate for "North" be April 1 (per Kimball) rather than March 31?

If the RowEndDate for North is equal to the RowStartDate for South, won't the results be incorrect when I execute a query that counts people per region on April 1, especially if the search criteria is @DateParameter Between RowStartDate and RowEndDate?

--Pete

pzajkowski

Posts : 31
Join date : 2009-08-10

Back to top Go down

SCD2 and RowStartDate, RowEndDate stamping? Empty Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  ngalemmo Mon Mar 29, 2010 3:37 pm

You can do it either way... obviously, if you use BETWEEN, the end date needs to be the day prior to the new effective date.

My guess is it is a holdover from the first edition. At the time, BETWEEN was not fully implemented in a lot of SQLs. In some DBs it wasn't supported, in others the from and to values could not be columns and so on.

But it also depends if you are using a full timestamp. In that case, you would not have a problem unless your date parameter happens to match the exact time in the database.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

SCD2 and RowStartDate, RowEndDate stamping? Empty Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  pzajkowski Tue Mar 30, 2010 11:05 am

Thanks for the feedback. I feel better having some confirmation that it's OK to have End & Start dates off by 1 rather than the same. Querying will be much simpler.

pzajkowski

Posts : 31
Join date : 2009-08-10

Back to top Go down

SCD2 and RowStartDate, RowEndDate stamping? Empty Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum