Complex Inventory Model

I am designing an inventory model with dimensions: Product, Location, Time, and Collection Type.
The relationships here are:
1. A Location is assigned Items that they can only have in stock
2. A Collection is assigned Items.
3. A Location is assigned Collections.

My fact looks like this:
Location_Skey
Item_Skey
Collection_Skey
Date_Skey
Optimal Quantity
Quantity On Hand
Reorder Threshold

My question here is when loading the fact...would I only load the items that actually have stock in...quantity on hand is greater than 0?

It depends on what you want to report from the Fact Table. If you want to be able to report the % of optimal quantity that is in stock, then you need Optimal Quantity in the Fact table.

Well looking at the average inventory on hand by location to minimize the number of automatic reorders placed by location. So if the optimal quantity is too low because of the number of orders placed for the item is too high per month then the optimal quantity would need to be increased. Or a change in the reorder threshold.

My question is...does it make sense to only load items that have stock?

kclark wrote:Well looking at the average inventory on hand by location to minimize the number of automatic reorders placed by location. So if the optimal quantity is too low because of the number of orders placed for the item is too high per month then the optimal quantity would need to be increased. Or a change in the reorder threshold.

Doesn't your inventory system do that already? What is it you are trying to accomplish with the inventory model? Is optimal quantity and reorder threshold a function of a combination of product and location or are they attributes of product (SKU)?

Well the INVENTORY model is complex because of the system setup. So we have to create different datamarts targeting the different goals...which the facts are linked by conformed dimensions. So here we are looking at the efficiency of the stock collection.

There are essentially 3 relationships here:
1. Item linked with Location (QuantityOnHand, QuantityOnOrder)
2. Item linked with a Stock Collection - A stock collection contains many items (Optimal Quantity, Reorder Threshold)
3. Location linked with a Stock Collection

All are many to many relationships.

Is a "stock collection" a mechanism used by your inventory system to assign reorder, threshold, and I would imaging counting, rules to a bunch of different items?

It would seem to me your measures are quantity on hand and quantity on order. Optimal quantity and reorder thresholds are attributes of the collection dimension rather than measures in the fact table.

Do you store rows with zero measures? That depends. It would seem to me that if an item is supposed to be inventoried at a location but at the moment there is no inventory, I would want to see a zero row. However, I would not create fact rows for items that are not inventoried in a particular location.

Ahhh, yes I see what you are saying.

Yes a stock collection assigns the thresholds and optimal quantity but per item...as the same item could be in multiple collections but have different values in for those fields. It's the association of the stock collection and item that determines those values.

I would not create fact rows for items that are not inventoried in a particular location

That was the original question because we don't have a "stock collection item" dimension so we have to load only what's in inventory into the fact. Good then that's ok. What do you think as far as the optimal quantity and threshold "attributes"...create a bridge for that????

I don't think you need a bridge... The fact row would have date, item, location and possibly other dimensions in addition to stock collection, so at that level of detail, the measures on that row would only be associated to one stock collection... correct?

Correct.

Sorry, just make sure I am understanding correctly.

If an item is SUPPOSED to be inventoried at a particular location but at the moment there is no inventory then load a zero row to the fact for that particular item at that particular location...right?

That what I would do. I would imagine your inventory system would have an entry for it. Having a row in the fact would help distinguish between out of stock items and items you don't stock at all.

I think the inventory system is storing nulls for items that have not been ordered yet as opposed to items that are out of stock (being that there was stock previously from an order). Should I load the null as a zero?

Don't know. Some of this stuff needs to be discussed with the business.

Oh ok, i thought there was some standard on handling nulls from a design standpoint. Thanks.