October 25, 2015 at 1:21 pm
I need to implement a daily inventory snapshot fact table but am a little unsure about the best way to go about it.
The basic requirement is to show the number of products on hand in each location at the beginning of each day. I know I can do that with a simple Inventory Fact table together with location, calendar and product dimensions.
e.g.
SKU123 Store1 20152410 qty=56
SKU123 Depot1 20152410 qty=200
However the business has decided that if the inventory is in a store (rather than in a depot) then they want to break the total quantity down into the quantity in the stock room, quantity on the shop floor, quantity off site etc
Is there a "best" way to do this?
Should I extend the original Fact table to include the quantities at each sub location?
E.g.
SKU123 Store1 20152410 total qty=56 stockroom=25 salesfloor=31 offsite=0
Or should I treat the stock room/salesfloor etc as locations in their own right and have one row in the fact table per "sub location"?
Eg.
SKU123 Store1_stockrm 20152410 qty=25
SKU123 Store1_salesfloor 20152410 qty=31
If I chose this second option I don't understand how I can handle the inventory held in a depot in the same fact table since they exist at a "higher" level in the organisational structure. (Can I have locations at different levels of a hierarchy in the same fact table?). Would I need separate fact tables - one for store/depot and one for sub locations?
Does the fact that there are more than 1m products alter the approach I should take?
Any help would be much appreciated. This is my first professional venture into dimensional modelling so I'd like to get it right!
October 26, 2015 at 9:10 am
Considering the fact that your end users would most likely want to see the sub-location numbers in different columns on the same line and totaled for a store, and the fact that the number of sub-locations are not likely to change/increase...I would go with the first option and create separate columns in the same snapshot fact table.
To achieve the same results through the second option would not be impossible, but I think it would unnecessarily complicate the structure and reporting...unless there is a very good reason to do so.
Edit: And yes, this is a pretty common scenario in a retail environment. I typically deal with it by using the first option.
October 26, 2015 at 9:36 am
Brilliant. Thanks. I was obviously overcomplicating things so thank-you for putting me on the right track.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply