Skip to main content

Topics

Topics enable the curation of self serve querying in Omni. Topics allow the curation of tables and join paths for a specific database view. Here we have curated the UI to only offer three tables in the data warehouse.

note

Tables without a join path will not be available inside a topic, to include more tables in a topic, simply create a valid join path.

Topic Usage

Once defined, Topics will be displayed in the Workbook view.

Topics are a declaration of desired base tables, and any associated tables that can be joined for analysis. By default, Omni will add all non-fanout tables that are joinable to a topic (ie. tables with many_to_one or one_to_one relationships to another table). The list of joinable tables can also be curated, both to remove joins generated by default or to force in tables that would fan out the base table (ie. many_to_many or one_to_many joins).

As you add new joins to your relationships file, Omni will offer the ability to add these joins to current topics. When joins are deleted, or adjusted to be invalid, they will remain in a topic and must be removed by hand. The IDE will warn when invalid joins exist, but special care should be paid if, for example, the cardinality of a join changes such that it should be removed from a topic, as that would need to be done by hand.

In addition to leaning on joins in the relationships file, custom joins can be added on a per-topic basis (see below)

Example Topics

topics:
order_items:
fields: [all_views.*, -inventory_items.*, -tag:pii, -users.id]
label: Transactional
joins:
inventory_items:
products:
distribution_centers: {}
users: {}

## note requires empty braces
users:
joins: {}
required_access_grants: [test, department]

cache_policy: policy_1

order_items_small:
base_view: order_items
joins:
users: {}

california_order_items:
base_view: order_items
default_filters:
users.state:
is: California
joins:
inventory_items:
products:
distribution_centers: {}
users: {}

Adding Joins to Topics

We will continue to build out better experiences for curating joins outside the IDE, but one trick to quickly enrich topics is to take advantage of the automatic knowledge Omni has of all your joins. When you save a topic for the first time, Omni will automatically include all non-fan-out joins or any joins marked as reversible. They can then be curated down to the desired set.

This can be used to add joins to existing topics by simply duplicating the base view and choosing new available joins. Below we show an example with a users topic where all joins have been excluded. We then make a users_2 topic to generate the full list of join options, so that we can easily pull in order_items. We can then delete our scratch work:

Topic Parameters

access_filters:

topics:
order_items:
joins:
orders:
user: {}
inventory_items:
products: {}
access_filters:
- field: products.brand
user_attribute: attribute_xyz
values_for_unfiltered: [is_admin]

See full doc

User attributes can also be utilized in joins, see below.

always_where_sql:

users:
always_where_sql: ${users.deleted_at} is null

orders:
base_view: order_items
always_where_sql: ${order_items.sale_price} != 0
joins:
inventory_items:
products:
distribution_centers: {}
users: {}
  • always_where_sql applies a filter that users cannot change. The filter is inserted into the WHERE clause of the generated SQL block for all queries in the Topic where always_where_sql is used
  • An always_where_sql condition is not displayed in the workbook UI to the user; it will only be visible by looking at the underlying SQL of any queries created
  • Use ${} syntax to write your always_where_sql condition. If you're not sure how to do so, you can add that filter using the UI in a test workbook, open up the SQL (hit the SQL button in the top right of your query), and then copy the syntax in the WHERE clause into your always_where_sql condition in your model.
  • UI filters are additive to the always_where_sql condition specified
  • Filters specified with always_where_sql are not removable, for optional filter conditions use default_filters (see below)
  • These filters are usually used to remove invalid data, like deleted records or internal transactions from the topic
  • In many cases, pushing these filters down into the ETL cycle may be sensible, if possible

See full page on filter syntax here.

base_view:

info_about_users:
base_view: users
joins: {}

california_order_items:
base_view: order_items
default_filters:
users.state:
is: California
joins:
inventory_items:
products:
distribution_centers: {}
users: {}
  • This is used with an aliased topic, often with default_filters
  • Omni expects unquoted text (quotes will be removed / ignored)
  • This also allows multiple topics over the same base table (say orders and orders_small)

base_view_label:

revenue:
base_view: order_items
base_view_label: Sold Items

joins:
inventory_items:
products:
distribution_centers: {}
users: {}
  • This is used to rename the base view when accessing it from a given topic
  • This can help specify the table's meaning in a specific context, e.g. order_items can be referred to as sold_items in a financial context to help the Finance team understand that these are sales records
  • Omni expects unquoted text (quotes will be removed / ignored)

cache_policy:

users:
joins: {}

cache_policy: policy_1

Sets a topic's cache policy. In the example above, the Users topic will use the "policy_1" cache policy defined in the model. Refer to cache_policies: to learn more about how to set cache policies for models and topics.

default_filters:

