skill: business-intelligence

should_trigger:
  - prompt: "Let our team ask revenue and churn questions in plain English and always get the same number back."
    why: Governed self-serve analytics in natural language — the core promise of the semantic/metrics layer.
  - prompt: "Define MRR once so every dashboard, report, and notebook computes it identically."
    why: Single shared metric definition is exactly what this skill encodes in versioned YAML.
  - prompt: "Connect our AI agent to the warehouse so it answers data questions without hallucinating SQL."
    why: Non-obvious — sounds like generic agent wiring, but the answer is to expose a semantic layer via MCP/metrics API so the agent selects governed metrics.
  - prompt: "Sales and finance report different revenue numbers and nobody can agree which is right — fix it."
    why: Non-obvious — sounds like a data bug; the real fix is one shared metric definition in the layer, not another query.
  - prompt: "Necesito una capa semántica para que el equipo consulte ventas por región en lenguaje natural."
    why: Spanish — semantic layer plus natural-language querying over governed metrics.

should_not_trigger:
  - prompt: "Design the dashboard layout and decide which charts to show on our exec view."
    route_to: dashboard
    why: This is visual presentation and panel arrangement, not defining the trusted number.
  - prompt: "Decide which 5 KPIs our SaaS should track this year and set their targets."
    route_to: kpi-framework
    why: Choosing which metrics matter and their targets is metric strategy, not technical definition.
  - prompt: "Write me a window-function query for a running total per customer."
    route_to: sql
    why: Hand-written portable query craft, not building or querying a governed metrics layer.
  - prompt: "Build a monthly board report deck that narrates the quarter's results."
    route_to: reporting
    why: Packaging results into a recurring narrative artifact, not the live ask-anything query layer.
  - prompt: "Forecast next quarter's revenue from the last three years of data."
    route_to: forecasting
    why: Projecting future values, not reporting governed historical numbers.

capability:
  - scenario: >
      Given an orders + customers + products warehouse schema, answer:
      "monthly gross revenue by product category for the last 2 quarters, EU customers only."
      Produce (a) a MetricFlow-style semantic model and (b) the metric-query spec that answers it.
    must_include:
      - Entities declared with join keys (order primary; customer and product foreign) so the layer owns the joins.
      - A measure with an explicit agg (e.g. order_amount, agg sum).
      - A metric (gross_revenue) defined over the measure, not raw SQL.
      - A time dimension (order_date) with a declared grain (day, rolled up to month).
      - The question decomposed into metric + group-by dimension (product_category) + time grain (month) + date range (last 2 quarters) + filter (region = EU) — not a hand-written SQL query.
      - A note that the agent should call the metrics API / MCP tool rather than write SQL against the warehouse.

# Evals — business-intelligence

These cases are routing and capability checks, not an automated test suite. Read `cases.yaml` against the triggers and boundary in `../SKILL.md`: each `should_trigger` prompt should land on this skill, each `should_not_trigger` prompt should route to the named sibling, and the `capability` scenario lists the rubric items a good answer must include (a semantic model with entities/measure/metric/time grain plus a metric-query spec, not raw SQL). There is no runner — a human or grader judges the responses. To check the artifact-shape claims, run `../scripts/verify.sh path/to/semantic-models/` against a sample MetricFlow or Cube model; it is read-only, never connects to a warehouse, and passes clean on an empty target.

# Authoring semantic models

Offloaded depth from SKILL.md §"Author the model." Worked patterns for real models that span multiple tables and metric shapes. MetricFlow-style YAML throughout; the same concepts map to Cube and warehouse-native objects.

## Multiple entities and join paths

Let the layer own joins. Declare the keys on each semantic model and the layer resolves the path; you never write `JOIN`.

```yaml
semantic_models:
  - name: orders
    model: ref('fct_orders')
    entities:
      - { name: order, type: primary, expr: order_id }
      - { name: customer, type: foreign, expr: customer_id }
      - { name: product, type: foreign, expr: product_id }
    dimensions:
      - { name: order_date, type: time, type_params: { time_granularity: day } }
    measures:
      - { name: order_amount, agg: sum, expr: amount }
      - { name: cost_amount, agg: sum, expr: unit_cost }

  - name: customers
    model: ref('dim_customers')
    entities:
      - { name: customer, type: primary, expr: customer_id }
    dimensions:
      - { name: region, type: categorical }        # reachable from orders via customer
      - { name: signup_date, type: time, type_params: { time_granularity: day } }

  - name: products
    model: ref('dim_products')
    entities:
      - { name: product, type: primary, expr: product_id }
    dimensions:
      - { name: product_category, type: categorical }
```

