Exporting to Apache Ossie

Apache Ossie (formerly Open Semantic Interchange) is an incubating Apache project defining a portable YAML/JSON specification for semantic model exchange across analytics, AI, and BI tools. Saiku ships an exporter that reads any Mondrian schema and produces a valid Ossie document — the same measure and dimension definitions become consumable by dbt, GoodData, Snowflake, Databricks, Salesforce, and every other tool with an Ossie converter.

Draft spec

Ossie is at v0.2.0.dev0 and marked as a draft. The Saiku exporter pins to this version — expect breaking-shape updates as Ossie stabilises. Track the apache/ossie roadmap for the current-effort working groups (hierarchy semantics + metric semantics are the two that matter most for Mondrian-shaped schemas).

Quick start

Pass a Mondrian XML schema in, get Ossie YAML out:

java -jar saiku-<version>.jar ossie-export \
  --in saiku-home/data/Pharma.xml \
  --out pharma.ossie.yaml

Or use stdin/stdout for scripting:

cat schema.xml | saiku ossie-export > schema.ossie.yaml

The command exits non-zero if input can’t be read (exit 2) or output can’t be written (exit 3), with a diagnostic line on stderr. Successful runs report how many semantic models were written and (if any) which cubes were skipped:

ossie-export: wrote 1 semantic model(s) to pharma.ossie.yaml

What maps to what

The exporter follows this mapping table 1:1. Everything except explicitly-listed exceptions lands in the target column verbatim.

For a Mondrian 4 schema — the shape everything Saiku ships uses:

Mondrian element	Ossie output
<Cube>	One semantic_model entry
<MeasureGroup table="...">	A fact dataset per measure group
<Dimension table="..." key="...">	One dim dataset
<Attribute keyColumn / nameColumn>	One field, with expression.dialects[0] = ANSI_SQL:<column>. Fields come from attributes, not levels.
<Attribute levelType="TimeYears"> (or Quarters/Months/Days)	Field gains dimension.is_time: true
Annotations on the <Level> that references an attribute	Lifted onto that attribute’s field — so annotate the level, not the attribute
<ForeignKeyLink dimension="..." foreignKeyColumn="...">	One relationship, fact → dim
<NoLink dimension="...">	Deliberately nothing — it declares the absence of a join
<Measure column="..." aggregator="sum">	One metric with both ANSI_SQL (SUM(fact.column)) and MDX ([Measures].[Name]) dialects
<Measure aggregator="distinct-count">	ANSI_SQL: COUNT(DISTINCT fact.column) — count, min, max and avg all translate too
<CalculatedMember><Formula>…</Formula></CalculatedMember>	Metric with an MDX-only expression — there’s no reliable ANSI SQL translation for an MDX formula
<Annotation name="saiku.semantic.description">	The element’s ai_context.instructions
<Annotation name="saiku.semantic.synonyms">	The element’s ai_context.synonyms[] (CSV, split and trimmed)
<Annotation name="saiku.semantic.pii">true	custom_extensions: [{vendor_name: SAIKU, data: '{"pii":true}'}] — a JSON boolean, not a string
saiku.semantic.{cardinality,grain,aggregation_kind,required_filters}	The same SAIKU vendor extension, values serialised as JSON strings

Older per-cube schemas map the same way with the older spellings: the cube’s own <Table> becomes the fact dataset, <Dimension foreignKey> plus the hierarchy’s primaryKey make the relationship, and fields come from <Level column="…">.

Worked example — Pharma cube

Input excerpt from saiku-home/data/Pharma.xml:

<Dimension name="Prescriber" table="dim_prescriber" key="Prescriber">
  <Attributes>
    <Attribute name="Prescriber" keyColumn="prescriberkey"
               nameColumn="prescribername" hasHierarchy="false"/>
    <Attribute name="NPI" keyColumn="prescribernpi" hasHierarchy="false"/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name="Prescriber" allMemberName="All Prescribers">
      <Level attribute="Prescriber">
        <Annotations>
          <Annotation name="saiku.semantic.pii">true</Annotation>
        </Annotations>
      </Level>
    </Hierarchy>
  </Hierarchies>
