Business Intelligence

Business intelligence

Answer business questions over the org's data through a governed semantic layer — define each metric once in versioned YAML, then route every "what was revenue last quarter by region" through that layer. Numbers come out consistent, auditable, and the same for everyone. This skill builds the layer and queries it in plain language.

The one rule

Never free-hand SQL against raw tables to answer a governed business question. Go through the layer.

Why, with numbers: in dbt's April 2026 benchmark (ACME Insurance, 11 questions × 20 runs, ~15-table schema), an LLM grounded in a semantic layer scored 98.2% (Claude Sonnet 4.6) / 100% (GPT-5.3 Codex) vs 90.0% / 84.1% for raw text-to-SQL on the same schema; on the unmodeled schema it was 72.7% vs 64.5%, and a 2023 GPT-4 baseline managed 32.7%. The layer is not bureaucracy — it is the accuracy. The model writing SQL against undecorated tables is the failure mode you are eliminating.

Your job is two motions: (1) build the metrics layer (entities, dimensions, measures, metrics) and (2) query it — translate a plain-language question into metric + dimensions + grain + filter, never into a hand-written query.

The four primitives

Every semantic layer (MetricFlow, Cube, warehouse-native) is built from the same four nouns. Learn these and the rest is syntax.

• Entities — the join keys. order_id is the primary entity of orders; customer_id is a foreign entity that joins to customers. Entities are how the layer knows how tables relate so it writes the join, not you.
• Dimensions — the axes you group and filter by, including time grains (order_date by day/week/month/quarter) and categoricals (region, product_category).
• Measures — a single aggregation of a column: sum(amount), count(distinct customer_id).
• Metrics — named, reusable expressions built over measures: gross_revenue, mrr, gross_margin_pct. This is what a human or agent actually asks for by name.

yamlCopy code
# MetricFlow-style semantic model for an orders table
semantic_models:
  - name: orders
    model: ref('fct_orders')
    entities:
      - name: order        # primary join key
        type: primary
        expr: order_id
      - name: customer     # foreign key -> customers semantic model
        type: foreign
        expr: customer_id
    dimensions:
      - name: order_date
        type: time
        type_params: { time_granularity: day }   # grain is declared, not implied
      - name: region
        type: categorical
    measures:
      - name: order_amount
        agg: sum            # explicit aggregation
        expr: amount

Decision: do you even need a layer?

Do not stand up a semantic layer for a spreadsheet. Branch on consumers and conflict, not on data size alone.

If the answer is "no," stop here and write the query. The layer earns its weight only when a definition has to be shared.

Pick the layer

MetricFlow was open-sourced (Apache 2.0) at Coalesce 2025 and contributed as an OSI reference implementation, so its YAML is a safe default authoring format regardless of which engine you land on.

Author the model

One definition, version-controlled, reviewed in PR — colocated with the models. Definitions belong in code review, not in a BI tool's UI where they silently fork.

yamlCopy code
# A metric defined once over the measure above
metrics:
  - name: gross_revenue
    label: Gross Revenue
    type: simple
    type_params:
      measure: order_amount     # built on the measure, not raw SQL
  - name: gross_margin_pct
    label: Gross Margin %
    type: ratio                 # ratio metric: numerator / denominator
    type_params:
      numerator: gross_profit
      denominator: gross_revenue

textCopy code
# Bad -> Good
Bad:  "revenue" SUM(amount) hand-written in Tableau,
      SUM(net_amount) in the Looker view,
      SUM(amount)-refunds in a notebook  -> three different numbers
Good: one `gross_revenue` metric in YAML; Tableau, the notebook,
      and the agent all query that one metric -> one number

For multi-entity join paths, additive vs non-additive vs ratio vs cumulative/derived metrics, semi-additive measures (balances, inventory snapshots), time spines, and the fan-out double-count trap, see references/authoring-semantic-models.md.

Query in plain language

Decompose the question into the four parts before any SQL exists. Never jump to a query.

textCopy code
Question: "MRR by plan, monthly, last 2 quarters, EU customers only"
  metric      -> mrr
  group by    -> plan
  time grain  -> month
  date filter -> last 2 quarters
  filter      -> region = 'EU'

You hand the layer that spec; it generates the governed SQL. You then explain the answer back in business terms ("EU MRR grew 8% QoQ, driven by the Pro plan"), not as a table dump.

Wire the agent

Expose the layer as an MCP / metrics tool. The agent selects governed metrics + dimensions; the layer returns the SQL/results. The agent never sees raw warehouse tables.

textCopy code
# Bad -> Good
Bad:  agent gets warehouse credentials, reads the schema,
      writes SELECT ... FROM raw.orders JOIN ...  -> 84-90% accurate, unauditable
Good: agent calls query_metrics(metric="gross_revenue",
      group_by=["region"], grain="month", filters=["region='EU'"])
      -> layer returns governed SQL/result, 98-100% accurate

Guardrails: deny raw-table access, validate that requested dimensions actually exist on the metric, reject any ungoverned aggregate. Full MCP pattern plus dbt SL GraphQL/JDBC and Cube REST/SQL/MCP query shapes are in references/wiring-agents-and-apis.md.

Reconcile conflicting numbers

When sales says revenue is X and finance says Y, it is almost never a query bug — it is two definitions. Do not write a third query to "settle it." Find the two definitions, pick the correct one, encode it once in the layer, and point both teams at it. The disagreement disappears because there is now one number to disagree about.

Portability (OSI)

Author toward the Open Semantic Interchange standard so definitions survive a tool switch. OSI is the vendor-neutral, Apache-2.0, YAML-based spec for datasets/metrics/dimensions/relationships, launched 2025-09-23 by Snowflake + dbt Labs, Cube, Salesforce/Tableau and others; v1.0 spec published on GitHub 2026-01-27. Write MetricFlow/OSI-shaped YAML; never invent a proprietary metric format trapped in one BI tool.

Anti-patterns

Verify

Run scripts/verify.sh [path] on your semantic-model directory. It is read-only, never touches a warehouse, and discovers candidate YAML, then warns (advisory) on: a metric with no underlying measure, a measure with no declared agg, a time dimension with no grain, duplicate metric names, and a .sql beside the model hand-rolling an aggregate the layer should own. It exits non-zero only on unparseable YAML; an empty or clean target passes clean.

Siblings: ../sql/SKILL.md · ../dashboard/SKILL.md · ../kpi-framework/SKILL.md · ../reporting/SKILL.md · ../analytics/SKILL.md · ../forecasting/SKILL.md