Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Inventory model: aging

2 posters

Go down

Inventory model: aging Empty Inventory model: aging

Post  AtoDW Thu Oct 18, 2012 9:30 am


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.


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

Back to top Go down

Inventory model: aging Empty Re: Inventory model: aging

Post  BoxesAndLines 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.

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

Back to top Go down

Back to top

- Similar topics

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