inventory snapshot - GMROI calculation

Hoping someone can advise on this.

I've got the DW Toolkit book and it's been invaluable in setting up a simple retail sales fact table with conformed dimensions. We've now moved on to chapter 3: inventory and our users have requested a stock snapshot, accumulating over many days. I've been reading the section on Enhanced Inventory Facts and I'm wondering if I should include these. Currently my fact table looks like this:

s_date_id (date skey)
s_prod_id (product skey)
s_loc_id (location skey)
qty_on_hand (DECIMAL(12,2))

The chapter in the book makes reference to Quantity Sold, Dollar Value at Cost, and Dollar Value at Latest Selling Price. Quantity sold I can understand and can derive from the sales fact table. But what about the dollar value at cost? Is this updated daily based on the current cost price, or should I use an aggregated total cost from the sales fact table? (we're not yet tracking cost price changes on the product dimension through Type 2 SCDs)

My second question: Our business operates a fairly large proportion of "special order" items, which typically we carry no stock of. Should I be writing these as "zero" quantity on hand records, or is it enough to have an absence of data (as such the stockouts are assumed). Just concerned about growth of database size.

Thanks for reading!

I'm going to be doing the exact same thing shortly...

The dollar value at cost is going to be essential for your GMROI calculation. It's the total dollar value of your on-hand quantity in terms of what you paid for it. Typically, you would get this from your OLTP system if it tracks costs (dollar-cost averaging). This shouldn't be "total on-hand quantity times the last price we paid a supplier for any of it". It ought to be a "pooling" of the inventory cost over time. However, you can make do with the "last cost" in a pinch - it's better than nothing - but your users will not get first quality information, especially if pricing is changing quickly, or you happen to carry large quantities of inventory.

As for writing zero-quantity records: As I understand it, this is also essential. You're making a periodic snapshot type table, which is known to get very large, and sparse (lots of zero values). The main problem with eliminating the zero entries is with average calculations - which you'll also be doing with GMROI. If you happen to have a product in stock only once during the year (montly measurements) - say 10 units in January, is the "average" stock level 10? Or is it 0.8? You would naturally say 0.8 units. However, I understand it's near impossible or extremely slow to formulate a query that "knows" there should be twelve snapshots, and to divide the total by twelve, instead of the actual number of rows found.

thanks for your comments, they've been a great help. I've got created a temporary fix so management can query basic results, but am working on another fact that will add functionality.

Not sure if I should raise this in a new thread, but now I'm trying to drill across, hittting a bit of a wall. I've read design tip #68 and can follow the SQL code given, but it confuses me that the two subqueries are using an inner join. Surely you would have to use a full outer join or something similar, to cover areas where you have sales but no stock, or stock but no sales (an example that fits with the forecasting vs actuals would be a delayed store opening etc). Isn't this likely to be slow, especially for complicated queries?

Would I for situations such as this be better off doing a UNION in a subquery, then summing the results across the common column headings?

Actually, yes and no - an INNER JOIN is correct for that example.

However, in your situation - for the same reason that you tried to "avoid" above, an OUTER JOIN is still not required :). Your inventory snapshot table should contain one record per "current" entity in your product dimension. Your sales fact table cannot contain facts for products that are not in your product dimension table. Therefore, every product referenced in your sales facts MUST be in your inventory snapshot table. Now, in your case, if you want to see inventory information for products that have no sales, you would need to use a LEFT JOIN. But if your drill-through requires information from both fact tables, an INNER JOIN is required.