Transaction vs Periodic vs Accumulating
Difference between 3 Fact Tables
| Transaction | Periodic | Accumulating |
|---|---|---|
| Stores lowest grain of data. | Stores current state of data at a regular interval of time. | Stores intermediate steps that has happened over a period of time. |
| one row per Transaction | one row per time period | one row per entire lifetime of the event |
| Date dimension is lowest table | Date/time is regular interval of snapshot frequency | multiple data dimensions for intermediate steps |
| Easy to aggregate | minimal aggregation | not easy to aggregate |
| largest database size | Medium Database size | Smallest Database Size |
| Only Insert | Only Insert | Insert and Update |
| Most business requirement | business performance is reviewed at regular interval | the Business process has multiple stages |