Dimensional Warehouse Model Data Model

Entity

Entity Inventory Cost Analysis

Description

The analysis of inventory items over a period of time. Inventory items can range from high volume, relatively low cost items such as surgical gloves and regular drugs like paracetamol to low volume high value items such as implantable devices or new high tech drugs. The style of this analysis is more suited to the high volume inventory items but can have application across all types of inventory. The analysis reports on elements such as cost, wastage, shrinkage, stock on hand and projected cover.

Attributes
Average Daily Inventory Use
Cost Of Items Per Period Amt
Inventory Carry Cost Per Period Amount
Inventory Date Dk
Inventory Purchases Per Period Amount
Inventory Shrinkage Per Period Amount
Inventory Use Per Period Amount
Inventory Waste Per Period Amount
Inventory Within One Month Of Expiration Amount
Item Master Dk
Number Of Days Supply On Hand
Number Of Inventory Units Used Per Period
Number Of Reporting Period Days
Number Of Units On Hand At End Of Period
Point Of Care Location Dk
Provider Dk

Relationship
Inventory Cost Analysis_Point Of Care Location Dimension_FK
Inventory Cost Analysis_Provider Dimension_FK
Inventory Cost Analysis_Calendar Dimension_FK
Inventory Cost Analysis_Item Master Dimension_FK

Primary Key
Inventory Cost Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Daily Inventory Use

Description	The average number of units of inventory required for use each day. This is based on a simple calculation of the inventory usage during the reporting period divided by the number of days in the reporting period. Some organizations may prefer to use a predictive demand model instead of this calculation so that other factors sucha as seasonality can be included. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
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

Cost Of Items Per Period Amt

Description	Cost of to store, hold, ship inventory during 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

Inventory Carry Cost Per Period Amount

Description	The moetary costs associated with the holding or storage of inventory items during the reporting period. These costs may include direct expenses relating to items with special storage requirements such as specific refrigeration or isolation as well as general storage costs that are typically allocated across many inventory items.
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

Inventory Date 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

Inventory Purchases Per Period Amount

Description	The monetary cost of inventory bought or otherwise acquired by the organization.
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

Inventory Shrinkage Per Period Amount

Description	The monetary cost of inventory lost to pilferage or otherwise unaccounted for. Inventory that cannot be accounted for through use, waste or returns is included in shrinkage. The extent of shrinkage is typically established in the course of a physical stock audit where any discrepancy between expected or book stock and actual is regarded as shrinkage.
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

Inventory Use Per Period Amount

Description	The monetary cost of inventory used by the organization 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

Inventory Waste Per Period Amount

Description	The monetary cost of inventory lost to expiration, spoilage, accident or breakage. These types of inventory loss must be recorded as inventory transactions otherwise they will be included in the inventory shrinkage as unaccounted for differences.
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

Inventory Within One Month Of Expiration Amount

Description	The monetary value of any inventory that is within one month of its expiration date at period end. This typically applies to items such as drugs and foodstuffs that can not be safely used beyond a predefined date. As inventory nears its expiration date, the organization may be able to accelerate its use or they may have to write it off as waste.
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

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

Number Of Days Supply On Hand

Description	The length of time for which the inventory on hand will be sufficient based on average demand. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
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

Number Of Inventory Units Used Per Period

Description	A count of inventory items used by the organization during the reporting 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 Reporting Period Days

Description	A count of the number of whole days included in the reporting 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 Units On Hand At End Of Period

Description	A count of units of an inventory item that the organization has in stock at the end of the recording 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

Point Of Care Location 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

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

Relationship Details

Inventory Cost Analysis_Point Of Care Location Dimension_FK

Is Identifying Relationship	true
Child Table	Inventory Cost Analysis
Child Role Name	inventory location
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	Point Of Care Location Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Inventory Cost Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Inventory Cost Analysis
Child Role Name	responsible 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	Provider Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Inventory Cost Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Inventory Cost Analysis
Child Role Name	inventory date
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

Inventory Cost Analysis_Item Master Dimension_FK

Is Identifying Relationship	true
Child Table	Inventory Cost Analysis
Child Role Name	inventory 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	Item Master 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

Inventory Cost Analysis PK

Key Attribute	Item Master Dk
Key Attribute	Provider Dk
Key Attribute	Point Of Care Location Dk
Key Attribute	Inventory Date Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model