Write query models that hold your complex transformation logic

If you have specific business logic/ use cases that cannot be captured simply by custom fields, the query model is usually the answer. This type of model will have access to inherited properties and custom fields like normal data models.

The common cases that you’ll need to write query models

• Complicated joins of multiple models
• Certain pre-aggregations are required
• Window functions and regex
• Your base tables are too large it does not make sense to explore directly
• Persist the query results

Note: Holistics's Query Model provides basic transformation and persistence functionality. If your business cases require many advanced transformation & data persistence capabilities, we recommend you use dedicated transformation tools to create efficient derived tables for direct querying.

Balance between pre-aggregation (typically addressed with query models) and self-exploration

• Commonly, pre-aggregation caters to people who need a high-level view, with little need for exploration. Sometimes, pre-aggregation is needed to circumvent a missing feature in Holistics. Pre-aggregation also let analysts have more control over the calculations and can test the results easier, since they already have a fixed set of dimension - metrics combination, and they just need to test results on those sets.
• You might even be able to not require the aggregated table at all and use relationships and visualizations to explore directly while allowing business users the flexibility to include other fields that were not part of the original question.
• For simple aggregations, you can use our aggregation in our dataset exploration interface.
• Avoid creating overspecialized pre-aggregations for a single/few reports. You should make code more flexible by pushing WHERE and GROUP BY conditions to the reporting layer rather than defining it in the modeling layer (query models) would not be to hardcode either of these WHERE or group conditions. Applying report/dashboard CONDITIONS/FILTERS to insert and change the WHERE and GROUP BY conditions for them. For example, you want to build a funnel chart for 2020 and for 3 product types.
  • With SQL-to-chart mindset, the coder builds an aggregated model with WHERE year = 2020 and WHERE product_type = a,b,c, also groups by product_type. Then in 2021, he will have to go and change the where + group conditions in the model and add/remove new product types every time the business user has a new product to consider or deprecate.
  • In another way, you don’t need to create an aggregated model as above. Instead of that, just use the base model and push conditions/filters to insert and change the WHERE and GROUP BY conditions for them.

Set up materialized views to automatically persist the results of complex query models

Complex query models can result in long query times.

You may consider using Model Persistence to push a physical table into your data warehouse on a schedule.

So queries using the model will only need to query this single pre-transformed table instead of all the referred tables and then transform the data. This might help in some cases to speed up query time.