Table CALENDAR_DATE_DIMENSION

DescriptionCalendar Date identifies the dates of the Gregorian calendar and is used to assist analysis by time dimension at day level.
Calendar month, quarter and year could also be designed as separate entities to allow more flexibility to join the time dimension to fact tables where the grain is day, month, quarter, or year. However, having such separate dimensions prevents the combination of measures stored in different fact tables at different levels of granularity. The sole Calendar Date dimension, with its "Month Row", "Quarter Row" and so on Flags avoids this inconvenience.

Other examples of potential aggregation hierarchies from the Calendar day or date include:
- Calendar date, Season
- Calendar date, Week number in calendar year
- Calendar date, Week number in calendar month.

Creating a view that joins the Calendar Date Dimension table and the Period Outrigger table allows the support of ad hoc measurement periods in a transparent way for business users.

Columns
CALENDAR_DATE_DIMENSION_IDCALENDAR_DATE_DIMENSION_ID
CALENDAR_DATECALENDAR_DATE
CALENDAR_MONTHCALENDAR_MONTH
CALENDAR_MONTH_ROW_FLAGCALENDAR_MONTH_ROW_FLAG
CALENDAR_MONTH_NAMECALENDAR_MONTH_NAME
CALENDAR_QUARTERCALENDAR_QUARTER
CALENDAR_QUARTER_ROW_FLAGCALENDAR_QUARTER_ROW_FLAG
CALENDAR_WEEK_NUMBERCALENDAR_WEEK_NUMBER
CALENDAR_YEARCALENDAR_YEAR
CALENDAR_YEAR_ROW_FLAGCALENDAR_YEAR_ROW_FLAG
COMPANY_HOLIDAY_FLAGCOMPANY_HOLIDAY_FLAG
DAY_NUMBER_IN_CALENDAR_MONTHDAY_NUMBER_IN_CALENDAR_MONTH
DAY_NUMBER_IN_CALENDAR_YEARDAY_NUMBER_IN_CALENDAR_YEAR
DAY_OF_WEEKDAY_OF_WEEK
FISCAL_MONTHFISCAL_MONTH
FISCAL_MONTH_ROW_FLAGFISCAL_MONTH_ROW_FLAG
FISCAL_QUARTERFISCAL_QUARTER
FISCAL_QUARTER_ROW_FLAGFISCAL_QUARTER_ROW_FLAG
FISCAL_WEEKFISCAL_WEEK
FISCAL_WEEK_NUMBERFISCAL_WEEK_NUMBER
FISCAL_YEARFISCAL_YEAR
FISCAL_YEAR_ROW_FLAGFISCAL_YEAR_ROW_FLAG
LAST_DAY_IN_CALENDAR_MONTH_FLAGLAST_DAY_IN_CALENDAR_MONTH_FLAG
PUBLIC_HOLIDAY_FLAGPUBLIC_HOLIDAY_FLAG
SEASONSEASON
WEEKDAY_FLAGWEEKDAY_FLAG
WEEK_NUMBER_IN_CALENDAR_MONTHWEEK_NUMBER_IN_CALENDAR_MONTH
WEEK_NUMBER_IN_CALENDAR_YEARWEEK_NUMBER_IN_CALENDAR_YEAR

Primary Key
CALENDAR_DATE_DIMENSION_PKCALENDAR_DATE_DIMENSION_PK

Indexes
CALENDAR_DATE_DIMENSION_PKCALENDAR_DATE_DIMENSION_PK

Column Details

 CALENDAR_DATE_DIMENSION_ID
