Skip to main content

Measures

Measures describe how to aggregate data in Omni. Measure are either aggregates of dimensions (max of profit, sum of revenue) or transformations on top of other measures (sum of profit / count of users).

Measure Examples​

page_count_average:
  sql: ${orders.page_count}
  aggregate_type: average

total_revenue:
  sql: ${orders.sale_price}
  aggregate_type: sum

percent_email:
  sql: |-
    ${count_email_sum}
     /
    ${count}

first_reply_date:
  sql: ${replies.received_at[date]}
  aggregate_type: min

Measure Arguments​

aggregate_type:​

total_revenue:
  sql: ${orders.sales_price}
  aggregate_type: sum

revenue_per_order:
  sql: ${orders.total_revenue} / ${orders.count}
  • Aggregate defines the aggregation method for an underlying dimension
  • For aggregates not offered by default, measures can be written entirely in SQL (sql: MEDIAN(${orders.price})
  • Current aggregates offered:
    • count
    • count_distinct
    • sum
    • min
    • max
    • average
    • median
    • percentile
      • Example: 
        perc_75_age:
        sql: ${users.age}
        aggregate_type: percentile
        percentile: 75

aliases:​

Similar to table level aliases, occasionally a field name may change in your database, which can cause content to break. To fix this, we can add aliases: to the field in question pointing references from the old field name to the updated field name, restoring content and eliminating content related errors. This behaves similar to table level aliases, as shown below:

	field_name:
		sql: ${'"FIELD_NAME'}
		aliases: [OLD_FIELD_NAME]

description:​

full_name:
  description: Full name based on first_name, last_name in CRM
  • Metadata about the field, made available in the workbook UI
  • Omni expects unquoted text (quotes will be removed / ignored)

display_order:​

count:
  aggregate_type: count
  display_order: 1
  • Omni expects a whole number
  • This will override the sort order for the field picker, inside the field's grouping (ie. inside a given view)
    • display_order will supersede alphabetical sorting
  • Note this will not move measures above dimensions
  • For example, if two fields in order_items are given display_order: 1 they will float to the top of the measure list in order_items, then sort alphabetically, and the remaining fields would be sorted alphabetically
  • To rearrange views, display_order can be used at the view level
  • For fields inside groups using group_label, the group will be ranked with the min of all the fields in the group (ie. if there are 3 fields with display_order of 4, 5 and {empty}, the group will have a display_order of 4)

drill_fields:​

  count:
    drill_fields: [view.field1, view.field2]
    aggregate_type: count

  count:
    drill_fields:
      [
        users.id,
        users.full_name,
        users.email,
        users.age,
        users.state,
        users.country,
        "users.created_at[date]"
      ]
    aggregate_type: count
  • Drill fields allow for curation of the drilling behavior for a given measure, using an array of fields for the subsequent query
  • Note to pull in date fields, they must be quoted per the example above

required_access_grants:​

cost_sum:
    sql: ${inventory_items.cost}
    format: currency
    aggregate_type: sum
    required_access_grants: <access_grant_reference>
  • required_access_grants: limit a user's ability to query a field based on an assigned user attribute
  • In order to define an access for a field, the referenced access_grant: has to already be developed in the model file. Read more about setting up an access grant here

filters:​

See full page on filter syntax here.

  count_california_seniors:
    aggregate_type: count
    filters:
      age:
        greater_than_or_equal_to: 65
      state:
        is: California

  count_ny_or_nj:
    aggregate_type: count
    filters:
      state:
        is: [New York, New Jersey]
  • Filtered measures can be built using aggregation alongside a dimension filter
  • Multiple filters can be added in the filter clause, and multiple values for a given filter can be added using array syntax
  • For more complex filter logic, it's often best to build a boolean filter to capture the complexity:
complex_dimension_example:
  sql: ${state} = 'California' OR ${age} > 65

count_complex_logic:
    aggregate_type: count
    filters:
      complex_dimension_example:
        is: true

format:​

sale_price:
  format: currency_2

revenue:
  format: big_2

created_at:
  timeframe_metadata:
    month:
      format: "%Y-%m-%d"
  • Sets default formatting for numbers in Omni, using a named format (see below)
  • Each format is two decimal places by default, except id, which does not include decimals
  • To set decimal length explicitly on a format, simply add {number_of_digits}, up to 4 digits
    • Examples: format: number_4, format: big_1, format: usdaccounting_0
    • When decimal length is not set, decimals will be truncated to the shortest possible length for each row
  • Formats can also be set on a per query basis using visualization configuration, using the same format types
  • There is no current control for . and , delimiters in numerics
note

Formats are applied after the SQL, thus will not impact grouping. To handle grouping with truncation use ROUND() or FLOOR().

  • Numeric formats:
    • number: 1,234.50 (number_2)
    • percent: 24.4% (percent_1)
    • id (numbers with no commas): 123450
    • billions: 1.20B (billions_2)
    • millions: 5.6M (millions_2)
    • thousands: 8.90K (thousands_2)
    • big: 5.60M; 1.23K; 12.23 (big_2)
      • millions if >1M; thousands if >1000; otherwise number
  • Time formats:
    • Time formats use d3 time formats (link)
    • Individual timeframe elements can be formatted using timeframe_metadata: (see example above)
    • Examples: "%Y-%m", "%Y-%m-%d", "%Y-%m-%d %H:%M:%S"
  • Currency formats:
    • By default currencies will use USD through the app, config is coming to change global defaults
    • Right now each format can be adjusted to USD, EUR, and GBP
    • It's recommended to use explicit currency formatting for now
    • accounting, usdaccounting, euraccounting, gbpaccounting: $(1,234.50) (usdaccounting_2)
    • currency, usdcurrency, eurcurrency, gbpcurrency: -£1,234.50 (gbpcurrency_2)
    • bigcurrency, bigusdcurrency, bigeurcurrency, biggbpcurrency: €5.60M; €1.23K; €12.23 (biggbpcurrency_2)
    • financial: (1,234.50) (financial_2)
      • Note financial does not contain a currency mark

group_label:​

order_count:
  group_label: Important Fields
  • This will nest a group of fields in the field picker for curated organization
  • Omni expects unquoted text (quotes will be removed / ignored)
  • Note measures and dimensions will still be in separate sections in the field picker under each view

hidden:​

sum_excluded_revenue:
  hidden: true
  • Remove the field from the UI. Still referenceable in the model; hidden in the workbook UI.
  • Expects 'true' or 'false'

label:​

full_name:
  label: Full Name
  • Label will override the field name for all UI appearances of the field
  • Omni expects unquoted text (quotes will be removed / ignored)
country:
  links:
    - url: https://www.google.com/search?q=${users.country}
      label: Google

id:
  sql: '"ID"'
  format: ID
  links:
    - url: https://sandbox.omniapp.co/dashboards/YX-irW2S/user+lookup?f--users.id=%28%27kind%21%27EQUALS%27%7Etype%21%27number%27%7Evalues%21%5B%27${ecomm__users.id}%27%5D%7Eis_negat*%7Eis_inclus*%29*ive%21false%01*_
      label: User Lookup Dashboard
  • Links will add external link to a templated URL into the drill menu
  • Omni expects unquoted text, and any field references using ${}
  • Other fields can be called in a link, and they will be silently added to the query
  • Note for crosslinking dashboards (as per example above), you inject the corresponding field into the filter URL structure; it's often easiest to do this from a filtered dashboard and then swapping in the dynamic link

sql:​

total_revenue:
  sql: ${orders.sales_price}
  aggregate_type: sum

revenue_per_order:
  sql: ${orders.total_revenue} / ${orders.count}

average_revenue:
  sql: AVG(${orders.sales_price})
  • The core declaration of the field definition. Best practice dictates using field references over raw database columns when calling other fields/dimensions. Dimensions may only be derived from other dimensions (rather than measures). Other fields can be called wrapping view.name in $, ie. $\{orders.id}.
  • Note that field type is implicit in Omni, and defined based upon the underlying fields database type. To change the type, simply CAST the field (for example, sql: ${zip_code}::string).

suggest_from_field:​

status_list:
  aggregate: list
  sql: ${status}
  suggest_from_field: ${status}
  • By default, filters will run a SELECT DISTINCT(${field}) to populate filter suggestions
  • In scenarios where that query may be less performant, or in places where developers may want to curate the suggestion list, suggest_from_field: can be used to suggest via an alternative field's distinct values
  • Omni expects a field reference, without ${}, for example order_items.status

suggestion_list​

status_list:
  aggregate: list
  sql: ${status}
  suggestion_list: [complete, pending]
  • By default, filters run a SELECT DISTINCT(${field}) to populate filter suggestions, suggestion_list will bypass the default behavior
  • suggestion_list can be used to explicitly set the list of filter options, both for performance reasons, and curation
  • Omni expects an unquoted list using [] and a comma delimited list of the values

tags​

total_profit:
  aggregate: sum
  sql: ${profit}
  tags: [finance, secure]
  • tags: are currently used for field picker search in the workbook and to curate fields: and drill_fields:
  • In the future, tags will be used to curate the field list, mask fields, or for other security and privacy configuration
  • Omni expects an unquoted list using [] and a comma delimited list of the values

view_label:​

sum_lifetime_orders:
  aggregate: sum
  sql: ${user_facts.lifetime_orders}
  view_label: users
  • This will nest a given field under a different view than it's default parent view, for example, grouping user_facts fields under the users view for better organization and discovery