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

Date dimension multiple dates

3 posters

Go down

Date dimension multiple dates Empty Date dimension multiple dates

Post  SnowShine429 Mon Feb 18, 2013 11:12 am

Hi everyone,

we have something called projects in our OLTP system(Olympic) and each project has a start and end date. any idea how to go about creating date dimension in this scenario? Do I needs two date dimensions, one for start date and the other for end date?

thanks in advance!!!

SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  thedude Mon Feb 18, 2013 11:21 am

Logically yes, physically no.

Use one physical date table and have to two columns in your fact for the dates. When you query you join twice to the date table if using both date dimensions. This type of dimension is called a role playing dimension.

thedude

Posts : 21
Join date : 2009-02-03
Location : London

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  SnowShine429 Mon Feb 18, 2013 12:03 pm

I'll end up having two columns for each date field(for example, year, quarter, month date etc). Is that what you are proposing?

SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  thedude Mon Feb 18, 2013 12:12 pm

Not quite. In your fact you will have two FK columns for start and end date. Year, quarter and month are attributes of the date and live in the date table.

thedude

Posts : 21
Join date : 2009-02-03
Location : London

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  SnowShine429 Mon Feb 18, 2013 12:17 pm

Thank you.

What would the year, quarter, month be based off of?
For example, if the start date is 08/01/2012 and the end date is 03/30/2013, what is the year going to be? I am not sure if am missing something here.

SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  thedude Mon Feb 18, 2013 12:30 pm

Date dimension multiple dates Dates11

Have a look at the above example. If you are still struggling with the concept try to read one of Kimball's dimensional modelling books as this will explain it in more detail and better than I can.

The above date dimension will need to contain more than the four records above, I have omitted the other records for this example.


Last edited by thedude on Fri Feb 22, 2013 6:14 am; edited 1 time in total

thedude

Posts : 21
Join date : 2009-02-03
Location : London

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  Steveo250k Thu Feb 21, 2013 1:52 pm

fact table
some stuff
more stuff
...
start_date_key = 8
end_date_key = 17
probably more stuff


date_dimension table
date_key
calendar_date
year
month
day_of_week
quarter
etc.

start_date_key in your fact table points to the 8th record (in this example, for this record) in the date_dimension table.
end_date_key in your fact table points to the 17th record in the date_dimensino table.

Don't try to combine your start and end dates. They are 2 separate dates that point to 2 separate records in the date dimension table.

As theDude pointed out in his example, the date_key does not have to be a serial integer. You could create a key by adding the year to a 2 digit month to a 2 digit day. Thus Feb 22, 2013 would be 20130222. The database doesn't care but it's easy for human readers to troubleshoot if there are problems.

Steveo250k

Posts : 6
Join date : 2012-08-10

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

Post  SnowShine429 Tue Feb 26, 2013 1:45 pm

Thank you both for the detailed and excellent explanation. I really appreciate all your help.

SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

Date dimension multiple dates Empty Re: Date dimension multiple dates

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