Dimensional Warehouse Model Data Model

Entity

Entity Underwriting Efficiency Analysis

Description

The analysis of the key performance indicators for underwriting activities and their impact on health plan organization revenue growth.

Attributes
Application Dimension Dk
Average Gross Written Premium Per New Member Amount
Average Gross Written Premium Per Retained Member Amount
Average Number Of Quotes Per Application
Average Number Of Quotes Per Renewal
Average Underwriting Turn-around Time For New Business
Average Underwriting Turn-around Time For Renewal Business
Calendar Dk
Geographic Area Dk
Group Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Number Of Applications Processed
Number Of Members Acquired
Number Of Members Retained
Number Of New Policies Issued
Number Of Policies Audited
Number Of Policy Renewals
Number Of Quotes
Organization Dk
Person Dk
Underwriting Turn-around Time For New Business
Underwriting Turn-around Time For Renewal Business
Written Gross Premium For New Business Amount
Written Premium Amount

Relationship
Underwriting Efficiency Analysis_Calendar Dimension_FK
Underwriting Efficiency Analysis_Geographic Area Dimension_FK
Underwriting Efficiency Analysis_Group Dimension_FK
Underwriting Efficiency Analysis_Application Dimension_FK
Underwriting Efficiency Analysis_Health Plan Product Dimension_FK
Underwriting Efficiency Analysis_Insurance Policy Dimension_FK
Underwriting Efficiency Analysis_Organization Dimension_FK
Underwriting Efficiency Analysis_Person Dimension_FK

Primary Key
Underwriting Efficiency Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Application 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

Average Gross Written Premium Per New Member Amount

Description	The average gross premium amount that has been written for new 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

Average Gross Written Premium Per Retained Member Amount

Description	The average gross premium amount that has been written for retained 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

Average Number Of Quotes Per Application

Description	The average number of quotes issued per application for applications processed in a defined period of time. 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

Average Number Of Quotes Per Renewal

Description	The average number of quotes issued per policy renewal processed in a defined period of time. 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

Average Underwriting Turn-around Time For New Business

Description	The average cycle time for passing an application through the underwriting 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

Average Underwriting Turn-around Time For Renewal Business

Description	The average cycle time for passing a policy renewal through the underwriting 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

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

Number Of Applications Processed

Description	The total number of applications for coverage that were processed during a defined period of time.
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 Acquired

Description	The number of new members enrolled with a health plan during a defined period of time for defined coverages or lines of business.
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 Retained

Description	The number of existing members retained with the health plan during a defined period of time for defined coverages or lines of business.
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 Issued

Description	The total number of new health plan policies written for a specified 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 Policies Audited

Description	Total number of groups or individuals audited for adherence to underwriting rules during a defined period of time.
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 Policy Renewals

Description	The total number of health plan policies that were renewed.
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 Quotes

Description	The total number of quotations processed by the underwriting department for new and renewal business during the reference 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

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

Person 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

Underwriting Turn-around Time For New Business

Description	Total cycle time for passing all applications received in a defined period of time through the underwriting 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

Underwriting Turn-around Time For Renewal Business

Description	Total cycle time for passing a policy renewal received in a defined period of time through the underwriting 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

Written Gross Premium For New Business Amount

Description	The total gross premium amounts for applications underwritten during the reference 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

Written Premium Amount

Description	Total premium generated from all policies. Premium for active policy components are adjusted according to the frequency of payment and term.
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

Underwriting Efficiency Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Application Dimension_FK

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

Underwriting Efficiency Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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

Underwriting Efficiency Analysis_Person Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Efficiency 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	Person 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

Underwriting Efficiency Analysis PK

Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk
Key Attribute	Application Dimension Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Organization Dk
Key Attribute	Person Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model