Dimensional Warehouse Model Data Model

Entity

Entity Supply Chain Spend Analysis

Description

Provides analysis of the healthcare organization's or its affiliate organizations' spending patterns for non-pharmacy items. The measures provide insight into potential savings for utilizing on-contract vs. off-contract spending, purchase of functional equivalents, and so on. Many healthcare organizations are members of group purchasing alliances allowing them to achieve lower unit costs based on the combined purchase volumes of the group. Under such arrangements the group will negotiate purchase contracts with vendors and the members can avail of these contract prices but are not always obliged to do so and in some cases a member may be able to agree a lower price with a vendor.

Attributes
Actual Purchased Quantity
Actual Spend Amount
Alliance Member Dk
Base Item Dk
Benchmark Average Price
Benchmark High Price
Benchmark Low Price
Contract High Each Price
Contract High Package Price
Contract Low Each Price
Contract Low Package Price
Contract UOM Conversion Rate
Functional Equivalent Base Item Dk
Functional Equivalent Item Master Dk
Functional Equivalent Low Price
Group Purchasing Organization Dk
Item Master Dk
Period End Calendar Dk
Period Start Calendar Dk
Potential Savings At Benchmark Average Price
Potential Savings At Benchmark High Price
Potential Savings At Benchmark Low Price
Potential Savings At Contract High Each Price
Potential Savings At Contract Low Each Price
Potential Savings At Functional Equivalent Low Price
Purchase Contract Dk
Purchase Contract Tier Dk
Supplier Dk

Relationship
Supply Chain Spend Analysis_Supplier Dimension_FK
Supply Chain Spend Analysis_Purchase Contract Dimension_FK
Supply Chain Spend Analysis_Purchase Contract Tier Dimension_FK
Supply Chain Spend Analysis_Base Item Dimension_FK
Supply Chain Spend Analysis_Item Master Dimension_FK
Supply Chain Spend Analysis_Period End Calendar Dimension_FK
Supply Chain Spend Analysis_Functional Equivalent Base Item Dimension_FK
Supply Chain Spend Analysis_Functional Equivalent Item Master Dimension_FK
Supply Chain Spend Analysis_Period Start Calendar Dimension_FK
Supply Chain Spend Analysis_Alliance Member Dimension_FK
Supply Chain Spend Analysis_Group Purchasing Organization Dimension_FK

Primary Key
Supply Chain Spend Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Actual Purchased Quantity

Description	The quantity of items purchased by the alliance member during the reporting period.
Data Type	Standards - Data Domains.ddm/Data Domains/Quantity Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Actual Spend Amount

Description	The financial amount spent by the alliance member during the reporting period.
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

Alliance 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

Base Item 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

Benchmark Average Price

Description	The average benchmark price based on all recorded purchases of the given item during the reporting period.
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

Benchmark High Price

Description	The highest benchmark price based on all recorded purchases of the given item during the reporting period.
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

Benchmark Low Price

Description	The lowest benchmark price based on all recorded purchases of the given item during the reporting period.
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

Contract High Each Price

Description	The highest contract per each price that was available for the given item during the reporting period.
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

Contract High Package Price

Description	The highest contract package price that was available for the given item during the reporting period.
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

Contract Low Each Price

Description	The lowest contract per each price that was available for the given item during the reporting period.
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

Contract Low Package Price

Description	The lowest contract package price that was available for the given item during the reporting period.
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

Contract UOM Conversion Rate

Description	The unit of measure (UOM) conversion rate between the per each price and the package price
Data Type	Standards - Data Domains.ddm/Data Domains/Rate [FLOAT(5)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Functional Equivalent Base Item 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

Functional Equivalent Item Master 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

Functional Equivalent Low Price

Description	The price of the lowest priced functional equivalent to the item actually purchased. A functional equivalent would be another item with the same properties, that could be used without any risk or degradation of service, such as a different brand of surgical gloves.
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

Group Purchasing Organization 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

Item Master 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

Period End 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

Period Start 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

Potential Savings At Benchmark Average Price

Description	The monetary amount of savings that might be achieved if purchases were based on the benchmark average price. Note that it is possible for the use of the benchmark average price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Potential Savings At Benchmark High Price

Description	The monetary amount of savings that might be achieved if purchases were based on the benchmark high price. Note that it is possible, even likely, for the use of the benchmark high price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Potential Savings At Benchmark Low Price

Description	The monetary amount of savings that might be achieved if purchases were based on the benchmark low price. Note that it is possible, though unlikely, for the use of the benchmark low price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Potential Savings At Contract High Each Price

Description	The monetary amount of savings that might be achieved if purchases were based on the contract high each price. Note that it is possible for the use of the contract high each price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Potential Savings At Contract Low Each Price

Description	The monetary amount of savings that might be achieved if purchases were based on the contract low each price. Note that it is possible, though unlikely, for the use of the contract low each price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Potential Savings At Functional Equivalent Low Price

Description	The monetary amount of savings that might be achieved if purchases were based on the functional equivalent low price. Note that it is possible, though unlikely, for the use of the functional equivalent low price to result in a higher cost if the purchases had actually been made at a lower price. Formula: [A-(B*C)] (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

Purchase Contract 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

Purchase Contract Tier 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

Supplier 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

Supply Chain Spend Analysis_Supplier Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is supplier
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	Organization Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Purchase Contract Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend 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	Purchase Contract Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Purchase Contract Tier Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend 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	Purchase Contract Tier Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Base Item Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is base item
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	Base Item Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Item Master Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is base item master
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	Item Master Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Period End Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is period end
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

Supply Chain Spend Analysis_Functional Equivalent Base Item Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is functional equivalent base item
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	Base Item Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Functional Equivalent Item Master Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is functional equivalent item master
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	Item Master Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Period Start Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is period start
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

Supply Chain Spend Analysis_Alliance Member Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is alliance member
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	Organization Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Supply Chain Spend Analysis_Group Purchasing Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Supply Chain Spend Analysis
Child Role Name	is group purchasing organization
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	Organization 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

Supply Chain Spend Analysis PK

Key Attribute	Alliance Member Dk
Key Attribute	Group Purchasing Organization Dk
Key Attribute	Supplier Dk
Key Attribute	Purchase Contract Dk
Key Attribute	Purchase Contract Tier Dk
Key Attribute	Base Item Dk
Key Attribute	Functional Equivalent Base Item Dk
Key Attribute	Item Master Dk
Key Attribute	Functional Equivalent Item Master Dk
Key Attribute	Period Start Calendar Dk
Key Attribute	Period End Calendar Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model