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

ai_context:

ai_context: |-
      you are the head of finance. you are concerned with the status of customer payments. you often need to project into the future how many invoices are due and for how much. you also need to know if any invoices are late so you can reach out to those customers.

here's an example question you may ask: 
        question: which customers are behind due on payment?
        
        answer: 
        
        {
          "limit": 1000,
          "sorts": [
            {
              "column_name": "share_rillet_omni_omni__invoices.due_date",
              "sort_descending": true,
              "is_column_sort": false,
              "null_sort": "OMNI_DEFAULT"
            }
          ],
          "table": "share_rillet_omni_omni__invoices",
          "fields": [
            "share_rillet_omni_omni__invoices.customer_id",
            "share_rillet_omni_omni__invoices.customer_name",
            "share_rillet_omni_omni__invoices.invoice_number",
            "share_rillet_omni_omni__invoices.due_date",
            "share_rillet_omni_omni__invoices.total_amount"
          ],
          "pivots": [],
          "dbtMode": false,
          "filters": {
            "share_rillet_omni_omni__invoices.due_date": {
              "is_negative": false,
              "kind": "BEFORE",
              "right_side": "today",
              "type": "date",
              "ui_type": "BEFORE"
            },
            "share_rillet_omni_omni__invoices.status": {
              "type": "string",
              "kind": "EQUALS",
              "values": [
                "unpaid"
              ],
              "is_negative": false,
              "case_insensitive": true
            }
          },
          "modelId": "aeb6f3f4-b0e5-4abb-97eb-eaeb42a16944",
          "version": 5,
          "rewriteSql": true,
          "row_totals": {},
          "fill_fields": [],
          "calculations": [],
          "column_limit": 50,
          "join_via_map": {},
          "column_totals": {},
          "userEditedSQL": "",
          "dimensionIndex": 0,
          "default_group_by": true,
          "join_paths_from_topic_name": "invoices"
        }

• This allows a free text area on the topic for providing context to the AI query generation. This may include:
  • Behavioral prompting you are the head of finance. you are concerned with the status of customer payments. you often need to project into the future how many invoices are due and for how much. you also need to know if any invoices are late so you can reach out to those customers.
  • Example queries: here's an example question you may ask: which customers are behind due on payment?
  • Or even structured examples of Omni query results (see above)

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.

auto_run:

big_expensive_topic:
  auto_run: false

small_interactive_topic:
  auto_run: true

• This argument will override the model level auto_run parameter for a specific topic
• Only accepts 'true' and 'false'

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.

description:

  order_items:
    label: Order Transactions

    description: All transactions related to orders from the online store.

    joins:
      inventory_items:
        products:
          distribution_centers: {}
      users: {}

• The description: parameter within a topic can be used to add context to the type of data that a user can create ad-hoc analyses from.
• Metadata about the topic, made available in the workbook UI.
• Omni expects unquoted text (quotes will be removed / ignored)

extends:

Coming soon.

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)

hidden:

hidden: true

• Remove the topic from the UI. Still reference-able in the model; hidden in the workbook.
• Expects 'true' or 'false'

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.

For more about topic relationships vs global relationships, see here.

template:

Coming soon.

views:

  opp_line_items:
    base_view: opp_line_items
    label: ARR Facts
    group_label: Salesforce

    joins:
      salesforce__opportunity:
        salesforce__opportunity_owner: {}
        salesforce__account: {}
        opp_line_item_facts:
          opp_line_item_start_facts: {}
          opp_line_item_end_facts: {}
          opp_line_item_current_facts: {}

### Here we are customizing our base metric (drr_sum and arr_sum) with topic-specific names.
### We are using extends to customize our vanlla opp_line_item_daily_facts three times,
### for each of the topic specific joins below (start, now, end).  We can customize anything
### about the view, but only touching some field names in this case and creating a measure

    views:
      opp_line_item_start_facts:
        extends: [ opp_line_item_daily_facts ]

        dimensions:
          drr_sum:
            label: Starting DRR

          arr_sum:
            label: Starting ARR

        measures:
          arr_sum_sum:
            sql: ${opp_line_item_start_facts.arr_sum}
            label: Starting ARR Sum
            aggregate_type: sum

      opp_line_item_end_facts:
        extends: [ opp_line_item_daily_facts ]

        dimensions:
          drr_sum:
            label: Ending DRR

          arr_sum:
            label: Ending ARR

        measures:
          arr_sum_sum:
            sql: ${opp_line_item_end_facts.arr_sum}
            label: Ending ARR Sum
            format: USDCURRENCY_0
            aggregate_type: sum

      opp_line_item_current_facts:
        extends: [ opp_line_item_daily_facts ]

        dimensions:
          drr_sum:
            label: Current DRR

          arr_sum:
            label: Current ARR

        measures:
          arr_sum_sum:
            sql: ${opp_line_item_current_facts.arr_sum}
            label: Current ARR Sum
            aggregate_type: sum

### Three topic-specific joins of the same fact table but with different dates
### to understand ARR at different points of the contract lifecycle (start, now, end)
    relationships:
      - join_from_view: opp_line_item_facts
        join_to_view: opp_line_item_start_facts
        join_type: always_left
        on_sql: ${salesforce__opportunity.id} = ${opp_line_item_start_facts.opp_id} AND
          ${opp_line_item_start_facts.date} =
          ${opp_line_item_facts.oli_first_date}
        relationship_type: one_to_one

      - join_from_view: opp_line_item_facts
        join_to_view: opp_line_item_end_facts
        join_type: always_left
        on_sql: ${salesforce__opportunity.id} = ${opp_line_item_end_facts.opp_id} AND
          DATEADD(day, 1, ${opp_line_item_end_facts.date}) =
          ${opp_line_item_facts.oli_last_date}
        relationship_type: one_to_one

      - join_from_view: opp_line_item_facts
        join_to_view: opp_line_item_current_facts
        join_type: always_left
        on_sql: ${salesforce__opportunity.id} = ${opp_line_item_current_facts.opp_id}
          AND ${opp_line_item_current_facts.date[date]} = CURRENT_DATE()
        relationship_type: one_to_one

Inside a given topic views allows adjustment of views only in the specific context of the topic. There are several great places to use this functionality. A great example is aliased tables in a topic - when aliasing a join for a specific topic, often topic-specific relationships are the best way to model, field names may also be adjusted or renamed or relabeled (see above).

Other use cases could be setting topic-specific drilling, topic-specific aggregate awareness with materialized_query, or other types of field tuning.

Here's another example with topic-specific view ordering:

  order_items:
    joins:
      users: {}
      products: {}

    views:
      order_items:
        display_order: 1
      users:
        display_order: 2
      products:
        display_order: 3

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.