Dimensional Warehouse Model Data Model

Entity

Entity Inventory Fact

Description

An account that tracks the use and replenishment of items in the course of performing the activities of the organization.

Attributes
Account Cd
Account Code Sk
Account Descr
Accounting Scenario Cd
Accounting Scenario Code Sk
Account Nm
Account Num
Account Status Dt
Account Status Reason Cd
Account Status Reason Code Sk
Account Status Type Cd
Account Status Type Code Sk
Account Type Cd
Account Type Code Sk
AWM Load Info Sk
Balance Amt
Base Amount Unit Of Measure Cd
Base Amount Unit Of Measure Code Sk
Closing Dt
Consignment Item Ind
Currency Cd
Currency Code Sk
Current Ind
Debit Credit Cd
Debit Credit Code Sk
Device Identifier
Domestic Manufacturer
Domestic Manufacturing
DWM Load Info Sk
Effective From Dt
Effective To Dt
Expiration Dt
Inventory Control Num
Inventory Date Dk
Inventory Dt
Inventory Fact Bk
Inventory Fact Dk
Inventory Fact Sk
Item Importance Cd
Item Importance Code Sk
Item Master Dk
Item Master Sk
Item Size
Lot Number
Manufacture Dt
Measurement Period Type Cd
Measurement Period Type Code Sk
Opening Dt
Period Balance Type Cd
Period Balance Type Code Sk
Point Balance Type Cd
Point Balance Type Code Sk
Point Of Care Location Dk
Point Of Care Location Sk
Provider Dk
Provider Sk
Received Dt
Serial Number
Source Cd
Source Code Sk
Tenant Sk
Unit Cost Amt
Valid From Ts
Valid To Ts

Relationship
Inventory Fact_Provider Dimension_FK
Inventory Fact_Calendar Dimension_FK
Inventory Fact_Item Master Dimension_FK
Inventory Fact_Point Of Care Location Dimension_FK

Primary Key
Inventory Fact PK

Dependencies

Reverse Dependencies

Attribute Details

Account Cd

Description	Indicates the specific type of the account being tracked.
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

Account Code Sk

Description	Account Cd describes the specific types of the accounts being tracked.
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

Account Descr

Description	A free form textual description of what is represented by the account.
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

Accounting Scenario Cd

Description	Indicates the nature of the value represented by the account balance. For example: Actual Budget Estimated Forecast
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

Accounting Scenario Code Sk

Description	Indicates the nature of the value represented by the account balance. For example: Actual Budget Estimated Forecast
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

Account Nm

