Complex Inventory Model
3 posters
Page 1 of 1
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?
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?
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
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.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Complex Inventory Model
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.
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
My question is...does it make sense to only load items that have stock?
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
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)?
Re: Complex Inventory Model
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.
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.
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
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.
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.
Re: Complex Inventory Model
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.
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????
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????
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
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?
Re: Complex Inventory Model
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?
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?
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
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.
Re: Complex Inventory Model
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?
kclark- Posts : 70
Join date : 2010-08-13
Re: Complex Inventory Model
Oh ok, i thought there was some standard on handling nulls from a design standpoint. Thanks.
kclark- Posts : 70
Join date : 2010-08-13
Similar topics
» How to model a complex region dimension
» Inventory Model
» Inventory model: aging
» Complex Dimensional Model Help - With History Product to Part
» Help with Complex One-to-Many relationships
» Inventory Model
» Inventory model: aging
» Complex Dimensional Model Help - With History Product to Part
» Help with Complex One-to-Many relationships
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|