Identifying Grain and Designing Coherent Fact Tables

Hello! Last time, you classified individual measures by how they can be aggregated. Now consider a different problem: two measures may each have a valid SUM, yet still not belong on the same fact row. Invoice-line revenue and whole-order shipping fees are a common example.

In about 40 minutes, you’ll learn to spot that mismatch, choose a coherent fact table for each measurement, and bring their results together without double counting. This completes the first module’s design decisions before the course turns to dimension tables and history.

---

Ask what each measurement describes

A fact table’s grain is a promise about every row. If the promise is “one row per invoice line,” a measure on that row should describe that invoice line. A number sourced from the invoice header does not become a line-level measure merely because an ETL join copies it onto every line.

Watch Data Modeling Grain Explained from Data with Baraa. The first segment contrasts line, whole-order, and monthly grains; the second shows how an order-level shipping charge can be repeated on line-level rows.

A quick diagnostic is to finish this sentence for each proposed measure:

“This value measures ___ for one ___, identified by ___, at the time ___.”

The second blank is the important one. “Revenue for one invoice line” and “shipping fee for one order” name different measurement units, even if both appear in a query about sales.

---

Why repeating a header value fails

Suppose an order contains two lines:

Order	Line	Product	Line revenue	Order shipping fee copied to line
501	1	Notebook	40	12
501	2	Pen set	60	12
502	1	Lamp	25	5

The line revenues total 125. The business charged 17 in shipping: 12 for order 501 and 5 for order 502. But SUM(shipping_fee) over these rows returns 29. Shipping has not ceased to be an additive amount; the problem is that the order-level amount has been represented twice.

Kimball Group’s Fistful of Flaws makes this distinction explicit. Read its discussion of established grain and mixed-grain facts, including the warning about year-to-date totals on detailed rows.

There are two sound designs for the shipping charge:

1. Keep it at its natural grain. Store one shipping amount per order in an order fact table; keep invoice-line revenue in a line fact table.
2. Allocate it to lines, if the business needs line-level shipping analysis. For example, allocate order 501’s fee in proportion to its line revenues. The Notebook receives $12(40/100)=4.8$, and the Pen set receives $12(60/100)=7.2$. Those are allocated shipping amounts, not the original order charge. Record the allocation basis and check that the allocated amounts sum to 12, including how rounding is handled.

The first option is usually simpler when the question is “How much shipping did we charge by month or customer?” The second answers an additional question—“How much shipping should be attributed to each product?”—but requires a defensible policy. An order can contain several products, so its unallocated shipping fee has no single product value. Adding a product_key to an order-level row would assert a relationship the data does not have.

Two tempting SQL repairs do not resolve the model:

• SUM(DISTINCT shipping_fee) removes equal values, not duplicate orders. If two different orders each have a fee of 12, it counts 12 only once.
• MAX(shipping_fee) per order can recover a fee copied identically onto every line, but only if you have validated that repetition and grouped by a genuinely unique order identifier. It still does not define shipping by product, and filtering lines first may omit orders you intended to include.

The reliable fix is to represent the measurement at its actual grain.

---

Different periods are different grains too

The mismatch is not limited to transaction headers and lines. A weekly total and a monthly total are distinct measurements, even when both columns are named measure1.

Notice that a displayed week can cross a month boundary. A complete weekly amount for such a week cannot simply be assigned to one month to recreate a monthly amount. Nor should a monthly total be copied onto each week: summing the copies would multiply it. If an atomic source is available, you can calculate separately defined weekly and monthly aggregates from that source. If only weekly totals are available, splitting a cross-month week requires additional detail or an explicit allocation rule; it is not a lossless conversion.

IBM’s Dimensional modeling: Identify the grain gives the broader design rule.

A monthly sales aggregate and an atomic sales-line table can coexist because each serves a declared purpose. They should not be stacked into one table with a grain_type flag and treated as if every row were an additional sale. Queries that omit the flag would count the same underlying activity at two levels.

Likewise, two sources need not automatically mean two facts, and two sales channels need not automatically mean incompatible grains. If their records represent the same kind of event at the same grain, with harmonized measures and dimensions, they may form one coherent fact table with a channel attribute. Decide from the measurement contract, not from file names or departmental ownership.

