Dimensional Warehouse Model Data Model

Entity

Entity Claim Adjudication Analysis

Description

The analysis of claims adjudication in relation to health plan expenditure and trends of health expenditure by type of treatment and health care provider. Includes analysis of post adjudication adjustments to identify improvements in adjudication and general claim handling protocols, and thereby improving operational efficiency and member/provider satisfaction.

Attributes
Adjudication Accuracy Rate
Allowed Claim Amount
Average Claim Discount Amount
Average Member Liability Amount
Average Number Of Adjustments Per Claim
Average Overpayment Amount
Average Paid Claim Amount
Calendar Dk
Claim Dk
Claim Line Dk
Claim Payment Amount
Co-insurance Amount
Co-payment Amount
Coverage Eligibility Dk
Covered Claim Amount
Deductible Amount
Disallowed Claim Amount
Discount Claim Amount
Encounter Dk
Geographic Area Dk
Group Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Liability Arising From Annual Maximum Benefit Amount
Liability Arising From Lifetime Maximum Amount
Liability Arising From Out-of-pocket Maximum Amount
Member Liability Amount
Member Penalty Amount
Number Of Adjustments
Number Of Claims Adjudicated
Number Of Claims Adjusted
Number Of Members
Out-of-pocket Amount
Overpayment Amount
Paid Claim Per Member Amount
Percentage Health Plan Liability
Percentage Member Liability
Plan Member Dk
Provider Dk
Submitted Claim Amount
Underpayment Amount

Relationship
Claim Adjudication Analysis_Calendar Dimension_FK
Claim Adjudication Analysis_Geographic Area Dimension_FK
Claim Adjudication Analysis_Group Dimension_FK
Claim Adjudication Analysis_Claim Dimension_FK
Claim Adjudication Analysis_Claim Line Dimension_FK
Claim Adjudication Analysis_Plan Member Dimension_FK
Claim Adjudication Analysis_Provider Dimension_FK
Claim Adjudication Analysis_Health Plan Product Dimension_FK
Claim Adjudication Analysis_Insurance Policy Dimension_FK
Claim Adjudication Analysis_Coverage Eligibility Dimension_FK
Claim Adjudication Analysis_Encounter Dimension_FK

Primary Key
Claim Adjudication Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Adjudication Accuracy Rate

Description	The accuracy of initial claim adjudication based on the number of claim adjustments occurring. Formula: [(1-(A/B))*100] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal 9 [DECIMAL(19,9)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Allowed Claim Amount

Description	The total allowed amounts of claims during the reference period. 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 Claim Discount Amount

Description	The average discount amount applied to claims. 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 Member Liability Amount

Description	The average member liability. 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 Adjustments Per Claim

Description	The average number of adjustments occurring on claims. This is expressed per thousand claims to make the business measure meaningful. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal 9 [DECIMAL(19,9)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Average Overpayment Amount

Description	The average amount overpaid on a claim, as recognized through claim adjustments. 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 Paid Claim Amount

Description	The average paid claim 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

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 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 Line 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 Payment Amount

Description	The total amount of claim payments made to healthcare providers.
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

Co-insurance Amount

Description	The total amount paid by members in the form of co-insurance.
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

Co-payment Amount

Description	The total amount of co-payments paid by 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

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

Covered Claim Amount

Description	The total amount of claims that are covered by the health plan. 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

Deductible Amount

Description	The total deductible amount paid by 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

Disallowed Claim Amount

Description	The total amount of submitted claims disallowed by the Health Plan Organization because the claim is for non covered services or exceeds the maximum charge allowed by the health benefits plan.
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

Discount Claim Amount

Description	The total amounts applied as discounts on claim line item amounts. The discount may be a certain percentage or set amount as per negotiated contract with a provider.
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

Encounter 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

Liability Arising From Annual Maximum Benefit Amount

Description	The total member liability arising from members exceeding their annual maximum benefit amount.
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

Liability Arising From Lifetime Maximum Amount

Description	The total member liability arising from members exceeding their lifetime maximum benefit amount.
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

Liability Arising From Out-of-pocket Maximum Amount

Description	The total amount that the health plan is liable for as a result of members exceeding their annual out-of-pocket maximum amount.
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

Member Liability Amount

Description	The total claim amount submitted that the member is liable for. 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

Member Penalty Amount

Description	The total penalty amount incurred by members for failure to comply with health benefits plan requirements regarding utilization management, prior authorization, referral, out-of-network or other policy provisions applicable to claimed medical services.
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 Adjustments

Description	The total number of adjustments occurring 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

Number Of Claims Adjudicated

Description	The total number of claims processed.
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 Adjusted

Description	The total number of claims adjusted.
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

Out-of-pocket Amount

Description	The total amount that members paid in the form of deductibles and co-payments. 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

Overpayment Amount

Description	The total amount recognized as overpaid through claim adjustments.
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

Paid Claim Per Member Amount

Description	The average amount that the Health Plan Organization has paid on claims for each member. Formula: [(A/B)*100] (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

Percentage Health Plan Liability

Description	The proportion of the claim amount paid versus the total claim amount submitted. 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 Member Liability

Description	The percentage of the total claim amount submitted that the member is liable for. 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

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

Submitted Claim Amount

Description	The benefit amount charged or billed by, or on behalf of, the 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

Underpayment Amount

Description	The total amount recognized as underpaid through claim adjustments.
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

Claim Adjudication Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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

Claim Adjudication Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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

Claim Adjudication Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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

Claim Adjudication Analysis_Claim Dimension_FK

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

Claim Adjudication Analysis_Claim Line Dimension_FK

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

Claim Adjudication Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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

Claim Adjudication Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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

Claim Adjudication Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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	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

Claim Adjudication Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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	Insurance Policy Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Claim Adjudication Analysis_Coverage Eligibility Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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	Coverage Eligibility Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Claim Adjudication Analysis_Encounter Dimension_FK

Is Identifying Relationship	true
Child Table	Claim Adjudication 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	Encounter 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

Claim Adjudication Analysis PK

Key Attribute	Claim Dk
Key Attribute	Claim Line Dk
Key Attribute	Plan Member Dk
Key Attribute	Provider Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Coverage Eligibility Dk
Key Attribute	Encounter Dk
Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model