DescriptionThe unique identifier of the Calendar Date Dimension.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identitytrue
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_DATE
DescriptionCalendar Date indicates the date expressed in the standard format of the modeled organization. For example; 21/05/2014.
Data TypeDATE
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH
DescriptionCalendar Month indicates the number of calendar months which have elapsed since the start of the calendar year till the calendar date. For example; 4.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH_ROW_FLAG
DescriptionCalendar Month Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Calendar Month in the hierarchy of Calendar Date. Typically, the last day of the month is often used by Financial Institutions as the reference for the month.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH_NAME
DescriptionCalendar Month Name indicates the name of the calendar month. For example; May.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_QUARTER
DescriptionCalendar Quarter indicates the textual name of each of the four calendar months periods in which a calendar year can be divided. For example; Q2, Second quarter (April to June).
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_QUARTER_ROW_FLAG
DescriptionCalendar Quarter Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Calendar Quarter in the hierarchy of Calendar Date. Typically, the last day of the quarter is often used by Financial Institutions as the reference for the quarter.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_WEEK_NUMBER
DescriptionCalendar Week Number indicates the calendar week of the calendar date.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_YEAR
DescriptionCalendar Year indicates the Gregorian calendar year number of the Calendar Date. One calendar year is made of 12 calendar months. For example; 2014.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_YEAR_ROW_FLAG
DescriptionCalendar Year Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Calendar Year in the hierarchy of Calendar Date. Typically, the last day of the year is often used by Financial Institutions as the reference for the year.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMPANY_HOLIDAY_FLAG
DescriptionCompany Holiday Flag classifies Calendar Dates based on whether or not they are a holiday for the entire modeled organization.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 DAY_NUMBER_IN_CALENDAR_MONTH
DescriptionDay Number In Calendar Month indicates the number of calendar days which have elapsed since the start of the calendar month till the calendar date. For example; 21.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 DAY_NUMBER_IN_CALENDAR_YEAR
DescriptionDay Number In Calendar Year indicates the number of calendar days which have elapsed since the start of the calendar year till the calendar date. For example; 141.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 DAY_OF_WEEK
DescriptionDay Of Week indicates the name of the calendar day in the week. For example; Wednesday.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_MONTH
DescriptionFiscal Month indicates the fiscal month to which the calendar date belongs.
The modeled organization may use a fiscal period as an accounting period. A fiscal period is used when it does not correspond to calendar periods.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_MONTH_ROW_FLAG
DescriptionFiscal Month Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Fiscal Month in the hierarchy of Calendar Date. Typically, the last day of the fiscal month is often used by Financial Institutions as the reference for the fiscal month.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_QUARTER
DescriptionFiscal Quarter indicates the fiscal quarter to which the calendar date belongs.
The modeled organization may use a fiscal period as an accounting period. A fiscal period is used when it does not correspond to calendar periods
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_QUARTER_ROW_FLAG
DescriptionFiscal Quarter Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Fiscal Quarter in the hierarchy of Calendar Date. Typically, the last day of the fiscal quarter is often used by Financial Institutions as the reference for the fiscal quarter.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_WEEK
DescriptionFiscal Week indicates the fiscal week to which the calendar date belongs.
The modeled organization may use a fiscal period as an accounting period. A fiscal period is used when it does not correspond to calendar periods
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_WEEK_NUMBER
DescriptionFiscal Week Number indicates the fiscal week of the calendar date.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_YEAR
DescriptionFiscal Year indicates the fiscal year to which the calendar date belongs.
The modeled organization may use a fiscal period as an accounting period. A fiscal period is used when it does not correspond to calendar periods.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_YEAR_ROW_FLAG
DescriptionFiscal Year Row Flag classifies rows of the Calendar Date Dimension based on whether the Calendar Date is used as the reference for the Fiscal Year in the hierarchy of Calendar Date. Typically, the last day of the fiscal year is often used by Financial Institutions as the reference for the fiscal year.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 LAST_DAY_IN_CALENDAR_MONTH_FLAG
DescriptionLast Day In Calendar Month classifies Calendar dates based on whether or not the calendar date is the last day of the calendar month.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PUBLIC_HOLIDAY_FLAG
DescriptionPublic Holiday Flag classifies Calendar Dates based on whether (1) or not (0) the day is a holiday available to the general population.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 SEASON
DescriptionSeason indicates each of the four periods of time in a calendar year that are defined by the equinoxes and solstices. For example; Spring.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEKDAY_FLAG
DescriptionWeekday Flag classifies Calendar Dates based on whether they are a day of the week or a day of the weekend. The flag is set to '1' for Monday, Tuesday, Wednesday, Thursday and Friday. The flag is set to '0' for Saturday and Sunday.
Data TypeCHAR(10)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEK_NUMBER_IN_CALENDAR_MONTH
DescriptionWeek Number In Calendar Month indicates the number of calendar weeks which have elapsed since the start of the calendar month till the calendar date. The week number is comprised between 1 and 5.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEK_NUMBER_IN_CALENDAR_YEAR
DescriptionWeek Number In Calendar Year indicates the number of calendar weeks which have elapsed since the start of the calendar year till the calendar date. For example; 21.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Primary Key Details

 CALENDAR_DATE_DIMENSION_PK
DescriptionThe unique identifier of the Calendar Date Dimension
MemberCALENDAR_DATE_DIMENSION_ID [BIGINT]

Index Details

 CALENDAR_DATE_DIMENSION_PK
DescriptionThe unique identifier of the Calendar Date Dimension
Is Uniquetrue
Is Clusteredfalse
MemberCALENDAR_DATE_DIMENSION_ID [BIGINT] Ascending