With this, `gross_revenue` grouped by `product_category` and filtered on `region` works even though those dimensions live on two other tables — the shared `customer` and `product` entities tell the layer how to join.

## Metric types

| Type | Shape | Example |
|---|---|---|
| **Simple** | one measure, additive across all dimensions | `gross_revenue = sum(amount)` |
| **Ratio** | numerator / denominator (two measures) | `gross_margin_pct = gross_profit / gross_revenue` |
| **Derived** | expression over other metrics | `net_revenue = gross_revenue - refunds` |
| **Cumulative** | running/windowed accumulation over time | `running_total_revenue` |

```yaml
metrics:
  - name: gross_revenue
    type: simple
    type_params: { measure: order_amount }

  - name: gross_profit
    type: derived
    type_params:
      expr: revenue - cost
      metrics:
        - { name: gross_revenue, alias: revenue }
        - { name: total_cost, alias: cost }

  - name: gross_margin_pct
    type: ratio
    type_params:
      numerator: gross_profit
      denominator: gross_revenue

  - name: running_total_revenue
    type: cumulative
    type_params:
      measure: order_amount
      window: null   # null = accumulate from the beginning of time
```

## Additive vs non-additive vs semi-additive

- **Additive** — safe to `sum` across every dimension including time. Revenue, order count. The default.
- **Non-additive** — cannot be summed; must be recomputed at each grain. Ratios, percentages, distinct counts. `count(distinct customer_id)` per month does **not** sum to the quarterly distinct count — declare it as a measure with `agg: count_distinct` and let the layer recompute per grain. Never pre-aggregate it.
- **Semi-additive** — additive across some dimensions but not time. Account balances, inventory snapshots, headcount: you sum across regions on a given day, but across days you take the *last* (or first) value, not the sum.

```yaml
measures:
  - name: account_balance
    agg: sum
    expr: balance
    agg_time_dimension: snapshot_date
    non_additive_dimension:           # semi-additive: collapse time to the last value
      name: snapshot_date
      window_choice: max
```

Getting this wrong silently triples your inventory or headcount when someone groups by month. If a measure is a balance/snapshot, it is semi-additive — flag it.

## Time spines and grains

A time spine is a dense date dimension table the layer uses to fill gaps (no orders on a day still shows a zero row) and to roll day → week → month → quarter consistently. Declare the smallest grain you store; the layer aggregates upward. Never let consumers guess the grain — an undeclared grain is the most common source of "the monthly number doesn't match the daily sum."

```yaml
models:
  - name: metricflow_time_spine
    time_spine:
      standard_granularity_column: date_day
    columns:
      - { name: date_day, granularity: day }
```

## The fan-out double-count trap — through the layer

Joining a one-to-many relationship before aggregating multiplies the "one" side. If `orders` has many `order_lines`, summing `order_amount` after joining lines counts each order's amount once per line.

The layer protects you **only if measures live on the right semantic model**: put `order_amount` on `orders` (one row per order) and `line_quantity` on `order_lines` (one row per line). Then the layer aggregates each measure on its own grain before joining, and the fan-out never happens. The trap reappears the moment someone hand-writes the join in SQL — which is the whole reason to query through the layer.

## Testing metric definitions

- Pin a known answer: a date range where you've hand-verified the total once, assert the metric returns it in CI.
- Cross-check grains: monthly summed to a quarter must equal the quarterly query for additive metrics; assert it.
- Assert distinct/ratio metrics do **not** match a naive sum (that's the signal they're correctly non-additive).
- Run `scripts/verify.sh` on the model directory to catch missing `agg`, missing grain, and duplicate metric names before review.

# Wiring agents and APIs

Offloaded depth from SKILL.md §"Wire the agent." How an LLM/agent consumes the semantic layer so it selects governed metrics instead of guessing SQL. The accuracy case is in SKILL.md (dbt 2026 benchmark: layer-grounded 98-100% vs raw text-to-SQL 84-90%).

## The pattern: agent → metrics tool → governed SQL

The agent never holds warehouse credentials and never sees raw tables. It calls a metrics tool whose inputs are the four primitives. The layer compiles the request to SQL, runs it, and returns the result.

```text
user question
  -> agent decomposes to: metric + dimensions + grain + filters
  -> agent calls metrics tool (MCP / metrics API) with those four parts
  -> semantic layer compiles to governed SQL, runs on the warehouse
  -> result + the metric definition used returned to the agent
  -> agent explains the answer in business terms
```

