Adv. DBS and Data Warehouse CSC5301 Ch3 Hachim Haddouti

1. Adv. DBS and Data WarehouseCSC5301Ch3Hachim Haddouti Hachim Haddouti

2. Chapter 3: The Warehouse moving up the value chain (not only sold products will be measured) Inventory levels: another semiadditive fact “Traditional inventory levels are not additive across time, because they represent snapshots of a level or a balance.” Compare to Grocery Store: once the product was sold it could not be counted again most measures in grocery store are additive.

3. Design Principle All measures that record a static level, such as inventory levels, financial account balances, and measures of intensity such as room temperatures, are inherently nonadditive across time. However, in these cases the measure may be usefully aggregated across time by averaging over the number of time periods. Note: SQL AVG????

4. Inventory Models •   Three Inventory models • The inventory snapshot model • The delivery status model • The transaction model

5. The inventory snapshot model 3 standard dimensions: • time, product, warehouse • no customer or store dimension unless allocated -- assigned to a purchaser • No Promotion dimension, why? • Fact: QOH Example: Every day or week we measure the inventory level and store the results in DB.

6. The inventory snapshot model (cont.) • Problem: very dense table. Huge! (grocery: sparse bcs only about 10 % sold each day) • 100,000 items in 2,000 stores * • 3*365 days = 220,000,000,000 records. • Terabytes of data. • Solution: Sparse spacing over time ( last 30 days at day level, then weekly for 11 months, then monthly for the prior 2 years = 102 snapshots). • Delete some older snapshots! Gross margin return on inventory (GMROI)

7. Simplest Inventory Schema Inventory Fact Time dim Time_key Product_key Warehouse_key Quantoty_on_hand Product dim Warehouse dim Only time series of inventory level of each product!!

8. GMROI: used to judge the quality of investment in inventory • number of turns -- qty shipped/qty on hand • days supply -- final qty on hand/avg qty shipped • gross profit -- value at selling price - value at cost • gross margin -- gross profit/value at selling price • GMROI (Gross Margin Return On Investment) -- number of turns * gross margin •  measuring effectiveness of inventory investment (high  lot of trun, low low truns) • extra fields: • qty shipped, value at selling price, value at cost (fully additive values)

9. The delivery status model • Build one record in DB for each product delivery to • warehouse • Track a series of well-defined events for a shipment. • Inventory steps: • received • inspected, • placed into inventory, • authorized to sell • picked from inventory • boxed • shipped. • exception conditions: failed inspection, damaged, lost, • returned, written off • “The philosophy of the delivery status fact table is to provide a continually updated status of the products received on a given purchase order.”

10. The delivery status model (cont.) • Dimensions: time, warehouse, product, vendor • Delivery Satuts table has 26 fields classified as follow: • <degenerate dim>, PO_number e.g. in order to group all the products purchased in one purchase order, OR to reference documents. • <auxiliary date fields>, measure spans of time • <additive quantities> • <unit price and costs>, will be used with each set of qty fields, e.g. to make value for shipped inventory at cost , at original selling price etc. Note: • Views of numerical date difference, such as Product Delivery Time, Receipt to Authorized Time. • Views to present 52 combination of Unit, Cost and quantities

11. Design Principle Document control numbers such as order numbers, invoice numbers, and bill of lading numbers usually are represented as degenerate dimensions (i.e., dimension keys with no corresponding dimension table) in fact tables where the grain of the table is the document itself or a line item in the document.

12. Delivery Status Schema, Inventory Delivery Status Time dim <Dim keys> <degenerate dim> <auxiliary date fields> <additive quantities> <unit price and costs> Product dim Vendor dim Warehouse dim

13. The transaction model: • Record every transaction that affects the inventory; • inventory transactions include, typically less than 100: • receive shipment line item • place into inspection • release from inspection • authorize for sale • pick from bin • package for shipment • Ship • bill customer • ...

14. The transaction model cont. Dimensions: time, warehouse, product, transaction. Contains the most detailed information possible about the inventory. Design Principle: “Transaction-level fact tables have a characteristic structure, with as much surrounding context as possible expressed in conventional dimensions. Frequently, a degenerate dimension such as a purchase order number is present. The list of facts is almost always a single amount field.”  Deducing more context, such as warehouse identification

15. DW Sizing: Food distributor, snapshot: 730 days x 60,000 products x 8 warehouses = 350,000,000 records fact table size = 350M x 4 fields x 4 bytes = 5.6G Garment retailer, delivery status: 2 years x 400,000 products x 4 warehouses x 1 vendor x 10 orders = 32,000,000 records size = 32M x 30 fields x 4 bytes = 3.8G Garment retailer, transaction: 2 years x 400,000 products x 4 warehouses x 1 vendor x 10 deliveries x 20 transactions = 640,000,000 records size = 640M x 6 fields x 4 bytes = 15.4 G