Templated Filters

Omni allows parameterization of SQL using the moustache template engine, which can be used in the definition of both views and fields. This is perhaps best understood through examples.

Overview

Templated filters can inject dynamic text into a SQL query, often a dimension or fact table. Our syntax requires an opening and closing reference around the dynamic input, using {{# }} to open, and {{/ }} to close to dynamic sections. Inside the braces, you use the name of the object, usually of the form view.field_name.filter (the word "filter"). This will correspond to a filter_only field in the view (see examples below). Inside the pair of braces, you will include the lookup value.

Default values can be set using {{^ }}default_value_clause{{/ }} immediately after the existing templated filter syntax. For example, {{# users.filter_field.filter }}name{{/ users.filter_field.filter }}{{^ users.filter_field.filter }}name like '%foo%'{{/ users.filter_field.filter }} would use a default filter of name like 'foo'.

Usage Examples

Parameterized WHERE clause in views based on an SQL query

This simple example allows a query-view to be parameterized. This example uses a filter-only field (see more on this in the dedicated section below), but can also reference a field in the view itself.

# Reference this view as user_fact_example
sql: |
  SELECT 
    user_id,
    COUNT(*) AS total_items
  FROM order_items
  WHERE 
    {{# user_fact_example.order_date.filter }} order_items.created_at {{/ user_fact_example.order_date.filter}}
    AND {{# user_fact_example.status.filter }} order_items.status {{/ user_fact_example.status.filter}}
  GROUP BY 1

dimensions:
  user_id:
    sql: '"USER_ID"'

  total_items:
    sql: '"TOTAL_ITEMS"'

measures:
  count:
    aggregate_type: count

filters:
  order_date:
    type: timestamp
  status:
    type: string
    suggest_from_field: order_items.status

The filters will appear as filter-only fields in the field picker, in their own section under the measures section. These can then be used as normal filters, and the specified expression is inserted in place.

Timeframe Selector

A field of dynamic granularity can be constructed such that a single filter on a dashboard swaps the granularity for multiple timeseries in one.

dimensions:
  created_date_dynamic:
    sql: |
      CASE 
        WHEN {{# order_items.timeframe_selector.filter }} 'Daily' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[date]} 
        WHEN {{# order_items.timeframe_selector.filter }} 'Weekly' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[week]} 
        WHEN {{# order_items.timeframe_selector.filter }} 'Monthly' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[month]}
      END
    label: Dynamic
    timeframes: [Date]

filters:
  timeframe_selector:
    type: string
    suggestion_list: [ Daily, Weekly, Monthly ]

This timeframe_selector filter can then be used either in workbooks or on dashboards across multiple tiles to update them all at once.

Conversion Rates

A common analytical pattern is to compare conversion rates with 1, 3, 7, 30, or however many days.

For the more static form, one might do something like this:

# Reference this view as order_items

dimensions:
  days_after_user_signup:
    sql: DATEDIFF(DAY,${users.created_at},${order_items.created_at})     # assumes a join has been made to a users view

measures:
  count_users_with_order_within_7_days:
    aggregate_type: count_distinct
    sql: ${user_id}
    filters:
      days_after_user_signup:
        less_than: 8

  user_created_to_order_conversion_rate_7d:
    sql: 1.0 * ${count_users_with_order_within_7_days} / NULLIF(${users.count},0)

This is very useful, but requires that measures be created for 1, 3, 7, 30, etc -day conversion rates. We can add a dynamic option:

measures:
  user_created_to_order_conversion_rate_dynamic:
    sql: |
      1.0 * COUNT(
        CASE
          WHEN {{# order_items.conversion_days.filter }} ${days_after_user_signup} {{/ order_items.conversion_days.filter }}
          THEN ${order_items.user_id}
        END)
      / NULLIF(${users.count},0)
    format: PERCENT_1

filters:
  conversion_days:
    type: number

Filter-only Fields

Currently filter-only fields can only be defined in the IDE. They require a name, and a type. The accepted types for a filter-only field are:

• timestamp
• string
• boolean
• interval
• array
• json
• number

Optionally, a suggestion_list or suggest_from_field parameter may be supplied to inform the suggestions for a string field.