SQL adapter over Apache Ossie

Saiku ships a Calcite SQL adapter that reads Apache Ossie YAML — the same format the Ossie exporter produces from your Mondrian schema — and exposes it as a queryable JDBC surface. Point any SQL client at it and you can SELECT from your Ossie datasets exactly as if they were real database tables. Under the hood, Calcite plans the query and pushes it down to your actual warehouse as native SQL.

What ships

The full first slice of the semantic-SQL surface: SELECT / WHERE / GROUP BY / explicit JOIN pushdown, scalar metric SELECT, relationship join views, AND auto-injected joins — the true semantic-layer prop, where users write FROM ORDERS o, CUSTOMERS c with no JOIN clause and Calcite injects the predicate from Ossie relationships. See “Auto-injected joins” below.

What you get

• Standard SQL over your Ossie datasets — anything Calcite understands, which is a large superset of ANSI SQL.
• Pushdown to the warehouse. Calcite reads the query, plans it against your warehouse (Postgres, Snowflake, H2, whatever), and emits native SQL. Aggregates run in the warehouse, not in the JVM.
• JDBC-native. Works with any JDBC client: dbt, DBeaver, Tableau, Power BI, psql, JetBrains DataGrip, custom code.
• Ossie renames survive. If your exporter emitted dataset names that don’t match the underlying warehouse tables (via Ossie’s source field), the adapter transparently maps back — SELECT * FROM CUSTOMERS becomes SELECT * FROM public.dim_customer_v2 at the warehouse.

Quick start

1. Produce the Ossie YAML

saiku ossie-export --in saiku-home/data/Pharma.xml --out pharma.ossie.yaml

See Exporting to Apache Ossie for the mapping table and worked example.

2. Write a Calcite connect model

Calcite JDBC connections take a JSON connect model that tells Calcite which factory to instantiate:

{
  "version": "1.0",
  "defaultSchema": "PHARMA",
  "schemas": [{
    "name": "PHARMA",
    "type": "custom",
    "factory": "org.saiku.sql.adapter.OssieSchemaFactory",
    "operand": {
      "ossieYaml": "/absolute/path/to/pharma.ossie.yaml",
      "jdbcUrl": "jdbc:postgresql://localhost:5432/warehouse",
      "jdbcUser": "app",
      "jdbcPassword": "changeme"
    }
  }]
}

Operand keys:

Key	Required	Description
ossieYaml	yes	Absolute path to the Ossie YAML file.
modelName	no	Name of the semantic_model[] entry to expose when the document carries multiple. Defaults to the first.
jdbcUrl	strongly encouraged	Warehouse JDBC URL. Without it, tables register but queries return zero rows.
jdbcUser, jdbcPassword	as needed	Warehouse credentials.

3. Connect

Properties p = new Properties();
p.put("model", "/path/to/model.json");
p.put("caseSensitive", "false");
try (Connection c = DriverManager.getConnection("jdbc:calcite:", p)) {
    var rs = c.createStatement().executeQuery(
        "SELECT REGION, COUNT(*) FROM PHARMA.PRESCRIBER GROUP BY REGION");
    while (rs.next()) System.out.println(rs.getString(1) + " → " + rs.getInt(2));
}

Or from any JDBC client — connection URL jdbc:calcite:model=/path/to/model.json.

4. Or run it as a network endpoint

For remote clients, use the saiku sql-serve CLI subcommand. It exposes two endpoints — an Apache Avatica endpoint for Avatica-aware clients AND a native Postgres wire endpoint for psql / pgAdmin / Tableau / DBeaver / dbt-postgres. Enable either or both:

saiku sql-serve \
  --ossie pharma.ossie.yaml \
  --schema PHARMA \
  --jdbc-url jdbc:postgresql://warehouse:5432/prod \
  --jdbc-user app --jdbc-password changeme \
  --port 8765 \
  --pg-port 5432

The server logs both URLs on startup. Clients then connect via:

Avatica:

jdbc:avatica:remote:url=http://localhost:8765
# with serialization=protobuf

Postgres wire (any native PG client):

# psql
PGSSLMODE=disable psql -h localhost -p 5432 saiku

# JDBC — pgjdbc defaults (extended query mode) work; simple mode is also fine
jdbc:postgresql://localhost:5432/saiku?sslmode=disable

Parameterised queries via PreparedStatement.setString(1, ...) etc. work out of the box.

