Table CALENDAR_DATE

DescriptionCALENDAR DATE contains the dates of the Gregorian calendar and is used to assist analysis by time dimension at day level.
Calendar month, quarter and year are in separate entities to allow more flexibility to join the time dimension to fact tables where the grain is day, month, quarter, or year.
If the grain of the time dimension is always the day, then the Calendar month, year, quarter, and year would better be collapsed into one table.

The aggregation hierarchy from the Calendar day or date is the following:
- Calendar date, Calendar month, Calendar quarter, Calendar year
- Calendar date, Season
- Calendar date, Fiscal period
- Calendar date, Week number in calendar year
- Calendar date, Week number in calendar month

Columns
CALENDAR_DATE_IDCALENDAR_DATE_ID
CALENDAR_DATECALENDAR_DATE
CALENDAR_MONTHCALENDAR_MONTH
CALENDAR_MONTH_ROWCALENDAR_MONTH_ROW
CALENDAR_MONTH_NAMECALENDAR_MONTH_NAME
CALENDAR_QUARTERCALENDAR_QUARTER
CALENDAR_QUARTER_ROWCALENDAR_QUARTER_ROW
CALENDAR_WEEK_NUMBERCALENDAR_WEEK_NUMBER
CALENDAR_YEARCALENDAR_YEAR
CALENDAR_YEAR_ROWCALENDAR_YEAR_ROW
COMPANY_HOLIDAYCOMPANY_HOLIDAY
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_ROWFISCAL_MONTH_ROW
FISCAL_QUARTERFISCAL_QUARTER
FISCAL_QUARTER_ROWFISCAL_QUARTER_ROW
FISCAL_WEEKFISCAL_WEEK
FISCAL_WEEK_NUMBERFISCAL_WEEK_NUMBER
FISCAL_YEARFISCAL_YEAR
FISCAL_YEAR_ROWFISCAL_YEAR_ROW
LAST_DAY_IN_CALENDAR_MONTHLAST_DAY_IN_CALENDAR_MONTH
PUBLIC_HOLIDAYPUBLIC_HOLIDAY
SEASONSEASON
WEEK_NUMBER_IN_CALENDAR_MONTHWEEK_NUMBER_IN_CALENDAR_MONTH
WEEK_NUMBER_IN_CALENDAR_YEARWEEK_NUMBER_IN_CALENDAR_YEAR
WEEKDAYWEEKDAY

Primary Key
CALENDAR_DATE_PKCALENDAR_DATE_PK

Column Details

 CALENDAR_DATE_ID
DescriptionThe unique identifier of the Calendar date
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identitytrue
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_DATE
DescriptionCalendar date expressed in the standard format of the insurance company.

e.g. 21/05/2004
Data TypeDATE
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH
DescriptionThe number of calendar months which have elapsed since the start of the calendar year till the calendar date.

e.g. 4
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH_ROW
DescriptionIndicates whether this row represents a Calendar month in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_MONTH_NAME
DescriptionThe name of the calendar month.

e.g. May
Data TypeVARCHAR(20)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_QUARTER
DescriptionThe textual name of each of the four calendar months periods in which a calendar year can be divided.
Each of the four calendar months periods in which a calendar year can be divided.

e.g. Q1
e.g. Second quarter (April to June)
Data TypeVARCHAR(20)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_QUARTER_ROW
DescriptionIndicates whether this row represents a Calendar quarter in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_WEEK_NUMBER
DescriptionThe identification of the calendar week of this calendar date.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_YEAR
DescriptionThe Gregorian calendar year number of the calendar date. One calendar year is made of 12 calendar months.

e.g. 2004
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_YEAR_ROW
DescriptionIndicates whether this row represents a Calendar year in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMPANY_HOLIDAY
DescriptionIndicates whether the calendar date is a holiday for the entire company.

e.g. Yes
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 DAY_NUMBER_IN_CALENDAR_MONTH
DescriptionThe number of calendar days which have elapsed since the start of the calendar month till the calendar date.

e.g. 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
DescriptionThe number of calendar days which have elapsed since the start of the calendar year till the calendar date.

e.g. 142
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 DAY_OF_WEEK
DescriptionThe name of the calendar day in the week.

e.g. Friday
Data TypeVARCHAR(20)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_MONTH
DescriptionCode indicating the fiscal month to which the calendar date belongs.
The company 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
DescriptionIndicates whether this row represents a Fiscal month in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_QUARTER
DescriptionCode indicating the fiscal quarter to which the calendar date belongs.
The company 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_QUARTER_ROW
DescriptionIndicates whether this row represents a Fiscal quarter in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_WEEK
DescriptionCode indicating the fiscal week to which the calendar date belongs.
The company 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
DescriptionThe identification of the fiscal week of this calendar date.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FISCAL_YEAR
DescriptionCode indicating the fiscal year to which the calendar date belongs.
The company 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
DescriptionIndicates whether this row represents a Fiscal year in the hierarchy of Calendar date.
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 LAST_DAY_IN_CALENDAR_MONTH
DescriptionIndicates whether the calendar date is the last day of the calendar month.

e.g. Yes
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PUBLIC_HOLIDAY
DescriptionIndicates whether the calendar date is a public holiday.

e.g. No
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 SEASON
DescriptionEach of the four periods of time in a calendar year that are defined by the equinoxes and solstices.

e.g. Spring
Data TypeVARCHAR(20)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEK_NUMBER_IN_CALENDAR_MONTH
DescriptionThe 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.

e.g. 3
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEK_NUMBER_IN_CALENDAR_YEAR
DescriptionThe number of calendar weeks which have elapsed since the start of the calendar year till the calendar date.

e.g. 21
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 WEEKDAY
DescriptionIndicates whether the calendar date is a day of the week or a day of the weekend. The flag is set to 'Yes' for Monday, Tuesday, Wednesday, Thursday and Friday. The flag is set to 'No' for Saturday and Sunday.

e.g. Yes
Data TypeCHAR(1)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Primary Key Details

 CALENDAR_DATE_PK
DescriptionThe unique identifier of the Calendar date
MemberCALENDAR_DATE_ID [BIGINT]