| Insurance Data Model |
Description | CALENDAR 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 |
Primary Key | |
![]() |
Column Details |
Description | The unique identifier of the Calendar date |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | true |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Calendar date expressed in the standard format of the insurance company. e.g. 21/05/2004 |
Data Type | DATE |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of calendar months which have elapsed since the start of the calendar year till the calendar date. e.g. 4 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Calendar month in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The name of the calendar month. e.g. May |
Data Type | VARCHAR(20) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The 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 Type | VARCHAR(20) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Calendar quarter in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The identification of the calendar week of this calendar date. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The Gregorian calendar year number of the calendar date. One calendar year is made of 12 calendar months. e.g. 2004 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Calendar year in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether the calendar date is a holiday for the entire company. e.g. Yes |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of calendar days which have elapsed since the start of the calendar month till the calendar date. e.g. 21 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of calendar days which have elapsed since the start of the calendar year till the calendar date. e.g. 142 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The name of the calendar day in the week. e.g. Friday |
Data Type | VARCHAR(20) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Code 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Fiscal month in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Code 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Fiscal quarter in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Code 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The identification of the fiscal week of this calendar date. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Code 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether this row represents a Fiscal year in the hierarchy of Calendar date. |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether the calendar date is the last day of the calendar month. e.g. Yes |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates whether the calendar date is a public holiday. e.g. No |
Data Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Each of the four periods of time in a calendar year that are defined by the equinoxes and solstices. e.g. Spring |
Data Type | VARCHAR(20) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | 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. e.g. 3 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of calendar weeks which have elapsed since the start of the calendar year till the calendar date. e.g. 21 |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Indicates 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 Type | CHAR(1) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Primary Key Details |
Description | The unique identifier of the Calendar date |
Member | CALENDAR_DATE_ID [BIGINT] |
| Insurance Data Model |