Table CLAIM_OFFER_SNAPSHOT_FACT

DescriptionThis 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 entity.
The measures are fully identified by the Reference month on which they are calculated and by the dimensions, which are:
- Specification (grain: Product component)
- Policyholder (grain: Person)
- Policy (grain:Individual agreement)
- Claim (grain: Claim folder)
- Party role in claim (grain: Claim adjuster)
- Channel role (grain: Servicing channel role)
- Approval limit

Columns
CLAIMS_RATIOCLAIMS_RATIO
COVERED_CLAIMS_RATIOCOVERED_CLAIMS_RATIO
LATE_CLAIM_PERCENTAGELATE_CLAIM_PERCENTAGE
OUTSTANDING_AMOUNTOUTSTANDING_AMOUNT
RECOVERY_BALANCE_OUTSTANDINGRECOVERY_BALANCE_OUTSTANDING
RECOVERY_INITIAL_BALANCERECOVERY_INITIAL_BALANCE
APPROVAL_LIMITAPPROVAL_LIMIT
NUMBER_OF_DAYS_SINCE_POLICY_INCEPTIONNUMBER_OF_DAYS_SINCE_POLICY_INCEPTION
NUMBER_OF_DAYS_BEFORE_COVER_CEASESNUMBER_OF_DAYS_BEFORE_COVER_CEASES
NUMBER_OF_DAYS_LEFT_IN_NEW_VALUE_PERIODNUMBER_OF_DAYS_LEFT_IN_NEW_VALUE_PERIOD
CALENDAR_DATE_IDCALENDAR_DATE_ID
CLAIM_FOLDER_IDCLAIM_FOLDER_ID
FINANCIAL_SERVICES_PRODUCT_IDFINANCIAL_SERVICES_PRODUCT_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
INSURANCE_POLICY_IDINSURANCE_POLICY_ID
PERSON_IDPERSON_ID
CLAIM_FOLDER_MINI_DIMENSION_IDCLAIM_FOLDER_MINI_DIMENSION_ID
FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_IDFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
INSURANCE_POLICY_MINI_DIMENSION_IDINSURANCE_POLICY_MINI_DIMENSION_ID
PERSON_MINI_DIMENSION_IDPERSON_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
CLAIM_SETTLEMENT_COSTCLAIM_SETTLEMENT_COST
CURRENT_AMOUNT_PAIDCURRENT_AMOUNT_PAID
CLAIMED_AMOUNTCLAIMED_AMOUNT
NUMBER_OF_MONTHS_SINCE_POLICY_INCEPTIONNUMBER_OF_MONTHS_SINCE_POLICY_INCEPTION
NUMBER_OF_MONTHS_SINCE_LAST_CLAIMNUMBER_OF_MONTHS_SINCE_LAST_CLAIM
NUMBER_OF_CLAIMS_DENIEDNUMBER_OF_CLAIMS_DENIED

Foreign Key
CLAIM_OFFE_CLAI_FKCLAIM_OFFE_CLAI_FK
CLAIM__FINANCI_FKCLAIM__FINANCI_FK
CLAIM_OFF_CLAI_FKCLAIM_OFF_CLAI_FK
CLAIM_O_FS_PROD_FKCLAIM_O_FS_PROD_FK
CLAIM_OFFE_CALE_FKCLAIM_OFFE_CALE_FK
CLAIM_OFFE_GEOG_FKCLAIM_OFFE_GEOG_FK
CLAIM_OFFE_INSU_FKCLAIM_OFFE_INSU_FK
CLAIM_OFFE_PERS_FKCLAIM_OFFE_PERS_FK
CLAIM_OFF_PERSO_FKCLAIM_OFF_PERSO_FK
CLAI_INSURANCE__FKCLAI_INSURANCE__FK
CLAI_GEOGRAPHI_FKCLAI_GEOGRAPHI_FK

Primary Key
CLAIM_OFFER_SNAPSHOT_FACT_PKCLAIM_OFFER_SNAPSHOT_FACT_PK

Column Details

 CLAIMS_RATIO
DescriptionProportion 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 TypeDECIMAL(8 , 5)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COVERED_CLAIMS_RATIO
DescriptionThe proportion of covered claims per total number of claims.


Measure Calculation Formula:
[/ Claim___Agreement_Rlship_Claim_id ]
Data TypeDECIMAL(8 , 5)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 LATE_CLAIM_PERCENTAGE
DescriptionThe 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 TypeDECIMAL(8 , 5)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 OUTSTANDING_AMOUNT
DescriptionThe estimate payment amount of claims outstanding at the end of the current period.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 RECOVERY_BALANCE_OUTSTANDING
DescriptionThe outstanding incoming recovery payment amount at the end of the period.


