AI Query API — Ossie models

The AI Query API for Ossie is the semantic-YAML counterpart of the AI Query API for OLAP cubes. Same discipline: agents fetch a self-describing schema, fill in a JSON request against it, the server validates every name, executes, and returns typed records. Same guardrails: agents never write SQL directly.

Where the OLAP AI API works against Mondrian cubes, this one works against models declared in Open Semantic Interchange / Apache Ossie YAML. Everything the workbench does — filters, sorts, aggregation overrides, crosstab pivot, chart view — is available programmatically through this API, plus a natural-language /ask layer and per-query analytics (anomaly detection, forecasting).

Note

Trying this from Claude Desktop / Cursor / Cline? The MCP server exposes the same operations as five typed tools: list_ossie_models, describe_ossie_model, search_field_values, run_ossie_query, preview_ossie_query. Everything on this page applies to the MCP tools too.

Quick orientation

Three endpoints cover ~90% of agent use:

Endpoint	Purpose
GET /rest/saiku/api/ai/ossie/models	List every Ossie model the caller can query.
GET /rest/saiku/api/ai/ossie/schema/{connection}/{model}	Self-describing schema — datasets, fields, metrics, relationships, JSON Schema of the request body, ready-made example bodies.
POST /rest/saiku/api/ai/ossie/query	Execute a typed shelf-state request. Records-format response by default; matrix on ?format=matrix.

Long-tail endpoints:

Endpoint	Purpose
POST /rest/saiku/api/ai/ossie/query/preview	Compile to SQL without executing. Same VALIDATION_ERROR shape as /query.
GET /rest/saiku/api/ai/ossie/values/search	Substring lookup of distinct values on a field.
POST /rest/saiku/api/ai/ossie/query/execute-async	Submit for background execution.
GET /rest/saiku/api/ai/ossie/query/status/{queryId}	Poll status.
GET /rest/saiku/api/ai/ossie/query/result/{queryId}	Fetch a completed async result.
DELETE /rest/saiku/api/ai/ossie/query/{queryId}	Cancel an in-flight query.
POST /rest/saiku/api/ai/ossie/row-detail	Ossie’s drillthrough analogue — re-runs the shelf as raw rows.
POST /rest/saiku/api/ai/ossie/anomaly	Runs the query then flags anomalies along a time axis.
POST /rest/saiku/api/ai/ossie/forecast	Projects future points using ETS / ARIMA / Prophet.
POST /rest/saiku/api/ai/ossie/ask	Natural-language ask. Needs an LLM key.
GET /rest/saiku/api/ai/ossie/ask/health	Whether the ask layer is configured on this instance.

All endpoints require an authenticated session; POST endpoints require the CSRF cookie/header pair. Same auth model as the OLAP AI API.

Step 1 — list the models

GET /rest/saiku/api/ai/ossie/models

[
  {
    "connectionName": "unknown_TPCDS",
    "modelName": "TPCDS",
    "description": "TPC-DS retail — sales, customers, products, stores.",
    "factDataset": "store_sales",
    "datasetCount": 5,
    "metricCount": 5
  }
]

The connectionName + modelName pair is the identifier used everywhere else.

Step 2 — fetch the schema

GET /rest/saiku/api/ai/ossie/schema/unknown_TPCDS/TPCDS

Add ?refresh=true to bypass the sample-value cache (default TTL is 5 minutes; override via SAIKU_AI_OSSIE_SAMPLES_TTL_MINUTES).

Response is dense by design — this is what makes the API self-describing:

