Views
View files contain view configuration, fields (dimensions and measures), relationships, and query definition (if the view is based upon a query in Omni rather than a database table).
View files will re-order on save:
- Metadata
- This defines attributes of the view
name
,label
,tags
,ignored
,hidden
- Definition
- This defines the table or query the given view is based upon
schema
,table_name
,query
,sql
,primary_key
- Fields
- These are the modeled objects that allow for self-service querying
dimensions
,measures
Adding New Views
Views can be added via the menu bar. The model will contain a view for each table in the database. Tables and views are nested under their schemas, and will be auto-foldered when a schema is defined in a view.
Additionally, modelers may toggle between user-defined fields in Omni, 'Model', or all fields including the untouched fields from the database schema, 'Combined':
Aliased Views
Often views need to be aliased or duplicated, for example in a two sided market with buyers and sellers pointing to a users table. These aliased views are automatically created via joins. This means if, rather than users, you want joins for buyers and sellers, you'd create two joins this. Note joins that are then required for buyers and sellers would also need to be declared to these aliased names.
- join_to_view: users
join_to_view_as: buyers
join_type: always_left
relationship_type: many_to_one
on_sql: ${order_items.user_id} = ${buyers.id}
reversible: false
- join_to_view: users
join_to_view_as: sellers
join_type: always_left
relationship_type: many_to_one
on_sql: ${order_items.user_id} = ${sellers.id}
reversible: false
View Definition
This section defines the data used in a given view. Views are defined from a single-schema, and either a table or a query.
fill_fields:
query:
fields:
[
"lead.created_date[week]",
lead.count
]
base_view: lead
filters:
lead.created_date:
time_for_duration: [ 30 weeks ago, 30 weeks ]
fill_fields: [ "lead.created_date[week]" ]
sorts:
- field: lead.created_date[week]
fill_fields
will fill an enumerated list, usually dates- Most useful when you want a query view as a base table for other date objects and want to ensure all dates are represented in the data set. This will avoid any ad hoc date series building.
- Note that to fill, Omni requires knowledge of the beginning and end of the fill set (ie
last 200 days
). If the date series is unbounded on either side, Omni will not fill the dimension. - Expects one or more dimensions from the query
primary_key_sql:
Primary keys have been moved inside fields for simpler declaration and organization. See the docs here.
If you'd like to define a compound primary key for a view (i.e. use multiple fields as the view's primary key), see custom_compound_primary_key_sql below.
custom_compound_primary_key_sql:
# Reference this view as user_orders_per_month
schema: PUBLIC
custom_compound_primary_key_sql: [ '"created_at_month"', '"id"' ]
query:
fields:
order_items.created_at[month]: created_at_month
users.id: id
order_items.count: dim_count
base_view: order_items
filters:
order_items.created_at:
time_for_duration: [ 6 months ago, 6 months ]
sorts:
- field: order_items.created_at[month]
topic: order_items
dimensions:
created_at_month: {}
id: {}
dim_count: {}
measures:
count:
aggregate_type: count
custom_compound_primary_key_sql
lets you define an array of field names that make up the view's compound primary key- In the example view above, each row represents one user's orders per month, so the compound primary key is comprised of "created_at_month" & "id"
- Note that
custom_compound_primary_key_sql
expects an array of single-quoted strings, so wrap the field names in single & double quotes
An alternative to custom_compound_primary_key_sql
is to create a concatenated field that contains those fields, and then setting the dimension level parameter of primary_key: true
for that field. This will increase readability and potentially make things easier in the case of debugging primary key related errors.
primary_key_field:
sql: concat(${field_1},'-',${field_2})
primary_key: true
hidden: true
query:
query:
fields:
order_items.created_at[date]: created_at
order_items.user_id: user_id
order_items.total_sales
filters:
orders.amount_returns:
not: 0
base_view: order_items
### note dimension names will match the aliased names above after the colon
dimensions:
created_at:
timeframes: [date]
sql: created_at
user_id:
sql: user_id
sales:
sql: order_items__total_sales ### example with a field that was not aliased in block above
- This will define a view from a workbook query. The query argument specifies the spec of the query for the view (fields, filters, base_view).
- Often the easiest way to build query views is via the UI and then promoting the view into the model.
- Fields in the query can be aliased using a colon to create cleaner names downstream (
user_id
instead oforder_items__user_id
)
schema:
schema: GITHUB
table_name: ASSET
- Defines the database schema for the given table_name or raw_sql_select.
sql:
sql: >
SELECT \*
FROM users
LIMIT 10
column_types:
- sql_type_name: INTEGER
column_name: id
- sql_type_name: VARCHAR
column_name: first_name
dimensions:
id:
sql: id
primary_key: true
first_name:
sql: first_name
- For views defined from raw sql queries. The raw_sql_select argument specifies the SQL query for the view (
sql:
) along with the column metadata (column_name, sql_type_name) - It's recommended to build SQL views from the workbook UI and push down to the model
table_name:
schema: Ecomm
table_name: Orders
- Omni will infer
table_name: {view_name}
if it is not defined explicitly.
View Metadata
These arguments define styling for the view in the UI.
aliases:
Occasionally the name of a table may change in your database, which can cause content pointing at that table to break. Rather than going through and tracking down everywhere that the content broke we can simply add aliases:
to the new view file. This will point all references from the old table's name to the updated table, restoring content and eliminating content related errors.
For example, below we had the OLD_ORDER_ITEMS_NAME
table renamed in our database to ORDER_ITEMS
. Upon refreshing the schema in Omni, our content built on top of this table broke due to the change in name. By making the below update to the ORDER_ITEMS.view
file in the model IDE, our content will now be fixed
# Reference this view as order_items
Schema: PUBLIC
table_name: ORDER_ITEMS
primary_key_sql: [ "${order_items.id}" ]
aliases: [OLD_ORDER_ITEMS_NAME]
default_drill_fields:
default_drill_fields:
[
id,
user_id,
users.full_name,
inventory_items.product_name,
sale_price,
margin,
"order_items.created_at[date]",
status
]
- This will set the default drill set for all measures in a given view
default_drill_set
will be overridden by a given fieldsdrill_fields
(more here)- Empty
default_drill_fields
will remove drill from a field (ie.default_drill_fields: []
)
display_order:
table_name: Orders
display_order: 1
- Omni expects a whole number
- This will override the sort order for the field picker amongst views
display_order
will supercede alphabetical sorting
- For example, if the orders view is given a
display_order:
it will float to the top of the field picker, and the remaining views would be sorted alphabetically
hidden:
hidden: true
- Remove the view from the UI. Still reference-able in the model; hidden in the workbook UI.
- Note that views will not be removed from the schema itself, so will be available through SQL querying
- The core difference between ignoring and hiding is that ignoring will block model references to the view, while hidden will not, in both cases the view will be hidden from the topic pivottable experience
- Expects 'true' or 'false'
ignored:
ignored: true
- Effectively removes the view from the model (soft delete, for example to hide undesirable schema tables). This will result in the breakage of references to the specific view.
- Note that views will not be removed from the schema itself, so will be available through SQL querying
- Expects 'true' or 'false'
materialized_query:
(Aggregate Awareness)
## This table would be built from the underlying user_facts table declared as:
##
## SELECT users.id as user_id,
## order_items.created_at[month] as month,
## COUNT(order_items.id) as count,
## SUM(order_items.sale_price) as total_sale_price
## FROM order_items
## JOIN users ON users.id = order_items.user_id
## GROUP BY 1,2
name: user_facts
## SQL declaration used to ensure column order matches the materalized query below
sql: >
SELECT user_id, month, count, total_sale_price
FROM user_facts
dimensions:
user_id: {} ## user_facts.user_id
month: {} ## user_facts.month
count: {} ## user_facts.count
total_sale_price: {} ## user_facts.total_sale_price
materialized_query:
fields:
[
users.id,
"order_items.created_at[month]",
order_items.count,
order_items.total_sale_price
]
base_view: order_items
Materialized query allows you to configure aggregate awareness between two different views. This is to be used on the aggregated view to direct Omni when to point at the aggregated table based on a set of fields from the base table that are being used.
For example:
- If we had an
order_items
table with several metrics in Omni, we could build auser_facts
view rolling up this table (note, Omni field names as the reference points). Order in the SQL statement matches order in the query reference (so usingsql:
can be better thantable:
to ensure order).user_facts.user_id
-->${users.id}
user_facts.month
-->${order_items.created_at[month]}
user_facts.count
-->${order_items.count}
user_facts.total_sale_price
-->${order_items.total_sale_price}
Note the mapping is done entirely with column order (fact table itself and materialized_query
).
See more about aggregate awareness here.
name:
# Reference this view as ecomm__users
schema: Ecomm
table: Users
label: Users With Orders
- Name is implicit in the file name of the view. This is how the view and fields are referenced, independent of the labels applied.
label:
schema: Ecomm
table: Users
label: Users With Orders
- Label will override the view name for all UI appearances of the view
- Omni expects unquoted text (quotes will be removed / ignored)
tags:
tags: [foo, bar]
tags:
- foo
- bar
- foobar
- Hidden feature, this will be used to curate view and field groups for UI curation and sharing
- Expects a comma-delimited array of strings
Joins
Fields
See Dimensions and Measures