Dimensional Warehouse Model Data Model

Entity

Entity Underwriting Resource Utilization Analysis

Description

The analysis of health plan resource utilization related to underwriting processes and activities.

Attributes
Average Underwriting Cost Per Member
Average Underwriting Cost Per Policy
Calendar Dk
Capital Expenditure For Organization
Cost Of Underwriting Rules Changes
Geographic Area Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Number Of Members
Number Of New Policies Issued
Number Of Policy Renewals
Number Of Underwriting Rules Changes
Organization Dk
Regulatory Compliance Costs
Underwriting Capex As Percentage Of Total
Underwriting Capital Expenditure Amount
Underwriting Cost
Underwriting Management Cost
Underwriting Management Personnel Cost
Underwriting Personnel Cost
Underwriting Risk Assessment Cost
Underwriting Risk Assessment Personnel Cost
Underwriting Rules Setting Cost
Underwriting Rules Setting Personnel Cost

Relationship
Underwriting Resource Utilization Analysis_Calendar Dimension_FK
Underwriting Resource Utilization Analysis_Geographic Area Dimension_FK
Underwriting Resource Utilization Analysis_Health Plan Product Dimension_FK
Underwriting Resource Utilization Analysis_Insurance Policy Dimension_FK
Underwriting Resource Utilization Analysis_Organization Dimension_FK

Primary Key
Underwriting Resource Utilization Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Underwriting Cost Per Member

Description	The average underwriting cost per 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 Underwriting Cost Per Policy

Description	The average underwriting cost per policy. Formula: [A/(B+C)] (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

Capital Expenditure For Organization

Description	Overall capital expenditure (CAPEX) figure for the organization.
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 Underwriting Rules Changes

Description	Total cost of maintaining the underwriting rules.
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

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 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 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 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 Underwriting Rules Changes

Description	The total number of underwriting rules, requiring a change 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

Regulatory Compliance Costs

Description	The cost of complying with underwriting related regulations, including the costs associated with validating underwriting rules and reporting the underwritten risk.
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

Underwriting Capex As Percentage Of Total

Description	The capital expenditure (CAPEX) for underwriting operations expressed as a percentage of the total capital expenditures of the company. 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

Underwriting Capital Expenditure Amount

Description	The amount of capital expenditure (CAPEX) that is attributable to underwriting.
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

Underwriting Cost

Description	Total cost of the underwriting processes. Formula: [A+B+C] (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

Underwriting Management Cost

Description	Total cost for managing and controlling the underwriting 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

Underwriting Management Personnel Cost

Description	The total cost of personnel required to manage and control the underwriting 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

Underwriting Personnel Cost

Description	The total cost of personnel required to perform the underwriting processes. Formula: [A+B+C] (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

Underwriting Risk Assessment Cost

Description	The total cost of underwriting activities. That is reviewing an application or renewal for completeness, assessing and analyzing risks, and applying the underwriting rules and rates to generate quotes.
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

Underwriting Risk Assessment Personnel Cost

Description	The total cost of personnel required to perform the underwriting risk assessment 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

Underwriting Rules Setting Cost

Description	Total cost of setting and maintaining underwriting rules.
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

Underwriting Rules Setting Personnel Cost

Description	The total cost of personnel required to perform the activities of underwriting rules setting and maintaining the rules.
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 Resource Utilization Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Resource Utilization 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 Resource Utilization Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Resource Utilization 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 Resource Utilization Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Resource Utilization 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 Resource Utilization Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Underwriting Resource Utilization 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 Resource Utilization Analysis_Organization Dimension_FK

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

Primary Key Details

Underwriting Resource Utilization Analysis PK

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

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model