Optimal SCD type 2 design
2 posters
Page 1 of 1
Optimal SCD type 2 design
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
effective_date
cust_email
Take customer for example,
customer_id <-- surrogate key
custno <-- natural key
effective_date
cust_email
kjfischer- Posts : 28
Join date : 2011-05-04
Re: Optimal SCD type 2 design
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.
That said, many modelers also implement an "is current" indicator flag to faciliate identification of current rows.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Similar topics
» Optimal SCD type 2 dimension design
» SCD Type II Design Decision
» Design Standards - Numeric Type 2 Dimension Attributes
» Incremental Load Testing -Type 1 & Type 2
» Need Help to Design Calendar +day type model +fact table to meet requirement
» SCD Type II Design Decision
» Design Standards - Numeric Type 2 Dimension Attributes
» Incremental Load Testing -Type 1 & Type 2
» Need Help to Design Calendar +day type model +fact table to meet requirement
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|