Dimensional Warehouse Model Data Model

Entity

Entity Member Service KPI For Growth Analysis

Description

Analysis of the key performance indicators of the organization's member service activities, to grow revenue.

Attributes
Average Member Satisfaction Score
Calendar Dk
Earned Premium Amount
Earned Premium Per Member Amount
Geographic Area Dk
Group Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Member Retention Rate
Member Satisfaction Score
Member Service Cost Amount
New Members Gained Due To Campaign
New Policies Gained Due To Campaign
Number Of Members
Number Of Product Market Withdrawals
Organization Dk
Percentage Earned Premium Growth
Plan Member Dk
Premiums Paid On Behalf Of The Members Amount
Provider Dk

Relationship
Member Service KPI For Growth Analysis_Calendar Dimension_FK
Member Service KPI For Growth Analysis_Geographic Area Dimension_FK
Member Service KPI For Growth Analysis_Plan Member Dimension_FK
Member Service KPI For Growth Analysis_Provider Dimension_FK
Member Service KPI For Growth Analysis_Group Dimension_FK
Member Service KPI For Growth Analysis_Organization Dimension_FK
Member Service KPI For Growth Analysis_Health Plan Product Dimension_FK
Member Service KPI For Growth Analysis_Insurance Policy Dimension_FK

Primary Key
Member Service KPI For Growth Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Member Satisfaction Score

Description	Average Member satisfaction score on Member satisfaction surveys. 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

Earned Premium Amount

Description	The amount of policy premium which relates to the period of the policy that has already elapsed at the point in time. For example: An annual policy covering a calendar year at a cost of $800 would have an earned premium value of $600 at the end of September. The remaining $200 is regarded ad not yet earned even if the full premium was paid in advance.
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 Premium Per Member Amount

Description	Total earned premium divided by number of members. 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

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

Member Retention Rate

Description	The percentage of members from previous period that remained members in the current period. Formula: [((1 - (current period A / previous period A) * 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

Member Satisfaction Score

Description	A codified numeric measure of an individual member's satisfaction with the products analyzed.
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

Member Service Cost Amount

Description	Total cost of performing the member service processes, including management of the interfaces with the member, managing member's information, and, member service delivery.
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 Members Gained Due To Campaign

Description	The number of 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/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

New Policies Gained Due To Campaign

Description	The sum of new policies issued due 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 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

Percentage Earned Premium Growth

Description	The increase (or decrease) of earned premium for the current period in comparison with the previous period expressed as a percentage of the earned premium in the previous period. Formula: [(((A) for current period/(A) from previous 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

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 The Members Amount

Description	The sum of the premiums paid in to provide coverage for the members in the segment and period analyzed.
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

Provider 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

Relationship Details

Member Service KPI For Growth Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Provider Dimension_FK

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

Member Service KPI For Growth Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Member Service KPI For Growth Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service 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

Primary Key Details

Member Service KPI For Growth Analysis PK

Key Attribute	Plan Member Dk
Key Attribute	Provider Dk
Key Attribute	Organization Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model