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 dimension design

5 posters

Go down

Optimal SCD type 2 dimension design Empty Optimal SCD type 2 dimension design

Post  kjfischer Thu May 05, 2011 2:17 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
effective_date
cust_email

.

kjfischer

Posts : 28
Join date : 2011-05-04

Back to top Go down

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

Post  VHF Thu May 05, 2011 4:21 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.

VHF

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

Back to top Go down

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

Post  datamodeller Fri May 06, 2011 3:46 am

True !

Ans also, in many cases you would need filter to extract on the current version of the record for which the max(effective_date) will not be efficient as you might end up in writing an inline view in your reporting layer rather the end_date as mentioned in the above post will do the trick.

datamodeller

Posts : 9
Join date : 2010-07-25

Back to top Go down

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

Post  kjfischer Fri May 06, 2011 12:14 pm

I have been told that the Oracle window/partition functions can handle this effeciently with just an effective_date; that is why I am asking...

kjfischer

Posts : 28
Join date : 2011-05-04

Back to top Go down

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

Post  hang Fri May 06, 2011 5:48 pm

As far as SCD 2 is concerned, a pair of SCD dates is a bare minimum by definition, just as VHF rightly said, anything less would not be called SCD 2.

Trying to minimise the number of SCD housekeeping attributes to achieve similar functionality at expense of ease of dimension use and performance is against the principle of dimensional modeling. Obviously max(effective_date) is highly costly for SCD, and hence not recommended.

The Window/partition functions are ANSI SQL extension, not just in Oracle, and designed to achieve some limited OLAP functionalities.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

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

Post  ngalemmo Sun May 08, 2011 7:27 pm

kjfischer wrote:I have been told that the Oracle window/partition functions can handle this effeciently with just an effective_date; that is why I am asking...

There are some fundimental problems using window functions such as LEAD and LAG to calculate the expiration date.

If you have rows that expire and not superceded by a new row (in other words, they just end), you cannot detect that in a window function. You would need some kind of flag indicating there is no 'next' row.

LEAD and LAG do not perform well in views. My experience has been that predicates may not be pushed do to a view containing LEAD or LAG. This means that the view will be fully materialized before predicates are applied. The reason for this is that predicates can affect the results of the window function. Views must present the set as defined in the view, so predicates are ignored to avoid ambiguous results.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

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