Data Modelling
Part of BI data modelling
Defining facts, dimensions and reporting grain
Define what each BI table row represents, identify valid keys and distinguish additive amounts from balances and ratios before building reports.
Define the grain of each fact table by finishing this sentence: “One row records one ___.” Then identify what distinguishes those rows.
Facts record events or observations; dimensions describe the things used to group and filter them. These choices determine whether a measure can be joined and summed without changing its meaning.
State the grain before joining tables
An order header, an order line and a daily stock observation are different grains. Repeated order IDs in an order-line table may be expected because an order can have several lines. Repeated combinations of order ID and line number need investigation if that combination is intended to identify each line.
Keep the detail the question needs and the source can support. An order-line fact can be grouped by order or month. A monthly product total cannot recover individual order lines that were never retained. State whether cancelled, returned or amended records are included and which date sets the reporting period.
| Candidate table | Illustrative row statement | Measure question |
|---|---|---|
| Order lines | One row per order and line identifier | Can quantity and line amount be summed for the selected population? |
| Daily stock | One row per product, location and observation date | Is the report showing a balance on a date rather than adding balances across dates? |
| Monthly targets | One row per product and target month | Is a daily target defined, or would it require an allocation rule? |
These are example designs. Check the actual source keys and business rules before adopting them.
Set the dimension grain
A product dimension might describe product name and category; a customer dimension might describe account segment. A key on the dimension side of an intended one-to-many relationship must identify one row at the chosen dimension grain. If one fact row matches several dimension rows, a join can multiply its measure.
A dimension is not necessarily one row per real-world entity for all time. Retaining historical customer segments can create several dated rows for one customer. Each version needs a distinct row key, and the fact must reference the version selected by the approved history rule. If only current values are needed, a current-value dimension may be simpler.
Classify measures before exposing them
Ask which groupings permit addition. Line quantity can usually be summed across distinct order lines for an agreed population. A daily balance represents a point in time; adding Monday's and Tuesday's balances does not produce a two-day balance. A percentage generally needs to be recalculated from its underlying counts or amounts at the requested grouping, according to its approved definition.
Keep a value at the grain where it is valid. Copying an order-level amount onto every order line makes a sum over the joined lines count some orders repeatedly. Use a line-level amount, calculate the order result once per order, or define a measure that respects the order grain. Summing distinct amount values is not a general repair: separate orders can have the same amount.
Check one reporting slice
For a closed period, compare row counts, distinct business keys and an approved total before and after joining tables. Then group by a dimension, inspect unmatched keys and examine unexpectedly repeated rows. Record the source extract time, filters and date field so the comparison can be reproduced.
The deliverable is a grain statement for each table, an explicit way to identify its rows, and a measure rule a report creator can explain. This table-design task is separate from investigating duplicate source entities or teaching report authors how to spot double counting.
