Dimensional Warehouse Model Data Model

Entity

Entity Marketing KPI For Growth Analysis

Description

Analysis of the key performance indicators for the marketing processes, to grow revenue.

Attributes
Average Member Satisfaction Score
Calendar Dk
Campaign Dimension Dk
Change In Earned Premium After Campaign
Change In Market Share Percentage
Change In Profit After Campaign
Claims Liability Amount
Earned Exposure Period
Earned Gross Premium Amount
Earned Gross Premium Growth
Earned Premiums From New Members
Earned Premiums From New Products
Geographic Area Dk
Gross Profit Amount
Group Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Market Share Amount
Market Value Amount
Member Satisfaction Score
New Groups Gained Due To Campaign
New Members Gained Due To Campaign
Number Of Members
Number Of Product Market Withdrawals
Organization Dk
Party Role Dk
Percentage Brand Recognition
Percentage Earned Premium From New Products
Percentage Earned Premiums From New Members
Percentage Market Share
Written Exposure Period
Written Gross Premium Amount

Relationship
Marketing KPI For Growth Analysis_Calendar Dimension_FK
Marketing KPI For Growth Analysis_Geographic Area Dimension_FK
Marketing KPI For Growth Analysis_Party Role Dimension_FK
Marketing KPI For Growth Analysis_Group Dimension_FK
Marketing KPI For Growth Analysis_Health Plan Product Dimension_FK
Marketing KPI For Growth Analysis_Insurance Policy Dimension_FK
Marketing KPI For Growth Analysis_Campaign Dimension_FK
Marketing KPI For Growth Analysis_Organization Dimension_FK

Primary Key
Marketing KPI For Growth Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Member Satisfaction Score

Description	Mean member satisfaction score. 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

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

Change In Earned Premium After Campaign

Description	Total premium income generated by a campaign. Sales generated by marketing campaigns measured by premium income. Formula: [A(before campaign) - A(after campaign)] (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

Change In Market Share Percentage

Description	The growth or decline of the market share in the current period as compared to the previous. Formula: [A for current period-A for previous period] (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

Change In Profit After Campaign

Description	This measure identifies the increase (or decrease) in profit that results from a campaign or similar activity. Formula: [A(before campaign) - A(after campaign)] (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

Claims Liability Amount

Description	Total claims paid plus claims incurred but not yet reported.
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

Earned Exposure Period

Description	The duration between the effective date of the policy and the reference date. The part of the coverage (risk exposure) period which has passed, meaning that the health plan 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').
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

Earned Gross Premium Amount

Description	The total premium amount applicable to the coverage risk period, gross of any reinsurer's share. For example, if a policy is paid annually, by the end of the 6th month of the coverage period only half of the paid premium has been earned by the health plan for providing insurance protection. If the policy was canceled, the remaining half may be refunded. Formula: [(A/B)*C] (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

Earned Gross Premium Growth

Description	Percentage increase (or decrease) of earned premium for the current period in comparison with the previous period. Formula: [(A current period / A prior period) -1)*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

Earned Premiums From New Members

Description	Total earned premium amount from the members added due to the campaign who were not enrolled in any product offered by the health plan before the campaign.
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

Earned Premiums From New Products

Description	Total earned premium that is produced by selling new products.
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

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 Profit Amount

Description	Gross profit amount obtained by subtracting the gross claim amount from the earned gross premium amount. 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

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

Insurance Policy 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

Market Share Amount

Description	The monetary value of the share of a market held by an organization or product at the reference date or the average value of such a market share during the measurement period.
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 Value Amount

Description	The monetary value a market for products or services at the reference date or the average value of such a market during the measurement period. This may be an actual measured value for past periods or it may be an estimated value for current or future periods.
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

Member Satisfaction Score

Description	The sum of selected members' satisfaction scores with the products analyzed for the reporting period.
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

New Groups Gained Due To Campaign

Description	The number of groups added due to the campaign who were not enrolled in any product offered by the health plan before the campaign.
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

New Members Gained Due To Campaign

Description	The number of members added due to the campaign who were not enrolled in any product offered by the health plan before the campaign.
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 Members

Description	The total count of members, during the period of analysis. This may be the total number of people with one or more active memberships at a given point in time during the period, or the average number of people with active memberships over the full period.
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 Product Market Withdrawals

Description	Counts the number of marketable products that are withdrawn and are no longer available for sale.
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

Party Role 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 Brand Recognition

Description	Measures the recognition of the brand name in the market. Expressed as a percentage of the population in the targeted market segments that know the brand name. This measure would typically be derived from the results of a survey targeting a representative sample population.
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

Percentage Earned Premium From New Products

Description	The monetary value of premiums earned from the sale of new products expressed as a percentage of the total earned premiums. 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

Percentage Earned Premiums From New Members

Description	The monetary value of premiums earned from the sale of products to new members expressed as a percentage of the total earned premiums. 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

Percentage Market Share

Description	The value of the share of a market held by an organization or product expressed as a percentage of the value of the total market. Formula: [(A/B)*100] (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

Written Exposure Period

Description	The duration between the effective date of the policy and its planned end 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

Written Gross Premium Amount

Description	The total premium amounts for the Written exposure periods, gross of any the reinsurer's share.
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

Relationship Details

Marketing KPI For Growth Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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

Marketing KPI For Growth Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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

Marketing KPI For Growth Analysis_Party Role Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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	Party Role Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Marketing KPI For Growth Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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

Marketing KPI For Growth Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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	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

Marketing KPI For Growth Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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	Insurance Policy Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Marketing KPI For Growth Analysis_Campaign Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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

Marketing KPI For Growth Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Marketing KPI For Growth 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	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

Marketing KPI For Growth Analysis PK

Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Party Role Dk
Key Attribute	Group Dk
Key Attribute	Organization Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Campaign Dimension Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model