Granularity (grain)

What is granularity?

Granularity, also called grain, answers one question: what does one row in this table represent?

That answer defines the level of detail in the data. It decides which questions you can answer, which measures make sense, which relationships work, and where double counting can appear.

Take sales data. The same business activity can be stored at several grains:

• One row per order line. Every product on every order is a row. This is detailed enough to analyse basket composition and product mix.

• One row per order. The order total is stored once. Product detail is gone, but customer and order-level analysis remain.

• One row per customer per month. Everything is aggregated. Compact and fast, but many details are lost permanently.

You can aggregate fine-grained data later. You cannot recover detail that was never stored.

Why Kimball declares grain first

In Kimball dimensional modelling, grain is declared before dimensions and facts. The four design steps are: choose the business process, declare the grain, identify the dimensions, and identify the facts.

The order matters. Every candidate dimension and every candidate measure must fit the grain. If one row is an order line, then product and quantity fit. A shipping fee charged once per order does not fit cleanly unless you allocate it across lines or keep it in a separate fact table.

Kimball calls the grain declaration a binding contract on the design. That sounds formal because it needs to be. Without the contract, people argue about columns before they agree what a row means.

Double counting and bad averages

Many report errors are grain errors wearing a calculation costume.

Double counting after a join
An order has three lines and one 10 euro delivery fee. If you join the order-level fee to the line-level table and then sum it, the fee becomes 30 euro. The join repeated the order-level value once per line.

Averages of averages
Store A has 100 tickets with an average of 50 euro. Store B has 10 tickets with an average of 20 euro. The true combined average is 47.27 euro, not 35 euro, because the stores have different ticket counts. Averaging already aggregated averages uses the wrong grain.

Mixed totals
A table that contains daily rows and monthly subtotal rows will overstate totals as soon as someone sums everything. Detail and subtotal rows belong in separate tables or clearly separated layers.

The first debugging question should be: what does one row represent in each table involved?

Fine grain versus coarse grain

Fine grain keeps options open. Order lines can become orders, daily totals, monthly totals, product trends, and customer-level views. Coarse grain is smaller and faster, but it throws away detail.

That trade-off can be reasonable. A daily inventory snapshot may be enough for stock trend reporting. Keeping every stock movement forever may be unnecessary for the dashboard at hand. But the choice should be explicit.

A common warehouse pattern is to keep the finest useful source grain in a base layer, then build aggregate tables for performance. That gives you detail for investigation and speed for common reports.

Grain in Power BI

Power BI models work best when fact tables have a consistent grain and connect to shared dimensions in a star schema. Dimensions sit on the one side of relationships. Fact tables sit on the many side.

Problems appear when different grains are forced into one table or directly related as if they were the same.

Example: sales are stored per order line per day, while budget is stored per store per month. Both can live in the same semantic model, but they should be separate fact tables connected through shared date, store, and product dimensions where appropriate. If you join monthly budget directly onto daily sales rows, the budget repeats for every day or line and totals become wrong.

Many apparent DAX problems are grain problems. Symptoms include:

• the same monthly budget appearing on every day;

• a total that does not match the intended business total;

• shipping cost repeated per line;

• a relationship that unexpectedly becomes many-to-many;

• a measure that changes depending on which path filters use.

Grain and KPI definitions

Every KPI has a grain, even when the definition forgets to say so.

Revenue per customer per month is not the same as revenue per order line. Average delivery time per shipment is not the same as average delivery time per order. If the numerator and denominator live at different grains, the KPI needs an explicit rule for how they meet.

Good KPI definitions state:

• what one row or event represents;

• which records are included or excluded;

• the time grain;

• the numerator and denominator grain;

• whether subtotals are recalculated or averaged.

This is less glamorous than a dashboard, but it prevents the meeting where three reports all claim to show the same number.

What to watch out for

Do not leave grain implicit
Write one sentence for every important table: one row is one order line, one invoice line, one customer per month, or one product-store-day snapshot.

Do not mix grains in one fact table
Detail rows and subtotal rows together are a trap. Split them.

Check source exports
ERP and Excel exports often mix details, subtotals, headers, and totals. Clean them before modelling.

Hide non-additive columns
Unit price, margin percentage, and averages should usually become measures, not raw columns that users can sum.

Use the one-sentence test
If a colleague cannot explain what one row represents in one sentence, the grain is not clear enough.

Cardinality is related but different
Granularity says what a row represents. Cardinality says how many unique values exist or how tables relate. Grain influences cardinality, but they are not the same concept.