Inventory model: aging

View previous topic View next topic Go down

Inventory model: aging

Post  AtoDW on Thu Oct 18, 2012 9:30 am

Hi,

I am modelling a data warehouse for a car parts seller. For inventory I designed a weekly enhanced snapshot fact table that stores, among other measures, weekly sales and gross profit for each sku. This way I can easily retrieve useful kpis such as GMROI, rotation, etc.

Chances are that at some point a new analysis requirement will emerge: tracking the age of parts, i.e. how much time has passed by from last sale. Parts value at hand can be divided by last-sold-period (say, last 3 months, 3-6 months, etc.).

I am thinking about the best solution for addressing this kind of analysis. The possible alternatives I can spot are:

1) Date of last sale in fact table
2) Number of days since last sale in fact table
3) Additional dimension with some attributes, e.g. months-since, weeks-since, etc.

The 2) is my preferred choice, though this measure is not additive at all. Maybe some quantitative info can still be derived, such as a weighted average aging stock.

What would you suggest? Many thanks in advance for your comments.
Regards

AtoDW

Posts : 2
Join date : 2009-08-01
Location : Salerno, Italy

View user profile

Back to top Go down

Re: Inventory model: aging

Post  BoxesAndLines on Fri Oct 19, 2012 3:52 pm

Sales and Inventory are two different facts. I would start from that point of view. Unless each part is uniquely identifiable, trying to link which sale debited which inventory item is futile. At an aggregate level you can see how sales is impacting quantity to adjust inventory levels.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

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