Unlike other business intelligence (BI) tools, Holistics works by connecting directly to your database (data warehouse). When a request happens, it runs the queries (SQL) against the database, formats, visualizes the results and then renders the output to your user's browsers. Since running queries against SQL databases is expensive, we store the results of the queries in a Holistics Cache.
The benefits are:
- Reduce waiting time for the report viewers
- Reduce loads on customers' databases since it will not send repeated queries to the databases.
You have total control over whether and how long your data will be stored in Holistics' cache (cache duration). Adjusting this number will tune the balance between report "realtime-ness" and report performance:
- A shorter cache duration will give fresher data, but also increase wait time and likely send more queries to your database as more users consume the report (affects performance).
- A longer cache duration will yield faster performance (report load instantly), but might give stale/overdue data.
Depending on the nature of your data needs, consider adjusting this number thoughtfully.
How Holistics cache works
In the report or dashboard, the cache settings of Reports and Dashboards can be accessed via the Refresh button or Preferences:
In the Cache Settings UI, you can configure:
- Enable Caching: switch on/off caching for this report/dashboard.
- Caching Duration: how long the data will be cached. After duration time, the cache will expire and be cleaned from Holistics' server.
If you know that a lot of your users will read this report in the morning, at around 8am when they go to work, one thing you can do to improve the viewing experience is to "preload" the report automatically prior to that.
This is done with our Automated Preload feature. You can enable Auto Preload to run at 7:45 AM and the data will be ready to use at 8:00 without waiting.
And it is possible to set multi-schedule for preloading in the Additional Preloads section
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?
Yes that is correct. When the filter value changes, the SQL query changed thus invalidates the cache. However, what can be done is you can use our Data Transforms functionality to build pre-summarized tables, so that the queries will only query the aggregated table instead of scanning the entire raw tables.