| Insurance Data Model |
Description | Fact entity that records measures related to the performance of sales and administration related activities.The measures are fully identified by the dimensions which are:- Time (grain: Calendar date)- Specification (grain: Product component) - Channel role (grain: Channel role) - Customer profile (grain: Customer profile)- Task (grain: Task)- Task performer (grain: Employee position) |
Foreign Key | |
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
Primary Key | |
![]() |
Column Details |
Description | Total amount of the collected 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 | Total amount of the collected revenue. |
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 proportion of the cost of billing and collection per collected revenue.This measure is not additive on the Time dimension.Measure Calculation Formula:[ Task_Actual_cost / Payment_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 | Indicator of the satisfaction of the customer with the sales process. This measure is not additive on the Time dimension. |
Data Type | DECIMAL(7 , 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 | Total amount of revenue generated through sales activities. |
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 elapsed time between generation of a lead and making first contact with the prospect or customer. |
Data Type | DECIMAL(20 , 6) |
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 marginal activity cost per product, per policy for that product.Measure Calculation Formula:[ Task_Actual_cost /] |
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 customers who bought policies and who did not own active policies beforehand. |
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 | Total number of new policies that were sold to customers. |
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 | Number of policies renewed during the specified period. |
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 | Total number of new and existing customers that are targeted by the sales activities. |
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 new customers in the campaign target list. |
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 | Total number of policies no longer in-force, due to termination at maturity or any other reason (cancellation), for specified period. |
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 proportion of the revenue realised as a result of the sales process, versus, the total cost of the sales and distribution process. This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount / Task_Actual_cost ] |
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 revenue that was not realised due to the termination of policies.Measure Calculation Formula:[sum() for policy events that are termination events] |
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 | Percentage of the revenue that is earned by the acquisition of new customers.This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_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 | Percentage of the revenue that is earned via sale of policies for new products.This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount /] |
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 | Measures the accuracy of the sales forecast as a ratio between actual sales versus forecasted sales.This measure is not additive on the Time dimension.Measure Calculation Formula:[(+)/ Campaign_Planned_agreement_sales ] |
Data Type | DECIMAL(7 , 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 of the due payments that are unsettled and overdue. |
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 | Proportion at which sales leads result in actual sales, to the total sales. Expresses the importance of sales lead identification in the total sales figures.This measure is not additive on the Time dimension.Measure Calculation Formula:[((/(+ Activity___Agreement_Rlship_Agreement_id ))/(+))*100] |
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 number of products sold to new or existing customers that were identified as sales lead.This measure is not additive on the Time dimension.Measure Calculation Formula:[( Customer_Status_date + Customer_Status_date )/(+)] |
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 | Proportion of the total sales incentives against the Selling, General and Administrative expenses (SGA).This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_part_Amount /(( Money_provision_part_Amount + Money_provision_part_Amount - Money_provision_part_Amount )+ Particular_activity_Actual_cost + Particular_activity_Actual_cost + Particular_activity_Actual_cost )*100] |
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 | Total number of products sold per customer.This measure is not additive on the Time dimension.Measure Calculation Formula:[( Customer_Status_date + Customer_Status_date )/ Financial_services_role_Role_player_id ] |
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 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 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 | 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 Geographic Area |
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 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 |
Description | The proportion of the revenue realised as a result of the sales process, versus, the total cost of the sales and distribution process. This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount / Task_Actual_cost ] |
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 the revenue realised as a result of the sales process, versus, the total cost of the sales and distribution process. This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount / Task_Actual_cost ] |
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 the revenue realised as a result of the sales process, versus, the total cost of the sales and distribution process. This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount / Task_Actual_cost ] |
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 the revenue realised as a result of the sales process, versus, the total cost of the sales and distribution process. This measure is not additive on the Time dimension.Measure Calculation Formula:[ Money_provision_cash_flow_Base_amount / Task_Actual_cost ] |
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 |
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 Role player 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 |
Foreign Key Details |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | SALES_AND_ADMINISTRATION_PERFORMANCE_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 | SALES_AND_ADMINISTRATION_PERFORMANCE_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 | SALES_AND_ADMINISTRATION_PERFORMANCE_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 | SALES_AND_ADMINISTRATION_PERFORMANCE_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 | SALES_AND_ADMINISTRATION_PERFORMANCE_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 | SALES_AND_ADMINISTRATION_PERFORMANCE_FACT |
Child Role Name | Verb2 |
Child Multiplicity | * |
Key Column | GEOGRAPHIC_AREA_ID [BIGINT] |
Parent Table | GEOGRAPHIC_AREA |
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 | SALES_AND_ADMINISTRATION_PERFORMANCE_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 |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | SALES_AND_ADMINISTRATION_PERFORMANCE_FACT |
Child Role Name | Verb3 |
Child Multiplicity | * |
Key Column | MARKET_SEGMENT_ID [BIGINT] |
Parent Table | MARKET_SEGMENT |
Parent Multiplicity | 1 |
Unique Constraint | MARKET_SEGMENT_PK |
Primary Key Details |
Description | The unique identifier of the Sales and administration performance 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] |
Member | MARKET_SEGMENT_ID [BIGINT] |
| Insurance Data Model |