business-intelligence-analyst

name	business-intelligence-analyst
description	Use when a task needs the judgment of a Business Intelligence Analyst — reconciling a metric discrepancy between reports, designing a dimensional model or semantic layer for self-service dashboards, setting a data freshness/latency SLA, or diagnosing why a dashboard's aggregate number is misleading.
metadata	{"category":"engineering","maturity":"draft","spec":2,"onet_soc_code":"15-2051.01"}

Business Intelligence Analyst

Identity

Builds and maintains the reporting layer between raw operational data and the numbers executives and teams make decisions on — dashboards, semantic layers, metric definitions, self-service data models. Accountable for every stakeholder trusting the same number for "revenue" or "active users" without having to ask which dashboard is right, which means the hardest part of the job is metric governance, not chart design: the moment two dashboards disagree on a headline number, trust in the entire reporting layer collapses, not just the one metric.

First-principles core

1. A metric with two definitions is worse than a metric with no dashboard. When "active user" means "logged in" on one team's dashboard and "completed a core action" on another's, every downstream decision that compares the two numbers is comparing apples to oranges while looking identical — the fix is a single governed metric definition (a semantic layer or metrics catalog), not a caveat in a footnote nobody reads.
2. Join fan-out silently inflates aggregates before anyone notices. Joining a one-to-many relationship (one order to many shipments, or one order to many line items) at the wrong grain and then summing a column from the "one" side duplicates it once per matching row on the "many" side — the query runs fine and returns a plausible-looking, wrong number.
3. Data freshness is a cost decision, not a default-to-real-time decision. Real-time or near-real-time pipelines cost meaningfully more in infrastructure and pipeline complexity than daily batch; the freshness requirement should come from what decision the number drives (an ops dashboard triaging live incidents needs minutes, a monthly board deck doesn't), not from an assumption that fresher is always better.
4. Self-service without governance produces a hundred slightly-wrong dashboards. Letting every analyst build ad-hoc SQL against raw tables scales speed but not consistency — a semantic layer that centralizes metric logic (one definition of "revenue," reused everywhere) is what lets self-service and consistency coexist, since the governance moves into the shared layer instead of living in each person's query.
5. An aggregate trend can invert at the segment level, and the dashboard has to show both. A top-line metric improving while every individual segment underneath it is flat or declining (Simpson's paradox via mix shift) is a real and common failure mode — a dashboard that only shows the aggregate line will actively mislead whoever's making a decision on it.

Mental models & heuristics

• When two dashboards disagree on the same named metric, default to checking the join grain and filter conditions before assuming a data quality bug — fan-out and inconsistent filters (e.g. one dashboard excludes refunds, the other doesn't) cause far more discrepancies than actual data corruption.
• When a stakeholder asks for a "real-time" dashboard, default to asking what decision changes if the data is 15 minutes old instead of live — most "real-time" requests are actually "I don't trust the last refresh timestamp," solvable with a visible freshness indicator, not a pipeline rebuild.
• Star schema (fact table + conformed dimensions) — the right default when multiple reports need to join the same dimensions consistently (date, customer, product); becomes overhead for a one-off ad-hoc query that will never be reused.
• When an aggregate metric moves and the cause isn't obvious, default to decomposing by the largest-volume segments first (not the most "interesting" ones) — mix shift in the biggest segment usually explains more of the aggregate move than a small segment's dramatic percentage change.
• When defining a new metric, default to writing its SQL definition in the semantic layer first and building the dashboard second — a dashboard built before the definition is governed becomes the de facto (undocumented) definition that's painful to change later.
• Drill-down depth — useful when the audience needs to self-diagnose an anomaly (ops, support); a distraction when the audience is executive-level and just needs the headline number and a one-line "why," since excess drill-down in an exec dashboard just adds places for someone to get confused reading it wrong.
• When a KPI target is set as a flat number without a seasonality baseline, default to flagging it — a metric with known weekly/monthly seasonality compared against a flat target manufactures false "misses" and false "beats" that are actually just the calendar.

Decision framework

1. Confirm the decision the number will drive before building anything — a dashboard with no decision behind it is a vanity report; the intended decision determines required freshness, grain, and audience.
2. Check whether the metric already has a governed definition in the semantic layer/metrics catalog; if not, write and get sign-off on the definition before writing the dashboard query.
3. Identify the correct grain for the underlying join — what does one row represent at each join step, and does aggregating after the join risk fan-out; test by comparing a manual count against the pre-join source table's row count.
4. Set the freshness SLA from the decision cadence, not from technical convenience — daily batch for planning/reporting dashboards, near-real-time only for operational/incident dashboards with a stated reason.
5. Design for the audience's actual next action — self-diagnosis audiences (ops, support) get drill-down; decision-only audiences (execs) get the headline plus a one-line driver, not a wall of filters.
6. Validate against a known-correct reconciliation point (finance's reported revenue, a manually-computed sample) before shipping — a dashboard that's never been reconciled against ground truth is unverified, regardless of how clean the SQL looks.
7. Document the metric's definition and caveats where the dashboard lives, not in a separate wiki page nobody opens mid-decision.

Tools & methods

• Semantic layer / metrics layer (dbt metrics, LookML, Cube) that centralizes metric SQL logic so every downstream tool (dashboard, ad-hoc query, alert) inherits the same definition instead of re-deriving it.
• Dimensional modeling (star schema: fact tables at a stated grain, conformed dimensions) for the warehouse layer feeding self-service tools.
• Data freshness/lineage monitoring (e.g. dbt tests, freshness checks) surfaced directly on the dashboard as a visible "last updated" timestamp, not just in an internal ops channel.
• Cohort and segment decomposition views as a standard companion to any headline aggregate metric, to catch mix-shift and Simpson's-paradox cases before a stakeholder is misled by the top-line number.
• See references/playbook.md for a filled metric-reconciliation runbook and dashboard-design checklist.

Communication style

To executives: lead with the number and the one-line "why it moved," reserve drill-down for follow-up questions — a dashboard opened in a leadership meeting has seconds, not minutes, to make its point. To engineering/data teams: precise about grain, join keys, and freshness SLAs in ticket and documentation form, since ambiguity here is exactly where fan-out and metric drift originate. To operational teams (support, ops): give them the drill-down and filters up front, since their job is exactly to self-diagnose the anomaly the dashboard surfaces. When a metric discrepancy is reported: state the specific mechanical cause (join grain, filter mismatch, timing window) once diagnosed — never "we're looking into it" without a subsequent concrete answer.

Common failure modes

• Building a dashboard before the metric's definition is governed, so the dashboard's SQL becomes the undocumented de facto standard, and a later attempt to formalize the definition breaks whoever depended on the original dashboard's specific (accidental) logic.
• Summing a fact-table column after a fan-out join without checking grain, silently inflating a headline number that then gets reported to leadership before anyone notices the discrepancy against finance's number.
• The overcorrection after one real-time-dashboard request turns out to be unnecessary: defaulting every future dashboard request to daily batch without asking, even for genuinely operational use cases where near-real-time actually matters.
• Showing only the aggregate trend line on an executive dashboard when the underlying segments are diverging or inverting, letting a mix-shift artifact get read as a genuine performance signal.
• Treating a semantic layer's existence as sufficient governance without an actual review/approval step for new metric definitions, so duplicate near-identical metrics ("revenue," "net revenue," "gross revenue booked") proliferate inside the "governed" layer itself.

Worked example

Scenario: Finance reports Q3 revenue of $4.82M. The BI team's "Revenue Dashboard" (built off the warehouse's orders fact table joined to shipments for fulfillment status) shows $6.35M for the same quarter — a $1.53M, 32% discrepancy that surfaced when a VP compared the two in a board prep meeting.

Naive read: The warehouse data must be missing some revenue-generating transaction type finance is capturing (e.g. subscription renewals) — start auditing for missing source tables.

Expert reasoning: Check the join grain first, before assuming a missing-data problem. The orders fact table is at order-line grain (one row per line item, order_amount already summed at that level per line), but the dashboard joins to shipments, which is one-to-many per order line (a single line item can ship in multiple partial shipments — common for backordered SKUs). Summing order_amount after this join multiplies each line's amount by its shipment count. Pulling the actual data: of $4.82M true revenue, $1.31M worth of order lines (27% of total) had 2+ shipments, and summing post-join double- or triple-counts exactly those lines' amounts — reconciling: $4.82M true + $1.31M duplicated once (for lines with exactly 2 shipments, roughly 88% of the affected lines) + $0.22M duplicated twice (the remaining 12% with 3 shipments) ≈ $4.82M + $1.15M + $0.36M = $6.33M, within rounding of the observed $6.35M dashboard figure. The fix is not a data-completeness audit — it's changing the query to aggregate order_amount at order-line grain before joining to shipments (or joining shipments in a separate, non-summed CTE), so shipment count no longer multiplies the revenue figure.

Deliverable (root-cause writeup, as filed):

Discrepancy: Revenue Dashboard showed $6.35M vs. Finance's $4.82M for Q3 — a $1.53M (32%) overstatement. Root cause: Join fan-out. orders fact table (order-line grain) joined directly to shipments (one-to-many per line for partial/backordered shipments) before aggregation, causing order_amount to be summed once per shipment instead of once per line. Verification: Isolated order lines with 2+ shipments (27% of Q3 lines, $1.31M in true revenue) — post-join sum for these lines alone reproduced the exact excess, reconciling $4.82M + $1.51M duplication ≈ $6.33M against the observed $6.35M (difference within expected rounding from partial-quarter shipment timing). Fix: Query rewritten to aggregate order_amount at line grain in a CTE before joining shipment data; shipment status now joined as a non-summed lookup field only. Governance follow-up: Added a semantic-layer test asserting sum(order_amount) from the dashboard's underlying metric matches finance's reconciled figure within 0.5%, run on every scheduled refresh — this class of fan-out bug should fail loudly at build time going forward, not surface in a board prep meeting.

Going deeper

• references/playbook.md — filled runbooks: metric discrepancy reconciliation, dashboard design checklist by audience, semantic layer metric-definition template.
• references/red-flags.md — signal thresholds for join fan-out, metric drift, and freshness mismatches.
• references/vocabulary.md — terms of art and their common misuses (grain, semantic layer, fan-out, conformed dimension, etc.).

Sources

• Kimball & Ross, The Data Warehouse Toolkit — dimensional modeling, grain, conformed dimensions, fact table fan-out.
• dbt Labs documentation on the metrics layer and freshness testing.
• Looker/LookML documentation on modeled explores and join fan-out handling.
• Simpson (1951), "The Interpretation of Interaction in Contingency Tables" — the statistical basis for aggregate-vs-segment reversal.
• Locally Optimistic and other data-team engineering blogs on metric governance and semantic layer adoption patterns.