| Insurance Data Model |
Description | This analytical subject area allows the monitoring and the identification of inefficiencies in the claims handling process. Resulting reports help to optimize suppliers networks and to improve operational efficiency and customer satisfaction. Dimensions:- Time (grain: Daily snapshot on a monthly, quarterly or yearly reference period)- Product component (grain: Product component)- Policy (grain: Policy)- Claim (grain: Claim)- Loss event (grain: Loss event)- Policyholder (grain: Person)- Organisation (grain: Branch)- Intermediary (grain: Intermediary)- Claim adjuster (grain: Person)The claim offer snapshot analysis records measures related to claim benefit volumes and amounts at a given point in time. All measures that are additive on the Time dimension are located in the |
Primary Key | |
![]() |
Column Details |
Description | Proportion of benefit amount paid to the total premium amount received.Measure Calculation Formula:[ Money_provision_part_Amount /( Money_provision_part_Amount + Money_provision_part_Amount )] |
Data Type | DECIMAL(8 , 5) |
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 proportion of covered claims per total number of claims.Measure Calculation Formula:[/ Claim___Agreement_Rlship_Claim_id ] |
Data Type | DECIMAL(8 , 5) |
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 ratio of late claims to the total number of newly opened claims during the period.Measure Calculation Formula:[ Claim_folder_Status_date / Claim___Agreement_Rlship_Claim_id ] |
Data Type | DECIMAL(8 , 5) |
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 estimate payment amount of claims outstanding at the end of the current period. |
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 outstanding incoming recovery payment amount at the end of the period.Measure Calculation Formula:[ Monetary_account_Balance - Money_provision_part_Amount ] |
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 outstanding incoming recovery payment amount at the beginning of the period. |
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 approval limit applicable for this type of claim to require more detailed investigation. |
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 | Identfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception. |
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 | Identifies the length of time between policy renewal date and the claim event. |
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 | Identifies the number of days between the loss event and the end of new value period for the loss item. |
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 | 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 Claim 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 Top level financial services agreement 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 Person 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 Claim 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 | Identifies the Top level financial services agreement 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 Person 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 | Total cost for claim settlement activities, including payment determination, claim disposition, claim closing and quality management. |
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 amount for the money provision element part (used for decomposing a money provision element). |
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 | Amount requested by the insured (or third parties and their insurers) under the claim. |
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 | Identfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception. |
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 | Identfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception. |
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 | Identfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception. |
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 | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CLAIM_FOLDER_ID [BIGINT] |
Parent Table | CLAIM |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | CLAIM_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CLAIM_OFFER_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 | false |
Child Table | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CLAIM_FOLDER_MINI_DIMENSION_ID [BIGINT] |
Parent Table | CLAIM_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | CLAIM_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CLAIM_OFFER_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 | true |
Child Table | CLAIM_OFFER_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 | CLAIM_OFFER_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 | true |
Child Table | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | INSURANCE_POLICY_ID [BIGINT] |
Parent Table | INSURANCE_POLICY |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | INSURANCE_POLICY_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | PERSON_ID [BIGINT] |
Parent Table | PERSON |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | PERSON_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | PERSON_MINI_DIMENSION_ID [BIGINT] |
Parent Table | PERSON_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | PERSON_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CLAIM_OFFER_SNAPSHOT_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | INSURANCE_POLICY_MINI_DIMENSION_ID [BIGINT] |
Parent Table | INSURANCE_POLICY_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | INSURANCE_POLICY_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CLAIM_OFFER_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 |
Primary Key Details |
Description | The unique identifier of the Claim offer snapshot Fact |
Member | CLAIM_FOLDER_ID [BIGINT] |
Member | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Member | CALENDAR_DATE_ID [BIGINT] |
Member | INSURANCE_POLICY_ID [BIGINT] |
Member | PERSON_ID [BIGINT] |
Member | GEOGRAPHIC_AREA_ID [BIGINT] |
| Insurance Data Model |