{
  "modelId": "unknown_TPCDS/TPCDS",
  "connectionName": "unknown_TPCDS",
  "modelName": "TPCDS",
  "factDataset": "store_sales",

  "datasets": {
    "item": {
      "name": "item",
      "source": "ITEM",
      "primaryKey": ["I_ITEM_SK"],
      "fields": {
        "i_brand": {
          "name": "i_brand",
          "label": "Brand",
          "type": "VARCHAR",
          "cardinality": "low",
          "sampleValues": ["AudioLine", "BookHouse", "CasualCo", "DeskPro"]
        },
        "i_category": {
          "name": "i_category",
          "label": "Category",
          "type": "VARCHAR",
          "cardinality": "low",
          "sampleValues": ["Apparel", "Books", "Electronics", "Furniture"]
        }
      }
    }
  },

  "metrics": {
    "total_sales": {
      "name": "total_sales",
      "expression": "SUM(\"store_sales\".\"SS_QUANTITY\" * \"store_sales\".\"SS_SALES_PRICE\")",
      "aggregationKind": "sum",
      "supportedOverrides": ["SUM", "AVG", "MIN", "MAX", "COUNT"]
    },
    "transaction_count": {
      "name": "transaction_count",
      "expression": "COUNT(*)",
      "aggregationKind": "count",
      "supportedOverrides": ["COUNT"]
    }
  },

  "relationships": [
    {
      "name": "store_sales_to_item",
      "from": "store_sales",
      "to": "item",
      "fromColumns": ["SS_ITEM_SK"],
      "toColumns": ["I_ITEM_SK"]
    }
  ],

  "requestSchema": { /* JSON Schema for the POST /query body */ },

  "examples": {
    "simpleGroupBy": {
      "description": "Total sales grouped by item brand",
      "body": {
        "model": "TPCDS",
        "rows": [{"dataset": "item", "field": "i_brand"}],
        "values": [{"metric": "total_sales"}]
      }
    }
  }
}

Notable affordances:

• label on each field is the Ossie spec’s human-readable name — surfaced everywhere columns render. NETREVENUE shows up as “Net Revenue”.
• sampleValues gives the agent real values to filter with. No hallucinating “US” when the actual value is “United States”.
• cardinality hints (low / medium / medium-high / high) — with estimatedDistinct when the warehouse supports APPROX_COUNT_DISTINCT. Agents can decide whether “filter by this column” is realistic.
• supportedOverrides — the set of aggregation overrides the translator will actually rewrite. COUNT(*) metrics only accept COUNT (surfaced by the fuzz suite).
• examples — copy-paste request bodies for the common shapes (simpleGroupBy, crosstab, topN).

Step 3 — execute a query

POST /rest/saiku/api/ai/ossie/query
Content-Type: application/json

Request:

{
  "connection": "unknown_TPCDS",
  "model": "TPCDS",
  "rows": [{"dataset": "customer", "field": "c_state"}],
  "values": [{"metric": "total_sales"}],
  "sorts": [{"metric": "total_sales", "direction": "DESC"}],
  "limit": 5
}

Response (records-format, the default):

{
  "queryId": "ossie-ai-a3f81",
  "runtime": 210,
  "columns": [
    {"key": "customer.c_state",  "label": "State",  "type": "dimension"},
    {"key": "total_sales", "label": "total_sales", "type": "metric", "aggregationKind": "sum"}
  ],
  "records": [
    {"customer.c_state": "CA", "total_sales": {"value": 1835.0, "formatted": "1835.00"}},
    {"customer.c_state": "NY", "total_sales": {"value": 1281.8, "formatted": "1281.80"}}
  ],
  "meta": {
    "rowCount": 2,
    "truncated": false
  }
}

Add ?format=matrix for cellSetHeaders + cellSetBody position- indexed output — the shape the OLAP records endpoint returns for downstream consumers that already handle it.

Filter operators

EQ, NEQ, LT, LTE, GT, GTE, IN, BETWEEN, IS_NULL, IS_NOT_NULL. Single-value ops use value; IN / BETWEEN use values. Empty IN synthesises the trivially-false predicate (returns zero rows without a parse error).

{
  "filters": [
    {"dataset": "customer", "field": "c_state", "op": "IN",
     "values": ["CA", "NY", "TX"]},
    {"dataset": "store_sales", "field": "SS_SALES_PRICE", "op": "GT",
     "value": "50"}
  ]
}

Aggregation overrides on the fly

Swap a metric’s declared outer aggregation via values[i].aggregation. The server validates against the metric’s supportedOverrides:

// Bad: SUM on a COUNT(*) metric
{"metric": "transaction_count", "aggregation": "SUM"}

// 400 Response
{
  "error": "VALIDATION_ERROR",
  "field": "values[0].aggregation",
  "message": "aggregation 'SUM' not supported for metric 'transaction_count'",
  "available": ["COUNT"]
}

K-anonymity suppression

Configure at the server via SAIKU_AI_KANONYMITY_K (default 5) and SAIKU_AI_KANONYMITY_MASK (default null). When enabled, rows whose count-shaped metric falls below the threshold get their metric cells masked, and a top-level meta.suppressed block records the count:

