Skip to main content

Dimensions

The dimension is an attribute that describes a row of data. It is used as the primary segmentation and grouping for querying. Dimensions can be date/time, strings, booleans, or numbers. Omni also has helpers for working with nested dimensions like JSON.

Dimension names must be unique within any given view (no same names). Names may use characters a-z (no capital letters), 0-9, or underscores, and start with a letter.

Your schema model will, by default, create dimensions for every column in your database

Dimension Examples​

full_name:
  sql: CONCAT(INITCAP(${users.first\_name}), ' ', INITCAP(${users.last_name}))
  label: Full Name

margin:
  sql: ${order_items.sale_price} - ${products.cost}

is_special_date:
  sql: |-
    CASE
      WHEN ${orders.created_at[date]} \>= '2022-04-14' AND ${orders.created_at[date]} \<= '2022-05-09'
      THEN 'Mothers Day 2022'
      WHEN ${orders.created_at[date]} \>= '2022-05-26' AND ${orders.created_at[date]} \<= '2022-06-20'
      THEN 'Fathers Day 2022'
      ELSE 'Other'
    END

timestamp_created_pst:
  sql: DATETIME(${marketing_orders_new.timestamp_created}, 'America/Los_Angeles')
  timeframes:
    - DATE
    - HOUR_OF_DAY
    - MONTH_NAME

 status_groups:
    sql: ${order_items.status}
    groups:
      - filter:
          is: [ Cancelled, Returned ]
        name: Test
      - filter:
          is: [ Processing, Shipped ]
        name: going
    else: Other
    label: Status Groups

Dimension Arguments​

The fields name​

first_name:
  label: Full Name
  sql: CONCAT(${first_name}, ' ' ${last_name})

is_from_california:
  sql: ${state} = 'California'
  • Fields as named as the top level object for a field, followed by a colon
  • Subsequent arguments are nested under the field with one tab of indentation

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]

convert_tz:​

created_date:
  sql: created_date
  convert-tz: false
  • This specifies that a field does not need to be converted to the query timezone.
  • This parameter can only be added to a dimension, and will apply to all the parameterizations of the dimension.
  • This is usually the right choice for date or week fields in the database, as the time segments don't make sense to convert.
  • Note, timezone conversion in the sql: is strongly discouraged as there may be challenges on sql generation for complex transformations like dimension fill vis-a-vis timezones

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

first_name:
  display_order: 1

last_name:
  display_order: 2
  • 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 supercede alphabetical sorting
  • For example, if the two fields above in uses are given display_order: they will float to the top of the field list in users, 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)

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 (id)
    • 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

groups:​

status_groups:
    sql: ${order_items.status}
    groups:
      - filter:
          is: [ Cancelled, Returned ]
        name: Test
      - filter:
          is: [ Processing, Shipped ]
        name: going
    else: Other
    label: Status Groups
  • groups: lets you bucket results with case-like logic by filtering the values of a field into labeled groups.
  • groups: generates CASE WHEN... SQL statements in the background.

group_label:​

name:
  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:​

id:
  hidden: true
  • Remove the field from the UI. Still referenceable in the model, but hidden in the workbook UI.
  • Expects 'true' or 'false'

ignored:​

_fivetran_deleted:
  sql: '"_FIVETRAN_DELETED"'
  ignored: true
  • Remove the field from the UI, and prevents references to the field
  • Often used to 'remove' fields from the raw database schema
  • Note ignored fields are still available through the SQL runner
  • Expects 'true' or 'false'

label:​

full_user_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)
# Example with two links
country:
  links:
    - url: https://www.google.com/search?q=${users.country}
      label: Google
    - url: https://www.bing.com/search?q=${users.country}
      label: Bing

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

order_by_field:​

bin:
  order_by_field: bin_order
  sql:   CASE
         WHEN price > 100 THEN 'High'
         WHEN price > 20 THEN 'Medium'
         ELSE 'Low'
	 END

