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.

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.

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. A ratio or unit price may require a different aggregation from an additive count.

Reference: Kimball Group: Fact Tables.