How should I model conversion events?
For instance, if a sales opportunity goes from "stage: prospecting" to "stage: validating", should that be treated as a changing dimension, or a conversion event in the fact table?
Max
Modeling Conversion Events in a Data Warehouse
business-intelligencedata-warehousedimensional-modeling
Best Answer
This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that involve a predefined series of steps. The timestamped.
You'll have a date key in the table that represents the date the opportunity hit each stage. If you need to reproduce what pipeline looked like on a given day or track the fact that an opportunity went into and out of a stage multiple times, you might need to consider the timestamped accumulating snapshot.