Ossie model reference

Overview

Custom models use Apache 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
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.

Full example

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

orders.ossie.yaml

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.
`relationships`	No	Relationships between datasets.
`metrics`	No	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.
`fields`	No	The 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:

- 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):

custom_extensions:
  - vendor_name: TURNTWO
    data: '{"date_filter_field": "lead_created_date", "date_filter_datatype": "DateTime"}'

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.
`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.

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

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.
`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. 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
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

- 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:

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.

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:

- 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:

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.

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:

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.
`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.

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.

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.

Agents

Marketing

Intelligence

Teamwork

Workspace

Deprecated

Ossie model reference

Overview

Full example

Model

Datasets

Limit a SELECT to the date range

Fields

Metrics

Which dataset a metric belongs to

How a metric aggregates

Metrics from several datasets

Expressions

Relationships

AI context

Interact extensions

On a dataset

On a field

On a metric

On a relationship

Checks on sync

​Overview

​Full example

​Model

​Datasets

​Limit a SELECT to the date range

​Fields

​Metrics

​Which dataset a metric belongs to

​How a metric aggregates

​Metrics from several datasets

​Expressions

​Relationships

​AI context

​Interact extensions

​On a dataset

​On a field

​On a metric

​On a relationship

​Checks on sync

Overview

Full example

Model

Datasets

Limit a SELECT to the date range

Fields

Metrics

Which dataset a metric belongs to

How a metric aggregates

Metrics from several datasets

Expressions

Relationships

AI context

Interact extensions

On a dataset

On a field

On a metric

On a relationship

Checks on sync