Table POLICY_PAYMENTS_FACT

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.

Columns
ADMINISTRATIVE_CHARGEADMINISTRATIVE_CHARGE
COMMISSION_PAID_INCOMMISSION_PAID_IN
COMMISSION_PAID_OUTCOMMISSION_PAID_OUT
GOVERNMENT_CHARGEGOVERNMENT_CHARGE
GROSS_PREMIUMGROSS_PREMIUM
INTEREST_INCOMEINTEREST_INCOME
NET_PREMIUMNET_PREMIUM
NUMBER_OF_BOUNCED_CHEQUESNUMBER_OF_BOUNCED_CHEQUES
OTHER_CHARGEOTHER_CHARGE
OTHER_DISCOUNT_AMOUNTOTHER_DISCOUNT_AMOUNT
TAX_AMOUNTTAX_AMOUNT
CALENDAR_DATE_IDCALENDAR_DATE_ID
FINANCIAL_SERVICES_PRODUCT_IDFINANCIAL_SERVICES_PRODUCT_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_IDFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
INSURANCE_POLICY_IDINSURANCE_POLICY_ID
PAYMENT_IDPAYMENT_ID
PERSON_IDPERSON_ID
CUSTOMER_IDCUSTOMER_ID
POLICY_TENUREPOLICY_TENURE
CHANNEL_ROLE_IDCHANNEL_ROLE_ID
LINE_OF_BUSINESS_IDLINE_OF_BUSINESS_ID
CHANNEL_ROLE_MINI_DIMENSION_IDCHANNEL_ROLE_MINI_DIMENSION_ID

Foreign Key
POLICY_PAY_CALE_FKPOLICY_PAY_CALE_FK
POLI_FINANCIAL__FKPOLI_FINANCIAL__FK
POLICY_PA_GEOG_FKPOLICY_PA_GEOG_FK
POLI_FS_PRODUC_FKPOLI_FS_PRODUC_FK
POLI_GEOGRAPHIC_FKPOLI_GEOGRAPHIC_FK
POLICY_PA_INSUR_FKPOLICY_PA_INSUR_FK
POLICY_PAY_PAYM_FKPOLICY_PAY_PAYM_FK
POLICY_PAY_PERS_FKPOLICY_PAY_PERS_FK
POLICY_PAY_CUST_FKPOLICY_PAY_CUST_FK
POLICY_PAY_CHAN_FKPOLICY_PAY_CHAN_FK
POLICY_PAY_CODE_FKPOLICY_PAY_CODE_FK
POLI_CHANNEL_RO_FKPOLI_CHANNEL_RO_FK

Primary Key
POLICY_PAYMENTS_FACT_PKPOLICY_PAYMENTS_FACT_PK

Column Details

 ADMINISTRATIVE_CHARGE
DescriptionFinancial value of the Insurer's Service or Administrative charge element included in the Gross premium for a Policy component.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMMISSION_PAID_IN
DescriptionAmount received by the Insurance Company when selling third-party policy components.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMMISSION_PAID_OUT
DescriptionAmount paid out by the Insurance Company when selling their policy components via Channels.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 GOVERNMENT_CHARGE
DescriptionFinancial value of the Government Charges element included in the Gross premium for a Policy component.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



 INTEREST_INCOME
DescriptionInterest amount gained from investing the premiums in the Money market.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



 NUMBER_OF_BOUNCED_CHEQUES
DescriptionThe number of premium payment cheques that have been rejected by a bank.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 OTHER_CHARGE
DescriptionA charge paid by the insured for non-insurance services such as inspection services, survey or legal costs etc.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



 TAX_AMOUNT
DescriptionFinancial value of the Tax element included in the Gross premium for a Policy component.
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



 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



 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



 INSURANCE_POLICY_ID
DescriptionThe unique identifier of the Top level financial services agreement
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



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



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



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



 CHANNEL_ROLE_ID
DescriptionThe unique identifier of the Channel role
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



 CHANNEL_ROLE_MINI_DIMENSION_ID
DescriptionThe unique identifier of the Channel role mini dimension
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

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



 POLI_FINANCIAL__FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_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_PA_GEOG_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_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_FS_PRODUC_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_PAYMENTS_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_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_PAYMENTS_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_PA_INSUR_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb1
Child Multiplicity*
Key ColumnINSURANCE_POLICY_ID [BIGINT]
Parent TableINSURANCE_POLICY
Parent Multiplicity1
Unique ConstraintINSURANCE_POLICY_PK



 POLICY_PAY_PAYM_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb2
Child Multiplicity*
Key ColumnPAYMENT_ID [BIGINT]
Parent TablePAYMENT
Parent Multiplicity1
Unique ConstraintPAYMENT_PK



 POLICY_PAY_PERS_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb3
Child Multiplicity*
Key ColumnPERSON_ID [BIGINT]
Parent TablePERSON
Parent Multiplicity1
Unique ConstraintPERSON_PK



 POLICY_PAY_CUST_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb4
Child Multiplicity*
Key ColumnCUSTOMER_ID [BIGINT]
Parent TableCUSTOMER
Parent Multiplicity1
Unique ConstraintCUSTOMER_PK



 POLICY_PAY_CHAN_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb6
Child Multiplicity*
Key ColumnCHANNEL_ROLE_ID [BIGINT]
Parent TableCHANNEL_ROLE
Parent Multiplicity1
Unique ConstraintCHANNEL_ROLE_PK



 POLICY_PAY_CODE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb7
Child Multiplicity*
Key ColumnLINE_OF_BUSINESS_ID [BIGINT]
Parent TableCODE
Parent Multiplicity1
Unique ConstraintCODE_PK



 POLI_CHANNEL_RO_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TablePOLICY_PAYMENTS_FACT
Child Role NameVerb5
Child Multiplicity*
Key ColumnCHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT]
Parent TableCHANNEL_ROLE_MINI_DIMENSION
Parent Multiplicity0..1
Unique ConstraintCHANNEL_ROLE_MINI_DIMENSION_PK

Primary Key Details

 POLICY_PAYMENTS_FACT_PK
DescriptionThe unique identifier of the Auto policy payments Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberINSURANCE_POLICY_ID [BIGINT]
MemberPAYMENT_ID [BIGINT]
MemberPERSON_ID [BIGINT]
MemberCUSTOMER_ID [BIGINT]
MemberCHANNEL_ROLE_ID [BIGINT]
MemberLINE_OF_BUSINESS_ID [BIGINT]