level_of_detail - Omni Docs

Level of Detail (LoD) fields control the granularity at which an aggregation is computed. When used as a dimension, an LoD produces a categorical value — one row per level of detail — which lets you perform an additional layer of aggregation (e.g., average customer lifetime spend).

You can also create LoD fields in the workbook! In the field browser of a query tab, click the on a dimension and then Modeling > New level of detail field.

How it works

The easiest way to understand an LoD dimension is to read it as SQL. For example, this max_age_in_country LoD finds the maximum user age within each country and attaches it to every user row:

max_age_in_country:
  sql: ${users.age}
  level_of_detail:
    aggregate_type: max
    fixed: [country]

SELECT
  users.country AS "country",
  users.id AS "id",
  lod.max_age AS "max_age_in_country"
FROM users
INNER JOIN (
    SELECT country,
           MAX(age) AS max_age
      FROM users
  GROUP BY country
) lod ON users.country = lod.country

The fixed: [country] clause corresponds to the GROUP BY country in the inner query — one row per country, the level of detail. The join then carries that country-level value back to each user row, which is why the LoD behaves like a categorical dimension: every user in the same country shares the same max_age_in_country value.

Syntax

<dimension_name>:
  level_of_detail:
    aggregate_type: <aggregation>
    <grouping_strategy>: [<field_name>]
    custom_primary_key_sql: <field_reference>
    cancel_query_filters: <true_false>
    filters:
      <field>:
        <filter_operator>: <value>
        cancel_query_filter: <true_false>

Properties

dimension_name

object

The name of the dimension. Dimension names must:

Be unique within the view
Start with a letter
Contain only alphanumeric characters and underscores

Hide dimension_name properties

level_of_detail

object

Configures the level of detail calculation for this dimension.

Hide level_of_detail properties

aggregate_type

string

required

The type of aggregation to apply. Supports standard aggregations like sum, average, count, min, max, and distinct-on aggregations.When the following aggregations are used, the custom_primary_key_sql parameter is required:

sum_distinct_on
average_distinct_on
median_distinct_on
percentile_distinct_on

grouping_strategy

string

required

The grouping strategy and the field to use to apply it, specified as grouping_strategy: [ field_name ]. Refer to the Examples section to see an example with complete syntax.The grouping strategy must be one of the following:

always_include - Adds specified dimensions to the query’s grouping, forcing a finer level of detail
always_exclude - Removes specified dimensions from the grouping, producing a coarser aggregation
fixed - Defines an absolute level of detail, replacing all query groupings

custom_primary_key_sql

string

A field reference that defines the key to use for deduplication when using *_distinct_on aggregate types, specified using ${} syntax. This allows you to aggregate over a different level than the view’s primary key, such as summing amounts by order ID when your view is at the order items level.This parameter is required when the following aggregate_types are used:

sum_distinct_on
average_distinct_on
median_distinct_on
percentile_distinct_on

cancel_query_filters

boolean

default:"false"

When true, ignores any row filters applied in the outer query when computing the LoD metric. Defaults to false.To selectively ignore specific filters while keeping others, use the filters parameter with cancel_query_filter instead.

filters

object

Applies filters to the level of detail calculation. See the Filters documentation for more information on Omni filter syntax.You can use cancel_query_filter: true within a filter to selectively ignore specific global filters for this level of detail only, while keeping other global filters active. This provides granular control compared to cancel_query_filters, which ignores all global filters.

Examples

Customer lifetime spend

customer_lifetime_spend:
  sql: ${sale_price}
  label: Customer Lifetime Spend
  level_of_detail:
    aggregate_type: sum
    always_include: [user_id]
    cancel_query_filters: true

Fixed level of detail

category_total:
  sql: ${revenue}
  level_of_detail:
    aggregate_type: sum
    fixed: [category]

Exclude dimensions from grouping

overall_average:
  sql: ${score}
  level_of_detail:
    aggregate_type: average
    always_exclude: [region]

Sum distinct on with custom primary key

sum_by_order:
  sql: ${order_items.amount}
  level_of_detail:
    aggregate_type: sum_distinct_on
    custom_primary_key_sql: ${order_items.order_id}
    fixed: []

Selectively ignore a specific global filter

arr_balance_by_logo:
  sql: ${test_query_view.arr_cumulative}
  level_of_detail:
    aggregate_type: max
    fixed: [logo, date]
    filters:
      date:
        is: ""
        cancel_query_filter: true  # Ignores global date filter for this level of detail only

Replace a cancelled filter with a different value

compare_to_california:
  sql: ${revenue}
  level_of_detail:
    aggregate_type: sum
    fixed: [state]
    filters:
      state:
        is: California  # Replaces any global state filter with California
        cancel_query_filter: true

Compare with cancel_query_filters (all-or-nothing)

customer_lifetime_spend:
  sql: ${sale_price}
  level_of_detail:
    aggregate_type: sum
    always_include: [user_id]
    cancel_query_filters: true  # Ignores all global filters

​How it works

​Syntax

​Properties

​Examples

How it works

Syntax

Properties

Examples