No SSL yet

The PG-wire endpoint replies 'N' to SSL handshakes so clients fall back to plaintext. Set sslmode=disable on JDBC clients or run psql with PGSSLMODE=disable. SSL/TLS + SCRAM auth are hardening follow-ups.

Both simple and extended query modes work. Default pgjdbc (extended) connects; preferQueryMode=simple is optional, not required.

What works today

SQL feature	Status	Notes
SELECT from a dataset	✅	Pushed down to the warehouse.
WHERE on dataset columns	✅	Pushed down.
GROUP BY + aggregates	✅	Aggregates run in the warehouse.
Explicit JOIN … ON between datasets	✅	Join predicate pushes down.
ORDER BY, LIMIT	✅	Pushed down where the warehouse dialect supports it.
information_schema / DatabaseMetaData.getTables()	✅	Datasets, metrics AND join views discoverable by BI tools.
Rename mapping (Ossie dataset name → warehouse table via source)	✅	Transparent.
Case-insensitive fallback (H2 UPPER vs Postgres lower)	✅	Adapter tries all cases.
Scalar metric SELECT — SELECT * FROM PHARMA.TOTAL_QUANTITY	✅	Expands the metric’s ANSI_SQL against its home dataset. Return type derived from the underlying column type (no coarse DOUBLE/BIGINT guesses).
Relationship join views — SELECT ... FROM PHARMA.FACT_PHARMA_JOIN_PRESCRIBER ...	✅	One view per Ossie relationship, named <from>_JOIN_<to>. The JOIN predicate lives in the YAML, not the query. Calcite pushes the whole thing down as a single JOIN — no runtime overhead.
Auto-injected joins — SELECT c.x, SUM(o.y) FROM ORDERS o, CUSTOMERS c GROUP BY c.x (no JOIN clause)	✅	Custom Calcite planner rule detects the Cartesian join between two Ossie datasets and injects the ON predicate from the relationship. Same result as writing the JOIN by hand. See “Auto-injected joins” below.
MDX-only metrics (calculated members)	✅ (invisible)	Correctly not exposed on the SQL surface. They live in Mondrian.

Not yet supported

Tracked on the Ossie/SQL epic:

• SSL/TLS + auth on both endpoints. Currently anonymous + plaintext. Table-stakes before any prod deployment.
• Binary format parameters/results on the PG-wire endpoint — Bind now decodes big-endian INT2/INT4/INT8 correctly (most BI-tool setInt/setLong calls) but full type-directed binary decoding (BOOL/DATE/NUMERIC/TIMESTAMP against the statement’s declared parameter OIDs) is a follow-up.
• Portal suspension — Execute always returns all rows regardless of the client’s maxRows request. Fine for interactive BI queries; matters for large-cursor pagination.
• Three-way auto-joins — FROM A, B, C where A↔B and B↔C both exist. Nested rewrites should cascade but this hasn’t been verified in tests yet.
• Cross-schema joins — joining an Ossie dataset with a non-Ossie table (from a different Calcite sub-schema). The auto-join rule bails when the two sides belong to different schemas.
• jdbc:saiku:// connect scheme. Today users go through jdbc:calcite: + a model.json file. A first-party JDBC driver comes with the Postgres wire work.
• Postgres wire protocol. Today the surface is JDBC-only. jdbc:saiku: on the wire (so psql / libpq clients connect natively) is #1386.

Worked example — Pharma cube

Given the Pharma Ossie YAML the exporter produces:

version: 0.2.0.dev0
semantic_model:
- name: Pharma Rx
  datasets:
  - name: fact_pharma
    source: public.fact_pharma
  - name: Prescriber
    source: public.dim_prescriber
    primary_key: [prescriberkey]
  relationships:
  - name: fact_pharma_to_Prescriber
    from: fact_pharma
    to: Prescriber
    from_columns: [prescriberkey]
    to_columns: [prescriberkey]

You can query it like:

-- Simple dataset scan.
SELECT * FROM "Pharma Rx".fact_pharma LIMIT 10;

-- Aggregate — pushed down as SUM() to Postgres.
SELECT SUM(quantity_units) AS total_units FROM "Pharma Rx".fact_pharma;

-- Scalar metric SELECT — same result as above, but the aggregate
-- expression lives in the Ossie YAML instead of the query.
SELECT * FROM "Pharma Rx"."Quantity";

