Analytics and Reporting
What this domain tests
Questions target metric definition, aggregation correctness, semantic layers, join traps and dashboard trust. You are expected to diagnose why two reports disagree.
Core rules
- Analytics maturity: descriptive (what happened) → diagnostic (why) → predictive (what will) → prescriptive (what to do).
- A metric is a number; a KPI ties a metric to a target and a decision. Certified metrics live in a semantic layer so every report uses one definition.
- Additivity: fully additive measures sum across all dimensions; semi-additive measures such as balances and inventory sum across some dimensions but not time, so use period-end snapshots; non-additive measures such as ratios must be recomputed from components, never averaged.
- Grain alignment: facts join dimensions at their declared grain. Fan traps (a one-to-many join) and chasm traps (unjoined paths between dimensions) inflate or double-count sums.
- Report design: one question per dashboard, visible definitions, a freshness timestamp, and row-level security enforced in the semantic layer rather than per report.
- Time intelligence needs a date dimension with fiscal calendars, not functions applied to raw timestamps.
Worked example
Finance reports 12.4M revenue; Sales reports 13.1M for the same month from the same warehouse. Reasoning: (1) Compare definitions - Sales uses gross bookings including cancelled orders; Finance uses net invoiced. (2) Compare grain - Sales joins order lines to a campaign dimension that duplicates lines, inflating totals. (3) Reconcile by defining net revenue once in the semantic layer, fixing the fan trap with a bridge table or pre-aggregation, and publishing both metrics with explicit labels. (4) Track variance between certified and legacy reports until legacy is retired. The root cause is definition drift or a row-multiplying join, not the database engine.
Traps that cost marks
- Averaging percentages or averaging averages instead of recomputing from numerator and denominator.
- Summing semi-additive balances across months.
- Ignoring SCD history and attributing old sales to a customer new segment.
- Relying on report-level filters instead of enforced row-level security.
- Dashboards with no definition, owner or freshness indicator.