Time Dimension

In a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup, and week lookups are not merged as a single table.  In a dimensional data modeling (star schema), these tables would be merged as a single table called TIME DIMENSION for performance and slicing data.

This dimensions helps to find the sales done on date, weekly, monthly and yearly basis. We can have a trend analysis by comparing this year sales with the previous year or this week sales with the previous week.

Example of Time Dimension

Year Lookup:

Year Id	Year Number	DateTimeStamp
1	2004	1/1/2005 11:23:31 AM
2	2005	1/1/2005 11:23:31 AM

Quarter Lookup:

Quarter Number	Quarter Name	DateTimeStamp
1	Q1	1/1/2005 11:23:31 AM
2	Q2	1/1/2005 11:23:31 AM
3	Q3	1/1/2005 11:23:31 AM
4	Q4	1/1/2005 11:23:31 AM

Month Lookup:

Month Number	Month Name	DateTimeStamp
1	January	1/1/2005 11:23:31 AM
2	February	1/1/2005 11:23:31 AM
3	March	1/1/2005 11:23:31 AM
4	April	1/1/2005 11:23:31 AM
5	May	1/1/2005 11:23:31 AM
6	June	1/1/2005 11:23:31 AM
7	July	1/1/2005 11:23:31 AM
8	August	1/1/2005 11:23:31 AM
9	September	1/1/2005 11:23:31 AM
10	October	1/1/2005 11:23:31 AM
11	November	1/1/2005 11:23:31 AM
12	December	1/1/2005 11:23:31 AM

Week Lookup:

Week Number	Day of Week	DateTimeStamp
1	Sunday	1/1/2005 11:23:31 AM
1	Monday	1/1/2005 11:23:31 AM
1	Tuesday	1/1/2005 11:23:31 AM
1	Wednesday	1/1/2005 11:23:31 AM
1	Thursday	1/1/2005 11:23:31 AM
1	Friday	1/1/2005 11:23:31 AM
1	Saturday	1/1/2005 11:23:31 AM
2	Sunday	1/1/2005 11:23:31 AM
2	Monday	1/1/2005 11:23:31 AM
2	Tuesday	1/1/2005 11:23:31 AM
2	Wednesday	1/1/2005 11:23:31 AM
2	Thursday	1/1/2005 11:23:31 AM
2	Friday	1/1/2005 11:23:31 AM
2	Saturday	1/1/2005 11:23:31 AM

Time Dimension:

Time Dim Id	Year No	Day of Year	Quarter No	Month No	Month Name	Month Day No	Week No	Day of Week	Cal Date	DateTimeStamp
1	2004	1	Q1	1	January	1	1	5	1/1/2004	1/1/2005 11:23:31 AM
2	2004	32	Q1	2	February	1	5	1	2/1/2004	1/1/2005 11:23:31 AM
3	2005	1	Q1	1	January	1	1	7	1/1/2005	1/1/2005 11:23:31 AM
4	2005	32	Q1	2	February	1	5	3	2/1/2005	1/1/2005 11:23:31 AM