Upcoming Launch: Dynamic Models (aka Query Parameters)

Note: This is an early feature launch announcement, meaning the feature is still in development. We make this announcement early to gather feedback and make sure what we build actually helps solve our users’ use cases.

Holistics is currently working on a significant improvement to our Modeling layer called “Dynamic Models” (feature request). This feature allows analysts to define reports that can take user inputs, and inject them directly into a model/report definition.

This feature is currently under development and is expected to be released in beta end of Q2 2023. Once released, it will only be available in Holistics 4.0.

How it works:

  • A new concept is introduced, called “dynamic model” — a data model that can take in parameters and will only be resolved in real-time.
  • Analysts can create reports from dynamic models
  • When a dynamic model is used in a report, end users can input values that feed directly into the model’s parameters

Passing user inputs into SQL query

Suppose we want to build a report that performs a simple distribution of “how many people bought X number of orders?”. We also want the end users to filter down by different customer demographics (e.g user gender).

The SQL for this analysis is complex and requires a subquery to pre-aggregate the number of orders. Without Dynamic Models, interactive filtering from end users is impossible within Holistics. With Dynamic Models, it is.

We build a dynamic model that can take in a gender parameter and pass them to the complex SQL definition inside.

Model order_distribution(gender_param: String) { 
  type: 'query'

  query: @sql
		WITH user_order_count AS (
		  SELECT o.user_id AS user_id
		    , count(o.id) AS user_orders
		  FROM ecommerce.orders o
		  LEFT JOIN ecommerce.users u ON o.user_id = u.id
		  WHERE u.gender = '${gender_param}'
		  GROUP BY 1
		SELECT user_orders
		    , count(user_id) AS total_users
		FROM user_order_count
		GROUP BY 1        
  dimension user_orders { ... }
  dimension total_users { ... }

Here, the parameter goes directly into the SQL definition.

When exploring a dataset, end users can set values for gender via the UI

Injecting user inputs with attributes

The model parameters can also be used with a measure/dimension definition.

Say you want a list of products and to apply a tax rate on top, but you want the end users to experiment with different tax rates to see what the final charge looks like.

Model products_with_tax (tax_rate: number) {
  dimension id { .. }
  dimension cost { .. }

  dimension cost_with_tax {
    type: 'number'
    definition: @sql cost * (1 + ${tax_rate}) ;;

The dynamic model will allow your end users to freely try different tax rates and see the end results on the fly.

Enroll in Beta

Please comment below or reach out if you’re interested in participating in the beta. Also, please share some of your use cases that would benefit from dynamic models.

If you have any questions about the feature, feel free to ask below.


Hi, would like to be in the beta for this. Thanks!


I would also like to participate in the beta, thanks.


Hi, I would like to be in the beta.

Here is one of my use cases (also posted on the original feature request):

We want to make a dashboard for financial “What If” analysis.

Say we have a dashboard with a pivot table with our actual P&L results and our forecasted results for the next years.

I would then like to use parameters to control for an x% increase or decrease to some metric and how that affects our results.

If I had a parameter, where I could set it to a 5% drop I could then inject that into my measure and all other measures, that depend on it.

This is something that I am currently trying to solve as we have hundreds different excel spreadsheets with different variations today, and it is very hard to manage.


Hi, would like to be in the beta for this. Thanks!

1 Like

Hi, would like to be in the beta for this as well. Thanks!

1 Like

Hi any updates on when the beta will launch? Thanks

Hi @Alex_H,

Thank you for your interest in our Dynamic Models.
Our team is actively implementing and researching the most optimal approach (and behavior) for this feature so I cannot promise you the exact launch date of the feature.
However, the ETA for private beta could roughly be in 2023 Q2 right after we do internal testing.

In the meantime, it would help us a lot if you could share more about how you think Dynamic Modeling is beneficial for you.

Hey there!

I wanted to share an update with you all on the feature we’ve been working on for the past three months. Our team has been putting in a lot of work to create Query Parameter feature.

Here’s how we define the param:

Model users_dynamic {
  name: 'users_dynamic'

  query: @sql 
    from public.users
    where gender = {{ gender_param }}
      and id > {{ id_param }}

  param gender_param {
    label: 'Param Gender'
    type: 'text'

  param id_param{
    label: 'Id Parameter'
    type: 'number'

As you can see, this code allows you to input your desired parameters when running a report. You simply define the param at the modeling layer using the code we provided, and then drag it into the conditions section when building a report.

We’re excited to share that we’ve made a lot of progress on this feature, and our team is working hard to make sure it’s user-friendly and accessible for everyone. Although it’s not quite ready yet, we wanted to give you an update to show you how much we’re working on it.

Thanks for your continued support and patience as we work on this feature. We can’t wait to share it with you soon!



Please don’t forget that dates are a common parameter requirement! Also - in the above video there was no differentiation with normal dimensions?

Actually, Parameter is considered as a modeling field. We’re adding syntax validation to make sure that the Parameter name is different from the Dimension (and Measure) name within the same Data Model.

Could you share what are your current use cases with Date Param? Do you want to limit the data by date to ensure the Performance or does the Date Param support your analytics use cases?
It would be much easier for us if you could share your sample Query that you need to inject date value from Dashboard Filter.

E.g. A date parameter used within a window function in the query to get a cohort size for a particular date range. I think window functions generally are where the parameters would be most useful for our use-cases.

1 Like