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

Date Dimension or DateTime Field

3 posters

Go down

Date Dimension or DateTime Field Empty Date Dimension or DateTime Field

Post  cal.sneds 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

Back to top Go down

Date Dimension or DateTime Field Empty Re: Date Dimension or DateTime Field

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

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

Back to top Go down

Date Dimension or DateTime Field Empty We are using date dimension

Post  hemantha 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)
hemantha
hemantha

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

Back to top Go down

Date Dimension or DateTime Field Empty Re: Date Dimension or DateTime Field

Post  cal.sneds 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

Back to top Go down

Date Dimension or DateTime Field Empty Re: Date Dimension or DateTime Field

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