Dimensional Warehouse Model Data Model

Entity

Entity Claims Financial Analysis

Description

Providers need to monitor and understand the costs and revenue associated with services. This can be done by aggregating the amounts on claim lines.
Example questions that could be answered include:
What is the average purchase price for certain Durable Medical Equipment?
How many units of a DME were put into service?
What are the Billed vs. Approved Charges by Department?
What are the total Non-Covered Charges by DRG?

Attributes
Approved Amt
Average Service Duration Cnt
Calendar Dk
Condition Dk
Contract Amt
Contract Pct
Currency Cd
Diagnosis Code Dk
Diagnosis Related Group Dk
Encounter Dk
Healthcare Insurer Dk
Line Item Charge Amt
Line Item Unit Charge Amt
Noncovered Charge Amt
Patient Dk
Point Of Care Location Dk
Postage Claimed Amt
Practitioner Dk
Provider Dk
Purchased Service Charge Amt
Purchase Price Amt
Repriced Allowed Amt
Repriced Approved Ambulatory Patient Group Amt
Repriced Approved Service Unit Ct
Repriced Flat Rate Amt
Repriced Saving Amt
Sales Tax Amt
Service Dk
Service Unit Cnt
Term Discount Pct
Unit Price Amt

Relationship
Claims Financial Analysis_Calendar_FK
Claims Financial Analysis_Point Of Care Location_FK
Claims Financial Analysis_Encounter_FK
Claims Financial Analysis_Condition Dimension_FK
Claims Financial Analysis_Provider Dimension_FK
Claims Financial Analysis_Patient Dimension_FK
Claims Financial Analysis_Service Dimension_FK
Claims Financial Analysis_Practitioner Dimension_FK
Claims Financial Analysis_Diagnosis Related Group_FK
Claims Financial Analysis_Healthcare Insurer Dimension_FK
Claims Financial Analysis_Diagnosis Dimension_FK

Primary Key
Claims Financial Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Approved Amt

Description	Amount approved by the Payer.
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 Service Duration Cnt

Description	The average length of time the service takes or is in effect. e.g. Average length of time durable medical equipment (DME) is needed.
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

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

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

Contract Amt

Description	Fixed monetary amount pertaining to the contract required if the service amounts are different than the Claim level 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

Contract Pct

Description	Percent of charges payable under the contract.
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

Currency Cd

Description	The currency in which financial amounts are denominated.
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
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

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

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

Healthcare Insurer 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

Line Item Charge Amt

Description	Charges related to this service.
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

Line Item Unit Charge Amt

Description	Per unit charge for the procedure on the bill line.
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

Noncovered Charge Amt

Description	Amount of the charge on this service line not covered by the payer.
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

Patient 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

Point Of Care Location 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

Postage Claimed Amt

Description	Cost of postage used to provide service or to process associated paper work.
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

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

Purchased Service Charge Amt

Description	The charge for the purchased service.
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

Purchase Price Amt

Description	Purchase price of the Durable Medical Equipment.
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

Repriced Allowed Amt

Description	The maximum amount determined by the repricer as being allowable under the provisions of the contract prior to the determination of the actual payment.
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

Repriced Approved Ambulatory Patient Group Amt

Description	Amount of payment by the repricer for the referenced Ambulatory Patient Group
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

Repriced Approved Service Unit Ct

Description	Number of service units approved by pricing or repricing entity.
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

Repriced Flat Rate Amt

Description	Amount used to determine the flat rate or per diem price by the repricing 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

Repriced Saving Amt

Description	The amount of savings related to Third Party Organization repricing of claims.
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

Sales Tax Amt

Description	Amount of sales tax attributable to the referenced Service.
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

Service 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

Service Unit Cnt

Description	The quantity of units, times, days, visits, services, or treatments for the service described by the HCPCS codes, revenue code or procedure code.
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

Term Discount Pct

Description	Discount percentage available to the payer for payment within a specific time period.
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

Unit Price Amt

Description	Price for One Unit of good or service. e.g. 837p CTP03 CMS1500
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

Claims Financial Analysis_Calendar_FK

Is Identifying Relationship	false
Child Table	Claims Financial 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

Claims Financial Analysis_Point Of Care Location_FK

Is Identifying Relationship	false
Child Table	Claims Financial 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	Point Of Care Location Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Claims Financial Analysis_Encounter_FK

Is Identifying Relationship	false
Child Table	Claims Financial 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	Encounter Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Claims Financial Analysis_Condition Dimension_FK

Is Identifying Relationship	true
Child Table	Claims Financial 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

Claims Financial Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Claims Financial 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

Claims Financial Analysis_Patient Dimension_FK

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

Claims Financial Analysis_Service Dimension_FK

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

Claims Financial Analysis_Practitioner Dimension_FK

Is Identifying Relationship	true
Child Table	Claims Financial 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

Claims Financial Analysis_Diagnosis Related Group_FK

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

Claims Financial Analysis_Healthcare Insurer Dimension_FK

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

Claims Financial Analysis_Diagnosis Dimension_FK

Is Identifying Relationship	true
Child Table	Claims Financial 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

Primary Key Details

Claims Financial Analysis PK

Key Attribute	Diagnosis Related Group Dk
Key Attribute	Patient Dk
Key Attribute	Provider Dk
Key Attribute	Encounter Dk
Key Attribute	Practitioner Dk
Key Attribute	Point Of Care Location Dk
Key Attribute	Calendar Dk
Key Attribute	Condition Dk
Key Attribute	Service Dk
Key Attribute	Healthcare Insurer Dk
Key Attribute	Diagnosis Code Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model