Dimensional Warehouse Model Data Model

Entity

Entity Pharmacy Claims Analysis

Description

The analysis of pharmacy claims submitted by members in terms of overall drug use, formulary compliance, and generic prescribing. This focuses on the drugs prescribed and dispensed to members of the health plan, whether they are branded or generic versions, the supply method, whether retail pharmacy or mail order, and whether the drugs were on or off formulary as well as associated costs for drugs and services. These insights can then be used to identify prescribers who might be encouraged to increase their use of generics and to identify members who could reduce their medication costs and the cost to the health plan by purchasing from pharmacies that are in network or though mail order options.

Attributes
Calendar Dk
Co-Payment Amount
Cost Of Pharmacy Claims Amount
Delivery Cost Amount
Generic Prescription Lines Amount
Geographic Area Dk
Health Plan Product Dk
In Network Prescription Lines Amount
Medication Dk
Number Of Days Supply
Number Of Generic Prescription Lines
Number Of Members With Pharmacy Claims
Number Of On Formulary Prescription Lines
Number Of Pharmacy Claim Lines
Number Of Pharmacy Claims
Number Of Prescription Lines Dispensed In Network
On Formulary Prescription Lines Amount
Percentage Formulary Compliance By Value
Percentage Formulary Compliance By Volume
Percentage Generic Dispensing By Value
Percentage Generic Dispensing By Volume
Percentage Network Compliance By Value
Percentage Network Compliance By Volume
Percentage Prescription Cost Savings
Person Dk
Pharmacy Order Dispense Dk
Pharmacy Order Dk
Practitioner Dk
Prescription Cost Savings Amount
Provider Dk
Reference Cost Of Prescribed Medications Amount

Relationship
Pharmacy Claims Analysis_Calendar Dimension_FK
Pharmacy Claims Analysis_Geographic Area Dimension_FK
Pharmacy Claims Analysis_Person Dimension_FK
Pharmacy Claims Analysis_Practitioner Dimension_FK
Pharmacy Claims Analysis_Provider Dimension_FK
Pharmacy Claims Analysis_Medication Dimension_FK
Pharmacy Claims Analysis_Pharmacy Order Dimension_FK
Pharmacy Claims Analysis_Pharmacy Order Dispense Dimension_FK
Pharmacy Claims Analysis_Health Plan Product Dimension_FK

Primary Key
Pharmacy Claims Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

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

Co-Payment Amount

Description	The amount of prescription costs that is incurred by the members of the health plan.
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

Cost Of Pharmacy Claims Amount

Description	The total cost of claims for dispensing prescriptions to member of the health plan.
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

Delivery Cost Amount

Description	The cost of any delivery services associated with the dispensing of medications to members of the health plan. Delivery costs are normally applicable where regular medications are dispensed to members via a mail order 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

Generic Prescription Lines Amount

Description	The cost of prescription lines where the dispensed medication was a generic rather than a branded drug.
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

Geographic Area 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

Health Plan Product 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

In Network Prescription Lines Amount

Description	The monetary value of individual prescription lines that were dispensed by a pharmacy that is part of a network recognized by the health plan.
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

Medication 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

Number Of Days Supply

Description	The number of dosage days that dispensed prescriptions of medications represent. Members acquiring their long term medications from a retail pharmacy might typically collect a 30 day supply at a time while those using a mail order service might receive a 90 day supply with each delivery. Prescriptions for short term medications such as an antibiotic for a chest infection would typically be for a shorter period such as 5 or 7 days.
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

Number Of Generic Prescription Lines

Description	The count of individual prescription lines where the medication dispensed was a generic rather than a branded drug.
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

Number Of Members With Pharmacy Claims

Description	The count of health plan members who submitted claims for medications or other pharmacy items during the analysis period.
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

Number Of On Formulary Prescription Lines

Description	The count of individual prescription lines where the medication dispensed was included on the health plan's formulary. Use of an agreed formulary helps the health plan ensure that drugs with a proven therapeutic benefit are supplied to members in the most cost effective manner.
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

Number Of Pharmacy Claim Lines

Description	The count of individual claim lines for prescribed drugs or other pharmacy services. A prescription might, for example, include line items for 4 separate medications.
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

