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.
| Year Id | Year Number | DateTimeStamp |
|---|---|---|
| 1 | 2004 | 1/1/2005 11:23:31 AM |
| 2 | 2005 | 1/1/2005 11:23:31 AM |
| 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 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 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 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 |
Learn data modeling design Skills on OLTP and OLAP from a US University Professor with…
These SQL commands are related with Oracle's data dictionary and can be used to get…
important DDL Statements from Oracle like Commit, Rollback, Grant, Revoke etc..
In this section, we will try to explain about important database DROP commands that are…
In this section, we will try to explain about important database DML commands that are…
In this section, we will try to explain about important database ALTER commands that are…
View Comments
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