---

Build a measurement-to-grain inventory

For a proposed sales model, write down a grain sentence for every candidate measure before choosing columns. Your ETL experience is useful here: look at the source’s natural key and update pattern, not just the target report. An amount that arrives once per order, a line identified by order plus line number, and a target revised once per department-month are different populations.

Candidate measurement	Natural measurement grain	Dimensions valid without allocation	Coherent destination
Line net revenue	One issued invoice line	Line product, customer, invoice date	Invoice-line fact
Shipping charged	One order	Order customer, order date	Order fact
Monthly revenue target	One department for one calendar month	Department, month	Department-month target fact
Weekly product sales total	One product-store-week	Product, store, week	Product-store-week aggregate fact, if needed

This inventory exposes two related tests:

Could each candidate row have exactly one value for every proposed dimension? The order charge cannot have exactly one product if the order spans products. A department-month target cannot have exactly one store or day unless those values have been allocated under a documented rule.

Would joining source records multiply either measure? Joining an order header to its lines makes the header fee repeat. Joining a department-month target to all daily sales in that department makes the target repeat once per matched sale. Both joins may be useful for retrieving context; neither makes the repeated value safe to sum at the finer grain.

Time and event identity matter as much as keys. Ordering, shipping, invoicing, payment, and returns can occur on different dates and describe different business processes. Even if they share an order ID, a payment total need not have the grain of an invoice line. Give each measurement a row definition appropriate to its event or observation.

A concise decision procedure is:

1. Declare the proposed fact row in a business sentence, including its identifying event or period.
2. Test every measure and dimension against that sentence. Check its source key, applicable date, and whether the value is unique at that row grain.
3. Separate incompatible measurements. Name each resulting fact table and its grain. Use an allocation only when the finer-grain answer is required and its rule can be defended.
4. Specify comparison rules. Identify the common dimensions and the level at which results can be compared. Do not infer a finer breakdown from a coarser fact.

This produces coherent fact tables rather than a collection of numeric columns that happen to be available.

---

Bring results together at a valid level

Separate tables do not mean separate analyses. For the order example, suppose fact_invoice_line has one row per invoice line and fact_order has one row per order, with order_id unique in fact_order. If every order in the report has at least one included invoice line, you can first total revenue to order grain, then combine the order-level results:

WITH revenue_by_order AS (
    SELECT
        order_id,
        SUM(net_revenue) AS net_revenue
    FROM fact_invoice_line
    GROUP BY order_id
)
SELECT
    d.calendar_year,
    d.calendar_month,
    SUM(r.net_revenue) AS net_revenue,
    SUM(o.shipping_fee) AS shipping_fees
FROM fact_order AS o
JOIN revenue_by_order AS r
    ON r.order_id = o.order_id
JOIN dim_date AS d
    ON d.date_key = o.order_date_key
GROUP BY
    d.calendar_year,
    d.calendar_month;

The join now matches one revenue subtotal to one order row. The shipping fee appears once per order, not once per line. This example deliberately reports by order date; reporting by invoice date might assign revenue to different months when an order is invoiced later or in parts. In that situation, establish the required date meaning and alignment before using this query. The inner join also deliberately excludes orders with no included lines; change the population rule if those orders must count.

For a monthly department target, the comparable sales figure would be aggregated to department-month before placing actual and target side by side. Product-level actuals remain available in the sales fact, but a product-level target-versus-actual comparison is not justified until someone defines a product allocation for the target. Shared dimensions make comparisons possible; they do not manufacture missing detail.

---

Conclusion

Incompatible grain is a relationship between measurements, not a flaw in SUM itself. Detect it by stating what one row and each measure represent, checking which dimensions genuinely have one value at that level, and watching for values multiplied by joins. Keep order charges, invoice-line revenue, weekly totals, and monthly targets in coherent tables unless a documented allocation establishes a legitimate finer-grain measure.

You have now covered the core decisions for designing fact tables from analytical requirements. In the next module, you’ll give those facts dependable descriptive context by designing dimension keys, attributes, and hierarchies.