Export to Apache Ossie¶
Apache Ossie is an open, vendor-neutral format for semantic models — datasets, fields, metrics and relationships in one YAML document, read by dbt, Snowflake and other tools of the modern data stack. Since 2.1.4 XLTable exports a cube definition to this format: the measures, dimensions, joins, descriptions and synonyms you wrote once for Excel can be handed to another tool or to an AI agent without rewriting them.
The export is a pure function of the definition text — it runs without a query to the warehouse and without changing the cube. Everything Ossie cannot express (hierarchies, formats, smart measures, Jinja, roles) is kept in the document as a vendor extension instead of being dropped, so the export loses nothing — see What goes to the vendor extension, and why.
How to get the document¶
On the Cubes page of the web application open the cube,
switch to the Definition tab and click Export → Apache Ossie (YAML)
above the text: the browser saves <cube>.ossie.yaml. The page is open to
analysts (ANALYST_GROUPS) and administrators, like the rest of
the cube internals.
The same document is served by the REST API — for scripts, CI pipelines and agents:
curl -u analyst:secret -o Sales.ossie.yaml \
https://xltable.company.local/api/cubes/Sales/ossie
GET /api/cubes/<cube>/ossie answers with text/yaml and a
Content-Disposition: attachment header. Query parameters:
Parameter |
Meaning |
|---|---|
|
The catalog with the |
|
The same document as |
|
Include the cube’s roles — user groups, visibility and the access filters of the row-level security — in the vendor extension. They are left out by default: access filters are internal to the company and the document is meant to leave the server. |
The cube is exported from its definition text as the engine reads it,
without the expansion of smart measures: the derived
measures (--offset, --exclude, --semi) stay single lines with
their tags in the extension, the generated twin sources do not appear. The
export works on the text, so the issues /issues reports do not stop it;
400 answers only what cannot be exported at all — a cube without
sources, a source without FROM, a subquery in FROM.
Mapping¶
One Ossie document per cube. The document version is 0.2.0.dev0 — the
current Ossie core specification, the one dbt and Snowflake target.
XLTable |
Ossie |
Notes |
|---|---|---|
Cube id, |
|
|
|
|
The dataset is named by the alias because Ossie expressions
reference |
A source built on a CTE ( |
|
Not portable — the CTE is SQL of the warehouse. |
A dimension level: |
|
The grain of the |
The fact columns the measures read ( |
|
|
A measure: |
|
Ossie metrics have no label, so the display name becomes the
|
The measure expression |
|
Anything else ( |
A calculated field
( |
|
Ossie does not define metric-to-metric references. When inlining is
impossible (a reference to a smart measure or to Jinja) the
expression is written as is and the extension carries
|
A metric with the base expression; the tag in the extension
( |
The derived measure keeps its name and format. |
|
|
|
Composite keys give several columns. |
|
The relationship as above plus |
|
|
Hierarchies are on the Ossie roadmap, not in the format yet. |
|
Roles, |
Not exported; with |
|
The script in the extension of the dataset, marked non-portable |
||
|
A fragment of the export of the unified example:
version: 0.2.0.dev0
name: myOLAPcube
description: "Sales fact cube …"
datasets:
- name: sales
source: db.Sales
fields:
- name: qty
expression:
dialects:
- dialect: OSSIE_SQL_2026
expression: qty
custom_extensions:
- vendor_name: XLTABLE
data: "{\"label\": \"Sales\", \"drillthrough\": [\"stores_name\", …]}"
- name: times
source: calendar
fields:
- name: times_month_str
expression:
dialects:
- dialect: OSSIE_SQL_2026
expression: month_str
label: Month
dimension:
is_time: true
custom_extensions:
- vendor_name: XLTABLE
data: "{\"date\": \"month\"}"
custom_extensions:
- vendor_name: XLTABLE
data: "{\"label\": \"Dates\", \"cte\": \"with calendar as (…)\", \"hierarchies\": {\"Dates\": [\"times_year_str\", …]}}"
metrics:
- name: sales_sum_qty
expression:
dialects:
- dialect: OSSIE_SQL_2026
expression: SUM(sales.qty)
description: Sales Quantity
ai_context:
synonyms:
- quantity
- units
- pieces
instructions: Units sold, pcs
custom_extensions:
- vendor_name: XLTABLE
data: "{\"format\": \"#,##0;-#,##0\"}"
relationships:
- name: sales_to_stores
from: sales
to: stores
from_columns:
- store
to_columns:
- id
What goes to the vendor extension, and why¶
Ossie covers the core of a semantic model — datasets, fields, metrics,
relationships, descriptions and AI context — and leaves the rest to
custom_extensions: a list of {vendor_name, data} entries on any
object, data being a JSON string. This is how dbt marks its
non-portable metric semantics, and XLTable does the same: every object gets
at most one entry with vendor_name: XLTABLE, and its data holds what
the format has no place for:
the source name (
label) and the--formatstrings — Excel presentation;hierarchies,folders,hidden— the field list as Excel shows it;smart— the smart-measure tags of a derived measure (offset,exclude,semi);calculated_field— a calculated field that could not be inlined, with its references;non_portable(with thedialectof the warehouse) — an expression written in the warehouse dialect orANSI_SQLbecause it is outside the Ossie expression language;date— the grain of a calendar level;cte,jinja,drillthrough— the SQL plumbing of a source;roles— only withroles=1.
A tool that does not know XLTable ignores the extensions and still gets a valid model with every dataset, field, metric and join; a tool that does can rebuild the cube from the document.
Limitations¶
Export only. XLTable does not import Ossie documents — the cube definition in SQL stays the source of truth; an importer (Ossie → draft cube) is planned together with the SSAS importer.
The portable expression language of Ossie is small: aggregates over columns, arithmetic and
CASE. Warehouse functions (date arithmetic,uniqExact,toStartOfMonthand the like) are exported in the warehouse dialect when Ossie names it —SNOWFLAKE,BIGQUERY,DATABRICKS— and asANSI_SQLwith anon_portablemark and the dialect name for ClickHouse, Trino, Greenplum, StarRocks and DuckDB. The text is the SQL of your warehouse either way.Smart measures are exported by their tags, not by the SQL the server generates for them: another tool sees the base measure and the intent (previous period, without the dimension, value at the last date), not a ready formula.
Roles and access filters are left out unless
roles=1; the document is not a way to transfer security — set it up in the target tool.The format version is
0.2.0.dev0: Ossie is young and the specification still moves. The document validates against the JSON Schema of that version; a later XLTable release follows the format.