How Holistics cache data
Holistics works by connecting directly to your SQL database instead of storing your raw data on our own server. When you access a report, Holsitics send report SQL query to the database, wait for the results and visualize them to the end users.
- Each Holistics widget will send a specific SQL query to your database
- After your database returns the results to Holistics, we will cache that result. The duration of this cache is configurable (min 10 minutes, and can be configured). The generated SQL query is the cache key.
- If within that cache duration, another user visit the same dashboard with the same set of filter values (ie same SQL query generated) - Holistics will intelligently retrieve the results of that query from our cache instead of sending a fresh query to your database
- What this means is that the data sent to the subsequent users will only be as fresh as the time the first user access the data.
- Even so, the subsequent user can choose to force reload, or change the filter parameters and Holistics will send a new query to your database.
You can specify Caching Duration to control how long your result sets are stored in Holistics's cache server before expiring.
Adjusting this parameter means balancing your needs for real-time data and report performance:
- Short cache duration gives you fresher data. But the cache expires faster. Users may occasionally suffer long loading time if the cache already expired.
- Long cache duration yields shorter report loading time, but might give stale data.
In reports/dashboards, Cache Settings can be accessed from the Refresh button or report Preferences:
In the Cache Settings UI, you can configure: Caching Duration: how long the data will be cached. After the specified time period, the cache will expire and is cleaned from Holistics's cache server.
Please note that currently, the caching
- Cannot be disabled
- The minimum interval is 10 minutes and cannot be set lower.
Is the cache invalid when I change filter values?
Question: I notice that data caching is not very useful when you have filters in the widget/report. As soon as you change filter value query is running over and you have to wait again. What can we do here?
Answer: When the filter value changes, the underlying query changed thus invalidated the cache. What we can suggest is that you can use our Transform Model functionality to build pre-aggregated tables, so that the queries will only query a small aggregated result set instead of scanning the entire raw tables.