Complex Inventory Model

View previous topic View next topic Go down

Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 1:11 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  Jeff Smith on Fri Oct 01, 2010 2:13 pm

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

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 2:29 pm

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

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 3:01 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  ngalemmo on Fri Oct 01, 2010 4:08 pm

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)?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 4:48 pm

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.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  ngalemmo on Fri Oct 01, 2010 5:04 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 5:32 pm

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????

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  ngalemmo on Fri Oct 01, 2010 5:39 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Fri Oct 01, 2010 5:51 pm

Correct.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Mon Oct 04, 2010 10:21 am

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  ngalemmo on Mon Oct 04, 2010 12:23 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Mon Oct 04, 2010 4:11 pm

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

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  ngalemmo on Mon Oct 04, 2010 4:18 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Complex Inventory Model

Post  kclark on Mon Oct 04, 2010 4:42 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Complex Inventory Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum