Modelling inventory quantities - fact or dimension

[kjfischer]

We currently have a part_dimension that is FK referenced in a invoice_detail_fact table.

This part_dimension is only current view with daily updates. Maybe incorrectly, it has a mix of attributes, including descriptive (part_class, cataegory, family, etc), status indicators (discontinued, in_catalog, on_internet, etc), and factual measures (available_quantity, current_cost, current_price, etc).

We have a requirement for viewing daily inventory levels: available_qty, shipping_reserved_qty. This will evolve to a hourly requirement...

I am thinking that in building a new fact table, such as, inventory_snapshot_fact, we would cleanup our part_dimension by removing factual measures and status indicators and create new part_status_dim in the process.

We have recently built a couple of other new fact tables for pricing and cost:
INVENTORY_COST_FACT
PART_DIM_ID
PURCHASE_COST_TYPE_DIM_ID
COST_EFFECTIVE_DATE
PART_ID
MIN_BREAK_QTY
MAX_BREAK_QTY
PART_COST_AMT

INTERNAL_INVENTORY_PRICE_FACT
PART_DIM_ID
VENDOR_DIM_ID
CURRENCY_DIM_ID
PRICE_EFFECTIVE_DATE
PART_ID
MIN_BREAK_QTY
MAX_BREAK_QTY
PART_PRICE_AMT

I am thinking that INVENTORY_SNAPSHOT_FACT would look like this:
PART_DIM_ID
VENDOR_DIM_ID
PART_STATUS_DIM_ID
PART_STATUS_TIMESTAMP
AVAILABLE_QTY
SHIPPING_RESERVED_QTY

And that the PART_STATUS_DIM might be a junk dimension holding all the various statuses that a part could have.

The original PART_DIMENSION could stay current view because it would just have the decriptive attributes which don't really change. The PART_STATUS_DIM might need to be SCD. If the requirement for viewing inventory quantities changes from daily to hourly, is there anything that we should plan for doing differently?

I appreciate any thoughts on this from those of you who have tackled this before.

[elmorejr]

Looks like you are moving down the right path. Moving the part "metrics" into their own facts is a good move. I do have a few questions:

1. You list "date" for some columns; are these real dates or dimension surrogates? If they are dates, I would recommend making these surrogate keys tied to a Date Dimension.

2. Same for the new inventory fact, instead of a timestamp, you should implement a Date Dimension surrogate (grain = 1 row per day) as well as a Time Dimension surrogate (grain = 1 row per second), if the inventory is going to move to an hourly snapshot.

3. Why/how would you implement a SCD on the "status junk" dimension? Junk usually means the status/flags/etc the are used to describe a fact, but do not belong in their own dimension. If the various statuses are truly junk, then I do not think SCD would apply.

[kjfischer]

Thanks for your reply.

We do have a "date dimension". The primary key of the date dimension is a date; it is not a sequence generated key. We currently do not have a time dimension. I have not worked in an environment where near real-time reporting was required.

In the source system, inventory levels (available_qty) is changing fequently, i.e. everytime a part is ordered, then the available_qty is changed. I have looked at Kimball's Human Resource snapshot fact example, but employee facts do not change as rapidly as inventory facts. So, I don't know if the modelling approach is transferable.

Regarding the part status flags and indicators, this is what I want to be able to report:

example,
available_quantity for parts that are available_on_internet and are stocked_in_warehouse

Maybe those are just descriptors and belong or should stay in the part_dimension?

[elmorejr]

Based on your example,

available_quantity for parts that are available_on_internet and are stocked_in_warehouse

I would say these are junk dimension attributes. They change as rapidly as the snapshot is changing (i.e. one hour a part is available in the warehouse, the next hour it could not be). Having your Part dimension change that rapidly would lead to a large number of Type 2 updates.

By putting those into a junk dim, you would still be able to capture the context.

"at this snapshot date (Date dim) and time (Time dim), there were 3 (measure) widgets (Part dim) in warehouse A (Warehouse dim) with a status of X & indicator Z (both in Status Junk dim)"

And if you are taking an hourly snapshot, then yes, your fact will grow very large, quickly (depending on the number of part/warehouse combinations). You may want to consider keeping an hourly fact that only maintains 24 or 72 hours of data. With another fact that keeps a daily snapshot for summary. And possibly other rollups if the daily is also too much data to maintain over time (weekly, monthly, etc.).

[Sponsored content]