bin_order:
  sql:   CASE
         WHEN price > 100 THEN 3
         WHEN price > 20 THEN 2
         ELSE 1
	 END
  • This will set a field to be ordered by another field, which will be pulled into any query silently
  • Most often used when you want the main field to label buckets that put them in non-alphabetical order
  • A common example is our handling of day_of_week (orders by day_of_week_number) or month (orders by month_of_year)
  • Omni expects an unquoted field reference

primary_key:​

id:
  primary_key: true
  • This will set the primary key on a given view
  • The primary key is used to prevent fan outs when calculating metrics with more than one table (in conjunction with the join relationship)
  • Primary keys can be defined in the workbook layer using the field menu (right click or carrot) and they can also be defined in the model
  • Omni expects the value for this parameter to be a boolean (true / false)
  • For situations where a database column is not available for primary_key declaration (say compound keys like CONCAT(id, '-', user_id), it is recommended to created a new field and set that field as the primary key

required_access_grants:​

full_name:
  type: string
  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

sql:​

full_name:
  sql: CONCAT(${users.first_name}, ' ', ${users.last_name})
  • 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:​

filters:
  status:
    type: string
    suggest_from_field: order_items.status

dimension:
  status_from_an_obscure_subquery:
    sql: status_from_an_obscure_subquery
    suggest_from_field: order_items.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:
  type: string
  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:​

full_name:
  type: string
  sql: CONCAT(${first_name}, ' ' ${last_name})
  tags: [pii, secure]

profit:
  type: number
  sql: ${sales} - ${cost}
  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

timeframes:​

created_at:
  sql: created_at
  timeframes:
    - date
    - week
    - day_of_week_name
  convert_tz: false
  • Sets the default time segmentations available for date / time fields in workbooks
  • If absent will use Omni default timeframes: raw, day, week, month, quarter, year
  • Timezone handling is possible using the convert_tz argument. Done in concert with connection-level timezone settings.

Note that date or time fields can reference parametrized timeframes in the model:

  • timestamp_created_at (the 'raw' reference)
  • timestamp_created_at[date]
  • timestamp_created_at[year]
  • timestamp_created_at[day\_of\_week\_name]
  • timestamp_created_at[month\_name]

Default timeframes: - raw - date - week - month - quarter - year

Additional timeframes (right click in the UI, or can be modeled): - millisecond - second - minute - hour - hour_of_day - day_of_week_name (these will sort by day_of_week_num) - day_of_week_num - day_of_month - day_of_quarter - day_of_year - month_name (these will sort by month_num) - month_num - quarter_of_year

Custom timeframes can be included using group_label:, with the group_label matching the label rather than the underlying field

  ## note, "Created At" not "created_at", as the group label must match the label, not the field
  created_at_minute_5:
    sql: id+1
    group_label: Created At

view_label:​

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

Filter-only Fields​

Filter-only fields are often used alongside templated filters to create fields for more specific use cases that only operate as filters, often to dynamically filter fact tables or subqueries.

Handling Schema Field Adjustment​

It's not uncommon that the raw schema has a field name we like, but needs to be adjusted in Omni to work properly for analytics - this can happen due to type casting issues, timezone adjustment, or other more complex reasons. To adjust raw schema fields, you can always leave the field and create a different field like this:

string_that_should_be_number: {}

adjusted_field:
  sql: ${string_that_should_be_number}::bigint

Alternatively, you may want to keep the raw field name, and adjust the field in place. For that, simple adjust the SQL on the base field as follows (note no $ on these):

string_that_should_be_number:
  sql: string_that_should_be_number::bigint

created_at:
  sql: DATETIME(created_at,  'America/Los_Angeles')

JSON Parsing​

Note that fields recognized as JSON (both true JSON and stringified JSON) can be parsed in the UI. Simply click on JSON results, and recognized nested structures will be offered as instant fields. For now, this is limited to modeled fields, rather than raw SQL. To select more than one field, use shift+click or command+click (see below).

Note that often when parsing JSON the typing may not be as expected (for example timestamps may be created as strings). Be sure to cast the field to the proper type for post-processing.