dbt / MetricFlow hookup

If you’re already running dbt with MetricFlow semantic models, you can bring them into Saiku with zero remodelling. dbt Core 1.12 emits an Open Semantic Interchange document alongside its usual build artefacts, and Saiku loads that file as-is through its normal datasource registration flow.

Once wired, everything Saiku exposes over an Ossie model works against your dbt semantic layer: the workbench, the typed AI Query API, the MCP tools, the natural-language /ask layer, anomaly and forecast endpoints.

Prerequisites

A dbt project with MetricFlow semantic models
dbt Core 1.12 or newer — earlier versions don’t emit the OSI document
The same warehouse your dbt project targets
A running Saiku instance (Saiku Cloud or self-hosted) — you need write access to the datasources directory on the host, or admin API access

The walkthrough

Compile your dbt project. The OSI document is emitted as part of any command that triggers a full parse — dbt compile, dbt run, dbt build.
Terminal window
```
cd path/to/your-dbt-project
dbt compile
```
The result lands at target/osi_document.json. It’s a single JSON file containing every semantic model your project declares, in the OSI v0.1.1 format.
Point Saiku at it. Register a datasource pointing at the JSON file and the warehouse dbt targets. On a self-hosted launcher this is a .sds file; on Saiku Cloud you can register via the admin API.
```
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataSource>
    <id>orders-ossie-01</id>
    <name>Orders</name>
    <type>OSSIE</type>
    <ossieYaml>/path/to/dbt-project/target/osi_document.json</ossieYaml>
    <location>jdbc:postgresql://your-warehouse:5432/analytics</location>
    <schema>semantic_model</schema>
    <username>saiku_reader</username>
    <password>...</password>
    <advanced>false</advanced>
    <enabled>true</enabled>
</dataSource>
```
Two things to note:
- The <ossieYaml> element takes any file Jackson’s YAML parser accepts — YAML and JSON. Point it at the exact file dbt wrote; no conversion step.
- The <schema> value is the name field on the semantic model dbt emits. For a fresh project that’s typically "semantic_model" (dbt’s default when no explicit name is given).

Verify. The model shows up in /ai/ossie/models immediately after Saiku’s next connection refresh.

curl -s -b cookies.txt https://your-saiku/rest/saiku/api/ai/ossie/models | jq

[
  {
    "connectionName": "unknown_Orders",
    "modelName": "semantic_model",
    "factDataset": "orders",
    "datasetCount": 2,
    "metricCount": 2
  }
]

Query. Every REST endpoint, MCP tool, and workbench feature now works against your dbt semantic layer. Ask a question:

curl -s -b cookies.txt -H "X-XSRF-TOKEN: $XSRF" \
  -H 'Content-Type: application/json' \
  -X POST https://your-saiku/rest/saiku/api/ai/ossie/query \
  -d '{
    "connection": "unknown_Orders",
    "model": "semantic_model",
    "rows": [{"dataset": "customers", "field": "customer_country"}],
    "values": [{"metric": "total_revenue"}, {"metric": "order_count"}],
    "sorts": [{"metric": "total_revenue", "direction": "DESC"}]
  }'

That’s the whole integration. No shadow modelling, no re-declaration, no conversion pipeline.

Keeping in sync

Because dbt writes osi_document.json on every compile, the integration stays fresh automatically:

Development loop. dbt compile on save (or via dbt-watch) keeps the file current while you’re developing metrics. Saiku picks up changes via the same admin refresh path it uses for OLAP cubes.
CI/CD. Wire your dbt CI job to copy target/osi_document.json to the location your Saiku instance reads. On Saiku Cloud, the admin API accepts direct uploads.
Production dbt jobs. Every dbt run in production writes a fresh document. Ship it alongside your dbt artefacts.

What dbt puts in the file

Every semantic model in your dbt project comes across:

Datasets. One per semantic_models[*] entry in your MetricFlow YAML. Includes the fully-qualified table name, primary key, description, and every dimension as a field.
Metrics. Simple and ratio metrics come through with their SQL expression. Cumulative metrics emit a warning and are dropped (the OSI 0.1.x spec doesn’t yet model window semantics).
Relationships. MetricFlow’s join inference (based on matching entity names across semantic models) becomes explicit Ossie relationships. Saiku’s Calcite auto-join rule picks them up at query time.
Labels. MetricFlow’s label: attributes on dimensions and metrics carry through as OSI field labels. NETREVENUE in the raw column renders as “Net Revenue” everywhere in Saiku.
AI context. Any ai_context: blocks you’ve added to your MetricFlow YAML (descriptions, sample values, synonyms) are surfaced through the Saiku AI schema for LLM consumers.

What’s not in v0.1.1

The OSI spec at v0.1.1 (what dbt 1.12 emits) doesn’t yet cover:

Cumulative / rolling / period-over-period metrics
Nested / derived metrics that reference other metrics
Custom aggregation functions beyond sum / count / avg / min / max

These are known gaps in the spec, not in Saiku. As OSI moves toward v0.2 with wider vendor participation, these will land. The integration will pick them up automatically because Saiku reads the same file dbt writes.

FAQ

Do I need to install anything on the dbt side? No. dbt compile in dbt-core 1.12+ writes the OSI document with no configuration.

What about older dbt versions? For dbt 1.10 and 1.11, you can convert your MetricFlow YAML to Ossie YAML with the small Python converter in the Saiku repository. Once your dbt version reaches 1.12, drop the converter and use target/osi_document.json directly.

Can I mix dbt-sourced and hand-authored Ossie models? Yes. Every .sds datasource registers one model. Point some at target/osi_document.json, others at hand-written YAML.

What if dbt emits a semantic_model with a name I don’t like? Set name on the outer semantic model in your MetricFlow YAML — dbt passes it through. If you don’t set one, dbt defaults to "semantic_model".