Inventory Data Warehouse
The Inventory Data Warehouse provides data to answer business intelligence questions
about inventory transaction cycles. The data warehouse was created by a former database student
for an independent study project. The former student had a strong background about inventory
transaction cycles through his work with the OneWorld product of the former J.D. Edwards (now
part of Oracle).
This document provides background on inventory transaction concepts, details about the
snowflake schema to support inventory transaction cycles, and a data dictionary about the table
design for the data warehouse. You will use the Inventory Data Warehouse for the assignment in
module 5 of course 2 and three assignments in course 3.
The course website contains SQL statements to create and populate the Inventory Data
Warehouse tables for both Oracle and MySQL. In course 2, you can use either Oracle or MySQL
for the assignment in module 5. When using Oracle for the assignment in module 5, you need to
create a sequence. The Oracle CREATE SEQUENCE statement is contained in the document
with Oracle CREATE TABLE statements. In course 3, you need to use Oracle for all
assignments.
1. Inventory Transaction Cycles
Inventory that is bought, sold, consumed, and produced is the heart of any manufacturing
and/or distribution company. Inventory transactions are frequent and commonplace. The
volume and significance of inventory transactions make them important in a data warehouse
design.
Because inventory management is a common and important yet difficult activity in many
organizations, ERP vendors have developed Enterprise Resource Management (ERP) software to
1/13/2021 Inventory Data Warehouse Description Page 2
provide software support. Typically, ERP software provides modules related to Manufacturing,
Distribution/Logistics, Financials, and HR/Payroll. Inventory is at the heart of the
Manufacturing and Distribution/Logistics modules. The work order, sales, and purchase life
cycles affect the perpetual inventory balance as shown in Figure 1. In addition, inventory
transactions including adjustments, transfers, issues, and reclassifications affect the perpetual
inventory balance.
Work Order Sales Purchasing
Life Cycle Life Cycle Life Cycle
Work Order Header Sales Order Entry Purchase Order Entry
Work Order
Pick List Generation Purchase Order Transfer
Generation
Shipment Purchase Order Receipt
Issue/Scrap Parts
Sales Update Create Voucher
Time Reporting
Inventory
Work Order Completions Transactions
Perpetual
Inventory Adjustments
Inventory
Manufacturing Accounting Master DB
Inventory Transfers
Perpetual Inventory
Transactions
Simple Issues
Inventory
Reclassifications
Figure 1: Life Cycles Affecting the Perpetual Inventory Balance
2. Snowflake Schema Description
To support reporting about inventory management, Figure 2 shows a snowflake schema
for the perpetual inventory balance. The snowflake schema provides a template that can be
customized to individual organizations. Dimension entity types such as Addr_Cat_Code1 allow
an organization to customize the design to specific requirements. The fact entity type,
1/13/2021 Inventory Data Warehouse Description Page 3
Inventory_Fact, contains several measures along with relationships to associated dimension
entity types. Several dimension entity types are related directly to the Inventory_Fact entity type.
Other dimension entity types such as Item_Cat_Code1 are indirectly related to the
Inventory_Fact entity type.
date_dim inventory_fact
Summary_by_Day
DateKey InventoryKey trans_type_dim
Inventory_Transaction
CalDay zip_codes BranchPlantKey TranTypeKey
CalMonth ZipKey CustVendorKey TransDescription
CalQuarter ZipCity DateKey TransTypeCodeId
CalYear ExtCost Inventory_Facility
ZipConcec
DateJulian ZipState ItemMasterKey
DayofWeek ZipWeight Quantity
FicalYear TransTypeKey branch_plant_dim
ZipZip
FiscalPeriod UnitCost BranchPlantKey
defines Related_Address_Book BPName
Part_Number_ BranchPlantId
addr_cat_code1 item _cat_code1 CarryingCost
CompanyKey
AddrCatCodeKey ItemCatCodeKey
CostMethod
cust_vendor_dim AddrCatCodeId ItemCatCodeId
AddrCatDesc ItemCatDesc
CustVendorKey item _m aster_dim
AddrBookId Ow ning_Company
ItemMasterKey
AddrCatCode1 Addr_Code1 Item_Code1 ItemCatCode1
AddrCatCode2 ItemCatCode2
Addr_Code2 Item_Code2
Address ItemDesc com pany_dim
City SecondItemId CompanyKey
Country ShortItemId
addr_cat_code2 item _cat_code2 CompanyId
Name ThirdItemId
AddrCatCodeKey ItemCatCodeKey CompanyName
PrimZip UOM
AddrCatCodeId ItemCatCodeId CurrencyCode
State
AddrCatDesc ItemCatDesc CurrencyDesc
Zip
Figure 2: Generic Snowflake Schema for the Perpetual Inventory Balance
The ERD representation of the snowflake schema is converted to a table design using the
normal conversion rules. In the conversion process, 1-M relationships convert to foreign keys in
the child tables. Appendix A contains a data dictionary for the table design.
1/13/2021 Inventory Data Warehouse Description Page 4
Appendix A: Data Dictionary for the Snowflake Schema Table Design
Appendix A contains a brief description of each column in the tables of the Inventory
Data Warehouse Schema. A number of columns are based on the Oracle OneWorld product
specifications.
Address Category 1 Table (addr_cat_code1)
This table defines address category codes related to customers/vendors. These codes allow
customers/vendors to be group. Example grouping might be customer type, customer area, etc.
AddrCatCodeKey Unique primary key value
AddrCatCodeId Four character category code
AddrCatDesc Thirty character category code description
Address Category 2 Table (addr_cat_code2)
This table defines address category codes related to customers/vendors. These codes allow
customers/vendors to be group. Example grouping might be customer type, customer area, etc.
AddrCatCodeKey Unique primary key value
AddrCatCodeId Four character category code
AddrCatDesc Thirty character category code description
Item Category 1 Table (item_cat_code1)
This table defines item master category codes related to item masters (parts) These codes allow
part numbers to be group. Example grouping might be product class, spare part, finish good, etc.
ItemCatCodeKey Unique primary key value
ItemCatCodeId Four character category code
ItemCatDesc Thirty character category code description
Item Category 2 Table (item_cat_code2)
This table defines item master category codes related to item masters (parts) These codes allow
part numbers to be group. Example grouping might be product class, spare part, finish good, etc.
ItemCatCodeKey Unique primary key value
ItemCatCodeId Four character category code
ItemCatDesc Thirty character category code description
1/13/2021 Inventory Data Warehouse Description Page 5
Zip Codes Table (zip_codes)
This table provides the basis to create many unique customer records for a variety with a variety
of zip codes.
ZipKey Primary Key, user defined.
ZipCity City related to zip code
ZipState State related to zip code
ZipZip Zip Code
ZipConsec The zip code plus this number define the range of zip codes for this city
ZipWeight The weight (percentage * 100) that will be applied to creating customers. All
ZipWeight columns totaled should equal 100.
Date Sequence Table (date_dim)
This table provides the date pattern. Date patterns can be daily, five days per week, weekly or
monthly.
DateKey Unique primary key value
DateJulian Julian date in the form of YYYYDDD. Where YYYY is the year and DDD is
the sequential date.
CalDay Calendar day from 1 to 31.
CalMonth Calendar month from 1 to 12
CalQuarter Calendar quarter from 1 to 4
CalYear Calendar year valid for ranges from 1900 to 2100
DayOfWeek Day of the week, 1 to 7, 1 is Sunday, 2 is Monday, etc
FiscalYear Corresponding Fiscal Year
FiscalPeriod Corresponding Fiscal Period
Transaction Type Table (trans_type_dim)
This table defines the various types of inventory transactions. Examples include transfers,
adjustments, shipments, receipts, etc. Some of the codes may not be used in the sample data for
the trans_type_dim table.
TransTypeKey Primary Key, coded to the following values.
TransTypeId =1 then inventory adjustment (IA)
TransTypeId =2 then inventory transfer (IT)
TransTypeId =3 then inventory simple issue (IS)
TransTypeId =4 then purchase order receipt (OV)
TransTypeId =5 then sales order shipment (AR)
TransTypeId =6 then mfg issue (IM)
TransTypeId =7 then mfg completion (IC)
TransTypeId =8 then mfg parent scrap (IS)
TransTypeId =9 then mfg component scrap (IZ)
TransTypeCodeId Corresponding One World cardex code, an example is IA for an adjustment.
TransDescription Transaction Type Description
1/13/2021 Inventory Data Warehouse Description Page 6
Customer Vendor Table (cust_vendor_dim)
This table defines possible customers and vendors involved with related sales and purchasing
related transactions.
CustVendorKey Unique primary key value
AddrBookId One World related address book number
Name Customer Name
Address Address
City
State
PrimZip Integer form of the zip code
Zip Zip code that could be in various forms (nnnnn, nnnnn-nnnn, etc)
Country Country
AddrCatCode1 OneWorld related Category code, foreign key to the address category code 1
AddrCatCode2 OneWorld related Category code, foreign key to the address category code 2
Item Master Table (item_master_dim)
This table defines item masters (ie part numbers).
ItemMasterKey Unique primary key value
ShortItemId OneWorld related short item id
SecondItemId OneWorld related 2nd item number
ThirdItemId OneWorld related 3rd item number
ItemCatCode1 OneWorld related category code, foreign key to the item category code1 table
ItemCatCode2 OneWorld related category code, foreign key to the item category code2 table
ItemDesc OneWorld related item master description
UOM OneWorld related primary unit of measure
Company Table (company_dim)
This table contains company records including the base currency.
CompanyKey Unique primary key value
CompanyId OneWorld related 5 character company id
CompanyName OneWorld related company name
CurrencyCode OneWorld related currency code
CurrencyDesc OneWorld related currency description
Branch Plant Table (branch_plant_dim)
This table contains the Branch Plant information.
BranchPlantKey Unique primary key value
BranchPlantId JDE related Branch Plant Id (12 character MCU)
CompanyKey Owning company for this branch, foreign key to Company table.
CarryingCost Carrying Cost percentage defined as a decimal
CostMethod OneWorld related Cost Method.
1/13/2021 Inventory Data Warehouse Description Page 7
BPName OneWorld related Branch Plant Name
Inventory Transaction Fact Table (inventory_fact)
This table contains the inventory transactions facts. Integer keys are used to help limit the size of
the rows. The measures are unit cost, quantity, and extended cost. InventoryKey is generated by
an Oracle sequence object (inventory_seq) in the data integration assignment in module 5 of course 2. For
the MySQL assignment in module 5, InventoryKey has an auto increment data type.
InventoryKey Unique primary key value
BranchPlantKey Transaction Branch, Foreign key to the branch plant table
DateKey Transaction Date, foreign key to the date table
ItemMasterKey Transaction Part Number, foreign key to the item master table
TransTypeKey Transaction Type, foreign key to the transaction type table
CustVendorKey Optional address book key that is a foreign key to the customer vendor table.
This column allows null values. The column is not null only on sales and
purchasing transactions.
UnitCost Unit cost with up to 4 decimals of precision
Quantity Quantity with up to 4 decimals of precision
ExtCost Extended Cost with up to 2 decimals of precision
);