Dimensional Warehouse Model Data Model

Entity

Entity HHS Reinsurance Analysis

Description

The transitional reinsurance program is a premium stabilization program designed to provide issuers with greater payment stability. The program reduces the uncertainty in rate setting for Qualified Health Plans (QHP) by limiting the extent of issuers financial losses and gains. It will provide increased payments to health insurance issuers that attract higher-risk populations, such as those with chronic conditions, and reduce the incentives for issuers to avoid higher-risk enrollees.

Attributes
Calendar Dk
Claim Amount Over The Reinsurance Cap
Claim Amount Subject To Reinsurance Payment
Claim Amount Under The Attachment Point
Claims Sum Amount
Condition Dk
Coverage Agreement Dimension Dk
Diagnosis Code Dk
Geographic Area Dk
Health Plan Group Dk
Health Plan Product Dk
HHS Coinsurance Rate
HHS Per Member Contribution Amount
Number Of Claiming Members
Number Of Claiming Members Exceeding The Reinsurance Cap
Number Of Claiming Members Subject To Reinsurance Payment
Number Of Claiming Members Under The Attachment Point
Number Of Claims
Number Of Members
Percentage Claim Amount Over The Reinsurance Cap
Percentage Claim Amount Subject To Reinsurance Payment
Percentage Claim Sum Amount Under The Attachment Point
Percentage Of Claiming Members
Percentage Of Claiming Members Exceeding The Reinsurance Cap
Percentage Of Claiming Members Subject To Reinsurance Payment
Percentage Of Claiming Members Under The Attachment Point
Person Dk
Plan Member Dk
Practitioner Dk
Provider Dk
Reinsurance Contribution Amount
Reinsurance Loss Amount
Reinsurance Payment Amount
Total Claim Payment Amount

Relationship
HHS Reinsurance Analysis_Calendar Dimension_FK
HHS Reinsurance Analysis_Geographic Area Dimension_FK
HHS Reinsurance Analysis_Person Dimension_FK
HHS Reinsurance Analysis_Plan Member Dimension_FK
HHS Reinsurance Analysis_Condition Dimension_FK
HHS Reinsurance Analysis_Diagnosis Code Dimension_FK
HHS Reinsurance Analysis_Provider Dimension_FK
HHS Reinsurance Analysis_Practitioner Dimension_FK
HHS Reinsurance Analysis_Health Plan Product Dimension_FK
HHS Reinsurance Analysis_Health Plan Group Dimension_FK
HHS Reinsurance Analysis_Coverage Agreement Dimension_FK

Primary Key
HHS Reinsurance Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

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

Claim Amount Over The Reinsurance Cap

Description	This monetary value of the portion of claims not subject to the routine reimbursement rules or the reinsurance payment calculations. A member may have multiple claims which must be summed up first to determine if an amount is to be included in this figure. It could be considered a loss for the healthcare provider. As at 2013, the reinsurance cap is set at $250,000. this means that if an individual member's claims exceed $250,000, the amount over this cap does not qualify for reinsurance. For example: The reinsurance cap for the current year is $250,000 and a healthplan member has three claims worth $280,000 in total. The $30,000 difference will not be included in the reinsurance payment calculations.
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

Claim Amount Subject To Reinsurance Payment

Description	Sum amount of claims where the member's claim amount is over the attachment point and under the reinsurance cap. This is the amount that is subject to the reinsurance payment calculations. A member may have multiple claims which must be summed up to determine if a portion of the total amount is to be included in this amount. For example: The attachment point for the current year is $60,000. The reinsurance cap is $250,000. A member has 3 claims with a total value of $70,000. This total will be included in this figure because it is above the attachment point and below the reinsurance cap. 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

Claim Amount Under The Attachment Point

Description	The sum amount for claims where the member's total claim amount is under the attachment point or the portion of claims for a member that fall under the attachment point. A member may have multiple claims - these must be summed up to determine what amount needs to be included in this figure. Amounts that fall under the attachment point do not qualify for reinsurance. As of 2013 the attachment amount is set at $60,000 per member meaning that if a member's claims total less than $60,000, they are included in this value. Where they exceed $60,000 the first $60,000 will be included in this figure. For example: The attachment point for the current year is $60,000 and a healthplan member has three claims worth $30,000 in total. This total is not included in the Claim Amount Under The Attachment Point figure because it is below the attachment point.
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

Claims Sum Amount

Description	The sum amount of all claims for all members of the healthplan organization. This figure can include multiple claim amounts from the same member.
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

