Query Views
Query views are workbook queries that have been saved to the shared model as a view. This model file type has a file name structure of <query_name>.query.view. The <query_name> is dynamic based on what the query tab is named in the workbook. This is a powerful mechanism that allows you to do light transformations on data which get compiled as CTEs when queried.
Create a query view
In a workbook, build out the desired query you want to save. Once your query is all ready, navigate to the workbook's menu, click Model and towards the bottom of the list you can select Save query as view.
Query view file
The parameters in your query view are generated for you when you save the view as a query from the model menu in a workbook.
Example query view file
# Reference this view as all_the_params
schema: PUBLIC
query:
# The values in this map (e.g. created_at_year) are the aliases these get in the SQL.
fields:
users.age: age
users.created_at[year]: created_at_year
calc_1: calc_1
base_view: users
calculations:
calc_1:
sql: ${users.age} OMNI_FX_PLUS 2
default_group_by: false
filters:
users.age_bin:
is: []
users.created_at:
is: "2021"
fill_fields: [ "users.created_at[year]" ]
sorts:
- field: users.created_at[year]
join_via_map:
orders: []
order_items: [ orders ]
topic: order_items
dimensions:
age: {}
created_at_year: {}
calc_1: {}
measures:
count:
aggregate_type: count
Parameters
schema:
References the query's underlying schema name from the chosen database connection.
Syntax: <db_schema_name>
Example:
schema: ecommerce
query:
The overarching parameter that defines all of the sub-parameters of the query view.
Syntax:
query:
Example:
query:
# The values in this map (e.g. created_at_year) are the aliases these get in the SQL.
fields:
users.age: age
users.created_at[year]: created_at_year
calc_1: calc_1
base_view: users
calculations:
calc_1:
sql: ${users.age} OMNI_FX_PLUS 2
default_group_by: false
filters:
users.age_bin:
is: []
users.created_at:
is: "2021"
fill_fields: [ "users.created_at[year]" ]
sorts:
- field: users.created_at[year]
join_via_map:
orders: []
order_items: [ orders ]
topic: order_items
dimensions:
age: {}
created_at_year: {}
calc_1: {}
measures:
count:
aggregate_type: count
dimensions:
Lists all of the fields selected in the query and makes them dimensions to allow for aggregation in another query.
Syntax:
dimensions:
<dimension_name>: {}
<dimension_name>: {}
<calc_name>: {}
<measure_name>: {}
Example:
dimensions:
age: {}
created_at_year: {}
calc_1: {}
measures:
A default parameter that a creating a COUNT(*) in the query, counting all rows. This parameter is applied to the generated query view regardless of whether a measure is in the query table. No other aggregations are listed here because they have been dimensionalized and listed as fields under the dimensions: parameter.
Syntax:
measures:
count:
aggregation_type: count
Example:
measures:
count:
aggregate_type: count
Query sub-parameters
fields:
Lists out all of the fields in the query results tab of the workbook. This is a sub-parameter of query:.
Syntax:
fields:
<view_name>.<field_name>: test
<view_name>.<field_name>: test2
calc_1: calc_name
calc_2: calc_name
Example:
fields:
users.age: age
users.created_at[year]: created_at_year
calc_1: calc_1
base_view:
References which view is used in the FROM of the generated SQL. Alternatively, you can think of the base view as the view that all subsequent views will join to when building a query. There is only one base view per query. This is a sub parameter of query:.
Syntax:
base_view: <view_name>
Example:
base_view: users
calculations:
Lists all of the calculations that are defined in the query. This parameter uses OMNI SQL table calculation syntax. This is a sub-parameter of query:.
Syntax
calculations:
<name_of_calculation>:
sql: $\{<view_name>.<field_name>\} <\OMNI SQL TABLE FUNCTION\>
Example:
calculations:
calc_1:
sql: ${users.age} OMNI_FX_PLUS 2
default_group_by:
Enables or disables a GROUP BY. This parameter is automatically set to false if there are no measures in the query view.
Syntax:
default_group_by: <true_or_false>
Example:
default_group_by: true
filters:
The filters: parameter lists out all of the filters that are applied in the query. This is a sub-parameter of query:.
Syntax:
filters:
<view_name>.<field_name>:
<filter_condition>: <value>
Example:
filters:
users.age_bin:
is: []
users.created_at:
is: "2021"
order_items.created_at:
time_for_duration: [ 30 complete days ago, 30 days ]
fill_fields:
Lists the fields that are filled as an enumerated list. This is a sub-parameter of query:.
Syntax:
fill_fields: ["<view_name>.<field_name>"]
Note: for date fields the specific date timeframe is specified in brackets, like in the example below.
Example:
fill_fields: [ "users.created_at[year]" ]
sorts:
Lists the field(s) that the query is sorted by. Note, you can sort a query by more than one column by holding down control or CMD plus the shift buttons on your keyboard while simultaneously clicking on the additional column header to add as a sort.
Syntax:
sorts:
- field: <view_name.field_name>
Example:
sorts:
- field: users.created_at[year]
join_via_map:
This is generated if the query view contains a user-written SQL query and the joins are not default joins so Omni generates a map of how to make the joins possible.
Syntax:
join_via_map:
<view_name>: []
<view_name>: [<view_name_to_map_to>]
Example:
join_via_map:
orders: []
order_items: [ orders ]
topic:
Defines the topic the query is using. If the query is not based on a defined topic, then this parameter will not be included in the generated query view modeling.
Syntax:
topic: <topic_name>
Example:
topic: users
bind_all_filters:
When set to true, queries built on top of the query view will pass the chosen filters into the query view definition. In SQL, this allows the filters set in the outer query to be passed into the inner query.
Syntax:
bind_all_filters: <true_or_false>
Example:
bind_all_filters: true
limit:
Defines the row limit of the query if a limit is set on the query in the workbook.
Syntax:
limit: <limit_value>
Example:
limit: 1000