Date Dimension or DateTime Field

View previous topic View next topic Go down

Date Dimension or DateTime Field

Post  cal.sneds on Mon Jun 15, 2009 12:02 pm

Hi,

I'm working on a project where dimensions have dates in them. An example is for a client where we have date of birth, joining date, date of death, etc. In fact i have about 6 different date columns.

Now, I'm trying to find out what the best practice is for this, do I set them as datetime fields and only add the actual date, or do I link them to the calendar dimension?

I've searched the internet, read my Kimball texts and haven't been able to come up with an answer yet.

Using datetime keeps a very clean star schema, however, at a greater cost of storage in comparison to integers.

Linking them to the calendar would make for faster searching on these fields and to be able to query on the different calendar attributes, months, years, etc... It would also use less storage. But, it creates a snowflake schema presenting a far more complicated model.

So I'm curious to hear how other people are dealing with this situation and to try and find out what the best practice actually is.

cal.sneds

Posts : 4
Join date : 2009-06-02
Location : Melbourne

View user profile

Back to top Go down

Re: Date Dimension or DateTime Field

Post  BoxesAndLines on Mon Jun 15, 2009 12:13 pm

I use snowflakes if there is any chance that reporting will want to leverage the time dimension or if I need to track time for the date. You could always push the dates down to the fact table if they are applicable.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

We are using date dimension

Post  hemantha on Tue Jun 16, 2009 12:14 am

Hemantha wrote:We use date table as date dimension. The dimension has many attributes such as date,week,month,year,week of month etc. We use it to get date of birth,account open date,account close date,month end balance date ect... You can connect it to main fact table using date_key.(snow flex model)
avatar
hemantha

Posts : 1
Join date : 2009-06-15
Age : 49
Location : Sri Lanka

View user profile

Back to top Go down

Re: Date Dimension or DateTime Field

Post  cal.sneds on Wed Jun 17, 2009 5:56 am

So basically, if there is a possibility that the business might want to query on the date, ie. break reports down to what year clients joined, use the date/time dimension, otherwise just use the datetime field.

cal.sneds

Posts : 4
Join date : 2009-06-02
Location : Melbourne

View user profile

Back to top Go down

Re: Date Dimension or DateTime Field

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