Number Of Pharmacy Claims

Description	The count of claims for prescriptions for drugs or other treatments. A prescription is typically an order written by a healthcare professional for a drug or other treatment for a patient.
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

Number Of Prescription Lines Dispensed In Network

Description	The count of individual prescription lines that were dispensed by a pharmacy that is part of a network recognized by the health plan.
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

On Formulary Prescription Lines Amount

Description	The cost of individual prescription lines where the medication dispensed was included on the health plan's formulary. Use of an agreed formulary helps the health plan ensure that drugs with a proven therapeutic benefit are supplied to members in the most cost effective manner.
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

Percentage Formulary Compliance By Value

Description	The monetary value of prescription lines that were for drugs listed on the health plan's formulary expressed as a percentage of the total cost of prescription lines dispensed. This gives an indication of how closely prescribers and dispensers are following the health plan's agreed formulary based on the cost of drugs dispensed. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Formulary Compliance By Volume

Description	The number of prescription lines that were for drugs listed on the health plan's formulary expressed as a percentage of the total number of prescription lines dispensed. This gives an indication of how closely prescribers and dispensers are following the health plan's agreed formulary based on volume rather than value. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Generic Dispensing By Value

Description	The monetary value of prescription lines that were for generic drugs expressed as a percentage of the total value of prescription lines dispensed. This gives an indication of the rate of generic prescribing / dispensing. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Generic Dispensing By Volume

Description	The number of prescription lines that were for generic drugs expressed as a percentage of the total number of prescription lines dispensed. This gives an indication of the rate of generic prescribing / dispensing. A similar measure describes this in terms of monetary value but can understate the generic use rate as branded drugs typically have a considerably higher unit cost. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Network Compliance By Value

Description	The monetary value of prescription lines that were dispensed by pharmacies that are part of a network recognized by the health plan expressed as a percentage of the total cost of prescription lines dispensed. This gives an indication of how closely members are following the health plan's recommended sources of supply of pharmacy items based on the cost of drugs dispensed. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Network Compliance By Volume

Description	The number of prescription lines that were dispensed by pharmacies that are part of a network recognized by the health plan expressed as a percentage of the total number of prescription lines dispensed. This gives an indication of how closely members are following the health plan's recommended sources of supply of pharmacy items based on volume rather than value. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Percentage Prescription Cost Savings

Description	The monetary value of savings made by the health plan and its members through discounts negotiated by the pharmacy benefits management service expressed as a percentage of a reference price of prescriptions dispensed where the assumption is that no discounts have been achieved. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula)
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

Person 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

Pharmacy Order Dispense 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

Pharmacy Order 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

Prescription Cost Savings Amount

Description	The monetary value of savings made by the health plan and its members through discounts negotiated by the pharmacy benefits management service. These are calculated against a reference price of prescriptions dispensed where the assumption is that no discounts have been achieved. Formula: [A-B] (see dependencies and their labels for measures used in the formula)
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

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

Reference Cost Of Prescribed Medications Amount

Description	The normal price or list price of the prescribed and dispensed medications had the members purchased from pharmacies where there are no negotiated discounts and where no generics were dispensed. This cost is used as a reference point to establish the scale of savings to members and to the health plan by complying with recommended medications and sources of supply.
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

Pharmacy Claims Analysis_Calendar Dimension_FK

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

Pharmacy Claims Analysis_Geographic Area Dimension_FK

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

Pharmacy Claims Analysis_Person Dimension_FK

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

Pharmacy Claims Analysis_Practitioner Dimension_FK

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

Pharmacy Claims Analysis_Provider Dimension_FK

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

Pharmacy Claims Analysis_Medication Dimension_FK

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

Pharmacy Claims Analysis_Pharmacy Order Dimension_FK

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

Pharmacy Claims Analysis_Pharmacy Order Dispense Dimension_FK

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

Pharmacy Claims Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Pharmacy Claims 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	Health Plan Product 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

Pharmacy Claims Analysis PK

Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Person Dk
Key Attribute	Practitioner Dk
Key Attribute	Provider Dk
Key Attribute	Medication Dk
Key Attribute	Pharmacy Order Dk
Key Attribute	Pharmacy Order Dispense Dk
Key Attribute	Health Plan Product Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model