Unlike other business intelligence (BI) tools, Holistics works by connecting directly to your database/data warehouse instead of storing your data in our own server. When you access a report, the report query will be sent to the database for processing, then the returned result set will be formated and visualized.
While this ensures that your data always sits securely in your ecosystem (as shared in Data Security section), there are a few caveats
- The report's loading time will largely depend on your database's specification. If the database is not optimized for fast reading, it will take more time for the report to load.
- The loading time also depends on the size of the result set, and the complexity of your queries.
To speed up report loading time, we have auto enabled Caching and Auto Preload for the report's result. When users open a report, instead of running a fresh query against your database, Holistics will fetch the aggregated result set from a cache server.
This approach brings a few benefits:
- Reducing waiting time for the report viewers
- Reducing loads on customers' databases since Holistics does not need to repeatedly send queries to the databases.
You can specify Caching Duration to control how long your result sets are stored in Holistics's cache server before expiring, and set Auto Preload schedule to automatically run queries and store the result set in the cache.
Adjusting these two parameters means balancing your needs for real-time data and report performance:
- Short cache duration and more frequent preload schedule give you fresher data, but Holistics will also send more queries to your database (which affects database performance). Users may occasionally suffer long loading reports if the cache already expired but preload time has not come yet.
- Long cache duration yields shorter report loading time and fewer database requests, 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.
If you expect a lot of your users to read a report at a certain time (for example at around 8:00 AM when they go to work), you can set schedule to "preload" the report automatically to improve report loading time.
If you set Auto Preload to run at 7:45 AM, the report query will be run at that time and the result set is cached. At 8:00 AM when users open the report, the result set will be ready to be fetched from the cache server.
It is possible to set multiple preloading schedules in 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?
Answer: When the filter value changes, the underlying query changed thus invalidated the cache. What we can suggest is that you can use our Data Transforms 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.