Condition 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

Coverage Agreement 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

Diagnosis Code 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

Health Plan 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

HHS Coinsurance Rate

Description	The is the reimbursement percentage rate used to calculate the reinsurance payment. As of 2013 the applicable coinsurance rate is set at 80%.
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

HHS Per Member Contribution Amount

Description	The annual fee that must be paid by the health plan organization to the U.S. Department of Health & Human Services (HSS) per healthplan member.
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 Claiming Members

Description	The number of healthcare plan members with claims. Each member should be counted once regardless of whether they have more than one claim.
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 Claiming Members Exceeding The Reinsurance Cap

Description	The number of members whose total claim amount is exceeding the reinsurance cap. If a member has more than one claim for the specified period, it is the total amount payable for all claims. For example: The reinsurance cap for the current year is $250,000. A healthplan member has three claims for the same year which total to $260,000. Therefore, this member must be included in this figure.
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 Claiming Members Subject To Reinsurance Payment

Description	The number of claiming members whose total claim payment amount is within the reinsurance payment parameters; i.e. Exceeds the attachment point and is below the reinsurance cap. If a member has more than one claim for the specified period, it is the total amount payable for all claims.
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 Claiming Members Under The Attachment Point

Description	The number of members whose total claim amount is under the attachment point. If a member has more than one claim for the specified period, it is the total amount for all their claims. No portion of these members' claims qualifies for reinsurance. For example: A healthplan member has 3 claims for the current year with a total value of $55000. The attachment point is $60000 for the same year. Therefore, this member must be included in this count.
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 Claims

Description	Total number of healthcare claims.
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 or total number of people who are enrolled in a healthcare plan during the period of analysis.
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

Percentage Claim Amount Over The Reinsurance Cap

Description	The claim amount over the reinsurance cap expressed as a percentage of the claims sum amount. 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 Claim Amount Subject To Reinsurance Payment

Description	The claim amount subject to reinsurance payment expressed as a percentage of the claims sum amount. 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 Claim Sum Amount Under The Attachment Point

Description	The claim amount under the attachment point expressed as a percentage of the claims sum amount. 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 Claiming Members

Description	The number of healthplan members with claims expressed as a percentage of the number of healthcare plan 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 Claiming Members Exceeding The Reinsurance Cap

Description	The number of claiming members over the reinsurance cap expressed as a percentage of the number of claiming 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 Claiming Members Subject To Reinsurance Payment

Description	The number of claiming members subject to reinsurance payment expressed as a percentage of the number of claiming 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 Claiming Members Under The Attachment Point

Description	The number of claiming members under the attachment point expressed as a percentage of the number of claiming 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

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

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

Practitioner 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

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

Reinsurance Contribution Amount

Description	The sum of the amount payable for reinsurance contribution. This is the calculated total amount healthcare plan organizations must pay for all of their 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

Reinsurance Loss Amount

Description	The remaining claim amount after the reinsurance payment calculation. This could be considered a loss by the healthcare plan organization. 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

Reinsurance Payment Amount

Description	The amount payable to the healthcare plan organization. This is based on the amount subject to reinsurance extended by a coinsurance rate. As of 2013 the applicable coinsurance rate is set at 80%. For example: The coinsurance rate for the year is 80% and the calculated claim amount subject to the reinsurance calculations is $100,000 for the same year. Therefore, a health plan organization would expect to receive $80,000 as their reinsurance payment amount. 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

Total Claim Payment Amount

Description	It is the sum of all line item amounts on a claim which are payable to the health plan organization, provider, member or other destination payee.
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

HHS Reinsurance Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Person Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Condition Dimension_FK

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

HHS Reinsurance Analysis_Diagnosis Code Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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	Diagnosis Code Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

HHS Reinsurance Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Practitioner Dimension_FK

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

HHS Reinsurance Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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

HHS Reinsurance Analysis_Health Plan Group Dimension_FK

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

HHS Reinsurance Analysis_Coverage Agreement Dimension_FK

Is Identifying Relationship	true
Child Table	HHS Reinsurance 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	Coverage Agreement 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

HHS Reinsurance Analysis PK

Key Attribute	Person Dk
Key Attribute	Plan Member Dk
Key Attribute	Condition Dk
Key Attribute	Diagnosis Code Dk
Key Attribute	Provider Dk
Key Attribute	Practitioner Dk
Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Health Plan Group Dk
Key Attribute	Coverage Agreement Dimension Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model