Templated Filters
Omni allows parameterization of SQL using the mustache 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
Note that the UI based timeframe control may often be a better path.
A field of dynamic granularity can be constructed such that a single filter on a dashboard swaps the granularity for multiple timeseries in one.
By editing the .view file, it is necessary to add a dynamic dimension and a filter. In the example below, replace all references to created_date with the appropriate date field and order_items with the fully qualified view reference.
# In the .view file
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
filters:
order_date:
type: timestamp
status:
type: string
suggest_from_field: order_items.status
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:
timestampstringbooleanintervalarrayjsonnumber
Optionally, a suggestion_list or suggest_from_field parameter may be supplied to inform the suggestions for a string field.
Parameters
SQL Query View Example
schema: PUBLIC
sql: |-
Select status, sum(sale_price) as sum_sale_price
from public.order_items
WHERE created_at BETWEEN {{filters.filter_only_parameter_example.dates.range_start}} AND {{filters.filter_only_parameter_example.dates.range_end}}
group by 1
dimensions:
status:
sql: '"STATUS"'
sum_sale_price:
sql: '"SUM_SALE_PRICE"'
measures:
count:
aggregate_type: count
filters:
dates:
type: timestamp
Parameters can be used in Omni in tandem with Filter-only Fields. The value of a filter-only field can be referenced using the structure {{filters.table_name.field_name.value}}, and can be used in definitions for other fields. When that filter-only field is applied to a workbook or dashboard, it can be used to change the value of the parameter field, and thus interact with the query accordingly.
Consider using the filter_single_select_only argument when using a string filter-only field to parameterize some selector.
Users can also reference the start or end range of a date filter using the structure {{filters.table_name.field_name.range_start}} and {{filters.table_name.field_name.range_end}}.