Skip to content
Saiku Cloud

AI Query API — OLAP cubes

The AI Query API lets an agent query a Mondrian OLAP cube without ever seeing or writing MDX. The agent fetches a self-describing schema, fills in a JSON request against it, the server validates every name, executes, and returns typed records. If a name is wrong, the error tells the agent exactly what to fix — no prompt engineering required.

This is the OLAP counterpart of the AI Query API for Ossie models (the SQL/semantic-YAML surface). Same discipline, same guardrails; this one works against Mondrian cubes instead of Ossie YAML. Sitting on top of it is the natural-language /ai/ask layer — the ask endpoint translates a plain-English question into exactly the request body documented here.

Quick orientation

Three endpoints cover ~90% of agent use:

EndpointPurpose
GET /rest/saiku/api/ai/cubesList every cube the caller can query.
GET /rest/saiku/api/ai/schema/{cubeId}Self-describing schema — measures, dimensions, hierarchies, levels, sample members, synonyms, and the request JSON Schema.
POST /rest/saiku/api/ai/queryExecute a typed request. Records-format response by default; matrix on ?format=matrix.

Long-tail endpoints (documented on the linked pages):

EndpointPurpose
GET /rest/saiku/api/ai/members/searchSubstring lookup of members on a level.
POST /rest/saiku/api/ai/scenario/whatifWrite-back what-if simulation.
POST /rest/saiku/api/ai/query/execute-asyncSubmit for background execution (poll status / result).
POST /rest/saiku/api/ai/anomalyRuns the query then flags anomalies along a time axis.
POST /rest/saiku/api/ai/forecastProjects future points (ETS / ARIMA / Prophet).
POST /rest/saiku/api/ai/askNatural-language ask. Needs an LLM key.

The cubeId everywhere is the connection/catalog/schema/cubeName quadruplet joined with /. All endpoints require an authenticated session; POST endpoints require the CSRF cookie/header pair.

Step 1 — list the cubes

GET /rest/saiku/api/ai/cubes
[
  {
    "connectionName": "unknown_foodmart",
    "catalog": "FoodMart",
    "schema": "FoodMart",
    "cubeName": "Sales",
    "cubeCaption": "Sales",
    "defaultMeasure": "Unit Sales",
    "measureCount": 8
  }
]

The connectionName/catalog/schema/cubeName quadruplet is the cube identifier used everywhere else.

Step 2 — fetch the typed schema

GET /rest/saiku/api/ai/schema/unknown_foodmart/FoodMart/FoodMart/Sales

Don’t URL-encode the slashes — the path template accepts the multi-segment connection/catalog/schema/cubeName form directly. The response is dense — this is what makes the API self-describing:

{
  "cubeId": "unknown_foodmart/FoodMart/FoodMart/Sales",
  "measures": {
    "store sales": {
      "name": "Store Sales",
      "uniqueName": "[Measures].[Store Sales]",
      "description": "Net retail revenue in USD across all transactions.",
      "synonyms": ["revenue", "turnover", "top-line"],   // accepted as `name` on input
      "unit": "USD",
      "aggregationKind": "sum"
    }
    // …8 measures total…
  },
  "measureAliases": { "revenue": "store sales", "turnover": "store sales" },
  "dimensions": {
    "time": {
      "name": "Time", "uniqueName": "[Time]",
      "hierarchies": {
        "time by": {
          "name": "Time By",
          "levels": {
            "quarter": {
              "name": "Quarter",
              "synonyms": ["quarterly", "qtr"],           // accepted as `level` on input
              "sampleMembers": [
                { "caption": "Q1", "uniqueName": "[Time].[Time By].[Quarter].&[Q1]" }
              ]
            }
          }
        }
      }
    }
  }
}

Synonyms and display-name aliases are accepted as input anywhere the canonical name is — an agent can say "revenue" and the server resolves it to Store Sales.

Step 3 — execute a query

“Show Store Sales and Unit Sales by Product Family, top 3 by Store Sales.”

POST /rest/saiku/api/ai/query
Content-Type: application/json
{
  "cube": "unknown_foodmart/FoodMart/FoodMart/Sales",
  "measures": [{ "name": "Store Sales" }, { "name": "Unit Sales" }],
  "rows": [{ "dimension": "Product", "hierarchy": "Products", "level": "Product Family" }],
  "order": [{ "by": "Store Sales", "direction": "desc" }],
  "limit": 3
}

cube accepts either the 4-segment object form or the compact "connection/catalog/schema/cube" string.

Response (200):

{
  "status": "SUCCESS",
  "format": "records",
  "metadata": {
    "generatedMdx": "SELECT NON EMPTY {[Measures].[Store Sales], [Measures].[Unit Sales]} ON COLUMNS, NON EMPTY TopCount([Product].[Products].[Product Family].Members, 3, [Measures].[Store Sales]) ON ROWS FROM [Sales]",
    "freshness": { "computedAtMillis": 1715798421042, "cached": false }
  },
  "data": [
    {
      "Product Family": "Food",
      "Store Sales": { "value": 409035.59, "formatted": "409,035.59", "unit": null },
      "Unit Sales":  { "value": 191940.0,  "formatted": "191,940",    "unit": null }
    }
  ],
  "totalRows": 3,
  "runtimeMs": 421
}

Each row is a self-describing object keyed by the human column caption. Every numeric cell is a typed envelope:

  • value — parsed number (for math / sorting / charting)
  • formatted — Mondrian’s pre-formatted display string (for UI)
  • unit — sniffed from the formatted string (USD, GBP, EUR, JPY, %) or null

generatedMdx is echoed for debugging; agents typically ignore it.

Matrix format

Position-indexed clients opt out of records with ?format=matrix — the response carries matrix instead of data, each row keyed by the column index as a string, cells still the typed {value, formatted, unit} envelope.

Privacy: k-anonymity

When ai.kAnonymity is set (default 5; 0 disables), the server masks small-cell measure values before the result crosses the AI boundary — any row whose in-result count measure falls below k has its measure cells masked with suppressed: true. Applies to records and matrix, and to the derived /ai/anomaly + /ai/forecast endpoints.

Step 4 — validation teaches the agent

Supply a name that doesn’t resolve and the server returns 400 with a body the agent can self-correct from — no retry prompting needed:

{
  "status": "VALIDATION_ERROR",
  "error": "Unknown measure 'Made Up Measure'",
  "field": "measures[].name",
  "available": ["Unit Sales", "Store Cost", "Store Sales", "Profit", "Customer Count"]
}

The agent reads field (what was wrong), reads available[] (the legal values), fixes, and retries. Two layers run: a shape validator (JSON Schema — missing fields, wrong types, enum violations, with array-indexed field paths like filters[0].op) and a semantic validator (cube resolution — names that are shape-valid but don’t exist). The contract is identical either way: read field, read available[], fix, retry.

Where to go next

  • AI Ask API — the natural-language layer that produces the request body above, plus the members/search, scenario/whatif, and pii-suggestions companion endpoints.
  • Agent Spaces — personas that scope this surface to an allowlisted cube set.
  • Agent Skills — admin-authored workflows discoverable from every ask.
  • MCP server — the same typed surface for external agent hosts (list_cubes, describe_cube, run_query, …).
  • AI Query API for Ossie models — the SQL/semantic-YAML counterpart.
  • AI inference API — a different surface: the design-time cube designer (profile → propose → render), not query execution.