Start with process and grain

What you'll do

Write a grain statement for a synthetic Sales process, expose a header-to-line duplication error and propose independent checks. You need SQL joins and the qualification concepts from F1-F2. Keep a local worksheet; this exercise requires no source connection, Fabric capacity or client data.

Begin with a decision

Ask what the reader needs to decide before selecting tables. "Review sales performance by product and month" implies different detail from "Count orders awaiting approval." A business process describes an activity; a table name only describes storage. Record the process, its owner, the reporting period and exclusions together.

Use this synthetic example: order A has two lines worth 60 and 40; order B has one line worth 50. There are two orders, three lines and 150 of line revenue. Those statements can all be true because they answer different questions. The model must preserve their differences.

Make the grain testable

Draft a sentence precise enough for a reviewer to reject. "One row per sale" is ambiguous. "One row per source order identifier and line identifier for included posted sales" names the key and the population. Add the treatment of cancellations, returns, discounts, tax and missing dimension members as questions until the business owner answers them.

Process: posted sales
Candidate grain: one included order line
Candidate key: order identifier + line identifier
Open decision: treatment of returns and header freight

This is a worksheet, not an executable Gold contract. A candidate key needs evidence of uniqueness, and a plausible sentence needs human ratification. Neither an agent's confidence nor a table's current row count supplies that approval.

Find the duplication trap

Suppose the order headers contain totals of 100 and 50. Joining them to the three lines produces header totals of 100, 100 and 50. Summing those repeated values yields 250. The join can be syntactically valid while the business measure is wrong.

Choose an amount that belongs at the declared grain. If a header-level amount must be allocated to lines, record an explicit allocation rule and independently reconcile its result. Do not quietly divide by the number of lines merely because it restores a total in this tiny example. Different line values, rounding and returns may invalidate that rule.

Turn the statement into checks

Design checks independently of the transformation. Compare line counts, distinct business keys, total revenue and revenue grouped by a reviewed dimension. A scalar total alone can hide equal and opposite category errors. Retain the expected population and rounding policy so an apparent mismatch has a defined meaning.

In an approved learning checkout, the documented offline demonstration illustrates analysis, fixture-only ratification and semantic generation:

❯node packages/powerbi/dimensional-model/tests/demo.mjs

Its generated artifacts demonstrate mechanics. It makes no service request and does not qualify your report. Write a handoff containing your candidate statement, unresolved decisions, independent checks and the person who must review the grain. The expected output is that handoff, not a green gate.

Check your understanding

1. Why does joining order totals to order lines inflate this example to 250?
2. Which words would distinguish an order fact from an order-line fact?
3. What evidence and human decision remain missing after the offline demonstration?