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.

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: {}

  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:

Access Filters and Row Level Permissions

While many topics are broadly available, there may be scenarios where inside a given topic, different users may have access to only their own rows of data. This is often the case when building customer facing applications and transactions are collected across customers in a single table vs a schema per table.

Row level permissions can be controlled via access_filter: in Omni. By mapping user-specific variables, user_attributes, to a correspondoing field, Omni can limit all exploration inside a given topic to only rows that match the user's associated value. Below is a simple example, to filter the order_items topic so that each brand can only see their own transactions, and the associated metadata for each transaction:

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

Here any query would require a join to order > inventory_items > products, and then the query would be filtered using:

• WHERE products.brand = "the_value_in_user_attribute_attribute_xyz"

Note that there is an explicit value in the associated user_attribute that can also be mapped to users where the user_attribute is not applied (and thus rows are not filtered. In this case users with attribute_xyz = "is_admin" would not have filtered data.

Note that users with no value in the associated user_attribute, in this case attribute_xyz = null will error as Omni expects a value for any assigned access_filters.

Topic Modeling

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 above

always_where_sql:

  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_sql_where is used
• An always_sql_where 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
• 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)

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.*, tags: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)