Skip to main content

Custom Fields

Most fields in the field picker come directly from your database schema. However, by using Custom Fields you can also create new ad-hoc dimensions and measures to define custom logic and calculations that can be reused.

Custom fields can use both SQL and modeled objects. Only users with "SQL Querier" access and up will be able to make SQL references in calculations (though any user that can touch the workbook page can create new fields from modeled objects). See more on permissions here.

Examples​

Custom Dimension

CASE
    WHEN ${users.state} = 'California' THEN 'West'
    WHEN ${users.state} = 'New York' THEN 'East'
    ELSE 'Other'
END
concat(${users.first_name}, ' ', ${users.last_name})

Custom Measure

count(distinct ${users.full_name})

Note how this custom measure also references the previously created full_name custom field. That logic is 'chained' together when expressed in the generated SQL.

Creating a Custom Field​

There are a few ways to create a new Custom Field. Once saved, the Custom Field will become available in the field picker.

  1. Fields can be created from field picker, using the "+ Add Field" menu at the bottom of the field list. Field can also be adjusted by right-clicking on any existing field and selecting 'Edit.'
  1. Right click on any existing dimension and choose an aggregation (e.g. count distinct, sum, average, min, max). This will automatically create a new custom measure that appears in the field picker.
  1. Fields can be added from parsed SQL. When queries are run in SQL, Omni will extract valid snippets into associated potential dimensions. They'll be show in a special section at the top of the field picker. To add these fields to the workbook, select "Add to Workbook" from the Query Fields section:

Custom Field Syntax​

You can think of a Custom Field as a snippet of SQL logic that gets injected into the generated SQL query whenever the field is brought into the analysis plane.

To create one, simply write a snippet of SQL inside the editor.

Optionally, you can leverage the substitution operator, ${view.field}, to reference other fields. The substitution operator makes code more reusable and modular, enabling you to reference other objects. It's particularly beneficial when you want to chain together logic. For example, if you previously created a different custom field, you can reference it without having to repeat the calculation again. And, if in the future you change the definition for that field, the change will propagate to everything else that relies on it.

When you add the custom field to a query, you can see the logic applied in the SQL block.

Create Filtered Measure From Results​

Filtered measures can be created directly from pivots, but right clicking on the measure underneath a given pivot. This is usually the fastest way to protoype and build filtered measures from existing fields, as they don't require any syntax to build.

Binning, Grouping, Bucketing​

Omni offers some accelerators for common custom calculations. To bin or group strings or values, select "Group" from the field picker menu. String values can be grouped into grouplets, including a left over bucket for remaining values:

Numbers can be binned using a comma delimited list of values. Omni will auto-generate bins based upon the max value and a proportional split, but the boundaries can also be applied by hand:

As with other custom fields, explicit names or views can be applied alongside descriptions.

Binning and Regrouping Aggregates​

A common workflow is looking at a distribution and then trying to understand patterns in the aggregation - percent of users by lifetime orders; distribution of brands by count of SKUs, distribution of sessions or activities by user.

For these analyses we can use saved views and binning to share distribution of aggregates. We got through the following multi-stage analysis to do this with UI, and we'll share an alternative in SQL.

  1. Build baseline query where we want to analyze distribution. Here we'll look at lifetime orders per user:
  1. Save a query view (see here). Note Omni will strip the limit from the query view in order to retain the full data set.
  2. Navigate to All Views & Fields to find our new table
  3. Bin our aggregate (per section above)
  4. Query and visualize:

As alternative path involves simply wrapping our first query in SQL, and inserting our own case statements. This is quicker, but will be more rigid if we need to adjust the underlying query. It is also a very simple approach to single-level re-aggregation (ie. wrap a subquery in parens and count the number of rows).