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: Figure 1.11
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 | DateTime Stamp |
| 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 |
Slowly Changing Dimensions