Dimensional Warehouse Model Data Model

Entity

Entity Sales And Distribution KPI For Growth Analysis

Description

Analysis of the key performance indicators for the sales and distribution processes to increase revenue and market share.

Attributes
Average Claims Paid On Behalf Of Members Amount
Average Premiums Paid On Behalf Of Members Amount
Calendar Dk
Campaign Dimension Dk
Claims Paid On Behalf Of Members Amount
Geographic Area Dk
Group Dk
Health Plan Product Dk
Intermediary Dimension Dk
Market Share Amount
Market Value Amount
Number Of Members
Number Of New Members Acquired
Number Of New Policies Acquired
Number Of Policies Sold
Number Of Policies Sold Per 100 Members
Number Of Policies Sold To Targeted Existing Members
Number Of Policies Sold To Targeted New Members
Number Of Products Sold Per 100 Members
Number Of Sales Leads Engaged
Number Of Sales Leads Targeted
Organization Dk
Percentage Market Share
Percentage Of Revenue From New Members
Percentage Of Revenue From New Products
Plan Member Dk
Premiums Paid On Behalf Of Members Amount
Revenue From New Members Amount
Revenue From New Products Amount
Total Sales Revenue Generated Amount

Relationship
Sales And Distribution KPI For Growth Analysis_Calendar Dimension_FK
Sales And Distribution KPI For Growth Analysis_Geographic Area Dimension_FK
Sales And Distribution KPI For Growth Analysis_Group Dimension_FK
Sales And Distribution KPI For Growth Analysis_Plan Member Dimension_FK
Sales And Distribution KPI For Growth Analysis_Campaign Dimension_FK
Sales And Distribution KPI For Growth Analysis_Organization Dimension_FK
Sales And Distribution KPI For Growth Analysis_Health Plan Product Dimension_FK
Sales And Distribution KPI For Growth Analysis_Intermediary Dimension_FK
Sales And Distribution KPI For Growth Analysis_Organization Dimension_FK1

Primary Key
Sales And Distribution KPI For Growth Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Claims Paid On Behalf Of Members Amount

Description	Average of the overall value of claims paid to all members in the defined dimensions. 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 Premiums Paid On Behalf Of Members Amount

Description	Average of the overall value of premiums collected from all members in the defined dimensions. 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

Claims Paid On Behalf Of Members Amount

Description	The value of claims paid on behalf of the member(s) for all 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

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

Market Share Amount

Description	The monetary value of the market share held by an organization or product at the reference date or the average 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

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 New Members Acquired

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

Number Of Policies Sold Per 100 Members

Description	Total number of policies sold per member. Formula: [(A/B)*100] (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 identified as 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 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 Policies Sold To Targeted New Members

Description	The number of new products sold to persons that were identified as 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 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 Products Sold Per 100 Members

Description	Total number of products sold per member. Formula: [((A+B)/C)*100] (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 Sales Leads Engaged

Description	The total number of targeted sales leads that were actually engaged in sales execution processes.
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 Leads Targeted

Description	The total number of targeted sales leads that were identified in sales planning processes.
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 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/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Percentage Of Revenue From New Members

Description	Percentage of the revenue that is earned by the acquisition of new members. 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 Of Revenue From New Products

Description	Percentage of the revenue that is earned via sale of new products. 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

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

Premiums Paid On Behalf Of Members Amount

Description	The value of premiums paid in to provide coverage for members in all products in which the members are or have been enrolled.
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

Revenue From New Members Amount

Description	The amount of revenue generated from sales to new members.
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

Revenue From New Products Amount

Description	The amount of revenue generated through sales of 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

Total Sales Revenue Generated Amount

Description	Total amount of revenue generated through sales activities.
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 KPI For Growth Analysis_Calendar Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Geographic Area Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Group Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution 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	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 KPI For Growth Analysis_Campaign Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Organization Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Health Plan Product Dimension_FK

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

Sales And Distribution KPI For Growth Analysis_Intermediary Dimension_FK

Is Identifying Relationship	true
Child Table	Sales And Distribution 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	Intermediary 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 KPI For Growth Analysis_Organization Dimension_FK1

Is Identifying Relationship	true
Child Table	Sales And Distribution KPI For Growth 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 And Distribution KPI For Growth 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	Organization Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Intermediary Dimension Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model