Skip to main content
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:
  1. Aggregates the specified measure
  2. Sorts by that measure
  3. Limits to the specified number of values
  4. 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

​
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

​
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