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

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]

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

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)

links:

  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

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). They can also be defined in the model.
• Omni expects 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.

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

• 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

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.