Fact type: claim lifecycle
Recognize when a workflow with milestone dates needs an accumulating snapshot fact.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
An insurance operations team needs to track each claim from opened to assigned, approved, paid, and closed, with elapsed days between milestones.
Workflow analysis fails when every milestone is isolated and analysts cannot see the end-to-end path for one claim.
- Pick the fact type that keeps one row per claim workflow.
- Represent milestone dates as columns that are filled as the workflow advances.
- The answer chooses an accumulating snapshot fact.
- The explanation states that one row represents one claim lifecycle.
- Ask whether the same business object moves through a known sequence.
- Milestone dates are clues that one row can accumulate over time.
- Creating disconnected milestone facts when the primary question is elapsed pipeline time.
- Using a periodic snapshot even though the business wants workflow progression.
Kimball accumulating snapshot for claim lifecycle
One row represents one claim and carries milestone dates that update as the workflow progresses. A Kimball accumulating snapshot fact.
Optimizes for
- • Elapsed-time analysis
- • Workflow bottleneck diagnosis
- • One-row lifecycle reporting
Trade-offs
- • Milestone updates rewrite the workflow row
- • Independent event analysis may 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.