Description	A word or phrase used to identify (but not uniquely) the account.
Data Type	Standards - Data Domains.ddm/Data Domains/Name Medium [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Account Num

Description	Account Num is an identifier used by the healthcare organization to identify an individual account.
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

Account Status Dt

Description	The effective date for the account status. For example: Account is pending with effect from 01/05/2013
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

Account Status Reason Cd

Description	Indicates why the account status was changed to its current state.
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

Account Status Reason Code Sk

Description	Indicates why the account status was changed to its current state.
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

Account Status Type Cd

Description	Indicates the life cycle state the account is currently in. For example: Submitted For Approval Pending Open Open Pending Closed Closed Pending Deletion
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

Account Status Type Code Sk

Description	Indicates the life cycle state the account is currently in. For example: Submitted For Approval Pending Open Open Pending Closed Closed Pending Deletion
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

Account Type Cd

Description	Indicates the major classification or grouping to which an account naturally belongs. For example: Expense Revenue Asset Statistical
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

Account Type Code Sk

Description	Indicates the major classification or grouping to which an account naturally belongs. For example: Expense Revenue Asset Statistical
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

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

Balance Amt

Description	The current value or amount of the account. Can be derived from all the amounts posted in account entries for the account.
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

Base Amount Unit Of Measure Cd

Description	Indicates the unit of measure for a non-monetary account
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

Base Amount Unit Of Measure Code Sk

Description	Indicates the unit of measure for a non-monetary account
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

Closing Dt

Description	The date upon which the account status is changed to closed.
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

Consignment Item Ind

Description	Indicates whether or not the inventory supply item is purchased on consignment. If the item is purchased on consignment, the healthcare organization does not pay for the inventory supply item until it is used.
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

Currency Cd

Description	Indicates the currency used as the basis for all monetary amounts.
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

Currency Code Sk

Description	Indicates the currency used as the basis for all monetary amounts.
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

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

Debit Credit Cd

Description	Indicates whether an account or an entry is a debit or a credit.
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

Debit Credit Code Sk

Description	Indicates whether an account or an entry is a debit or a credit.
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

Device Identifier

Description	A fixed portion of a UDI that identifies the specific version or model of a device and the labeler of that device. Part of UDI, Unique Device 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

Domestic Manufacturer

Description	Indicates whether the inventory item manufacturer is headquartered in the country of the modeled organization.
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

Domestic Manufacturing

Description	Indicates whether the inventory item was created in the country of the modeled organization.
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

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

Expiration Dt

Description	The expiration date of the item in the inventory.
Data Type	Standards - Data Domains.ddm/Data Domains/Date Time [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Inventory Control Num

Description	The text string that uniquely identifies this asset within the inventory control system used by the organization. This is not the same as serial number.
Data Type	Standards - Data Domains.ddm/Data Domains/String [VARCHAR(255)]
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	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Inventory Dt

Description	The date for the Calendar row. This is a DATE column using the database's native date format.
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

Inventory Fact Bk

Description	Concatenated values of a set of business attributes that uniquely identify the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Business Key [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Inventory Fact 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	true

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

Item Importance Cd

Description	This field contains an indicator of the level of importance of an item considered for this inventory location, such as an indicator signifying whether the item is considered critical for this inventory location.
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

Item Importance Code Sk

Description	This field contains an indicator of the level of importance of an item considered for this inventory location, such as an indicator signifying whether the item is considered critical for this inventory location.
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

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	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

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

Item Size

Description	The magnitude or size of the inventory item. For example: 200 mg. 2 cubic meters 150 kgs
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Lot Number

Description	This field contains the lot number of the service item in inventory. Part of UDI, Unique Device 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

Manufacture Dt

Description	Date service item was produced by the manufacturer. Part of UDI, Unique Device Identifier.
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

Measurement Period Type Cd

Description	Indicates the balance calendar period. For example: Day Week Month Quarter Half Year
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

Measurement Period Type Code Sk

Description	Indicates the balance calendar period. For example: Day Week Month Quarter Half Year
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

Opening Dt

Description	The date upon which the account is opened and is ready for transactions.
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

Period Balance Type Cd

Description	Indicates the method by which the amount reflected by the account balance is calculated. For example: Amount Maximum Minimum Mean Median Mode Total Total Average Net
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

Period Balance Type Code Sk

Description	Indicates the method by which the amount reflected by the account balance is calculated. For example: Amount Maximum Minimum Mean Median Mode Total Total Average Net
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

Point Balance Type Cd

Description	Indicates the nature of the point in time within an interval of time at which the account balance was recorded. For example: Beginning or Opening Balance Interim Balance - Statement Balance - Capital Balance - YTD Balance End or Closing Balance
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

Point Balance Type Code Sk

Description	Indicates the nature of the point in time within an interval of time at which the account balance was recorded. For example: Beginning or Opening Balance Interim Balance - Statement Balance - Capital Balance - YTD Balance End or Closing Balance
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

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	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

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

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	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Provider 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

Received Dt

Description	The date upon which the product or inventory item in question was received into inventory. This might be the most recent receipt of volume items such as bed sheets or the actual receipt date of a single high value serial number tracked item such as a piece of medical equipment.
Data Type	Standards - Data Domains.ddm/Data Domains/Date Time [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Serial Number

Description	Unique identifier of a specific instance of an item. Part of UDI, Unique Device 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

Source Cd

Description	The code that corresponds to the Source 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

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

Unit Cost Amt

Description	The unit cost of a single item of inventory. This will be related to the accounting policy for particular inventory items. High cost drugs for example may be accounted for at an individual dose level while paracetamol may be per 100 pills. Similarly hospital beds may be accounted for at an individual bed level while surgical gloves will be counted per box.
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

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	false
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

Inventory Fact_Provider Dimension_FK

Is Identifying Relationship	false
Child Table	Inventory Fact
Child Role Name	purchasing provider
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 Fact_Calendar Dimension_FK

Is Identifying Relationship	false
Child Table	Inventory Fact
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 Fact_Item Master Dimension_FK

Is Identifying Relationship	false
Child Table	Inventory Fact
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

Inventory Fact_Point Of Care Location Dimension_FK

Is Identifying Relationship	false
Child Table	Inventory Fact
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

Primary Key Details

Inventory Fact PK

Key Attribute

Inventory Fact Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model