Date dimension multiple dates

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!!!

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.

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

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.

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.

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.

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.

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