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 assignments 3 to 5.
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 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.
EMBED Visio.Drawing.6
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, 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.
Figure 2: Oracle Snowflake Schema for the Inventory Data Warehouse
In the Oracle schema diagram, the solid lines are required relationships with foreign keys having a NOT NULL constraints. Dashed lines indicate optional relationships in which foreign keys allow null values. Appendix A contains a data dictionary for the table design.
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
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
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.
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
);
DATE \l 1/8/2017 Inventory Data Warehouse Description Page PAGE 7