Atomic Warehouse Model Data Model

Entity

Entity Health Plan Product Detail

Description

A product offering of healthcare coverages for a health benefits plan group or person. A health plan is offered or administered by a healthcare insurer that provides financing or delivery of healthcare services to, or on behalf of, members. The health plan product is developed by the healthcare insurer as a marketable offering.

Attributes
Brand
Brand Name Fee
Concept Development Cost
Concept Development Support Cost
Creation Cost Amt
Deductible Carryover Ind
Development Cost Amt
Effective From Dt
Effective To Dt
End Dt
Exposure Basis Unit Of Measure Code Sk
Health Plan Code Qualifier Code Sk
Health Plan Code Sk
Health Plan Descr
Health Plan External Reference
Health Plan Nm
Health Plan Product Sk
Health Plan Type Code Sk
Individual Group Ind
Launch Cost Amt
Line Of Business Code Sk
Load Info Sk
Marketing Budget
Marketing Execution Cost
Marketing Planning Cost
Market Pos
Market Research Cost
Market Share
NHPI Num
Product Code
Product Code Qualifier Sk
Product Status Code Sk
Product Time To Market Range Code Sk
Profitability
Risk Type Code Sk
Selling General And Administration SGA Cost
Source Code Sk
Start Dt
Tenant Sk
Total Marketing Cost
Type Code Sk
Valid From Ts
Valid To Ts

Relationship
Health Plan Product Detail_Anchor_FK

Primary Key
Health Plan Product Detail PK

Dependencies

Reverse Dependencies

Attribute Details

Brand

Description	A name, logo, trademark, or service mark that is associated with the product.
Data Type	Standards - Data Domains.ddm/Data Domains/Name [VARCHAR(30)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Brand Name Fee

Description	Fee charged to a member on top of the usual co-payment amount for selecting the brand name drug when a generic drug is available.
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

Concept Development Cost

Description	Total cost of developing the product concept.
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

Concept Development Support Cost

Description	Total costs for providing support to the concept development activities.
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

Creation Cost Amt

Description	The expense amount involved in creation of the product between the concept development and the launch.
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 Carryover Ind

Description	Indicating whether or not a deductible amount can be carried over from another policy.
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

Development Cost Amt

Description	The expense amount involved in development of the product.
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

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

End Dt

Description	The date upon which the product is deemed to have ceased to exist.
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

Exposure Basis Unit Of Measure Code Sk

Description	Indicates the basis used for an insured exposure based on units, number of employees, predicted healthcare costs etc.
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

Health Plan Code Qualifier Code Sk

Description	Indicates the format of the plan code.
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

Health Plan Code Sk

Description	An alphanumeric code assigned to the plan.
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

Health Plan Descr

Description	A free-text statement used to explain the meaning of the plan.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Health Plan External Reference

Description	The number or code allocated to a plan for identification and communication purposes.
Data Type	Standards - Data Domains.ddm/Data Domains/String [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Health Plan Nm

Description	The label by which the plan is commonly known.
Data Type	Standards - Data Domains.ddm/Data Domains/Name [VARCHAR(30)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Health Plan Product Sk

Description	Anchor surrogate key for the Encounter data.
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 Type Code Sk

Description	Identifies the plan type.
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

Individual Group Ind

Description	Indicates whether the health plan product is designed to cater to the needs of persons or health benefits plan groups. For example: G = Group P = Person
Data Type	Standards - Data Domains.ddm/Data Domains/Alphanumeric Small [VARCHAR(6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Launch Cost Amt

Description	The expense amount involved in the launch of the product.
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 Of Business Code Sk

Description	Indicates the nature of commercial enterprise to which this health plan product is directed or associated.
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

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

Marketing Budget

Description	The marketing budget for planned marketing activities for promotion of sales during the period covered by the market information: campaigns, marketing events, advertisement, etc.
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

Marketing Execution Cost

Description	Total of component expenses required for the execution of marketing.
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

Marketing Planning Cost

Description	Actual portion of the marketing costs that is allocated to planning.
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

Market Pos

Description	Positioning of the product relative to rival Products in the targeted market segment. For example: Market Leader Second Third
Data Type	Standards - Data Domains.ddm/Data Domains/Small Counter [SHORT]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Market Research Cost

Description	Actual portion of the marketing costs that is allocated to market research.
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

Market Share

Description	Percentage of a given market that has adopted, or is expected to adopt, this health plan product.
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

NHPI Num

Description	National Health Plan Identifier
Data Type	Standards - Data Domains.ddm/Data Domains/Alphanumeric [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Product Code

Description	An alphanumeric code assigned to the product.
Data Type	Standards - Data Domains.ddm/Data Domains/Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Product Code Qualifier Sk

Description	Designates the format of the product code.
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

Product Status Code Sk

Description	Indicates the status of the health plan product.
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

Product Time To Market Range Code Sk

Description	Indicates the total cycle time range to bring the health plan product to the market. This is the total time that elapses between the first introduction of a product idea and it's first availability in the market.
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

Profitability

Description	The target profit for the specified product as cash 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

Risk Type Code Sk

Description	Indicates the types of risk or financial exposure to the health plan.
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

Selling General And Administration SGA Cost

Description	The selling, general and administration (SGA) expenses of the product.
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

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

Start Dt

Description	The date upon which the product is deemed to have come into existence.
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

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

Total Marketing Cost

Description	Total of component expenses required for the overall marketing process.
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

Type Code Sk

Description	Identifies the actual type of the entry based on the entity type hierarchy defined in the business data model.
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

Relationship Details

Health Plan Product Detail_Anchor_FK

Is Identifying Relationship	true
Child Table	Health Plan Product Detail
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	Health Plan Product
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Health Plan Product Detail PK

Key Attribute	Health Plan Product Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Atomic Warehouse Model Data Model