Data Modeling Best Practices

Chapter 1 · Practice 3 of 4

Prefer the most detailed grain the source data supports

From Start from the business process and the grain

Why it matters

Detailed facts can always be rolled up; summarised facts cannot be split back down. Microsoft's fact table guidance notes that while you can store facts at a higher granularity, it is not easy to split measure values to lower levels later, so you trade detailed analysis for size.

Start with the atomic grain, such as each order line, and build aggregate tables on top when queries need speed. Microsoft calls these aggregate fact tables: rollups of a base fact table that exist to accelerate common queries. The base table stays the source of truth.