Dimensional Warehouse Model Data Model

Entity

Entity Sales And Distribution Optimization And Expense Analysis

Description

Analysis of the sales and distribution processes to monitor expenditure and to optimize efficiency and effectiveness.

Attributes
Average Member Acquisition Cost Amount
Average Member Satisfaction With Sales Force
Calendar Dk
Call Center Cost Amount
Campaign Dimension Dk
Commission Cost Amount
Cost Of Acquiring New Members Amount
Geographic Area Dk
Group Dk
Health Plan Product Dk
Intermediary Dimension Dk
Lead Generation To First Contact Cycle Time
Member Satisfaction With Sales Force
Number Of New Members Acquired
Number Of New Members Per Salesperson
Number Of New Policies Acquired
Number Of New Policies Acquired Per Salesperson
Number Of Policies Sold To Targeted Existing Members
Number Of Policies Sold To Targeted New Members
Number Of Products Sold Per Sales Lead
Number Of Sales Leads Generated
Number Of Sales People
Number Of Targeted Existing Members
Number Of Targeted Members
Number Of Targeted New Members
Organization Dk
Plan Member Dk
Revenue Generated Per Sales Expense Amount
Sales Actual Amount
Sales Cost Amount
Sales Forecast Accuracy
Sales Forecast Amount
Sales Management Personnel Utilization
Sales Personnel Utilization
Sales Planning Personnel Utilization
Support Function Cost Amount

Relationship
Sales And Distribution Optimization And Expense Analysis_Calendar Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Geographic Area Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Group Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Plan Member Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Campaign Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Health Plan Product Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Organization Dimension_FK
Sales And Distribution Optimization And Expense Analysis_Intermediary Dimension_FK

Primary Key
Sales And Distribution Optimization And Expense 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

Average Member Satisfaction With Sales Force

Description	The average satisfaction score recorded by members in relation to the personnel involved in the sales process. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
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

Call Center Cost Amount

Description	The total cost related to call center activities including for member service, provider service, and other call centers.
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

Commission Cost Amount

Description	The total commission cost including commissions paid to employees, brokers, agents and other intermediaries.
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

Cost Of Acquiring New Members Amount

Description	The sum of all associated costs in new member acquisition.
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

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

Intermediary 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

Lead Generation To First Contact Cycle Time

Description	The elapsed time between generation of a lead and making first contact with the prospect or member.
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

Member Satisfaction With Sales Force

Description	An indication of the satisfaction of members with the personnel involved in the sales process based on a numeric scoring system. This relates to new members recruited by the sales process.
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 Acquired

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 New Members Per Salesperson

Description	The number of members who bought policies and who did not own active policies beforehand, measured per salesperson. 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

Number Of New Policies Acquired

Description	Total number of new policies that were sold to 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 Policies Acquired Per Salesperson

Description	Total number of new policies that were sold to members who did not own active policies beforehand, measured per salesperson. 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

Number Of Policies Sold To Targeted Existing Members

Description	The number of new products sold to members that were previously identified as campaign targets, who bought through the campaign, and, who owned one or more products prior to the campaign. 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 To Targeted New Members

Description	The number of new products sold to members that were previously identified as campaign targets, 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 Products Sold Per Sales Lead

Description	The number of products sold to new or existing members that were identified as sales lead. Formula: [(A+B)/C] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number Of Sales Leads Generated

Description	Total number of new and existing members that are targeted by the sales activities.
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 Sales People

Description	The number of sales people involved in the campaign or other selling activity.
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 Targeted Existing Members

Description	Of the members included in the campaign, the number who had policies (i.e. played an ownership role on one or more in-force or active policies). 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 Targeted Members

Description	Total number of existing and new members in the campaign target list. May be applied to group or individual members as appropriate to the campaign. 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

Number Of Targeted New Members

Description	The number of new members in the campaign target list (including cross-sales targets). 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

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

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

Revenue Generated Per Sales Expense Amount

Description	The value of revenue realized as a result of the sales process, versus, the total cost of the sales and distribution process. 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

Sales Actual Amount

Description	The value of sales actually achieved during a campaign or any other sales 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

Sales Cost Amount

Description	Total cost associated with the sales processes.
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

Sales Forecast Accuracy

Description	Measures the accuracy of the sales forecast as a ratio between actual sales versus forecasted sales. Formula: [(A/B)] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Sales Forecast Amount

Description	The value of sales forecast or predicted for a campaign or any other sales 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

Sales Management Personnel Utilization

Description	Actual utilization of human resources (personnel) expressed in full time equivalent (FTE) days for the sales management activities.
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Sales Personnel Utilization

Description	Actual utilization of human resources (personnel) by the sales and distribution process, expressed in full time equivalent (FTE) days.
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Sales Planning Personnel Utilization

Description	Actual utilization of human resources (personnel) expressed in full time equivalent (FTE) days for the sales planning activities.
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Support Function Cost Amount

Description	The total cost related to policy administration, finance, IT, HR and other support functions.
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

Sales And Distribution Optimization And Expense Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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 And Distribution Optimization And Expense Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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 And Distribution Optimization And Expense Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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 And Distribution Optimization And Expense Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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

Sales And Distribution Optimization And Expense Analysis_Campaign Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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 And Distribution Optimization And Expense Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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 And Distribution Optimization And Expense Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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

Sales And Distribution Optimization And Expense Analysis_Intermediary Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution Optimization And Expense 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	Intermediary 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 And Distribution Optimization And Expense Analysis PK

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

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model