Power Up your Big Apps with Binary Index Expansion
When dealing with Mega Apps (Qlikview applications in the order of 100+ Million rows), it is important for user perception to have the “Preload” option enabled in QlikView Server for that document. That way, the first user to access the application after a reload will not need to wait while the file opens in the server, as he would otherwise. However, using the Preload options does not guarantee that heavy QlikView objects will be calculated quickly.
That’s when the memory cache enters into play: when the first user makes a selection and the chart caclulates, the new state for that chart will be now stored in RAM (Server-Side) so that when the next user makes the same selection, the chart will be calculated instantly. So, if the first user waited, for instance, 10 seconds for the chart to be updated, the next user will retrieve it almost instantly.
It is a common recommendation to use automatic pre-caching of documents when the waiting times are above the acceptable limit, so that not even the first user has to wait. There are a few procedures you can use to programatically generate the cache for the fields and selections you think are most used. I recently used this technique with a customer who was experiencing near-unacceptable performance on some sheets and was able to reduce waiting times between selections from 10 – 15 seconds down to an instant response. I like to refer to this pre-caching process as “Binary Index Expansion” since associations in the QlikView Data Model are being “indexed” into cache so that the next time they are required, they can be found quickly.
The general idea is to cycle through all the possible values in a field, select each of them and wait for the objects to calculate before proceeding to the next selection. It is important to consider objects that have cyclic dimensions, since if the pre-cache only considers one field in the Cyclic Group, the object will still take the original times to calculate when changing the current field.
As I was writing the script (initially on a vbs file, and using some examples provided by QlikTech) I tried to make it the most generic possible, so that it could be reused in different applications, across different servers. And here is the result of that process: an exe application on which you input the important parameters and start the Indexing process. There are still adjustments I plan to apply to make it more efficient and add some features, so I thought I would provide my readers with a Beta version of the app and listen to your input/ideas about it. Please not that, as it is still a Beta version, there will be some restrictions, such as not being able to run it as a batch process, it still requires user input.
Below is a video I made to exemplify the use of the app.
The sample exe file can be downloaded from here.