> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getinteract.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Ossie model reference

> How to write custom semantic models as Ossie YAML files: datasets, fields, metrics, relationships and the Interact extensions.

## Overview

Custom models use [Apache Ossie](https://github.com/apache/incubator-ossie), an open YAML format for semantic models. Interact reads Ossie version `0.2.0.dev0` and adds a few optional settings of its own through `custom_extensions`.

Each file:

* ends in `.ossie.yaml` or `.ossie.yml`
* lives under the **Model path** of your [custom semantic layer](https://docs.getinteract.com/org-settings/semantic-layer/custom-models.md)
* holds one model with at least one dataset

You can split models over several files. All files are loaded into one semantic layer, together with the [Interact models](https://docs.getinteract.com/org-settings/semantic-layer/data-mapping.md).

## Full example

Two datasets, a relationship between them, and three metrics.

```yaml orders.ossie.yaml theme={null}
version: 0.2.0.dev0
name: orders
description: Orders and the stores they were placed in.
datasets:
  - name: orders
    source: my-project.analytics.fact_orders
    primary_key: [order_id]
    description: One row per order.
    ai_context:
      instructions: Use this for revenue and order counts. Revenue includes VAT.
      synonyms: [sales, transactions]
    fields:
      - name: order_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: order_id
        datatype: String
      - name: store_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: store_id
        datatype: String
        custom_extensions:
          - vendor_name: TURNTWO
            data: '{"hidden": true}'
      - name: order_created_at
        label: Order date
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: order_created_at
        datatype: DateTimeTz
      - name: is_new_customer
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: is_new_customer
        datatype: Boolean
      - name: revenue_inc_vat
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: price_inc_vat
        datatype: Decimal
  - name: stores
    source: my-project.analytics.dim_store
    primary_key: [store_id]
    fields:
      - name: store_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: store_id
        datatype: String
      - name: store_city
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: city
        datatype: String
relationships:
  - name: orders_to_stores
    from: orders
    to: stores
    from_columns: [store_id]
    to_columns: [store_id]
metrics:
  - name: order_count
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: COUNT(DISTINCT orders.order_id)
  - name: revenue
    description: Total order value including VAT.
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(orders.revenue_inc_vat)
    custom_extensions:
      - vendor_name: TURNTWO
        data: '{"title": "Revenue", "format": "currency", "direction": "up"}'
  - name: average_order_value
    description: Revenue divided by order count.
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: 1.0 * {orders.revenue} / NULLIF({orders.order_count}, 0)
    custom_extensions:
      - vendor_name: TURNTWO
        data: '{"dataset": "orders", "title": "AOV", "format": "currency"}'
```

With this file, a query for `orders.revenue` and `orders.average_order_value` by `stores.store_city` per month joins `fact_orders` to `dim_store` on `store_id`, and computes the average order value from the summed revenue and order count, not as an average of averages.

***

## Model

The top level of a file.

| Key | Required | Description |
| - | - | - |
| `version` | No | The Ossie version, `0.2.0.dev0`. |
| `name` | Yes | Name of the model. |
| `description` | No | What the model covers. |
| `datasets` | Yes | One or more [datasets](#datasets). |
| `relationships` | No | [Relationships](#relationships) between datasets. |
| `metrics` | No | [Metrics](#metrics). Each metric belongs to one dataset. |

***

## Datasets

A dataset is one table, or one `SELECT`, that you can query.

| Key | Required | Description |
| - | - | - |
| `name` | Yes | Unique name. Used in member names such as `orders.revenue`. Must not be the name of an Interact dataset (those start with `interact_`), or the dataset is ignored. |
| `source` | Yes | A table reference such as `my-project.analytics.fact_orders`, or a `SELECT` statement. |
| `primary_key` | No | Columns that identify a row. Each one must be a field of the dataset. |
| `unique_keys` | No | Other column sets that identify a row. |
| `description` | No | Shown in Interact and given to agents. |
| `ai_context` | No | Extra guidance for agents. See [AI context](#ai-context). |
| `fields` | No | The [fields](#fields): dimensions and time dimensions. |

Interact checks plain table references against the warehouse and flags fields whose column doesn't exist. Datasets with a `SELECT` as source are not checked.

### Limit a SELECT to the date range

A `SELECT` source can contain a `{DATE_FILTER(column)}` token. Interact replaces it with a filter on the query's date range, so the warehouse scans only the dates it needs. Point the dataset at a time field with the `date_filter_field` extension:

```yaml theme={null}
- name: app_sessions
  source: >-
    SELECT session_id, session_created_at, label
    FROM my-project.analytics.sessions
    WHERE platform = 'app' AND {DATE_FILTER(session_created_at)}
  custom_extensions:
    - vendor_name: TURNTWO
      data: '{"date_filter_field": "session_created_at"}'
  fields:
    - name: session_created_at
      expression:
        dialects:
          - dialect: ANSI_SQL
            expression: session_created_at
      datatype: DateTimeTz
```

| Token | Becomes |
| - | - |
| `{DATE_FILTER(column)}` | `column` between the start and end of the query's date range |
| `{DATE_FILTER(column, 7)}` | The same, starting 7 days earlier |
| `{DATE_FILTER(column, 7, 1)}` | The same, starting 7 days earlier and ending 1 day later |

A query without a date range replaces the token with `TRUE`. The `date_filter_field` must be a time field of the dataset.

The filter compares the column with `TIMESTAMP` values. When the column is a `DATETIME` or `DATE`, set the `date_filter_datatype` extension to `DateTime` or `Date`, and Interact converts the date range to that type (in UTC):

```yaml theme={null}
custom_extensions:
  - vendor_name: TURNTWO
    data: '{"date_filter_field": "lead_created_date", "date_filter_datatype": "DateTime"}'
```

> [!TIP]
>
> A plain table partitioned on a date column often needs no `SELECT` with `{DATE_FILTER(...)}`. When the time field is an expression on the partition column, such as `TIMESTAMP(DATE(created_at))`, BigQuery usually skips the other partitions already. Check the bytes a query scans with a dry run before adding one.

***

## Fields

Fields are the dimensions of a dataset: what you group and filter by.

| Key | Required | Description |
| - | - | - |
| `name` | Yes | Unique within the dataset. |
| `expression` | Yes | The SQL for the field. See [Expressions](#expressions). |
| `datatype` | No | `String`, `Integer`, `Decimal`, `Float`, `Boolean`, `Date`, `Time`, `DateTime`, `DateTimeTz` or `Opaque`. |
| `dimension.is_time` | No | Marks a time dimension. When left out, fields of type `Date`, `DateTime` or `DateTimeTz` are time dimensions. |
| `label` | No | Display name. |
| `description` | No | Shown in Interact and given to agents. |
| `ai_context` | No | Used as the description when `description` is empty. |

Time dimensions can be grouped by day, week, month and so on, in the timezone set in [Data mapping](https://docs.getinteract.com/org-settings/semantic-layer/data-mapping.md#timezone).

> [!WARNING]
>
> Interact treats every time dimension as a `TIMESTAMP`. For a `DATE` or `DATETIME` column, convert it in the expression, for example `TIMESTAMP(orders.order_date)`, or BigQuery rejects the query.

Set `datatype` to the type the expression returns, not the type of the underlying column:

| Expression returns | `datatype` |
| - | - |
| `TIMESTAMP`, including anything wrapped in `TIMESTAMP(...)` | `DateTimeTz` |
| `DATETIME` | `DateTime` |
| `DATE` | `Date` |

So `TIMESTAMP(orders.order_date)` is `DateTimeTz`, even though `order_date` is a `DATE` column.

A field can stand in for a field of another dataset with the `conforms_to` extension. See [Metrics from several datasets](#metrics-from-several-datasets).

***

## Metrics

Metrics are the numbers you calculate: sums, counts, ratios.

| Key | Required | Description |
| - | - | - |
| `name` | Yes | Unique within its dataset. If it starts with the dataset name and an underscore (`orders_revenue`), that prefix is dropped (`orders.revenue`). |
| `expression` | Yes | An aggregate SQL expression, such as `SUM(orders.revenue_inc_vat)`. See [Expressions](#expressions). |
| `description` | No | Shown in Interact and given to agents. |
| `ai_context` | No | Used as the description when `description` is empty. |

### Which dataset a metric belongs to

Interact links a metric to the dataset its expression uses, for example `orders` in `SUM(orders.revenue_inc_vat)`. When the expression uses more than one dataset, or none, set the dataset with the `dataset` [extension](#interact-extensions). Sync fails with `set custom_extensions TURNTWO "dataset"` when it cannot tell.

### How a metric aggregates

Interact reads the aggregation from the expression:

| Expression | Aggregation |
| - | - |
| `SUM(...)` | sum |
| `COUNT(...)` | count |
| `COUNT(DISTINCT ...)` | count distinct |
| `AVG(...)`, `MIN(...)`, `MAX(...)` | avg, min, max |
| Anything else, such as `{orders.revenue} / {orders.order_count}` | derived |

Set the `agg` extension to override it.

A derived metric either refers to other metrics or aggregates columns itself, not both:

* `{orders.revenue} / {orders.order_count}` refers to metrics. Interact computes each one and divides the results.
* `SUM(orders.revenue_inc_vat) / COUNT(orders.order_id)` aggregates columns. Interact treats it like any other aggregate on its dataset.
* `{orders.revenue} / COUNT(orders.order_id)` mixes both, and sync rejects it. Make the count a metric of its own and refer to it.

### Metrics from several datasets

A query can combine metrics from different datasets, such as leads from `leads` and purchased matches from `lead_matches`. Interact aggregates each dataset on its own rows and then combines the results on the query's dimensions. A ratio across two datasets is therefore not inflated by a join that repeats rows.

For this to work, each dimension and filter in the query must be available to each metric's dataset, in one of three ways:

* the metric's dataset can reach the dimension's dataset through [relationships](#relationships)
* the metric's dataset has a time field with the same name, for a time dimension
* the metric's dataset has a field with the `conforms_to` extension pointing at the dimension

```yaml theme={null}
- name: lead_date
  expression:
    dialects:
      - dialect: BIGQUERY
        expression: TIMESTAMP(DATE(leads.date_created_utc))
  datatype: DateTimeTz
  custom_extensions:
    - vendor_name: TURNTWO
      data: '{"conforms_to": "lead_matches.lead_created_date"}'
```

When a dimension or filter isn't available to every metric's dataset, Interact joins all datasets in one query instead, with its usual protection against rows that the join repeats.

***

## Expressions

Fields and metrics have an `expression` with one or more SQL dialects:

```yaml theme={null}
expression:
  dialects:
    - dialect: BIGQUERY
      expression: SAFE_DIVIDE(revenue, orders)
    - dialect: ANSI_SQL
      expression: revenue / NULLIF(orders, 0)
```

Interact uses `BIGQUERY` when present, then `ANSI_SQL`, then the first one listed.

* **A plain column name** in a field or metric, such as `order_id`, refers to that column in the dataset's own source. Interact adds the dataset name (`orders.order_id`), so a column with the same name in a joined dataset doesn't clash.
* **`dataset.column`**, such as `orders.price_inc_vat`, refers to a column in a metric expression.
* **`{dataset.member}`** refers to another field or metric, such as `{orders.revenue}`. Use this for ratios, so Interact can combine the underlying aggregates correctly. `{member}` refers to a member of the same dataset.

***

## Relationships

Relationships let a query combine datasets. Each relationship is **many-to-one**: `from` is the detailed dataset (many rows), `to` is the dataset it looks up (one row per key).

| Key | Required | Description |
| - | - | - |
| `name` | Yes | Unique name. |
| `from` | Yes | The detailed dataset, for example `orders`. |
| `to` | Yes | The lookup dataset, for example `stores`. |
| `from_columns` | Yes | Join columns in `from`. |
| `to_columns` | Yes | Matching columns in `to`, in the same order. |

Interact joins with a `LEFT JOIN` from the dataset that can reach every other dataset in the query, along the shortest chain of relationships. If no such dataset exists, the query fails with `no relationship path connects these datasets`.

When two chains are equally short, for example `lead_matches` → `brands` directly and via `categories`, mark the intended relationship with the `preferred` extension. Sync reports remaining ties as `X reaches Y over N equally short join paths`.

```yaml theme={null}
custom_extensions:
  - vendor_name: TURNTWO
    data: '{"preferred": true}'
```

A custom dataset can have a relationship to an Interact dataset, for example `to: interact_products`.

Use the `extra_condition` extension to add a condition to the join, such as matching on label as well:

```yaml theme={null}
- name: orders_to_stores
  from: orders
  to: stores
  from_columns: [store_id]
  to_columns: [store_id]
  custom_extensions:
    - vendor_name: TURNTWO
      data: '{"extra_condition": "orders.label = stores.label"}'
```

***

## AI context

`ai_context` gives agents extra guidance. It can be a string, or an object:

```yaml theme={null}
ai_context:
  instructions: Revenue includes VAT. Use net_revenue for margin questions.
  synonyms: [sales, turnover]
```

On a dataset, `instructions` and `synonyms` are given to agents. On fields and metrics, `ai_context` is only used when `description` is empty.

> [!TIP]
>
> For how a metric is displayed (currency, percent), use the `format` extension instead of writing it in `ai_context`. Interact formats the value in reports and charts.

***

## Interact extensions

Interact-specific settings go in `custom_extensions` with `vendor_name: TURNTWO`. `data` is a JSON string:

```yaml theme={null}
custom_extensions:
  - vendor_name: TURNTWO
    data: '{"title": "Revenue", "format": "currency"}'
```

A file whose `data` is not valid JSON, or has a value Interact doesn't accept, is skipped on sync.

### On a dataset

| Key | Description |
| - | - |
| `title` | Display name. |
| `date_filter_field` | Time field that fills `{DATE_FILTER(...)}` tokens in a `SELECT` source. See [above](#limit-a-select-to-the-date-range). |
| `date_filter_datatype` | Type of the `{DATE_FILTER(...)}` columns: `DateTimeTz` (`TIMESTAMP`, the default), `DateTime` or `Date`. |

### On a field

| Key | Description |
| - | - |
| `title` | Display name. Takes precedence over `label`. |
| `hidden` | `true` hides the field from lists and pickers. Other fields and metrics can still refer to it. |
| `warehouse_type` | The column type in the warehouse, for reference. |
| `conforms_to` | A field of another dataset that this field means the same as, such as `"lead_matches.lead_created_date"`, or a list of them. See [Metrics from several datasets](#metrics-from-several-datasets). |

### On a metric

| Key | Description |
| - | - |
| `dataset` | The dataset the metric belongs to. Needed when the expression uses more than one dataset. |
| `member` | The metric's name within the dataset, when it should differ from `name`. |
| `agg` | `sum`, `count`, `count_distinct`, `avg`, `min`, `max` or `derived`. Overrides the aggregation read from the expression. |
| `title` | Display name. |
| `format` | How values are shown: `currency` (euro), `percent` (a fraction such as `0.23` shows as 23%), `ratio_percent` (`4` shows as 400%), or `multiplier` (shows as `2.50x`). |
| `category` | A group name for the metric in pickers. |
| `direction` | `up` when higher is better, `down` when higher is worse. Used to color changes. |
| `hidden` | `true` hides the metric from lists and pickers. |

### On a relationship

| Key | Description |
| - | - |
| `extra_condition` | Extra SQL added to the join with `AND`. Qualify columns with the dataset name, for example `orders.label = stores.label`. |
| `preferred` | `true` makes this relationship win over an equally short chain. See [Relationships](#relationships). |

***

## Checks on sync

When you sync, Interact checks every file and the combined model. A file is skipped when:

* it is not valid YAML, or misses a required key
* a `TURNTWO` extension is not valid JSON or has an unknown value
* a metric's dataset cannot be determined, or points to a dataset that doesn't exist

The combined model is then checked for:

* duplicate dataset names, and duplicate field or metric names within a dataset
* `primary_key` columns that aren't fields
* `{DATE_FILTER(...)}` tokens without a time `date_filter_field`
* `conforms_to` values that aren't fields
* derived metrics that both refer to metrics and aggregate columns
* datasets that reach another dataset over equally short join paths without a `preferred` relationship

The **Custom models** tab shows the first skipped file and the first model problem, each with the number of other problems.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.