Measure Calculation Formula:
[ Monetary_account_Balance - Money_provision_part_Amount ]
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 RECOVERY_INITIAL_BALANCE
DescriptionThe outstanding incoming recovery payment amount at the beginning of the period.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 APPROVAL_LIMIT
DescriptionThe approval limit applicable for this type of claim to require more detailed investigation.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_DAYS_SINCE_POLICY_INCEPTION
DescriptionIdentfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_DAYS_BEFORE_COVER_CEASES
DescriptionIdentifies the length of time between policy renewal date and the claim event.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_DAYS_LEFT_IN_NEW_VALUE_PERIOD
DescriptionIdentifies the number of days between the loss event and the end of new value period for the loss item.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_DATE_ID
DescriptionIdentifies the Calendar date dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIM_FOLDER_ID
DescriptionIdentifies the Claim dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FINANCIAL_SERVICES_PRODUCT_ID
DescriptionIdentifies the Financial services product dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 GEOGRAPHIC_AREA_ID
DescriptionIdentifies the Geographic Area dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 INSURANCE_POLICY_ID
DescriptionIdentifies the Top level financial services agreement dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_ID
DescriptionIdentifies the Person dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIM_FOLDER_MINI_DIMENSION_ID
DescriptionIdentifies the Claim mini dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
DescriptionIdentifies the Financial services product mini dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 GEOGRAPHIC_AREA_MINI_DIMENSION_ID
DescriptionIdentifies the Geographic Area Mini Dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 INSURANCE_POLICY_MINI_DIMENSION_ID
DescriptionIdentifies the Top level financial services agreement mini dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_MINI_DIMENSION_ID
DescriptionIdentifies the Person mini dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 POPULATION_TIMESTAMP
DescriptionA timestamp that identifies when this Fact Entity's instance was populated
Data TypeTIMESTAMP
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIM_SETTLEMENT_COST
DescriptionTotal cost for claim settlement activities, including payment determination, claim disposition, claim closing and quality management.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CURRENT_AMOUNT_PAID
DescriptionThe amount for the money provision element part (used for decomposing a money provision element).
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIMED_AMOUNT
DescriptionAmount requested by the insured (or third parties and their insurers) under the claim.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_MONTHS_SINCE_POLICY_INCEPTION
DescriptionIdentfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_MONTHS_SINCE_LAST_CLAIM
DescriptionIdentfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_CLAIMS_DENIED
DescriptionIdentfies the length of time between a policy inception and event date to ascertain claims made shortly after policy inception.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

 CLAIM_OFFE_CLAI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCLAIM_FOLDER_ID [BIGINT]
Parent TableCLAIM
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCLAIM_PK



 CLAIM__FINANCI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
Parent TableFINANCIAL_SERVICES_PRODUCT
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintFINANCIAL_SERVICES_PRODUCT_PK



 CLAIM_OFF_CLAI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCLAIM_FOLDER_MINI_DIMENSION_ID [BIGINT]
Parent TableCLAIM_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintCLAIM_MINI_DIMENSION_PK



 CLAIM_O_FS_PROD_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID [BIGINT]
Parent TableFS_PRODUCT_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintFS_PRODUCT_MINI_DIMENSION_PK



 CLAIM_OFFE_CALE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCALENDAR_DATE_ID [BIGINT]
Parent TableCALENDAR_DATE
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCALENDAR_DATE_PK



 CLAIM_OFFE_GEOG_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnGEOGRAPHIC_AREA_ID [BIGINT]
Parent TableGEOGRAPHIC_AREA
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintGEOGRAPHIC_AREA_PK



 CLAIM_OFFE_INSU_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnINSURANCE_POLICY_ID [BIGINT]
Parent TableINSURANCE_POLICY
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintINSURANCE_POLICY_PK



 CLAIM_OFFE_PERS_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnPERSON_ID [BIGINT]
Parent TablePERSON
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintPERSON_PK



 CLAIM_OFF_PERSO_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnPERSON_MINI_DIMENSION_ID [BIGINT]
Parent TablePERSON_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintPERSON_MINI_DIMENSION_PK



 CLAI_INSURANCE__FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnINSURANCE_POLICY_MINI_DIMENSION_ID [BIGINT]
Parent TableINSURANCE_POLICY_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintINSURANCE_POLICY_MINI_DIMENSION_PK



 CLAI_GEOGRAPHI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCLAIM_OFFER_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnGEOGRAPHIC_AREA_MINI_DIMENSION_ID [BIGINT]
Parent TableGEOGRAPHIC_AREA_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintGEOGRAPHIC_AREA_MINI_DIMENSION_PK

Primary Key Details

 CLAIM_OFFER_SNAPSHOT_FACT_PK
DescriptionThe unique identifier of the Claim offer snapshot Fact
MemberCLAIM_FOLDER_ID [BIGINT]
MemberFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
MemberCALENDAR_DATE_ID [BIGINT]
MemberINSURANCE_POLICY_ID [BIGINT]
MemberPERSON_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]