Launched: 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( AS user_orders
		  FROM ecommerce.orders o
		  LEFT JOIN ecommerce.users u ON o.user_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.

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

Can we use this feature with Holistics 3.0, or do we need to use 4.0?
NVM, just found the message in the post.

Once released, it will only be available in Holistics 4.0.

My use case is filtering queries inside CTE. If we could use this feature inside Transform Model’s Custom SQL, that would be great. If this is only for 4.0, I’m going to plan to use 4.0 now.

1 Like

Hi there,

We’ve heard your feedback and recognized the importance of date parameters. Our team has been diligently working to include Date Param support in our upcoming beta release.

For more information, please watch our demo video:

To create a Date Param and use it in the SQL definition of your Query Model, follow these steps:

  1. Define the Date Param:
param created_at_param {
  label: 'Created At Param'
  type: 'datetime'
  1. Use the Date Param in your SQL definition:
FROM ...
WHERE {% filter(created_at_param) %} date_column {% end %}

Stay tuned for more updates on this exciting new feature!

Enroll in Beta

If you’re interested in participating in the beta, please let us know by filling our beta request form


Hi Khai,

I just tried this out and it doesn’t seem to be working for me.

Screenshot 2023-08-03 at 13.43.11

I have defined the parameter as instructed in the video, and also changed the code to add to the where clause. I am trying this out in the preview of the model before I push into production.

When I run this the SQL that is generated is incorrect and won’t even run.

Here I have applied a filter to the parameter:
Screenshot 2023-08-03 at 13.44.03

The SQL that is generated has this where the param should be:
Screenshot 2023-08-03 at 13.45.14

And at the end of the SQL statement, we have this:

So the parameter isn’t getting applied correctly.

Any thoughts?

Hi @DataGeekDude,

I just checked and it seems like Query Parameter hasn’t been enabled for your account.
We have enabled it now so you can refresh to see the changes.
Do let us know if the problem persists.

1 Like

Hi everyone,

The beta program for Query Parameters has officially closed and we are pleased to announce the official release of this feature.

The beta program has helped us a lot in refining your experience and fixing unexpected bugs thus further enhancing your user experience.

We would like to send our special thanks to all of our beta users for your support during this time.

For more information, please refer to our documentation Query Parameters | Holistics Docs.

Feel free to let us know if you have any questions or concerns.

1 Like