Date & Time Dimension

Date Dimension

Dimension of date is one of the most common dimension on which you analyze your data. All time trending , historical and period comparison analysis etc use date. With reference to the “Dimensional Modeling”, typically the unique key of this dimension is the date. Against each date, there are attributes.

*All examples here are in US date format

Usage Comments:

• Date related data can be essentially divided in two categories:

- The activity, which has happened during a period. This is called “P&L” type of data.
- The status as at the end of a particular period. This is called “Balance Sheet” type of data.

• The attributes of the “end of Week/Month/Quarter/Year”, essentially point out to the measures, which for example talk about the balance in accounts/aging of complaints etc.
  
• The attributes of “Calendar day/week/month/quarter/year” point to the measures which for example talk about total sales closed/total service calls attended/total rooms booked.
  
• Some of the attributes, can also be generated dynamically or calculated using functions in the ETL software. There are also OLAP layers which calculate these functions dynamically.

There can be many other attributes like

• “Number of Days since beginning of Week, month, quarter and year”: This attribute states on how many days have elapsed since the beginning of the week, month, quarter and year to which this date belongs.
  
• This attribute is used to analyze on the trends of activities as we move into a particular period. For example if you have a sales cycle on the monthly basis, you can track on how the revenue moves from the day1 to day 10(day) of the beginning of the sales cycle.
  
• “Number of Days left to the end of Week, month, quarter and year”: This attribute states on how many days are left to the end of the week, month, quarter and year to which this date belongs.

  This attribute is used to analyze on the trends of activities as we move into a particular period. For example if you have a sales cycle on the monthly basis, you can track on how the revenue moves when 5 days to 1 day is left to the end of the sales cycle.

Time Dimension

Many people feel that analyzing on time dimension is over-perfecting the art. However it is core to many companies and is as important as date dimension. ALL (perhaps without exception) retail companies, service companies (like call centers, telecommunication providers) and mass communication companies (media, entertainment) want to analyze the customer traffic, employee productivity trends across a day, to manage the strength of their staff, and promoting methods to smoothen the traffic. The principle of staff management and traffic management across dates will also apply across the day. Apart from across the day trends, the data on TAT etc (which for few transaction runs into few minutes) helps in fine tuning the methods through operation-research tricks.

The attributes of time dimension are:

• Hour of the day
• Minute of the hour
• AM/PM
• Part of the Day (Day Split into slots)
• Peak/Non Peak /Medium