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 Q1 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.

9 Likes

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

3 Likes

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

3 Likes

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.

4 Likes

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

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