Inventory Kimball & Ross, Chapter 3OverviewValue ChainInventory ModelsInventory Periodic Snapshot ModelInventory Periodic Snapshot Model - ChallengeSemiadditive FactsEnhanced Inventory FactsInventory Transactions ModelInventory Transactions Model - Con’tInventory Accumulating Snapshot ModelValue Chain IntegrationData Warehouse Bus ArchitectureData Warehouse Bus Architecture – Cont’dData Warehouse Bus MatrixConformed DimensionsTypes of Dimension ConformityCentralized Dimension AuthorityConformed FactsAcknowledgementsInventoryKimball & Ross, Chapter 3OverviewValue chain implicationsInventory periodic snapshot model, transaction and accumulating snapshot modelsSemi-additive factsEnhanced inventory factsData Warehouse bus architecture and matrixConformed dimensions and factsValue ChainThe value chain identifies the natural, logical flow of an organization’s primary activities. See Fig. 3.1Operational source systems produce transactions or snapshots at each step in the value chain. They generate interesting performance metrics along the way.Each business process generates one or more fact tables.Inventory ModelsInventory periodic snapshotInventory level of each product measured daily (or weekly) – represented as a separate row in a fact tableInventory transactionsAs products move through the warehouse, all transactions with impact on inventory levels are recordedInventory accumulating snapshotOne fact table row for each product updated as the product moves through the warehouseInventory Periodic Snapshot ModelBusiness needAnalysis of daily quantity-on-hand inventory levels by product and storeBusiness processRetail store inventoryGranularityDaily inventory by product at each storeDimensionsDate, product, storeFactQuantity on handInventory Periodic Snapshot Model - ChallengeVery dense (huge) fact tableAs opposed to retail sales, which was sparse because only about 10% of products sell each day60,000 items in 100 stores = 6,000,000 rowsIf 14 bytes per row: 84MB per dayOne-year period: 365 x 84MB = 30GBSolution: Reduce snapshot frequencies over timeLast 60 days at daily levelWeekly snapshots for historical dataFor a 3-year period =208 snapshots vs. 3x365=1095 snapshots; reduction by a factor of 5Semiadditive FactsInventory levels (quantity on hand) are additive across products or stores, but NOT across dates = semi-additive factsCompare to Retail Sales:once the product is sold it is not counted againStatic level measurements (inventory, balances…) are not additive across date dimension; to aggregate over time use average over number of time periods.Enhanced Inventory FactsNumber of turns = total quantity sold / daily average quantity on handDays’ supply = final quantity on hand / average quantity soldGross profit = value at latest selling price - value at costGross margin = gross profit / value at latest selling priceGMROI (Gross Margin Return On Inventory) GMROI = number of turns * gross marginmeasures effectiveness of inventory investmenthigh = lot of turns and more profit, low = low turns and low profitNeed additional facts: quantity sold, value at cost, value at latest selling price GMROI is not additive and, therefore, is not stored in enhanced fact table. It is calculated from the constituent columns.Inventory Transactions ModelRecord every transaction that affects inventoryReceive productPlace product into inspection holdRelease product from inspection holdReturn product to vendor due to inspection failurePlace product in binAuthorize product for salePick product from binPackage product for shipmentShip product to customerReceive product from customerReturn product to inventory from customer returnRemove product from inventoryInventory Transactions Model - Con’tDimensions: date, warehouse, product, vendor, inventory transaction type.The transaction-level fact table contains the most detailed information possible about the inventory.It is useful for measuring the frequency and timing of specific transaction types.It is impractical for broad data warehouse questions that span dates or products. To give a more cumulative view of a process, some form of snapshot table often accompanies a transaction fact table.Inventory Accumulating Snapshot ModelBuild one record in the fact table for each product delivery to the warehouseTrack disposition of a product until it leaves the warehouseReceivingInspectionBin placementAuthorization to sellPickingBoxingShippingThe philosophy of the inventory accumulating snapshot fact table is to provide an updated status of the product shipment as it moves through above milestones.Rarely used in long-running, continuously replenished inventory processes.More on this in chapter 5.Value Chain IntegrationBoth business and IT organizations are interested in value chain integrationDesire to look across the business to better evaluate overall performanceData marts may correspond to different business processesNeed to look consistently at dimensions shared between business processesNeed an integrated data warehouse architectureIf dimension table attributes in various marts are identical, each mart is queried separately; the results are then outer-joined based on a common dimension attribute = drill acrossData Warehouse Bus ArchitectureCannot built the enterprise data warehouse in one step.Building isolated pieces will defeat consistency goal.Need an architected incremental approach data warehouse bus architecture.See Fig. 3.7By defining a standard bus interface for the data warehouse environment, separate data marts can be implemented by different groups at different times. The separate data marts can be plugged together and usefully coexist if they adhere to the standard.Data Warehouse Bus Architecture – Cont’dDuring architecture phase, team designs a master suite of standardized dimensions and facts that have uniform interpretation across the enterprise.Separate data marts are then developed adhering to this architecture.Data Warehouse Bus MatrixSee Figure 3.8The rows of the bus matrix correspond to business processes data martsSeparate rows should be created if:the sources are different,the processes are different, ora row represents more
View Full Document