Date dimension multiple dates

View previous topic View next topic Go down

Date dimension multiple dates

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  thedude on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  thedude on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  thedude on Mon Feb 18, 2013 12:30 pm



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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  Steveo250k on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: Date dimension multiple dates

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