{
  "records": [
    {"customer.c_state": "MA", "transaction_count": {"formatted": "null"}}
  ],
  "meta": {
    "rowCount": 4,
    "suppressed": {"count": 4, "reason": "k-anonymity threshold k=5"}
  }
}

Applies to records + matrix output, and to the derived /ai/anomaly and /ai/forecast endpoints — suppressed rows are masked before the anomaly scorer or forecaster sees them, so a small-cohort value can’t leak back through an annotation.

PII redaction

Fields marked pii: true in the Ossie YAML are stripped from the schema view entirely. Callers can’t reference them; a query that does returns VALIDATION_ERROR naming the field as “unknown”.

Step 4 — how the API teaches the agent

Every bad name comes back with a candidate list. The agent’s next attempt picks one:

// Request with a typo
{"rows": [{"dataset": "geographi", "field": "region"}], "values": [{"metric": "net_revenue"}]}

// 400
{
  "error": "VALIDATION_ERROR",
  "field": "rows[0].dataset",
  "message": "unknown dataset 'geographi'",
  "available": ["fact_pharma", "geography", "payer", "product"]
}

The same shape covers unknown fields, unknown metrics, unsupported filter operators, BETWEEN with fewer than two values, sort refs that name both metric and field, limit ≤ 0, empty rows/columns/values, and timeAxis on /anomaly + /forecast that doesn’t appear in the query.

Step 5 — preview

POST /rest/saiku/api/ai/ossie/query/preview

Same body as /query. Response:

{
  "queryId": "ossie-ai-preview-9fe75acf",
  "status": "PREVIEW",
  "generatedSql": "SELECT \"customer\".\"C_STATE\" AS \"customer.c_state\", SUM(\"store_sales\".\"SS_QUANTITY\" * \"store_sales\".\"SS_SALES_PRICE\") AS \"total_sales\" FROM \"store_sales\", \"customer\" GROUP BY \"customer\".\"C_STATE\""
}

Uses the same translator the executor runs — you see 1:1 what /query would dispatch.

Step 6 — values search

GET /rest/saiku/api/ai/ossie/values/search?connection=unknown_TPCDS&dataset=customer&field=C_STATE&q=CA

{
  "matches": ["CA"]
}

Runs SELECT DISTINCT ... WHERE UPPER(CAST(... AS VARCHAR)) LIKE '%...%' LIMIT n. Omit q for the first N distinct values.

Step 7 — row detail (drillthrough)

POST /rest/saiku/api/ai/ossie/row-detail?maxrows=5

Same body as /query. The server re-runs the shelf with values=[] so the executor emits raw rows instead of an aggregate. Response is records-format with meta.truncated: true when the row cap is hit (default 100, max 10 000).

Step 8 — async

For queries you expect to take more than a few seconds:

1. Submit — POST /query/execute-async. Same body as /query. Response 202: {"queryId": "...", "status": "PENDING"}.

2. Poll — GET /query/status/{queryId}. Status transitions: PENDING → RUNNING → DONE | FAILED | CANCELLED.

3. Fetch — GET /query/result/{queryId}. 202 with {queryId, status} while running, 200 with the full records response (or ?format=matrix) on DONE.

4. Cancel — DELETE /query/{queryId}.

Note

Every handle is scoped to the caller’s principal. A foreign session polling another user’s queryId gets a 404 — the same shape as an unknown id, so status codes can’t be used as an existence oracle. Admins can poll any handle.

Step 9 — analytics

Anomaly detection

POST /rest/saiku/api/ai/ossie/anomaly

{
  "query": {
    "connection": "unknown_TPCDS", "model": "TPCDS",
    "rows": [{"dataset": "date_dim", "field": "d_month"}],
    "values": [{"metric": "total_sales"}]
  },
  "timeAxis": "date_dim.d_month",
  "method": "zscore",
  "threshold": 1.5
}

Detectors: zscore (classic z-score, sigmas from mean), mad (median absolute deviation, robust to outliers), stl (seasonal-trend decomposition; falls back to zscore on non-seasonal data).

Response is the records shape with an anomaly-annotated metric cell where the detector flagged a point:

