Skip to main content

Query Parameters

Introduction​

By default, when you filter data when exploring a data model via the Exploration UI or via a Dashboard, what happens behind the scene is as follows:

  1. Your model is compiled into a full SQL statement.
  2. The SQL is run against your database and returns a result set
  3. The filtering condition is applied on the result set, and the final result is displayed to you.

However, there are cases in which you require the filtering condition to be included in the model's SQL in step 1 instead of being applied only in step 3.

Example use cases​

  • To draw a histogram representing the revenue distribution of customers from city X, you will need to calculate revenues of customers from city X first, and then have a second aggregation to group their revenue values into various bins and calculate bin size. The filter condition WHERE city = 'X' needs to be included in the first aggregation.

  • In an international company, to calculate and display monetary values (like revenue) in different currencies, it is intuitive to allow end-users to freely insert exchange rate into the calculation from the familiar dashboard interface. With only static SQL, you will need to aggregate the revenue into one currency and then cross-join the result with an exchange rates table to intentionally create a "fan-out" situation. This is often a computationally expensive operation.

These complex analytics use cases can now be solved efficiently with Holistics's Query Parameters feature.

How it works​

For example, you have an order_items_aggr model which counts how many orders have a certain number of items:

order_items_aggr.model.aml
Model order_items_aggr {
  ...
  query: @sql
    with aggr as (
      select
        oi.order_id
        , count(*) as items_count
      from ecommerce.order_items  oi
      left join ecommerce.orders o on oi.order_id = o.id
      where {% filter(order_date_param) %} o.created_at {% end %}
      group by 1
    )

    select
      items_count
      , count(*) as orders_count
    from aggr
    group by 1
  ;;

  // Query param declaration
  param order_date_param {
    label: 'Order Created At'
    type: 'datetime'
  }
}

If you only want to check orders created on the date '2024-10-01', by default this is not possible because the final model does not have an "Order Created At" field to filter on. With Query Parameters, you can insert the filter condition inside the SQL:

# order_items_aggr model
with order_items_aggr as (
  with aggr as (
      select
        oi.order_id
        , count(*) as items_count
      from ecommerce.order_items  oi
      left join ecommerce.orders o on oi.order_id = o.id
      
      # date filter from dashboard is applied INSIDE of the CTE
      where date(o.created_at) = '2024-10-01'
      group by 1
    )

    select
      items_count
      , count(*) as orders_count
    from aggr
    group by 1 
)

select
  items_count,
  orders_count
from order_items_aggr

In the following section, we will walk you through the steps to set up and use query parameters.

Set up​

To use Query Parameters in your analytics setup, you need to declare it in the Query Model first, and then (optionally) link your dashboard filters to the Query Parameters.

1. Declare Query Parameters in Query Model​

To use Query Parameters in a Query Model, you will need to:

  1. Declare the parameters in the model definition with param parameter
  2. Incorporate the query parameter in the main SQL using the {% filter(param) %} column_name {% end %} expression as "placeholder" for the incoming filter.

In the example above, we have the following model definition:

order_items_aggr.model.aml
Model order_items_aggr {
  ...
  // Query param declaration
  param order_date_param {
    label: 'Order Created At'
    type: 'datetime'
  }

  query: @sql
    with aggr as (
      select
        oi.order_id
        , count(*) as items_count
      from ecommerce.order_items  oi
      left join ecommerce.orders o on oi.order_id = o.id

      // Placeholder for end-user filter
      where {% filter(order_date_param) %} o.created_at {% end %}
      group by 1
    )

    select
      items_count
      , count(*) as orders_count
    from aggr
    group by 1
  ;;
}

When you include your model in a dataset, you will see the parameter appears under the Fields list. You can drag it into the Conditions area, and use it similarly to how you use a filter with normal fields.

tip

The full list of Query Model's parameters is documented in the AML Models - Query Model reference page.

If you need to interact with a Query Parameter from a dashboard, the process is similar to how you create a filter from a dataset field:

Advanced Use Case: Dynamic Dimension Selection​

Assuming that you have a model with two dimensions: dim1 and dim2, and you want to use a dashboard filter to dynamically choose dim1 or dim2 to include in your calculation. This can be implemented using Query Parameter and the CASE statement as follows:

model_with_dynamic_dim.model.aml
Model model_with_dynamic_dim {
  type: 'query'
  label: 'Model with dynamic dim'
  ...

  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 }}
  ;;
}

After that, you can create a dashboard filter from the dimension_choice Query Parameter. Depending on the choice, either dim1 or dim2 will be selected thanks to the CASE statement.

Let us know what you think about this document :)