How to use Query Models
Introduction
If you have specific business logic that cannot be simply captured using table models, relationships and custom fields, Query Models are usually the go-to answer. However, since Holistics does not aim to be a full-fledged data transformation tool, Query Models have several limitations and should not be over-used.
Common use cases for 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
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 fixed pre-aggregation models and explorable models
Commonly, pre-aggregation models 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.
On the other hand, this type of model will be restrictive for users who want to freely explore data and answer their own ad-hoc questions on the fly.
In other words, both types of models have their own places, their usages depends on the audience of your datasets and dashboards. There is no absolute right answer - deciding when and where to use these two models is a balancing act.
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 create a physical table in 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.