</Dimension>

<MeasureGroup name="Rx" table="fact_pharma">
  <Measures>
    <Measure name="Quantity" column="quantity" aggregator="sum" formatString="#,##0"/>
  </Measures>
  <DimensionLinks>
    <ForeignKeyLink dimension="Prescriber" foreignKeyColumn="prescriberkey"/>
  </DimensionLinks>
</MeasureGroup>

Produces this Ossie fragment:

version: 0.2.0.dev0
semantic_model:
- name: Pharma Rx
  datasets:
  - name: dim_prescriber
    source: dim_prescriber
    fields:
    - name: Prescriber
      expression:
        dialects:
        - dialect: ANSI_SQL
          expression: prescribername
      custom_extensions:
      - data: "{\"pii\":true}"
        vendor_name: SAIKU
    - name: NPI
      expression:
        dialects:
        - dialect: ANSI_SQL
          expression: prescribernpi
      custom_extensions:
      - data: "{\"pii\":true}"
        vendor_name: SAIKU
    primary_key:
    - prescriberkey
  relationships:
  - name: fact_pharma_to_dim_prescriber
    from: fact_pharma
    to: dim_prescriber
    from_columns:
    - prescriberkey
    to_columns:
    - prescriberkey
  metrics:
  - name: Quantity
    expression:
      dialects:
      - dialect: ANSI_SQL
        expression: SUM(fact_pharma.quantity)
      - dialect: MDX
        expression: "[Measures].[Quantity]"

Two things worth noticing. The field’s SQL expression is prescribername — the display column, not the key — because that’s the honest single-column answer for a member. And the PII annotation, declared on the <Level>, has travelled onto the field as a SAIKU custom_extension, so a downstream consumer can act on it without knowing anything about Mondrian.

What isn’t (yet) supported

The converter reads both schema shapes — Mondrian 4’s <MeasureGroups> / <Dimensions> model and the older per-cube <Dimension> / <Hierarchy> embedding. Since every schema Saiku ships is Mondrian 4, that’s the ordinary path, not the exotic one: exporting the bundled FoodMart schema writes all six of its models, Warehouse and Sales included.

What still doesn’t come across cleanly:

• Parent-child hierarchies. Emitted as flat levels. The recursive relationship isn’t expressible in Ossie’s current dataset-oriented shape — the hierarchy working group is in flight upstream.
• Attributes with no resolvable column. A field needs an expression, so an attribute whose column can’t be determined is dropped rather than emitted as an invalid one. In practice this bites on compound keys, where the exporter takes the display column (or the last key column) as the honest single-column answer.
• Cubes that produce no datasets at all — typically a cube whose fact table can’t be resolved. Those are reported by name on stderr and left out, rather than failing the whole document.

Consuming the output

Ossie YAML is validation-checked against apache/ossie’s osi-schema.json — every file the exporter produces round-trips through the schema with zero findings (there’s a unit test that asserts this on every commit). Downstream consumers:

• dbt — Ossie’s reference converters include a dbt module.
• Snowflake, Salesforce, GoodData, Polaris, Databricks — same directory.
• Apache Superset and Metabase — no first-party converter yet at time of writing, but the SQL-over-Ossie work on the Saiku roadmap (parent epic saiku#1387) will make Saiku itself queryable as a semantic layer via SQL.

Related

• Saiku semantic annotations — the annotation keys the exporter reads (which map into Ossie’s ai_context + custom_extensions).
• Well-known Ossie extensions — the receiving end. Once your schema exports to Ossie YAML, the saiku.display / saiku.roles / saiku.pii well-knowns are how you author annotations directly there.
• Schema structure — where the <Annotations> block lives inside your Mondrian schema.
• apache/ossie repository — upstream spec, converters, roadmap.