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

Time Dimension Diagram

Year Lookup:

Year IDYear NumberDateTimeStamp
120041/1/2005 11:23:31 AM
220051/1/2005 11:23:31 AM

Quarter Lookup:

Quarter NumberQuarter NameDateTimeStamp
1Q11/1/2005 11:23:31 AM
2Q21/1/2005 11:23:31 AM
3Q31/1/2005 11:23:31 AM
4Q41/1/2005 11:23:31 AM

Month Lookup:

Month NumberMonth NameDateTimeStamp
1January1/1/2005 11:23:31 AM
2February1/1/2005 11:23:31 AM
3March1/1/2005 11:23:31 AM
4April1/1/2005 11:23:31 AM
5May1/1/2005 11:23:31 AM
6June1/1/2005 11:23:31 AM
7July1/1/2005 11:23:31 AM
8August1/1/2005 11:23:31 AM
9September1/1/2005 11:23:31 AM
10October1/1/2005 11:23:31 AM
11November1/1/2005 11:23:31 AM
12December1/1/2005 11:23:31 AM

Week Lookup:

Week NumberDay of WeekDateTimeStamp
1Sunday1/1/2005 11:23:31 AM
2Monday1/1/2005 11:23:31 AM
3Tuesday1/1/2005 11:23:31 AM
4Wednesday1/1/2005 11:23:31 AM
5Thursday1/1/2005 11:23:31 AM
6Friday1/1/2005 11:23:31 AM
7Saturday1/1/2005 11:23:31 AM

Time Dimension:


Time Dim IdYear NoDay Of YearQuarter NoMonth NoMonth NameMonth Day NoWeek NoDay of WeekCal DateDate Time Stamp
120041Q11January1151/1/20041/1/2005 11:23:31 AM
2200432Q12February1512/1/20041/1/2005 11:23:31 AM
320051Q11January1171/1/20051/1/2005 11:23:31 AM
4200532Q12February1532/1/2001/1/2005 11:23:31 AM



(Visited 19,624 times, 1 visits today)

One comment

  • Hi
    I need to create a Datawarehouse for Employees in my company.How to start,How to select as Attributes,Heirarchy ,Facts


Leave a Reply

Your email address will not be published. Required fields are marked *