Creating level of detail (LOD) fields

A level of detail (LOD) field calculates an aggregation at a granularity you specify, independent of the dimensions in the query. Use them when you need a value like “lifetime spend per customer” or “first order date” to stay attached to every row, even as you add or remove other dimensions.

LOD basics

What's an LOD field?

In a standard query, an aggregation is computed at the granularity of whatever dimensions are in the GROUP BY clause. Add a dimension and the aggregation regroups; remove one and it regroups again.An LOD field opts out of that behavior. You define the granularity once — for example, “per user” or “across the whole dataset” — and the value persists regardless of the dimensions in the query.

Can you explain an LOD using SQL?

LODs can be mapped directly to a SQL equivalent.Consider the following example, a max_age_in_country LOD dimension that finds maximum user age within each country and attaches it to every user row.

SQL

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 Omni definition for this LOD dimension would look like the following:

Omni model YAML

dimensions:
  max_age_in_country:
    sql: ${users.age}
    level_of_detail:
      aggregate_type: max
      fixed: [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.

Why are LOD fields useful?

LOD fields are useful any time you need a metric at a different grain than the rest of your analysis, for example:

A customer-level total used while breaking out by product category
A first-touch date used while analyzing activity over time
A grand total used while showing per-region values

Can LODs be created as dimensions?

Yes. 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).

Can LODs be created as measures?

Yes. When used as a measure, an LoD produces an aggregation of an aggregation, such as an average of averages or a percent of total.

Requirements

To create an LOD field, you’ll need Querier, Modeler, or Connection Admin permissions.

Creating a level of detail field

You can create an LOD field directly from the field browser in a workbook or in the model IDE.

In the workbook

Open the level of detail editor

In the field browser of a query tab, hover over a dimension and click the icon that appears.
Select Modeling > New level of detail field.
Enter a display name for the field in the Field Label field.

Set the aggregation

Use the Aggregation dropdown to choose an aggregate type, such as Count distinct.

Choose a grouping strategy

Use the Level of Detail Grouping and Dimension(s) fields to pick a strategy and the dimensions that define your level of detail. See Grouping strategies below for more information.

Define optional fields

You can also add filters, define the field’s scope, and add a description and group label.

Save the field

Click Add to save the field. It appears in the field browser under your custom dimensions and behaves like any other field.

In the IDE

You can create LOD measures and dimensions in the model IDE with the level_of_detail parameter. For example:

LOD dimension definition

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

See the level_of_detail parameter references for measures and dimensions for the full list of supported parameters.

Promote the LOD field to the shared model to make it accessible in new and existing workbooks built on the model.

Grouping strategies

Three strategies control how the LOD field interacts with the dimensions in your query:

Strategy	What it does	Use it when…
Fixed	Aggregates at the exact dimensions you list. Ignores every other dimension in the query.	You want the LOD value to stay constant no matter what’s in the query, such as a customer’s lifetime spend when you add a product dimension
Include	Aggregates at the dimensions in the query, plus the dimensions you specify.	You want the LOD to follow the query but force additional dimensions into the grouping
Exclude	Aggregates at the dimensions in the query, minus the dimensions you specify.	You want a “total” that ignores a specific dimension, such as a grand total that ignores region and is used to compute percent of total

Common patterns

A few LOD patterns come up often enough that they’re worth recognizing on sight. Each example below shows the same field built two ways — the workbook editor inputs and the equivalent YAML in the model IDE — so you can pick whichever surface you’re working in.

See the level_of_detail parameter references for measures and dimensions for additional examples.

Customer lifetime spend

Calculate the total spend per customer so you can bucket customers ($0–100, $100–500, etc.) even while viewing orders by product category.

Workbook
Omni YAML

The fields in the LOD editor would look like the following:

Field to aggregate - Sale price
Aggregation - Sum
Level of Detail Grouping - Fixed
Fixed dimension(s) - User ID

The YAML definition of the LOD would look like the following:

customer_lifetime_spend:
  sql: ${sale_price}
  level_of_detail:
    aggregate_type: sum
    fixed: [user_id]

First-touch cohort date

Find each user’s first order date so you can group users into acquisition cohorts.

Workbook
Omni YAML

The fields in the LOD editor would look like the following:

Field to aggregate - Order date
Aggregation - Min
Level of Detail Grouping - Fixed
Fixed dimension(s) - User ID

The YAML definition of the LOD would look like the following:

first_order_date:
  sql: ${order_date}
  level_of_detail:
    aggregate_type: min
    fixed: [user_id]

For a full walkthrough of building cohorts on top of an LOD field, see Build cohort analysis using level of detail.

Percent of total

Calculate the grand total of a metric so you can compare each row’s value to the total — for example, what percentage of total sales each region represents. The result is the company-wide total attached to every row, ready to divide against.

Workbook
Omni YAML

The fields in the LOD editor would look like the following:

Field to aggregate - Sales
Aggregation - Sum
Level of Detail Grouping - Exclude
Fixed dimension(s) - Region

The YAML definition of the LOD would look like the following:

total_sales_excluding_region:
  sql: ${sales}
  level_of_detail:
    aggregate_type: sum
    always_exclude: [region]

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

Creating level of detail (LOD) fields

LOD basics

Requirements

Creating a level of detail field

Grouping strategies

Common patterns

Customer lifetime spend

First-touch cohort date

Percent of total

Next steps

​LOD basics

​Requirements

​Creating a level of detail field

​Grouping strategies

​Common patterns

​Customer lifetime spend

​First-touch cohort date

​Percent of total

​Next steps

LOD basics

Requirements

Creating a level of detail field

Grouping strategies

Common patterns

Customer lifetime spend

First-touch cohort date

Percent of total

Next steps