| Insurance Data Model |
Description | Fact entity that records the measures related to policy component payments.The measures are fully identified by the dimensions, which are:- Time (grain: day)- Policy (grain: Policy component)- Geographic area of the Policyholder's home (grain: Postcode)- Vehicle (grain: vehicle profile)- Specification (grain: Coverage)- Channel role (grain: Channel role)The measures are additive across all dimensions.For example, Gross premium measure represent the gross premium amount per day, per policy component, per geographic area of the policyholder's home, per insured vehicle profile, per coverage, and per selling channel role. |
Primary Key | |
![]() |
Column Details |
Description | Financial value of the Insurer's Service or Administrative charge element included in the Gross premium for a Policy component. |
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 received by the Insurance Company when selling third-party policy components. |
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 paid out by the Insurance Company when selling their policy components via Channels. |
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 | Financial value of the Government Charges element included in the Gross premium for a Policy component. |
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 gross premium charged for the risk represented by the policy component period (after applying all rating procedures, including NCD and after addition of any commission, charges and taxes).Gross premium=Net premium-NCD discount -Other discount+Administrative charge+Government charge+Other charge+Tax +Commission |
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 | Interest amount gained from investing the premiums in the Money market. |
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 | Net premium for the Policy component for the risk period, EXCLUDING Service and Government Charges, Discount, Stamp Duty, Tax, and Commission.Pure cost of protection, or the premium covering the present value of future claims.Alias for Pure premium. |
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 number of premium payment cheques that have been rejected by a bank. |
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 | A charge paid by the insured for non-insurance services such as inspection services, survey or legal costs etc. |
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 by which the Basic premium for a Policy Component is reduced due to special factors pertinent to the risk (except No Claim Discount percentage where separate attribute applies). |
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 | Financial value of the Tax element included in the Gross premium for a Policy component. |
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 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 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 Top level financial services agreement |
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 | The unique identifier of the Payment |
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 | The unique identifier of the Person |
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 | The unique identifier of the Customer |
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 | 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 unique identifier of the Channel role |
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 | The unique identifier of the Code |
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 | The unique identifier of the Channel role mini dimension |
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 |
Foreign Key Details |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_PAYMENTS_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_PAYMENTS_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_PAYMENTS_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_PAYMENTS_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_PAYMENTS_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_PAYMENTS_FACT |
Child Role Name | Verb1 |
Child Multiplicity | * |
Key Column | INSURANCE_POLICY_ID [BIGINT] |
Parent Table | INSURANCE_POLICY |
Parent Multiplicity | 1 |
Unique Constraint | INSURANCE_POLICY_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | POLICY_PAYMENTS_FACT |
Child Role Name | Verb2 |
Child Multiplicity | * |
Key Column | PAYMENT_ID [BIGINT] |
Parent Table | PAYMENT |
Parent Multiplicity | 1 |
Unique Constraint | PAYMENT_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_PAYMENTS_FACT |
Child Role Name | Verb3 |
Child Multiplicity | * |
Key Column | PERSON_ID [BIGINT] |
Parent Table | PERSON |
Parent Multiplicity | 1 |
Unique Constraint | PERSON_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_PAYMENTS_FACT |
Child Role Name | Verb4 |
Child Multiplicity | * |
Key Column | CUSTOMER_ID [BIGINT] |
Parent Table | CUSTOMER |
Parent Multiplicity | 1 |
Unique Constraint | CUSTOMER_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | POLICY_PAYMENTS_FACT |
Child Role Name | Verb6 |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_ID [BIGINT] |
Parent Table | CHANNEL_ROLE |
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_PAYMENTS_FACT |
Child Role Name | Verb7 |
Child Multiplicity | * |
Key Column | LINE_OF_BUSINESS_ID [BIGINT] |
Parent Table | CODE |
Parent Multiplicity | 1 |
Unique Constraint | CODE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | POLICY_PAYMENTS_FACT |
Child Role Name | Verb5 |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT] |
Parent Table | CHANNEL_ROLE_MINI_DIMENSION |
Parent Multiplicity | 0..1 |
Unique Constraint | CHANNEL_ROLE_MINI_DIMENSION_PK |
Primary Key Details |
Description | The unique identifier of the Auto policy payments Fact |
Member | CALENDAR_DATE_ID [BIGINT] |
Member | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Member | GEOGRAPHIC_AREA_ID [BIGINT] |
Member | INSURANCE_POLICY_ID [BIGINT] |
Member | PAYMENT_ID [BIGINT] |
Member | PERSON_ID [BIGINT] |
Member | CUSTOMER_ID [BIGINT] |
Member | CHANNEL_ROLE_ID [BIGINT] |
Member | LINE_OF_BUSINESS_ID [BIGINT] |
| Insurance Data Model |