{
  "records": [
    {
      "date_dim.d_month": "December",
      "total_sales": {
        "value": 4235.75,
        "formatted": "4235.75",
        "anomaly": {"score": 1.85, "expected": 2900.12, "direction": "high"}
      }
    }
  ],
  "anomaly": {"method": "zscore", "threshold": 1.5, "anomalyCount": 1}
}

Forecast

POST /rest/saiku/api/ai/ossie/forecast

{
  "query": { /* ... */ },
  "timeAxis": "date_dim.d_month",
  "method": "ets",
  "horizon": 3,
  "interval": 0.95
}

Forecasters: ets, arima, prophet. Historical records untouched; projections land under a top-level forecast block keyed by metric:

{
  "records": [ /* historical rows */ ],
  "forecast": {
    "total_sales": {
      "method": "ets",
      "horizon": 3,
      "confidence": 0.95,
      "points": [
        {"index": 4, "value": 573.41, "lower": 417.73, "upper": 729.08},
        {"index": 5, "value": 598.97, "lower": 378.81, "upper": 819.14}
      ]
    }
  }
}

Step 10 — natural-language ask

GET /rest/saiku/api/ai/ossie/ask/health

{"configured": true, "provider": "anthropic (claude-sonnet-4-6)"}

Enable at the server:

• saiku.ai.ask.provider = anthropic | openai
• env ANTHROPIC_API_KEY (Anthropic) or OPENAI_API_KEY (OpenAI)
• optional saiku.ai.ask.model — model id override
• optional saiku.ai.ask.endpoint — custom base URL for OpenAI-compatible proxies (vLLM, Ollama, Together)

Then:

POST /rest/saiku/api/ai/ossie/ask

{
  "connection": "unknown_TPCDS",
  "model": "TPCDS",
  "question": "What's total revenue per state for the CA and NY brands?",
  "history": [
    {"role": "user", "content": "show me sales by product"},
    {"role": "assistant", "content": "here's revenue by brand..."}
  ]
}

history is optional — every turn is passed to the LLM so it can resolve follow-ups like “what about by state?”. Response:

{
  "question": "...",
  "connection": "unknown_TPCDS",
  "model": "TPCDS",
  "queryUsed": { /* the OssieAiQueryRequest the LLM produced */ },
  "response": { /* the full records-format execution result */ }
}

The LLM is forced into structured output via tool_use (Anthropic) / tool_choice: function (OpenAI) whose schema mirrors OssieAiQueryRequest. Off-topic questions come back as an OFF_TOPIC envelope surfaced as 400 with the reason attached.

MCP integration

The five Ossie tools are exposed via the MCP endpoint alongside the six OLAP tools:

MCP tool	REST equivalent
list_ossie_models	GET /ai/ossie/models
describe_ossie_model	GET /ai/ossie/schema/{c}/{m}
search_field_values	GET /ai/ossie/values/search
run_ossie_query	POST /ai/ossie/query
preview_ossie_query	POST /ai/ossie/query/preview

Claude Desktop, Cursor, Cline — anything that speaks MCP — sees all eleven tools once authenticated.

A typical agent loop

1. list_ossie_models or GET /models — pick a model.
2. describe_ossie_model or GET /schema/{c}/{m} — read datasets, fields, metrics, examples.
3. If the user names a value the schema didn’t sample — search_field_values or GET /values/search — confirm the spelling.
4. Build a query body from examples.simpleGroupBy (or another example) as a template, substituting the user’s dimensions and metrics.
5. run_ossie_query or POST /query — if the response is a VALIDATION_ERROR, pick from available and retry.
6. On a chart request → repeat with format: "matrix".
7. On “explain the trend” → /anomaly or /forecast.
8. On “show me the underlying rows” → /row-detail.

Or for natural-language flows: skip 4–7 and use /ask.

Where the models come from

• Write your own OSI YAML — declare datasets, metrics, relationships. Point Saiku at it via a .sds datasource file.
• Point at an existing dbt project — the dbt hookup guide walks through the ~200-line converter we ship that reads MetricFlow YAML and emits OSI-compliant YAML.
• Export a Mondrian schema — the saiku ossie-export CLI in the Saiku launcher converts a Mondrian XML schema to OSI YAML.

See also

• Ossie / OSI on Apache — the spec + example YAMLs
• MCP server — the tool wrapper for LLM agents
• dbt hookup — point Saiku at your existing dbt project
• OLAP AI Query API — the Mondrian-cube counterpart of this API