Creating dynamic dimension with query parameters

Tips and Hacks
1

Assuming that you have a model model_with_2_dims with 2 dimensions: dim1 and dim2. You want your users to be able choose an option from a few selected values, then dynamically select dim1 or dim2 based on that input during runtime.

Model model_with_2_dims {
  dimension dim1 { ... }
  dimension dim2 { ... }
}

You can do that with the code below:

Model model_with_dynamic_dim {
  type: 'query'
  label: 'Model with dynamic dim'
  description: ''
  data_source_name: 'my_ds'
  owner: 'my_email'

  param dimension_choice {
      label: 'Selected Dimension'
      type: 'text'
      allowed_values: ["choice1", "choice2"]
  }

  dimension dynamic_dimension {
    label: 'Dynamic Dimension'
    type: 'text'
    hidden: false
    definition: @sql {{ #SOURCE.dynamic_dimension }};;
  }

  query: @sql
    select CASE
        WHEN {% filter(dimension_choice) %} 'choice1' {% end %} THEN dim1
        WHEN {% filter(dimension_choice) %} 'choice2' {% end %} THEN dim2
        ELSE 'unknown'
    END as dynamic_dimension
    from {{ #model_with_2_dims }}
  ;;
}

The full documentation for this can be found here.

Cheers!

3 Likes