Balances, Differential Facts & Inventory History

A long history of inventory snapshots by product-location or of financial positions by book/account-CUSIP can be very large, costly to maintain and inefficient to query. Storing differences from day-to-day rather than the complete snapshot reduces the volume if only a few of the products or positions are affected from day to day. In a typical retail situation, about 10% of the inventory changes in a day, making such a differential approach appealing. In financial systems, a much smaller number of position changes each day, however it is common to include value of the position as a metric, resulting in nearly all positions changing daily. For those systems a differential approach would make sense only if query-time valuation can be included and proves cost-effective. For inventory balances, the approach uses the folloiwng data:

• End-of-Week table (EOW) that stores a complete snapshot of inventory for the complete assortment of products for each location
• Daily Delta Table (DELTA) that stores deltas from day to day only for changed positions.

The inventory as of any day is computed by selecting the qualifying rows from the end of last week (EOW) and all intervening daily deltas (DELTA) up to the date required, and summing corresponding keys. A view can be setup to perform this computation. There are some issues that need particular attention:

1. Correct handling of non-additive attirbutes, such as Indicators and status codes
2. Addressing the performance of assortment queries, i.e. what products are sold by a store, or how many stores carry a particular product.
3. Addressing Out-of-Stock (OOS) requirements. The problem with OOS queries is that they are looking for missing inventory, i.e. querying for the absence of something.
4. Address high movement filtering requirements. Rules for such filtering may be too complex to apply directly
5. Adderss trend and graph queries over long time ranges of inventory history. These queries if executed using the EOW+DELTA approach may be very costly. Special alternatves, on the other hand, can be very efficient.