Dimensional Warehouse Model Data Model

Entity

Entity Campaign Profitability Analysis

Description

The campaign profitability analysis records measures related to the revenues generated by a campaign as well as the costs associated with it.

Attributes
Average Member Acquisition Cost Amount
Calendar Dk
Campaign Commission Paid Amount
Campaign Commission Received Amount
Campaign Cost Amount
Campaign Dimension Dk
Campaign Gain Amount
Campaign New Premium Income Amount
Geographic Area Dk
Group Dk
New Premiums Purchased By Existing Members Amount
New Premiums Purchased By New Members Amount
Number Of Member Responses
Number Of New Members Gained
Number Of Policies Sold
Plan Member Dk
Premium Income Amount
Response Per Campaign Cost
Revenue Cost Ratio

Relationship
Campaign Profitability Analysis_Calendar Dimension_FK
Campaign Profitability Analysis_Geographic Area Dimension_FK
Campaign Profitability Analysis_Group Dimension_FK
Campaign Profitability Analysis_Plan Member Dimension_FK
Campaign Profitability Analysis_Campaign Dimension_FK

Primary Key
Campaign Profitability Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Member Acquisition Cost Amount

Description	Average cost for acquiring a new member 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 Commission Paid Amount

Description	Total commission paid to direct sales staff, intermediaries and other agents related to campaigns.
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

Campaign Commission Received Amount

Description	Total commissions received by the health plan when selling third-party products in campaigns.
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

Campaign Cost Amount

Description	The total costs of a 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

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

Campaign Gain Amount

Description	The total financial gain realized from a campaign. This amount is the revenue attributed to the campaign minus the campaign cost. 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

Campaign New Premium Income Amount

Description	The total amount of new premium income generated by the campaign. 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

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

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

New Premiums Purchased By Existing Members Amount

Description	The total earned premium purchased by members who owned one or more products prior to the campaign, who either increased their premiums or who purchased further products. May be applied to group or individual members as appropriate to 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

New Premiums Purchased By New Members Amount

Description	The total new earned premium due from members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to 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

Number Of Member Responses

Description	Count of campaign responses from members.
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 New Members Gained

Description	The number of members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to 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 Policies Sold

Description	The total number of policies sold.
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

Plan Member 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

Premium Income Amount

Description	The amount of premium income earned during the 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

Response Per Campaign Cost

Description	The number of member responses per money spent on the campaign. Depending on the nature of the campaign this might be modified to represent, for example, the number of responses per $1000 spent on the campaign. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal 9 [DECIMAL(19,9)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Revenue Cost Ratio

Description	The amount of revenue realized divided by the amount spent on the marketing process. 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

Relationship Details

Campaign Profitability Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Campaign Profitability 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

Campaign Profitability Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Campaign Profitability 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

Campaign Profitability Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Campaign Profitability 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

Campaign Profitability Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Campaign Profitability 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	Plan Member Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Campaign Profitability Analysis_Campaign Dimension_FK

Is Identifying Relationship	true
Child Table	Campaign Profitability 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

Primary Key Details

Campaign Profitability Analysis PK

Key Attribute	Plan Member Dk
Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk
Key Attribute	Campaign Dimension Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model