Table POLICY_SNAPSHOT_FACT

DescriptionFact 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.

Columns
EARNED_EXPOSUREEARNED_EXPOSURE
UNEARNED_EXPOSUREUNEARNED_EXPOSURE
WRITTEN_EXPOSUREWRITTEN_EXPOSURE
WRITTEN_PREMIUMWRITTEN_PREMIUM
CALENDAR_DATE_IDCALENDAR_DATE_ID
CHANNEL_ROLE_IDCHANNEL_ROLE_ID
FINANCIAL_SERVICES_PRODUCT_IDFINANCIAL_SERVICES_PRODUCT_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
CHANNEL_ROLE_MINI_DIMENSION_IDCHANNEL_ROLE_MINI_DIMENSION_ID
FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_IDFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
LINE_OF_BUSINESS_IDLINE_OF_BUSINESS_ID
CLAIMS_AMOUNTCLAIMS_AMOUNT
CLAIMS_COUNTCLAIMS_COUNT

Foreign Key
POLICY_SNA_CALE_FKPOLICY_SNA_CALE_FK
POLICY_SNA_CHAN_FKPOLICY_SNA_CHAN_FK
POLI_FINANCIAL__F1POLI_FINANCIAL__F1
POLICY_SN_GEOG_FKPOLICY_SN_GEOG_FK
POLI_CHANNEL_RO_F1POLI_CHANNEL_RO_F1
POLI_FS_PRODUC_FK1POLI_FS_PRODUC_FK1
POLI_GEOGRAPHIC_F1POLI_GEOGRAPHIC_F1
POLICY_SNA_PROD_FKPOLICY_SNA_PROD_FK

Primary Key
POLICY_SNAPSHOT_FACT_PKPOLICY_SNAPSHOT_FACT_PK

Column Details

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



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



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



 WRITTEN_PREMIUM
DescriptionThe financial value of the Policy component calculated on the Written exposure.
Data TypeDECIMAL(14 , 2)
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



 CHANNEL_ROLE_ID
DescriptionIdentifies the Channel role 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



 CHANNEL_ROLE_MINI_DIMENSION_ID
DescriptionIdentifies the Channel role 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



 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



 LINE_OF_BUSINESS_ID
DescriptionThe unique identifier of the Category
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIMS_AMOUNT
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CLAIMS_COUNT
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

 POLICY_SNA_CALE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_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



 POLICY_SNA_CHAN_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCHANNEL_ROLE_ID [BIGINT]
Parent TableCHANNEL_ROLE
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCHANNEL_ROLE_PK



 POLI_FINANCIAL__F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_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



 POLICY_SN_GEOG_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_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



 POLI_CHANNEL_RO_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_SNAPSHOT_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT]
Parent TableCHANNEL_ROLE_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintCHANNEL_ROLE_MINI_DIMENSION_PK



 POLI_FS_PRODUC_FK1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_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



 POLI_GEOGRAPHIC_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_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



 POLICY_SNA_PROD_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_SNAPSHOT_FACT
Child Role NameVerb1
Child Multiplicity*
Key ColumnLINE_OF_BUSINESS_ID [BIGINT]
Parent TablePRODUCT_GROUP
Parent Multiplicity1
Unique ConstraintPRODUCT_GROUP_PK

Primary Key Details

 POLICY_SNAPSHOT_FACT_PK
DescriptionThe unique identifier of the Auto policy volume Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberCHANNEL_ROLE_ID [BIGINT]
MemberFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberLINE_OF_BUSINESS_ID [BIGINT]