Dynamic Dimensions Selection

knowledge checkpoint

A grasp of these concepts will help you understand this documentation better:

• Parameter Fields
• AQL Expression
• Dimension

Introduction

Using Parameter Fields, we can create a chart with a dropdown input that allows users to change the chart's dimension dynamically.

Dynamic Dimension Selection

Assuming that you have a model users_model with country_name, city_name, and gender dimensions. You want to build a dashboard that let user pick which dimension to break down by.

In the video, you want your metric to be dynamically broken down by Countries, Cities, and Genders

This can be implemented using Parameter Field and AQL Expression

// This is a model with a dynamic dimension set via a parameter
Model users_model {
  dimension country_name { }
  dimension city_name { }
  dimension gender { }
}

Step 1: Create a Parameter Field

First, create a Parameter Field in the model.

// This is a model with a dynamic dimension set via a parameter
Model users_model {
  
  dimension country_name { }
  dimension city_name { }
  dimension gender { }
  
  // Parameter to allow users to choose which dimension to use dynamically
  param dim_choice {
    label: 'Dimension Choice'
    type: 'text'
    allowed_values: ['Countries', 'Cities', 'Gender']
  }

}

Step 2: Create a Dynamic Dimension

Create a Dynamic Dimension that changes based on the selected parameter value.

// This is a model with a dynamic dimension set via a parameter
Model users_model {
    
  dimension country_name { }
  dimension city_name { }
  dimension gender { }
  
  // Parameter to allow users to choose which dimension to use dynamically
  param dim_choice { }

  // Dynamic dimension that changes based on the selected parameter value
  dimension breakdown_dim {
    label: 'Dynamic Breakdown Dimension'
    type: 'text'
    
    definition: @aql case(
      when: 'Countries' in users_model.dim_choice
      , then: users_model.country_name

      , when: 'Cities' in users_model.dim_choice
      , then: users_model.city_name

      , when: 'Gender' in users_model.dim_choice
      , then: users_model.gender
    ) ;;
  }
}

Step 3: Use Dynamic Breakdown Dimension in the report

In the report, you can use Dynamic Breakdown Dimension as a dimension to break down your metric.

Step 4: Use Dimension Selector Parameter Field in the dashboard filter

In the dashboard filter, you can use Dimension Selector Parameter Field to dynamically choose which dimension to use in the report.

Advanced: Dimension selection across multiple models

If the list of dimensions spread across multiple models:

1. Create a separate model to contain the param

     Model param_model {
     type: 'query'
   
     query: @sql select 1 ;;
   
     // Parameter to allow users to choose which dimension to use dynamically
     param dim_choice {
       label: 'Dimension Choice'
       type: 'text'
       allowed_values: ['Countries', 'Cities', 'Gender']
     }
   }

2. Add the param_model to the Dataset and define the breakdown_dimension at the dataset level

   Dataset ecommerce {
     models: [countries, cities, users, orders, param_model] // Include param_model to dataset
   
     relations: [
       relationship(orders.user_id > users.id, true),
       relationship(users.city_id > cities.id, true),
       relationship(users.country_code > countries.code, true),
     ]
   
     // Define dynamic dimension at dataset
     dimension breakdown_dim {
       label: 'Dynamic Breakdown Dimension'
       type: 'text'
   
       model: users // Specify the model that the dynamic dimension belongs to
       
       // List of dimensions come from 3 different models: countries, cities, and users.
       definition: @aql case(
         when: 'Countries' in param_model.dim_choice
         , then: countries.name
   
         , when: 'Cities' in param_model.dim_choice
         , then: cities.name
   
         , when: 'Gender' in param_model.dim_choice
         , then: users.gender
       ) ;;
     }
   }

Understanding the model property in Dataset Dimensions

In this dynamic dimension example, we're referencing fields from multiple models (countries.name, cities.name, and users.gender). To avoid fan-out issues, we should assign the dimension to the model at the most granular level in the relationship chain.

Since our relationships form a hierarchy (countries → cities → users), the users model is the appropriate choice because:

• It's closer to the "many" side of the relationships
• It maintains the correct granularity when dimensions from higher-level models (countries, cities) are used
• It prevents duplicate rows when aggregating data

A good rule of thumb: When creating a dimension that references fields from multiple models, assign it to the model that is furthest along the "one-to-many" relationship chain (closest to the "many" side).