| Insurance Data Model |
Description | Fact entity that records the snapshot measures related to policy components.The measures are fully identified by the dimensions, which are:- Policy (grain: Policy component)- Geographic area of the Policyholder's home (grain: Postcode)- Channel role (grain: Channel role)- Time (grain: Month)The measures are additive on all the dimensions.For example, Earned premium measure represents the earned premium for a given reference date, per policy component, per geographic area of the policyholder's home, per insured vehicle profile, per coverage, and per selling channel role. |
Foreign Key | |
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
Primary Key | |
![]() |
Column Details |
Description | The duration between the effective date of the Policy component and the reference date, expressed as an amount of days.The part of the coverage (risk exposure) period which has passed, meaning that the insurance company has had a risk exposure during that time.For example, if a policy is paid annually, by the end of the 6th month of the coverage period half of the exposure period has passed (is 'earned') and half is yet to come (is 'unearned'). The example statement above assumes that the exposure can be earned proportionally with the time passed. If the probability of the loss event is not constant over time, than the earned exposure may not be proportional with the time passed, hence it may need to be expressed as an accumulated probability rather than a number of days.Measure Calculation Formula:[A- Coverage_component_Status_date ] |
Data Type | DECIMAL(14 , 2) |
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 duration between the reference date of the Policy component and the planned end date, expressed as an amount of days.The part of the coverage (risk exposure) period which has not passed, meaning that the insurance company will have a risk exposure during that time.For example, if a policy is paid annually, by the end of the 6th month of the coverage period half of the exposure period has passed (is 'earned') and half is yet to come (is 'unearned').The example statement above assumes that the exposure can be earned proportionally with the time passed. If the probability of the loss event is not constant over time, than the unearned exposure may not be proportional with the coverage time yet to pass, hence it may need to be expressed as an accumulated probability over the remaining coverage period rather than a number of days.Measure Calculation Formula:[A- Coverage_component_Planned_end_date ] |
Data Type | DECIMAL(14 , 2) |
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 duration between the effective date of the Policy component and its planned end date, expressed as an amount of days.This assumes that the exposure is proportional with the time passed. If the probability of the loss event is not constant over time, than the written exposure may not be strictly proportional with the time passed, hence it may need to be expressed as an accumulated probability rather than as a number of days.Measure Calculation Formula:[ Coverage_component_Planned_end_date - Coverage_component_Inception_date ] |
Data Type | DECIMAL(14 , 2) |
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 financial value of the Policy component calculated on the Written exposure. |
Data Type | DECIMAL(14 , 2) |
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 | Identifies the Calendar date dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Channel role dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Financial services product dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Geographic Area dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Channel role mini dimension of this Fact Entity. |
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 | Identifies the Financial services product mini dimension of this Fact Entity. |
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 | Identifies the Geographic Area Mini Dimension of this Fact Entity. |
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 | A timestamp that identifies when this Fact Entity's instance was populated |
Data Type | TIMESTAMP |
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 unique identifier of the Category |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
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 |
Foreign Key Details |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CALENDAR_DATE_ID [BIGINT] |
Parent Table | CALENDAR_DATE |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | CALENDAR_DATE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_ID [BIGINT] |
Parent Table | CHANNEL_ROLE |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | CHANNEL_ROLE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Parent Table | FINANCIAL_SERVICES_PRODUCT |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | FINANCIAL_SERVICES_PRODUCT_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | GEOGRAPHIC_AREA_ID [BIGINT] |
Parent Table | GEOGRAPHIC_AREA |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | GEOGRAPHIC_AREA_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT] |
Parent Table | CHANNEL_ROLE_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | CHANNEL_ROLE_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID [BIGINT] |
Parent Table | FS_PRODUCT_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | FS_PRODUCT_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | GEOGRAPHIC_AREA_MINI_DIMENSION_ID [BIGINT] |
Parent Table | GEOGRAPHIC_AREA_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | GEOGRAPHIC_AREA_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_SNAPSHOT_FACT |
Child Role Name | Verb1 |
Child Multiplicity | * |
Key Column | LINE_OF_BUSINESS_ID [BIGINT] |
Parent Table | PRODUCT_GROUP |
Parent Multiplicity | 1 |
Unique Constraint | PRODUCT_GROUP_PK |
Primary Key Details |
Description | The unique identifier of the Auto policy volume Fact |
Member | CALENDAR_DATE_ID [BIGINT] |
Member | CHANNEL_ROLE_ID [BIGINT] |
Member | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Member | GEOGRAPHIC_AREA_ID [BIGINT] |
Member | LINE_OF_BUSINESS_ID [BIGINT] |
| Insurance Data Model |