Dimensional Warehouse Model Data Model

Entity

Entity Encounter / Coverage Eligibility Bridge

Description

An association between the patients Encounter (visit) and the Patient's insurance policy.

Attributes
Authorization Dt
Authorization Num
AWM Load Info Sk
Billing Status Code Sk
Charge Suspend Ind
Copay Limit Reached Ind
Coverage Eligibility Dk
Coverage Eligibility Sk
Current Ind
DWM Load Info Sk
Effective From Dt
Effective To Dt
Encounter Dk
Encounter Sk
Insurance Verification Status
Insured Relation To Patient Code Sk
Insured Relation To Patient End Dt
Insured Relation To Patient Start Dt
Notice Of Admission Date Ts
Patient Relation To Insured Code Sk
Relationship Type Cd
Relationship Type Code Sk
Report Of Eligibility Date Ts
Signature Code Sk
Signature Date Ts
Source Code Sk
Stoploss Limit Reached Ind
Tenant Sk
Valid From Ts
Valid To Ts
Verification Date Time

Relationship
Encounter / Provider Patient Insurance Rlshp_Provider Patient Insurance_FK
Encounter / Provider Patient Insurance_Encounter_FK

Primary Key
Encounter / Coverage Eligibility Bridge PK

Dependencies

Reverse Dependencies

Attribute Details

Authorization Dt

Description	The date upon which the insurer issued authorization for patient eligibility.
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

Authorization Num

Description	An insurer issued authorization number or code usually obtained prior to all non-emergency admissions and within 48 hours of an emergency admission.
Data Type	Standards - Data Domains.ddm/Data Domains/String [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

AWM Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row in the atomic data warehouse.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Billing Status Code Sk

Description	Indicates the status of the insurance billing.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Charge Suspend Ind

Description	Indicates whether or not charges should be suspended for a patient
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

Copay Limit Reached Ind

Description	Indicates whether or not the patient has reached the co-pay limit.
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

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

Coverage Eligibility Sk

Description	A numeric surrogate key used to uniquely identify entities in the atomic model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Current Ind

Description	Set to 'Y' if this is the most recent row of a group having the same Surrogate Key.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

DWM Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Effective From Dt

Description	Establishes a period where a set of attributes are true according to the business.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Effective To Dt

Description	Ends a period of effectivity.
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

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

Encounter Sk

Description	A numeric surrogate key used to uniquely identify entities in the atomic model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Insurance Verification Status

Description	The status of this patient's relationship with this insurance carrier.
Data Type	Standards - Data Domains.ddm/Data Domains/String [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insured Relation To Patient Code Sk

Description	Indicates the insured's relationship to the patient.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insured Relation To Patient End Dt

Description	The date after which the insured's relationship to the patient is no longer effective.
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

Insured Relation To Patient Start Dt

Description	The date upon which the insured's relationship to the patient began. For example: Parent (insurer) and child (patient)
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

Notice Of Admission Date Ts

Description	The date and time that a notice of admission was sent.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Patient Relation To Insured Code Sk

Description	The relationship of the patient to the insured. For example: Patient is insured Spouse Natural child/insured financial responsibility Natural child/Insured does not have financial responsibility Step child Foster child Ward of the court Employee Unknown Handicapped dependent Organ donor Cadaver donor Grandchild Niece/nephew Injured plaintiff Sponsored dependent Minor dependent of a minor dependent Parent Grandparent
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Type Cd

Description	The code that corresponds to the Relationship Type Code Sk.
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Relationship Type Code Sk

Description	The code identifying the actual type of the relationship as defined in the business data model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Report Of Eligibility Date Ts

Description	The date and time that a Report of Eligibility (ROE) was received
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Signature Code Sk

Description	Indicates how the patient/subscriber authorization signature was obtained and how it is being retained by the provider. For example: Signed CMS-1500 claim form on file Signed authorization for release of any medical or other information necessary to process this claim on file. Signed authorization for assignment of benefits on file. Signature generated by provider because the patient was not physically present for services.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Signature Date Ts

Description	The date and time that the patient/subscriber authorization signature was obtained.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Source Code Sk

Description	The origin of the data identifying the actual load source, vendor, manual key entry, or context of the data in a specific row in the database.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Stoploss Limit Reached Ind

Description	Indicates whether or not the patient has reached the stop loss limit established in the Contract Master.
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

Tenant Sk

Description	The surrogate key of the entry identifying the legal owner of the data.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid From Ts

Description	Establishes a period where a set of attributes are true in the source system. This would be populated with the transaction timestamp and would be used for the snapshot date.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid To Ts

Description	Ends a period of validity.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Verification Date Time

Description	The date and time that the healthcare provider verified that the patient has the indicated benefits.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Details

Encounter / Provider Patient Insurance Rlshp_Provider Patient Insurance_FK

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

Encounter / Provider Patient Insurance_Encounter_FK

Is Identifying Relationship	true
Child Table	Encounter / Coverage Eligibility Bridge
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

Primary Key Details

Encounter / Coverage Eligibility Bridge PK

Key Attribute	Coverage Eligibility Dk
Key Attribute	Encounter Dk
Key Attribute	Relationship Type Code Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model