Dimensional Warehouse Model Data Model

Entity

Entity Sales Performance Analysis

Description

The sales performance of the healthcare insurance is analyzed by comparing the planned or forecasted sales volumes and premiums to their corresponding actual numbers and amounts. Includes reference analysis of forecasted performance of the market as a whole.

Attributes
Average Number Of Policies Per Intermediary
Average Premium Amount
Average Written Premium Per Policyholder Amount
Calendar Dk
Campaign Dimension Dk
Geographic Area Dk
Gross Claim Amount
Gross Claims Ratio
Gross Earned Premium Amount
Group Dk
Health Plan Product Dk
Market Forecasted Average Premium Per Policy Amount
Market Forecasted Number Of Policies
Market Forecasted Total Premium Income Amount
Number Of Intermediaries
Number Of Policies In-force
Number Of Policyholders
Organization Dk
Percentage Forecasted Market Share
Planned Average Premium Amount
Planned Gross Claim Amount
Planned Gross Claims Ratio
Planned Gross Earned Premium Amount
Planned Number Of Policies In-force
Planned Number Of Policyholders

Relationship
Sales Performance Analysis_Calendar Dimension_FK
Sales Performance Analysis_Geographic Area Dimension_FK
Sales Performance Analysis_Group Dimension_FK
Sales Performance Analysis_Campaign Dimension_FK
Sales Performance Analysis_Health Plan Product Dimension_FK
Sales Performance Analysis_Organization Dimension_FK

Primary Key
Sales Performance Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Number Of Policies Per Intermediary

Description	Average number of policies. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Average Premium Amount

Description	The actual average premium amount per policy at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Average Written Premium Per Policyholder Amount

Description	Average revenue per policyholder who holds a given type of insurance product. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Calendar Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Campaign Dimension Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Geographic Area Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Gross Claim Amount

Description	The actual total cost of gross incurred claims at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Gross Claims Ratio

Description	The gross incurred claims cost divided by the gross earned premiums amount. This ratio is expressed at a given point in time. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Gross Earned Premium Amount

Description	The actual amount of total gross earned premiums at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Group Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Health Plan Product Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Market Forecasted Average Premium Per Policy Amount

Description	The market forecasted total premium income divided by the market forecasted number of policies. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Market Forecasted Number Of Policies

Description	The forecasted number of policies that will be sold, endorsed or renewed by the whole market. Typically, this measure is expressed at country level.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Market Forecasted Total Premium Income Amount

Description	The forecasted total premium income resulting from the policies that will be sold, endorsed or renewed by the whole market. Typically, this measure is expressed at country level and represents the market size.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number Of Intermediaries

Description	The number of intermediaries (agents, brokers, etc.) at the reference date.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number Of Policies In-force

Description	The actual number of in-force policies at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number Of Policyholders

Description	The actual number of active policyholders at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Organization Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Percentage Forecasted Market Share

Description	The insurance company's planned gross earned premium amount expressed as a percentage of the market forecasted total premium income. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Average Premium Amount

Description	The planned average premium amount per policy, as that amount would be at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Gross Claim Amount

Description	The planned total cost of gross incurred claims, as that cost would be at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Gross Claims Ratio

Description	Total planned gross incurred claims cost divided by the total planned gross earned premiums amount. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Gross Earned Premium Amount

Description	The planned amount of total gross earned premiums, as that amount would be at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Number Of Policies In-force

Description	The planned number of in-force policies, as that number would be at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned Number Of Policyholders

Description	The planned number of active policyholders, as that number would be at a given point in time.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Details

Sales Performance Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Calendar Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Sales Performance Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Geographic Area Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Sales Performance Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Group Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Sales Performance Analysis_Campaign Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Campaign Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Sales Performance Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_ONE
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Health Plan Product Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Sales Performance Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Sales Performance Analysis
Child Multiplicity	ZERO_TO_ONE
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Organization Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Sales Performance Analysis PK

Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk
Key Attribute	Campaign Dimension Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Organization Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model