Improving Performance with Cache Warming
by Michael Raam , Principal Data Analytics - Pyramid Analytics
We all like our computer systems to run as fast possible – and when things slow down our frustration level mounts. In the world of business intelligence, there can be multiple causes for slowdowns, such as hardware problems, networking bottlenecks, and overly complex analytics. Regardless of the reason for the slowdown, there is a way to improve performance in many situations. The magical wand comes in the form of cache warming of the data source – which we will discuss in this blog.
Before proceeding, we need to acknowledge that there will be scenarios where cache warming does not improve the situation. But the good news is that cache warming is a technique which can often help in improving performance and response time.
How Does Cache Warming Work?
When a query is run against a data source and the results are already available, the results will be taken from the cache and therefore recalculation will not be necessary. Herein lies the key to solving our slowdown problems. The trick is that after each “data source process”, we can run those queries that are part of our analytics. This allows us to prepare the results in cache “before” the query is requested in runtime.
Implementing Cache Warming in BI Office
So how do we do perform cache warming in BI Office? The good news is that BI Office has a built-in method for identifying whether or not a “data source process” has occurred. This happens in the Publication/Alert engine which reruns queries using analytics that have already been defined.
In this blog, we show how to use the Publication/Alert engine in combination with Dynamic text in order to utilize cache warming and minimize the processing load placed on BI Office.
STEP 1 –
Checking for Data Process Event
First, we need to verify whether a “data process event” has occurred for a specific data source.
- Within the Publications module, we open the Alerts screen and click the Status button.
- We choose the Refresh option to force BI Office to check if the cache has been refreshed.
- We specify the data source.
If the Refresh value is TRUE, we know that there has been a data process event and that the cache has been refreshed. In this case, we will run our queries immediately, so that the results will be “waiting” in the cache for an actual runtime query.
If the Refresh value is FALSE, we leave the cache “as is” since there has not been a new data process event.
STEP 2 –
Using Dynamic Text Wizard
By creating dynamic text formulas for our reports, we can force the cache of a given data source to be refreshed.
- We click Dynamic Text button in the Alerts screen.
- We select a data cell to sample. In this example, Data (0, 0).
- The system generates text based on the selected cell.
STEP 3 –
Scheduling Cache Warming
Since the cache is systematically deleted after each data process event, we need to warm (repopulate) the cache following each data process event. When the data process event happens on a known schedule (say at midnight), we can instruct BI Office when exactly to warm the cache. This is a recommended mode of operation.
When we need cache warming to function in a more dynamic fashion, we can data sampling at a given time interval. At each interval, we check whether the data process event has occurred. And if so we run our queries to warm (repopulate) the cache.
STEP 4 –
Viewing Cache Warming in Action
In the Job Execution list shown below, the green bars show activation of cache warming. The Gray bars show instances of sampling without activation of cache warming.