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

Dimension Snowflaking

4 posters

Go down

Dimension Snowflaking Empty Dimension Snowflaking

Post  tim_goodsell 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

Back to top Go down

Dimension Snowflaking Empty Re: Dimension Snowflaking

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Dimension Snowflaking Empty Re: Dimension Snowflaking

Post  Jeff Smith 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

Back to top Go down

Dimension Snowflaking Empty Re: Dimension Snowflaking

Post  hang 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

Back to top Go down

Dimension Snowflaking Empty Re: Dimension Snowflaking

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