Dimensional Warehouse Model Data Model

Entity

Entity Revenue Cycle Snapshot

Description

A snapshot fact table which includes measures, time stamps and indicators related to the provision of healthcare servicesby practitioners and the billing and payments associated with these activities.

Attributes
Adjustment Amount
Adjustment Indicator
Adjustment Reason
Billing Amount
Billing Date
Claim Count
Claim Dk
Claim Payment Amount
Claim Payment Date
Claim Submitted Amount
Claim Submitted Date
Copay Amount
Copay Payment Amount
Copay Payment Date
Diagnosis Related Group Dk
Final Adjudication Indicator
First Time Pass Indicator
Healthcare Insurer Dk
Initial Adjudication Date
Initial Adjudication Outcome
Initial Adjudication Reason
Latest Adjudication Outcome
Latest Adjuducation Date
Latest Adjustment Date
Patient Dk
Point Of Care Location Dk
Practitioner Dk
Provider Dk
Repricing Date
Repricing Indicator
Service Date
Service Dk

Relationship
Revenue Cycle Snapshot_Patient Dimension_FK
Revenue Cycle Snapshot_Practitioner Dimension_FK
Revenue Cycle Snapshot_Provider Dimension_FK
Revenue Cycle Snapshot_Point Of Care Location Dimension_FK
Revenue Cycle Snapshot_Claim Dimension_FK
Revenue Cycle Snapshot_Diagnosis Related Group Dimension_FK
Revenue Cycle Snapshot_Healthcare Insurer Dimension_FK
Revenue Cycle Snapshot_Service Dimension_FK

Primary Key
Revenue Cycle Snapshot PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Adjustment Amount

Description	The monetary amount of all adjustments made to the claim 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

Adjustment Indicator

Description	Indicates that changes were made to the claim after it was issued. This may be done where errors were identified in the original claim or where the insurer chanes the claim or requests changes to be made to the claim on the basis of coverage agreements.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Adjustment Reason

Description	The reason why a claim was adjusted. It may relate to the latest or most significant adjustment.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Billing Amount

Description	The monetary amount charged or to be charged for a 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

Billing Date

Description	Date on which the coding and billing of the service was complete.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Claim Count

Description	Used to count fact rows. Always set to 1.
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

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

Description	The monetary amount of all payments made in settlement of the claim 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

Claim Payment Date

Description	The date on whch a claim was paid by the Payer. Where multiple payments have been processed this will be the most recent date.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Claim Submitted Amount

Description	The monetary amount claimed from a Payer in respect of a 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

Claim Submitted Date

Description	The date upon which the claim was submitted.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Copay Amount

Description	The monetary amount to be recovered directly from a patient in respect of a 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

Copay Payment Amount

Description	The monetary amount of all payments made in settlement of the copay element of the claim by the patient.
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

Copay Payment Date

Description	The date on whch a copay was paid by the Patient. Where multiple payments have been processed this will be the most recent date.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
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

Final Adjudication Indicator

Description	Indicates that the claim is regarded as having been completely dealt with by the payer in that there will be no further processing of this claim.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

First Time Pass Indicator

Description	Indicates that the claim was passed for payment on its first review or adjudication by the payer.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
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

Initial Adjudication Date

Description	The date on which a claim was reviewed by the payer and an initial decision made as to its validity under the terms of a coverage agreement.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Initial Adjudication Outcome

Description	A classification of the result of the first review or adjudication of a claim by the Payer. For example: Passed for Payment Rejected Refer back for more information Refer back for error correction Refer for Repricing
Data Type	Standards - Data Domains.ddm/Data Domains/Enumeration [VARCHAR(20)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Initial Adjudication Reason

Description	The reason why a claim was adjudicated as it was in the first review. This is generally only relevant in the case of claims not passed on first review.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Latest Adjudication Outcome

Description	A classification of the result of the most recent review or adjudication of a claim by the Payer. This is only relevant in the case of claims not passed on first review. It may relate to a “current” position of a particular claim or a final adjudication. For example: Passed for Payment Rejected Refer back for more information Refer back for error correction Refer for Repricing
Data Type	Standards - Data Domains.ddm/Data Domains/Enumeration [VARCHAR(20)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Latest Adjuducation Date

Description	The most recent date on which a claim was reviewed by the Payer and a decision made as to its validity under the terms of a coverage agreement. This is only relevant in the case of claims not passed on first review. It may relate to a “current” position of a particular claim or a final adjudication.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Latest Adjustment Date

Description	The date on which a claim was most recently adjusted by the Payer under the terms of a coverage agreement.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
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

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

Repricing Date

Description	The date on whch a claim was repriced or recoded.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Repricing Indicator

Description	Indicates that the claim was repriced or recoded by the payer. This may be done where a number of treatments are claimed sparately but could be claimed under a different DRG based charge code leading to a lower claim value.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Service Date

Description	Date of the service for which payment is sought. Where a service relate to an extended period this date would typically indicate the end of such period.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
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

Relationship Details

Revenue Cycle Snapshot_Patient Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Practitioner Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Point Of Care Location Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Claim Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Diagnosis Related Group Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Healthcare Insurer Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Revenue Cycle Snapshot_Service Dimension_FK

Is Identifying Relationship	true
Child Table	Revenue Cycle Snapshot
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

Primary Key Details

Revenue Cycle Snapshot PK

Key Attribute	Patient Dk
Key Attribute	Practitioner Dk
Key Attribute	Provider Dk
Key Attribute	Point Of Care Location Dk
Key Attribute	Healthcare Insurer Dk
Key Attribute	Claim Dk
Key Attribute	Diagnosis Related Group Dk
Key Attribute	Service Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model