MCP is the emerging transport: the semantic layer is exposed as a tool server, the agent picks from the catalog of governed metrics and dimensions, and the layer returns results. Cube ships an AI API / MCP support for exactly this; expose the catalog as tools so metric selection is the only path.

## Query shapes by engine

### Cube — REST

```json
{
  "measures": ["orders.gross_revenue"],
  "dimensions": ["products.product_category"],
  "timeDimensions": [
    { "dimension": "orders.order_date", "granularity": "month",
      "dateRange": "last 2 quarters" }
  ],
  "filters": [
    { "member": "customers.region", "operator": "equals", "values": ["EU"] }
  ]
}
```

### Cube — SQL API (governed; the engine, not the agent, expands the metric)

```sql
SELECT product_category, gross_revenue
FROM orders
WHERE region = 'EU'
GROUP BY 1;
```

### dbt Semantic Layer — GraphQL

```graphql
query {
  query(
    metrics: [{ name: "gross_revenue" }]
    groupBy: [{ name: "products__product_category" },
              { name: "orders__order_date", grain: MONTH }]
    where: [{ sql: "{{ Dimension('customers__region') }} = 'EU'" }]
  ) { jsonResult }
}
```

dbt SL also exposes a **JDBC** endpoint with a `semantic_layer` SQL dialect, so BI tools and apps issue metric queries as if they were SQL while the layer compiles the real warehouse SQL. Both GraphQL and JDBC are the supported app-facing surfaces — point the agent at one of them, not at the warehouse.

## Prompt guidance that forces metric selection

Put this in the agent's system prompt / tool description:

- "To answer any data question, you MUST call `query_metrics`. You may not write or execute SQL against the warehouse."
- "Choose `metric` from the provided catalog. If no metric matches, say so and stop — do not invent an aggregate."
- "Express time as a `grain` (day/week/month/quarter) and a date range, never as a hand-written date filter."
- "Return the metric name you used so the number is auditable."

The goal is to make "call the metrics tool" the only available action, so the model's strong-but-imperfect SQL writing is never in the loop for a governed number.

## Guardrails (enforce in the tool, not just the prompt)

- **Deny raw-table access.** The agent's credentials reach the metrics API only — no direct warehouse connection. A prompt rule is not a control; the missing credential is.
- **Validate dimensions exist.** Reject a request whose `group_by` or `filter` names a dimension not declared on the metric. Return the valid options so the agent can retry.
- **Reject ungoverned aggregates.** If a request smuggles a raw expression instead of a catalog metric, refuse.
- **Return the definition used.** Every answer carries the metric name and grain so a human can audit it later and so two answers to the same question are provably identical.
- **Bound the blast radius.** Apply row-level security / tenant filters in the layer, not in the agent, so the agent cannot widen its own access.

#!/usr/bin/env bash
# verify.sh — read-only sanity check for semantic-model definitions.
# Stock macOS bash 3.2 compatible. Never connects to a warehouse.
# Exits non-zero ONLY on unparseable YAML; every metric-modeling check is advisory [warn].
# Empty / no-candidate target passes clean.
set -u

TARGET="${1:-.}"
WARN=0
FAIL=0
CHECKED=0

note() { printf '%s\n' "$*"; }
warn() { printf '[warn] %s\n' "$*"; WARN=$((WARN + 1)); }
fail() { printf '[fail] %s\n' "$*"; FAIL=$((FAIL + 1)); }
ok()   { printf '[ok] %s\n' "$*"; }

if [ ! -e "$TARGET" ]; then
  note "[skip] target not found: $TARGET"
  exit 0
fi

# Discover candidate semantic-model files: YAML containing layer keywords,
# or Cube .js/.yml files declaring cube(/measures:.
CANDIDATES=""
while IFS= read -r f; do
  [ -z "$f" ] && continue
  if grep -Eqi '(^|[[:space:]])(semantic_models|metrics|measures)[[:space:]]*:' "$f" 2>/dev/null \
     || grep -Eqi 'cube\(' "$f" 2>/dev/null; then
    CANDIDATES="$CANDIDATES$f"$'\n'
  fi
done <<EOF
$(find "$TARGET" -type f \( -name '*.yml' -o -name '*.yaml' -o -name '*.js' \) 2>/dev/null)
EOF

CANDIDATES="$(printf '%s' "$CANDIDATES" | grep -v '^$' || true)"

if [ -z "$CANDIDATES" ]; then
  note "[skip] no semantic-model candidates under: $TARGET"
  exit 0
fi

# Pick a YAML parser if available.
YAML_CHECK=""
if command -v python3 >/dev/null 2>&1 && python3 -c "import yaml" >/dev/null 2>&1; then
  YAML_CHECK="python3"
