Dimension Snowflaking

View previous topic View next topic Go down

Dimension Snowflaking

Post  tim_goodsell on Sun Dec 04, 2011 11:33 pm

Hi

I have a Member Dimension which has a number of date fields (Join Date etc), is it best to use a date key (snowflake to reference a date dimension) or keep the column a date type

Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Dimension Snowflaking

Post  BoxesAndLines on Mon Dec 05, 2011 11:14 am

Just keep as a date column. You can always join to the date dim on the date if you need to leverage the date dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Dimension Snowflaking

Post  Jeff Smith on Tue Dec 06, 2011 3:29 pm

One thing to consider is that datetime fields are twice the size as integers. Date dimensions tend to be small so joins to the the date dimension don't usually take a long time and joins on integers will be faster than joins on datetimes. Member Dimensions can be big so if you have a lot dates in the dimension, making them integers can have a dramatic effect on total size.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimension Snowflaking

Post  hang on Wed Dec 07, 2011 8:20 am

I would not snowflake any dimension by another dimension unless it's necessary. Very likely a date attribute in a dimension may work perfectly without the need for other calendar attributes in date dimension. Storing the straight attribute value instead of a FK in a dimension is a general guideline for dimensional modelling, and the date attribute should be no exception.

Enven if the Member dimension is a monster dimension (>1 million), I don't think storing an integer date key has a significant performance advantage, or storage saving for that matter, than the straight date value. Since the date dimension is small, joining by natural date should be just as fast as joining by integer date key as B&L suggested. For monster dimension, the focus should be on those highly repeated and lengthy textual attributes, as having those attributes as outriggers can indeed reduce the size of monster dimension dramatically.

hang

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

View user profile

Back to top Go down

Re: Dimension Snowflaking

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