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

Optimal SCD type 2 design

2 posters

Go down

Optimal SCD type 2 design Empty Optimal SCD type 2 design

Post  kjfischer Thu May 05, 2011 2:13 pm

I am wondering if there are advantages or an optimal way to design dimension? Specifically, is a single "effective_date" attribute ok? Or do you recommend a start_date and end_date? Is it necessary to have a "current_flag" indicator or can you just use the max(effective_date) to get that?

Take customer for example,

customer_id <-- surrogate key
custno <-- natural key


Posts : 28
Join date : 2011-05-04

Back to top Go down

Optimal SCD type 2 design Empty Re: Optimal SCD type 2 design

Post  VHF Thu May 05, 2011 4:14 pm

At a minimum you should have "begin effective date" and "end effective date" fields for each row. Designate a special far-future value such as 2999-12-31 to place in the "end effective date" field for current records. Any BETWEEN queries you do to pull records as-of a particular point in time will work OK, and you could use WHERE End_Effective_Date = #2999-12-31# to select current rows.

That said, many modelers also implement an "is current" indicator flag to faciliate identification of current rows.


Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

Back to top

- Similar topics

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