Data Grain

Data grain describes what one row represents. It can be one order, one order line, or one day's total for one shop. State that meaning before choosing keys or combining tables; a list of column names alone may not explain it. It is also the first thing a metric definition has to fix, because a measure is only additive with respect to a stated grain.

One order can become several rows

Suppose an order table has one row per order, and a support table has three cases for that order. Joining on order ID produces three matching rows. Counting those rows gives three, although there is only one order. Summing the repeated order total can also triple it. That inflation follows from the join’s cardinality rather than from any error in the data: the rows on the one side are repeated, so a measure stored there is added once per match.

Counting distinct order IDs fixes that particular count, but does not fix every measure. Decide whether to aggregate support cases first, preserve a different output grain, or calculate each measure separately.

The counted entity and output grain can differ

A daily shop report can have one row per day and shop while its measure counts distinct orders. Its row grain is day-and-shop, not order.

When combining data, check keys, uniqueness, and how each measure behaves at the intended grain. Measures that cannot be summed across every dimension need naming as such — a balance that may be added across accounts but not across days is semi-additive, and a ratio is usually not additive at all. A ratio or unit price may require a different aggregation from an additive count.

References

• Kimball Group: Fact Tables