-- Join via the relationship's foreign key.
SELECT
    p.prescribername,
    SUM(f.quantity_units) AS units,
    COUNT(*) AS rx_count
FROM "Pharma Rx".fact_pharma f
JOIN "Pharma Rx"."Prescriber" p ON f.prescriberkey = p.prescriberkey
GROUP BY p.prescribername
ORDER BY units DESC
LIMIT 20;

-- Same query using the pre-materialised join view — the ON predicate
-- lives in the Ossie YAML. Users don't need to remember which columns
-- link fact_pharma to Prescriber.
SELECT
    prescribername,
    SUM(quantity_units) AS units,
    COUNT(*) AS rx_count
FROM "Pharma Rx".fact_pharma_JOIN_Prescriber
GROUP BY prescribername
ORDER BY units DESC
LIMIT 20;

All five run entirely in Postgres via Calcite’s JDBC pushdown — the JVM never sees individual rows for the aggregates.

Auto-injected joins

Users don’t need to remember which columns link ORDERS to CUSTOMERS. Just list both datasets in FROM and Calcite will inject the ON predicate for you:

-- User writes:
SELECT c.REGION, SUM(o.AMOUNT) AS TOTAL
FROM "Pharma Rx".fact_pharma o, "Pharma Rx"."Prescriber" c
GROUP BY c.REGION;

-- The adapter's OssieAutoJoinRule detects the Cartesian join between two datasets
-- in the same Ossie schema, looks up the relationship, and rewrites to:
SELECT c.REGION, SUM(o.AMOUNT) AS TOTAL
FROM "Pharma Rx".fact_pharma o JOIN "Pharma Rx"."Prescriber" c ON o.prescriberkey = c.prescriberkey
GROUP BY c.REGION;

The rewrite happens during Calcite’s optimisation phase — the whole thing still pushes down to the warehouse as a single JOIN query.

When the rule fires

• The join must be Cartesian. Explicit JOIN … ON … clauses are never overridden — the user asked for a specific predicate and we respect it.
• All tables must be Ossie-owned datasets in the same OssieSchema. Cross-schema and non-Ossie tables leave the query unchanged.
• Exactly one Ossie relationship must link each pair being auto-joined. Multiple candidates raise AmbiguousJoinException with the list of candidate names — the user must add an explicit ON to pick one. Silent-wrong-results is the failure mode we’re guarding against.

N-way support

FROM A, B, C, … (three or more tables in a single Cartesian) also auto-joins. The rule walks down through nested Joins to reach every raw TableScan, then builds a fresh left-deep join chain using the relationships that link each new table into the already-joined set. Pharma-scale queries against fact + multiple dims work with no JOIN clauses.

When it doesn’t fire

• No relationship between two datasets that would need to be linked → Cartesian remains (probably not what the user wants, but honest).
• Self-joins (FROM A a, A b) → left as Cartesian.
• Cross-schema joins mixing Ossie tables with non-Ossie ones.

Join views

For every Ossie relationship, the adapter registers a pre-materialised view named <from>_JOIN_<to>. The view’s SQL is the JOIN of the two datasets on the relationship’s from_columns / to_columns, and its row type is the concatenation of both datasets’ columns (with numeric suffixes on collisions).

Given this Ossie fragment:

relationships:
- name: fact_pharma_to_Prescriber
  from: fact_pharma
  to: Prescriber
  from_columns: [prescriberkey]
  to_columns: [prescriberkey]

The adapter exposes PHARMA.fact_pharma_JOIN_Prescriber with the union of both tables’ columns. Users write:

SELECT * FROM "Pharma Rx".fact_pharma_JOIN_Prescriber WHERE prescribername LIKE 'Dr%';

and Calcite pushes down SELECT * FROM fact_pharma JOIN dim_prescriber ON fact_pharma.prescriberkey = dim_prescriber.prescriberkey WHERE prescribername LIKE 'Dr%' — no runtime overhead over writing the JOIN by hand.

Multi-column relationships are supported: the ON predicate ANDs each from_columns[i] = to_columns[i] pair.

Related

• Exporting to Apache Ossie — how to produce the YAML file this adapter reads.
• Saiku semantic annotations — the annotations that survive into the Ossie YAML and (in a future slice) drive metric/dimension resolution at query time.
• Apache Calcite adapter framework — the underlying planner.
• Apache Ossie repository — upstream spec.