Modelling for how tools query.
WHAT REPORTING TOOLS DO
Generate queries from user interactions with a model you define.
WHY THAT MATTERS
You cannot control the query, only the structure it queries.
WHAT STRUCTURES WORK WELL
Star schemas, which tools understand natively Wide tables, where joins are avoided entirely
WHAT WORKS BADLY
Deeply normalised models requiring many joins Ambiguous join paths between tables Tables whose grain is unclear
WHY AMBIGUOUS JOIN PATHS MATTER
The tool may choose a path you did not intend, producing wrong results silently.
WHAT TO DO
Define joins explicitly, and remove ambiguity.
WHAT FAN-OUT DOES IN A REPORTING TOOL
Inflates measures when a one-to-many join is involved.
WHAT PREVENTS IT
Measures held at a single grain, with separate tables for different grains.
WHAT TO PRE-AGGREGATE
Anything queried frequently over large volumes.
WHY
Dashboards refreshing against raw data are the commonest source of unexpected cost.
WHAT TO EXPOSE
Only tables intended for consumption.
WHAT TO NAME CLEARLY
Everything, since users see the names.
WHAT TO TEST
The tool's generated queries, against expected results.
