Dimensional modeling for sales fact with product and inventory dimension

Question

I am building a dimensional model for sales analysis that has a fact called Sales and is linked with a Product dimension.

Point is that for each day the Product inventory will change, and this information is important for them to analyse why a specific product wasn't sold (for example, on day XX/XX the product 123456 wasn't sold because there where no products in the inventory).

I'd like to know the best option to modeling this situation and if possible a short explanation about how it'd work.

Thanks in advance!

Answer 1

This is a pretty broad discussion question, so here' some discussion.

Dimension tables

--  Products  -----
ProductId
Name
 (etc.)

Contains one row per product being tracked ProductId should be a surrogate key

--  Time  --------
TimeId
ReportingPeriod (Q1, week 17, whatever as desired)
 (etc.)

Contains one row for every day being tracked. Once the results of a day’s activities are known, it can be added to the warehouse

Note that TimeId does not have to be a surrogate key

Fact tables

--  Inventory  -------------------------
ProductId
TimeId

Once the results of a day’s activities are known, they can be added to the warehouse One row (per day) for each product, listing the inventory available as of the end of that day

But then it gets complex: just what data is needed, and what data is availabe? Assuming the data is for one day, possible facts to track and record include:

StartingInventory  -- What you had at the start of the day
UnitsReceived  --  Units received for storage today
UnitsSold  --  Units sold (that cannot be sold again) but not yet shipped
UnitsShipped  --  Units shipped (sold or otherwise)
EndingInventory  --  Units in stock at end of day

It gets complex fast. Again, much depends on what information you have available and what questions will be asked of your warehouse.