Skip to main content

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 / composite 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 / composite 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 of order_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 fields drill_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'

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)

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 a user_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 using sql: can be better than table: 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.

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​

See Relationships & Joins

Fields​

See Dimensions and Measures