dynamic_top_n - Dimensions

This parameter is useful for creating pre-defined rankings like “Top 10 States by Sales” or “Bottom 5 Products by Revenue” without requiring manual filters. This allows you to:

Create dimensions that show only the most relevant values for analysis
Improve query performance by limiting the number of returned values, or
Build reports with ranking without maintaining manual lists

When used, Omni generates a query that:

Aggregates the specified measure
Sorts by that measure
Limits to the specified number of values
Returns the dimension values that meet the criteria

Syntax

<dimension_name>:
  sql: <sql_expression>
  dynamic_top_n:
    n: <number>
    by: <measure_reference>
    desc: <true|false>
    else: <string|null>

Properties

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

string

required

The SQL expression that defines the dimension field.

object

Configuration for dynamic top N or bottom N filtering.

Hide dynamic_top_n properties

number

required

The number of values to return (e.g., 10 for top 10).

string

required

A reference to the measure used for sorting and ranking. Use the format view_name.measure_name.

boolean

default:"true"

Sort order for the ranking:

true (default): Returns the top N values (highest to lowest)
false: Returns the bottom N values (lowest to highest)

string | null

default:"'Other'"

Controls how values outside the top N or bottom N are handled:

String value (e.g., "Other", "Remaining") - Groups all non-top-N values under the specified label
null - Excludes non-top-N values entirely, showing only the top N values without an aggregated group

By default, non-top-N values are grouped as Other.

Query-time parameterization

You can override the default n value at query time without modifying the model definition. This allows you to dynamically adjust the number of returned values for analysis. Use the syntax field_name[number] where number overrides the model’s default n value. For example, if you have a dimension named top_5_states with n: 5 in the model, you can use top_5_states[2] in your query to limit results to the top 2 states instead. When you override the n value:

The dimension returns the specified number of top or bottom values based on the query-time parameter
The Other bucket (if configured with the else parameter automatically adjusts to include all remaining values
If no parameterization is provided, the dimension uses the model-defined default n value

Consider the following example:

Model definition

dimensions:
  top_5_states:
    sql: ${users.state}
    dynamic_top_n:
      n: 5
      by: orders.count

And in the query, to override the defined n value:

top_5_states returns 6 rows: the top 5 states + Other
top_5_states[2] returns 3 rows: the top 2 states + Other
top_5_states[10] returns 11 rows: the top 10 states + Other

Examples

Top 10 states by order count

dimensions:
  top_10_states_by_orders:
    sql: ${users.state}
    dynamic_top_n:
      n: 10
      by: orders.count

Bottom 3 countries by order count

dimensions:
  bottom_3_countries_by_orders:
    sql: ${users.country}
    dynamic_top_n:
      n: 3
      by: orders.count
      desc: false

Top 20 products by revenue

dimensions:
  top_products_by_revenue:
    sql: ${products.name}
    dynamic_top_n:
      n: 20
      by: order_items.total_revenue

Top 5 sales reps by deal value

dimensions:
  top_sales_reps:
    sql: ${sales_reps.name}
    label: Top 5 Sales Reps
    dynamic_top_n:
      n: 5
      by: deals.total_value
      desc: true

Top 10 customers with custom label for remaining values

dimensions:
  top_10_customers:
    sql: ${customers.name}
    dynamic_top_n:
      n: 10
      by: orders.total_sales
      else: "All Other Customers"

Top 5 categories without grouping remaining values

dimensions:
  top_5_categories_only:
    sql: ${products.category}
    dynamic_top_n:
      n: 5
      by: order_items.total_revenue
      else: null

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

dynamic_top_n - Dimensions

Syntax

Properties

Query-time parameterization

Examples

​Syntax

​Properties

​Query-time parameterization

​Examples

Syntax

Properties

Query-time parameterization

Examples