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
1Monday1/1/2005 11:23:31 AM
1Tuesday1/1/2005 11:23:31 AM
1Wednesday1/1/2005 11:23:31 AM
1Thursday1/1/2005 11:23:31 AM
1Friday1/1/2005 11:23:31 AM
1Saturday1/1/2005 11:23:31 AM
2Sunday1/1/2005 11:23:31 AM
2Monday1/1/2005 11:23:31 AM
2Tuesday1/1/2005 11:23:31 AM
2Wednesday1/1/2005 11:23:31 AM
2Thursday1/1/2005 11:23:31 AM
2Friday1/1/2005 11:23:31 AM
2Saturday1/1/2005 11:23:31 AM

Time Dimension:

Time Dim IdYear NoDay of YearQuarter NoMonth NoMonth NameMonth Day NoWeek NoDay of WeekCal DateDateTimeStamp
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/20051/1/2005 11:23:31 AM

One thought on “Time Dimension

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

    Thanks
    R.B

Leave a Reply

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