elif command -v yq >/dev/null 2>&1; then
  YAML_CHECK="yq"
fi

ALL_METRIC_NAMES=""

while IFS= read -r f; do
  [ -z "$f" ] && continue
  CHECKED=$((CHECKED + 1))
  note "--- $f"

  case "$f" in
    *.yml|*.yaml)
      if [ "$YAML_CHECK" = "python3" ]; then
        if ! python3 -c "import sys,yaml; list(yaml.safe_load_all(open(sys.argv[1])))" "$f" >/dev/null 2>&1; then
          fail "YAML does not parse"
          continue
        fi
      elif [ "$YAML_CHECK" = "yq" ]; then
        if ! yq -e '.' "$f" >/dev/null 2>&1; then
          fail "YAML does not parse"
          continue
        fi
      else
        # No parser: brace/indent sanity check (tabs are illegal in YAML).
        if grep -Pq '\t' "$f" 2>/dev/null || grep -q "$(printf '\t')" "$f" 2>/dev/null; then
          warn "tab character in YAML (use spaces) — install python3+pyyaml or yq for a real parse"
        fi
      fi
      ;;
  esac

  # --- Advisory metric-modeling checks (line-grep heuristics) ---

  # A metrics: block but no underlying measure/agg anywhere in the file.
  if grep -Eqi '(^|[[:space:]])metrics[[:space:]]*:' "$f"; then
    if ! grep -Eqi '(measure|agg|aggregation)[[:space:]]*:' "$f"; then
      warn "has a metrics: block but no measure/agg in the same file — is the metric grounded in a measure?"
    fi
  fi

  # A measures: block but at least one measure may lack an agg.
  if grep -Eqi '(^|[[:space:]])measures[[:space:]]*:' "$f"; then
    MEAS_NAMES=$(grep -Ec '^[[:space:]]*-[[:space:]]*(name|name:)' "$f" 2>/dev/null || echo 0)
    if ! grep -Eqi '(^|[[:space:]])(agg|aggregation)[[:space:]]*:' "$f"; then
      warn "measures: block with no agg/aggregation declared — every measure needs an explicit aggregation"
    fi
  fi

  # Time dimension with no grain/granularity.
  if grep -Eqi 'type[[:space:]]*:[[:space:]]*time' "$f"; then
    if ! grep -Eqi '(time_granularity|granularity|grain)[[:space:]]*:' "$f"; then
      warn "time dimension present but no grain/granularity declared — undeclared grain causes daily-vs-monthly mismatches"
    fi
  fi

  # Hand-rolled aggregate in a .sql beside the model.
  DIR=$(dirname "$f")
  while IFS= read -r s; do
    [ -z "$s" ] && continue
    if grep -Eqi '(sum|count|avg|min|max)[[:space:]]*\(' "$s" 2>/dev/null; then
      warn "hand-rolled aggregate in $(basename "$s") beside the model — the layer should own this aggregation"
    fi
  done <<INNER
$(find "$DIR" -maxdepth 1 -type f -name '*.sql' 2>/dev/null)
INNER

  # Collect metric names for duplicate detection across files.
  NAMES=$(grep -A1 -Ei '(^|[[:space:]])metrics[[:space:]]*:' "$f" 2>/dev/null \
          | grep -Eoi '^[[:space:]]*-[[:space:]]*name:[[:space:]]*[A-Za-z0-9_]+' \
          | sed -E 's/.*name:[[:space:]]*//' 2>/dev/null || true)
  if grep -Eqi '(^|[[:space:]])metrics[[:space:]]*:' "$f"; then
    NAMES=$(awk '/[[:space:]]*metrics[[:space:]]*:/{inm=1} inm && /name:/{print $NF}' "$f" 2>/dev/null || true)
    ALL_METRIC_NAMES="$ALL_METRIC_NAMES$NAMES"$'\n'
  fi
done <<EOF
$CANDIDATES
EOF

# Duplicate metric names across all candidates.
DUPES=$(printf '%s' "$ALL_METRIC_NAMES" | grep -v '^$' | sort | uniq -d || true)
if [ -n "$DUPES" ]; then
  while IFS= read -r d; do
    [ -z "$d" ] && continue
    warn "duplicate metric name '$d' — a metric must be defined exactly once"
  done <<EOF
$DUPES
EOF
fi

note "---"
note "checked $CHECKED file(s); $WARN warning(s); $FAIL failure(s)"

if [ "$FAIL" -gt 0 ]; then
  exit 1
fi
exit 0