REST API¶
Since 2.1.3 XLTable exposes its cubes over plain HTTP: three endpoints return the list of cubes, a cube schema and an aggregated pivot query as JSON. It is the same engine, the same security roles, the same row-level security, cache and logs that serve Excel PivotTables and AI assistants — without XMLA and without an MCP client. Use it from ETL pipelines, scripts, internal portals and BI tools that can call an HTTP endpoint.
As everywhere in XLTable, the API takes names, not SQL: the client sends cube, dimension and measure names, the server builds and runs the query. Raw SQL access to the warehouse is never exposed.
BI tools that speak to databases rather than to HTTP APIs (DataLens, DBeaver) connect through the SQL endpoint instead: the same cubes as PostgreSQL tables, built on top of this API.
Endpoints¶
Endpoint |
What it returns |
|---|---|
|
|
|
The cube schema: dimensions with their levels, measures, folders and
the semantics from the cube definition (descriptions, synonyms,
|
|
An aggregated pivot query. The JSON body is the pivot specification
of the |
With the olap_definition cube source (CUBE_SOURCE=database) and
more than one database in the warehouse, pass the database name: a
database query parameter on the GET endpoints, a database field
in the body of POST /api/query. A single database is selected
automatically. Only the databases visible to the user under
DATABASES / GROUP_DATABASES count: a hidden one
named in the request is reported as unknown (400).
Query¶
Request:
{
"cube": "Sales",
"dimensions": ["Store", "Month"],
"measures": ["Amount", "Quantity"],
"filters": [
{"level": "Year", "type": "in", "values": ["2025"]},
{"level": "Month", "type": "between", "values": ["2025-01", "2025-06"]}
],
"limit": 1000
}
dimensions and measures are names from the cube schema (case
sensitive); at least one of them is required. filters are applied before
aggregation: in, not_in, between (exactly [low, high]) and
paths — member paths down a hierarchy, "level" being its top level on
the path: {"level": "Year", "type": "paths", "values": [["2024"],
["2023", "2023-Q1"]]} keeps the whole of 2024 plus one quarter of 2023 (an
OR of the paths, each path an AND of its levels). A user’s own access
filters (row-level security) are always added on top of the request filters
and cannot be widened by them. limit caps the number of result rows (1000
by default).
Response:
{
"cube": "Sales",
"columns": [
{"name": "Store", "type": "string"},
{"name": "Month", "type": "date"},
{"name": "Amount", "type": "number"},
{"name": "Quantity", "type": "number"}
],
"rows": [
{"Store": "North", "Month": "2025-01-01", "Amount": 1250.5, "Quantity": 42}
],
"row_count": 1,
"truncated": false
}
columns lists the result columns in order with a type for formatting and
sorting on the client — number, string, date or bool (dates
travel as ISO strings in JSON, so the type is the only way to tell them from
text). truncated: true means the query produced more rows than
limit; pass a larger limit for the rest. Every row is a full
combination of the requested levels — there are no subtotal rows; for totals
run another query with fewer dimensions (or none: only measures).
Example with curl and a user account:
curl -u analyst:secret https://xltable.company.local/api/cubes
curl -u analyst:secret -H "Content-Type: application/json" \
-d '{"cube":"Sales","dimensions":["Store"],"measures":["Amount"]}' \
https://xltable.company.local/api/query
Licensing¶
In the server edition the REST API is a license feature flag of its own:
the boolean api field of the license. Without it every data endpoint
answers 403 with "REST API is not included in your license";
Excel and MCP are not affected (and the mcp flag does not open the
API — the two are independent). With the flag, named seats are counted per
user in the one shared pool: a user occupies one seat whether they come from
Excel, an AI assistant or the API. The license page of the admin console
shows REST API: included when the flag is present.
Errors¶
Errors come back as {"error": "<message>"}:
Status |
When |
|---|---|
|
The request is not a JSON object, a cube, level or measure name is unknown, a filter is malformed, the database is unknown, or the engine refused the query (a cube closed to the user by its roles, a syntax problem in the cube definition). The message names the problem and, where it helps, the valid names. |
|
No valid credentials / the user has no access, the license lacks the
|
|
The warehouse connection failed, or the server is overloaded
( |
Logging and cache¶
API queries go through the same path as MCP queries: with
WRITE_LOG the console shows the PIVOT SPEC, SQL and
RESULT blocks, the log folder gets the SQL and Jinja dumps, and the
shared SQL result cache serves identical queries to Excel, assistants and
API clients alike (see Logging and cache).
Reports of the web pivot table¶
The web pivot table (/reports) keeps its reports as JSON documents in
REPORTS_FOLDER; the same documents are available through the API,
so scripts and AI agents can create and change reports:
GET /api/reports— the calling user’s reports (an administrator gets everyone’s):id,title,owner,cube,database,updatedandcan_edit;POST /api/reports— create (the body is the document,ownerand the timestamps are set by the server); the response carries the newid;GET /api/reports/<id>—{"id", "report", "can_edit"};PUT /api/reports/<id>— replace the document,DELETE— delete: the owner or an administrator (ADMIN_GROUPS) only;GET /api/schema/report— the JSON Schema of the document.
Reading a report is open to every authenticated user: the data it shows is
cut by the user’s own roles and access filters. The queries part of a
document is the specification of POST /api/pivot; the charts part is
the presentation (sort, totals, expanded members). The routes need the
web license feature.
Cube status for analysts¶
The Cubes page of the web application is built on five
GET endpoints. They are open to analysts (ANALYST_GROUPS) and
administrators only — the definition text, the SQL of the sources and the
roles are internal to the cube; everyone else gets 403. The database
query parameter selects the catalog as on the other endpoints.
GET /api/cubes-overview—{"source", "cubes": [{"name", "description", "access", "status", "error", "hint", "modified", "path"}], "can_show_all", "hidden", "create_url"}. With cube files (source: "folder") every cube carries itsstatus(ok/error), the file path and modification time; with theolap_definitiontable the list comes without statuses — ask/issuesper cube. Cubes closed to the user by their roles are left out and counted inhidden;?all=1lists them too (administrators and the free edition only,can_show_all).GET /api/cubes/<cube>/issues—{"cube", "status", "issues": [{"severity", "check_id", "line", "section", "message"}], "error", "hint"}: the findings of the definition checker (ERROR/WARNING) with line numbers, and the parse result of the server for this user.GET /api/cubes/<cube>/definition—{"cube", "text", "path", "modified"}: the definition text as the engine reads it, read-only.GET /api/cubes/<cube>/structure— the cube as the server understood it for the calling user:dimensionswithlevels,measuresandcalculated_fields— every field with the same keys (linein the definition text,name,sql,alias,table,format),sources(type,line,fields) androles(groups, visibility,access_filterswith lines). UnlikeGET /api/cubes/<cube>, which any user and MCP agents receive, this document includes the SQL.GET /api/cubes/<cube>/ossie— the cube definition as an Apache Ossie semantic model, a YAML file attachment (<cube>.ossie.yaml);?format=jsonfor JSON,?roles=1to include the roles and access filters. The mapping and the vendor extension are described in Export to Apache Ossie.
POST /api/pivot accepts "debug": true from an analyst or an
administrator: the response then carries sql (the text sent to the
warehouse), elapsed (seconds) and cached (the result came from the
SQL cache). For other users the flag is ignored.