Fact type: daily inventory
Recognize when inventory reporting needs a periodic snapshot fact.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
An operations team needs end-of-day inventory on hand by product and warehouse for every day, even when no movement happened that day.
Inventory on hand is state. Movement events explain changes, but they do not directly store the end-of-day balance analysts need to trend.
- Pick the fact type that records one product, one warehouse, and one date per row.
- Include an on-hand quantity measure at that periodic grain.
- The answer chooses a periodic snapshot fact.
- The explanation states that one row represents product plus warehouse plus snapshot date.
- Ask whether the business needs state at regular intervals.
- A day with no movement can still need a row.
- Using only movement transaction events and forcing every report to recalculate state.
- Storing current inventory on a product dimension and losing history.
Kimball periodic snapshot for daily inventory
One row represents a product, warehouse, and snapshot date, with on-hand quantity as the measure. A Kimball periodic snapshot fact.
Optimizes for
- • End-of-day balance reporting
- • Inventory trend analysis
- • Fast daily operations dashboards
Trade-offs
- • Stores rows even when no movement occurs
- • Movement explanations still need transaction facts
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.