california_order_items:
base_view: order_items
default_filters:
users.state:
is: California
joins:
inventory_items:
products:
distribution_centers: {}
users: {}
  • default_filters will filter all rows in a topic by default, by creating an automatic UI filter in the topic
  • default_filters are removeable, for unremoveable filter conditions use alway_sql_where (see above)

See full page on filter syntax here.

fields:

  order_items:
fields: [all_views.*, -inventory_items.*, -tag:pii, -users.id]
joins:
inventory_items:
products:
distribution_centers: {}
users: {}

order_items:
fields: [all_views.*, -user_facts.*, user_facts.lifetime_value]
joins:
users: {}
user_facts: {}

base_table:
fields: [my_specific_view.*, -my_specific_view.some_field]
joins:
my_specific_view: {}
  • Fields allows Omni to curate the specific fields available inside a topic
  • By default, all fields are included from a topic across the base view and other included views
  • Fields can be excluded using -
  • Fields can be reference either:
    • View: view.*, note the * operator to include the full view
    • Field: view.field
    • Tag: tag:pii, will exclude fields across all views/fields with said tag
    • All Fields: all_fields.*, note the special name to call all fields
  • Order of operations will use the all_fields, then views, then tags, then fields
  • This means a view can be excluded but fields can then be layers back in
    • Adding a field back from a view:
    • fields: [-my_specific_view.*, my_specific_view.field_to_add_back]
    • Removing a field from a tag:
    • fields: [-all_fields.*, tag:marketing, -view.field_to_remove]

group_label:

ad_spend:
joins: {}
group_label: Marketing

users:
joins: {}
group_label: Marketing

orders:
joins: {}
group_label: Operations

returns:
joins: {}
  • This is used to group Topics in the UI, for easier navigation and organization
  • Omni expects unquoted text (quotes will be removed / ignored)
  • This is most often used to group topics by data set or by team
  • Topics without group labels will be ungrouped below the group list (see example below)

joins:

order_items:
joins:
inventory_items:
products:
distribution_centers: {}
users: {}
  • This will declare other views as part of a given topic
  • Omni expects a tree structure based upon the join path (ie. each table in the list will be nested under the table it joins through)
  • For example, in the topic above:
    • inventory_items and users join directly to order_items
    • products join to inventory_items
    • distribution_centers join to products
    • users and distribution_centers require because they have no children
  • The final view in each node of the tree requires a bracket pair, , we hope to improve this IDE experience

label:

label: California
  • Label will override the topic name for all UI appearances
  • Omni expects unquoted text (quotes will be removed / ignored)

relationships:

  order_items:
joins:
buyers: {}
sellers:
seller_attributes: {}

relationships:
- join_from_view: order_items
join_to_view: users
join_to_view_as: buyers
join_type: always_left
on_sql: ${buyers.id} = ${order_items.buyer_id}
relationship_type: many_to_one

- join_from_view: order_items
join_to_view: users
join_to_view_as: sellers
join_type: always_left
on_sql: ${sellers.id} = ${order_items.seller_id}
relationship_type: many_to_one

### note this from is not aliased in the context of the join,
### but connection is implied by nesting in topic above
- join_from_view: users
join_to_view: user_attributes
join_to_view_as: seller_attributes
join_type: always_left
on_sql: ${users.id} = ${seller_attributes.user_id}
relationship_type: one_to_one

The relationships sections allows users to add one-off joins at the topic level instead of the relationships file. These topics will not be broadly applied across any topic, but rather only for a specific topic in the model. These can be useful for one-off use cases, rare aliasing, or simply for different join organization schemes. The syntax will mirror general relationship declarations and syntax

User Attributes in Joins (Access Filtered Joins)

  order_items:
joins:
sellers: {}

relationships:
- join_from_view: order_items
join_to_view: users
join_to_view_as: sellers
join_type: always_left
on_sql: ${sellers.id} = ${order_items.seller_id} AND ${sellers.organization_id} = {{ omni_attributes.seller_org_id }}
relationship_type: many_to_one

Here we show a join that will only bring seller data for the specific seller_org_id (user attribute) associated with each user. This can be useful in cases where we want to keep all of the rows in the data set (ie. not vanilla access filters), but permiss the metadata only (here the sellers table).

required_access_grants:

access_grants:
secure:
user_attribute: "gets_secure"
allowed_values: [true]

users:
joins: {}
required_access_grants: [secure]

Access grants are implemented in two parts: on the model and on topics. Access grants require users to possess designated access privileges, granted through user attributes and allowed values, in order to interact with and query on particular topics.

Topic level permissions can be controlled by creating an access_grant: in the model file, see this access grant documentation for more details. Omni will map user-specific variables, user_attributes, to a corresponding allowed value and determine if the user has the necessary permissions to access that topic. Check out this example of how to implement an access grant within a topic.