4
The Date Dimension
The Date dimension provides hierarchies for analyzing clickstream data for different dates or date ranges, such as over weeks, months, or individual days.
This chapter contains the following topics:
Date Dimension Hierarchies
The Date dimension contains the following hierarchies:
The sections that follow list all levels in each hierarchy, followed by a diagram that provides a graphical representation of the hierarchy.
The Date Calendar Hierarchy
This hierarchy contains the following levels, listed from top to bottom. For details about the attributes of each level, see Date Dimension Levels.
- Calendar Year
- Calendar Quarter
- Calendar Month
- Day
Figure 4-1 The Date Calendar Level Hierarchy
Text description of the illustration date-cal.gif
The Date Fiscal Hierarchy
This hierarchy contains the following levels, listed from top to bottom. For details about the attributes of each level, see Date Dimension Levels.
- Fiscal Year
- Fiscal Quarter
- Fiscal Month
- Day
Figure 4-2 The Date Fiscal Level Hierarchy
Text description of the illustration date-fis.gif
The Date Week Hierarchy
This hierarchy contains the following levels, listed from top to bottom. For details about the attributes of each level, see Date Dimension Levels.
Figure 4-3 The Date Week Level Hierarchy
Text description of the illustration date-wee.gif
Date Dimension Levels
The Date dimension is comprised of the following levels. For each level, information about each attribute (column name) is provided in the following format:
- Attribute: Description (example value)
levels are presented in descending order. In addition to the pre-defined attributes, each level contains five "generic" attributes that can be defined by the user.
CLK_L_CALENDAR_YEAR
- Calendar_Year_Begin_Date : First date in the calendar year; natural key . (01-JAN-1997)
- Calendar_Year_End_Date : Last date in the calendar year; time series attribute. (31-DEC-1997)
- Calendar_Year_Time_Span : Number of days in the calendar year; time series attribute. (365)
- Calendar_Year_Number : Full calendar year number. (1997, 2000, etc.)
- Calendar_Year_Attributes 1-5 : Calendar year level user-defined attributes.
CLK_L_CALENDAR_QUARTER
- Calendar_Qtr_Begin_Date : First date in the calendar quarter; natural key . (01-APR-1997)S
- Calendar_Qtr_End_Date : Last date in the calendar quarter; time series attribute . ( 30-JUN-1997)
- Calendar_Qtr_Time_Span : Number of days in the calendar quarter; time series attribute. (91)
- Calendar_Qtr_Name : Calendar quarter name. (4Q1999, 1Q2000, etc.)
- Calendar_Qtr_of_Year : Calendar quarter number in this calendar year . (1, 2, 3, 4)
- Calendar_Qtr_of_Epoch : Calendar quarter serial number. ( 1, 2, 3, ...)
- Calendar_Qtr_Attributes 1-5: User-defined attributes.
CLK_L_CALENDAR_MONTH
- Calendar_Month_Begin_Date : First date in the calendar month; natural key. (01-JAN-1997)
- Calendar_Month_End_Date : Last date in the calendar month; time series attribute. (31-JAN-1997)
- Calendar_Month_Time_Span : Number of days in the calendar month; time series attribute. (28, 29, 30, 31)
- Calendar_Month_Name : Full name of the calendar month. ( January, February, ..., December)
- Calendar_Month_Short_Name : Shortened name of the calendar month . (Jan, Feb, ..., Dec)
- Calendar_Month_of_Quarter : Calendar month number in this calendar quarter . (1, 2, ..., 3)
- Calendar_Month_of_Year : Calendar month number in this calendar year . (1, 2, ..., 12)
- Calendar_Month_of_Epoch : Calendar month serial number . (1, 2, 3, ...)
- Calendar_Month_Attributes 1-5: User-defined attributes.
CLK_L_FISCAL_YEAR
- Fiscal_Year_Begin_Date : First date in the fiscal year; natural key. (01-JAN-1997)
- Fiscal_Year_End_Date : Last date in the fiscal year; time series attribute. (31-DEC-1997)
- Fiscal_Year_Time_Span : Number of days in the fiscal year; time series attribute. (365)
- Fiscal_Year_Number : Fiscal year number .
- Fiscal_Year_Attributes 1-5: Fiscal year level user-defined attributes.
CLK_L_FISCAL_QUARTER
- Fiscal_Qtr_Begin_Date : First date in the fiscal quarter; natural key . (01-APR-1997)
- Fiscal_Qtr_End_Date : Last date in the fiscal quarter; time series attribute . (30-JUN-1997)
- Fiscal_Qtr_Time_Span : Number of days in the fiscal quarter; time series attribute. (91)
- Fiscal_Qtr_Name : Fiscal quarter name . ( User-defined)
- Fiscal_Qtr_of_Year : Quarter number in this fiscal year . (1, 2, 3, 4)
- Fiscal_Qtr_of_Epoch : Fiscal quarter serial number . ( 1, 2, 3, ...)
- Fiscal_Qtr_Attributes 1-5: Fiscal quarter level user-defined attributes.
CLK_L_FISCAL_MONTH
- Fiscal_Month_Begin_Date : First date in the fiscal month; natural key. (01-JAN-1997)
- Fiscal_Month_End_Date : Last date in the fiscal month; time series attribute . (31-JAN-1997)
- Fiscal_Month_Time_Span : Number of days in the fiscal month; time series attribute. (28, 29, 30, 31)
- Fiscal_Month_Name : Full name of the fiscal month. (January, February, ..., December)
- Fiscal_Month_Short_Name : Shortened name of the fiscal month . ( Jan, Feb, ..., Dec)
- Fiscal_Month_of_Quarter : Fiscal month number in this fiscal quarter . (1, 2, ..., 3)
- Fiscal_Month_of_Year : Fiscal month number in this fiscal year . (1, 2, ..., 12, 13)
- Fiscal_Month_of_Epoch : Fiscal month serial number . (1, 2, 3, ...)
- Fiscal_Month_Attributes 1-5 : Fiscal month level user-defined attribute s.
CLK_L_WEEK
- Week_Begin_Date : First date in the week; natural key . (01-APR-2001)
- Week_End_Date : Last date in the week; time series attribute . (07-APR-2001)
- Week_Time_Span : Number of days in the week; time series attribute. (always equals 7)
- Week_Name : Full name of the week. (April 1, 2001 - April 7, 2001)
- Week_Short_Name : Shortened name of the week . (4/1/01 - 4/7/01)
- Week_of_Epoch : Week serial number. (1, 2, 3, ...)
- Week_Attributes 1-5 : Week level user-defined attributes.
CLK_L_DAY
- Day_Begin_Date : First date in the day; natural key . ( 01-JAN-1997)
- Day_End_Date : Last date in the day; time series attribute . (01-JAN-1997)
- Day_Time_Span : Number of days in the day; time series attribute. (always equal to 1)
- Day_Name : Full name of the day. (Sunday, Monday, ..., Saturday)
- Day_Short_Name : Shortened name of the day. (Sun, Mon, ..., Sat )
- Day_Event : Indicates special events such as disasters. (none, Stock Market Crash, etc.)
- Day_Fiscal_Period : Fiscal period in which this day falls .
- Day_Holiday : Name of holiday on which this day falls. (none, New Year's Day, Thanksgiving).
- Day_Season : Season in which this day falls . ( Winter, Spring, Summer, Fall)
- Day_Weekday : Flag that indicates whether this day is a weekday . (Y, N)
- Day_Workday : Flag that indicates whether this day is a workday . ( Y, N)
- Day_Last_of_Month : Flag that indicates whether this day is the last of the month. (Y, N)
- Day_of_Week : Day number in this week . ( 1, 2, ..., 7)
- Day_of_Calendar_Month : Day number in this calendar month. (1, 2, ..., 31)
- Day_of_Calendar_Quarter : Day number in this calendar quarter . ( 1, 2, ..., 366)
- Day_of_Calendar_Year : Day number in this calendar year . (1, 2, ..., 366)
- Day_of_Fiscal_Month : Day number in this fiscal month. (1, 2, ..., 31)
- Day_of_Fiscal_Quarter : Day number in this fiscal quarter . (1, 2, ..., 366)
- Day_of_Fiscal_Year : Day number in this fiscal year . (1, 2, ..., 366)
- Day_of_Epoch: Day serial number . (1, 2, 3, ...)
- Day_Attributes 1-5: User-defined atttributes.
