Dimensional Warehouse Model Data Model

Entity

Entity Insurance Policy / Plan Member Bridge

Description

An association between Insurance Policy Dimension and Plan Member Dimension.

Attributes
AWM Load Info Sk
Current Ind
DWM Load Info Sk
Effective From Dt
Effective To Dt
Insurance Policy Dimension Dk
Insurance Policy Dimension Sk
Late Enrollment Ind
Membership End Dt
Membership Lifetime Benefit Accumulator
Membership Notification Dt
Membership Start Dt
Membership Status Cd
Membership Status Code Sk
Membership Status Reason Cd
Membership Status Reason Code Sk
Membership Year To Date Benefit Accumulator
Membership Year To Date Deductible Accumulator
Membership Year To Date Deductible Accumulator Out-of-Network
Membership Year To Date Out-of-Pocket Accumulator
Membership Year To Date Out-of-Pocket Accumulator Out-of-Network
Plan Member Dk
Plan Member Sk
Pre-existing Condition Waiver
Relationship Type Cd
Relationship Type Code Sk
Source Code Sk
Tenant Sk
Type of Membership Cd
Type of Membership Code Sk
Valid From Ts
Valid To Ts

Relationship
Insurance Policy / Plan Member Bridge_Plan Member Dimension_FK
Insurance Policy / Plan Member Bridge_Insurance Policy Dimension_FK

Primary Key
Insurance Policy / Plan Member Bridge PK

Dependencies

Reverse Dependencies

Attribute Details

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

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

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

Insurance Policy Dimension 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

Late Enrollment Ind

Description	Indicates whether the member is considered to have been continuously enrolled in a plan for a specified period or has switched plans during the criteria period, since their enrollment date or notification date, whichever is later. 1 enrolled late 0 not late
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

Membership End Dt

Description	The date upon which the membership of the policy ended.
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

Membership Lifetime Benefit Accumulator

Description	The total benefits ever paid to date for this member for the agreement.
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

Membership Notification Dt

Description	The date upon which the organization became aware of a membership. This may occur prospectively, i.e. before the enrollment date or retrospectively, i.e. after the enrollment date has passed.
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

Membership Start Dt

Description	The date upon which the membership of the policy commenced.
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

Membership Status Cd

Description	Indicates the current status of the membership. For example, if Mary separates from her husband, she may no longer elect to include spouse coverage on her individual policy, so while her policy and resulting membership will remain active her husbands will be terminated.
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

Membership Status Code Sk

Description	Indicates the current status of the membership. For example, if Mary separates from her husband, she may no longer elect to include spouse coverage on her individual policy, so while her policy and resulting membership will remain active her husbands will be terminated.
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

Membership Status Reason Cd

Description	Indicates the reason for the current membership status or why it has changed. For example: Divorced Employment Opted out Overage Dependent Retired Terminated
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

Membership Status Reason Code Sk

Description	Indicates the reason for the current membership status or why it has changed. For example: Divorced Employment Opted out Overage Dependent Retired Terminated
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

Membership Year To Date Benefit Accumulator

Description	The total benefits paid year-to-date for this agreement for this member for the given agreement.
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

Membership Year To Date Deductible Accumulator

Description	Amount of deductible on the agreement already paid by the member during the current contract year. Applies to benefits in-network, where applicable.
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

Membership Year To Date Deductible Accumulator Out-of-Network

Description	Amount of deductible on the agreement already paid by the member during the current contract year for benefits received out-of-network.
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

Membership Year To Date Out-of-Pocket Accumulator

Description	Amount of deductibles and co-payments on the agreement already paid by the member during the current contract year. Applies to benefits in-network, where applicable.
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

Membership Year To Date Out-of-Pocket Accumulator Out-of-Network

Description	Amount of deductibles and co-payments on the agreement already paid by the member during the current contract year. Applies to benefits out-of-network.
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

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

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

Pre-existing Condition Waiver

Description	Indicates whether enrolment occurred without consideration for pre-existing conditions.
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

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

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

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

Type of Membership Cd

Description	Indicates the type of membership under which benefits are paid. For example: Consolidated Omnibus Budget Reconciliation Act (COBRA) Standard Surviving Insured Tax Equity and Fiscal Responsibility Act (TEFRA)
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

Type of Membership Code Sk

Description	Indicates the type of membership under which benefits are paid. For example: Consolidated Omnibus Budget Reconciliation Act (COBRA) Standard Surviving Insured Tax Equity and Fiscal Responsibility Act (TEFRA)
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

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

Relationship Details

Insurance Policy / Plan Member Bridge_Plan Member Dimension_FK

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

Insurance Policy / Plan Member Bridge_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Insurance Policy / Plan Member 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	Insurance Policy 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

Insurance Policy / Plan Member Bridge PK

Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Plan Member Dk
Key Attribute	Relationship Type Code Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model