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 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]
  primary_key_sql: [ "${new_leads.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.

query:

query:
  fields:
    - order_items.user_id
    - order_items.total_sales
  filters:
  orders.amount_returns:
    not: 0
  base_view: order_items
  primary_key_sql: [ "${query_name.order_items_user_id}" ]

• 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.

schema:

schema: GITHUB
table_name: ASSET
primary_key_sql: [ "${github__asset.id}" ]

• 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

• 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). Often the easiest way to build query views is via the UI and then promoting the view into 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.

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)

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'

name:

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:
  • 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

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]

Joins

See Relationships & Joins

Fields

See Dimensions and Measures