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.
Hello,
would be interested in trying this out but both the instruction video and the request form is not accessible.
BR
/Baber
Hi,
I just re-enabled the video so you can now watch it. I re-enabled the form as well.
Let me know what you think.
Mike.
Hi Miguel,
This looks like the ideal tool to use to solve large data-set documents. I was wondering if I could get the exe file and try it out on my qvw documents? Thanks again.
Eamonn
Hi ,
can you please share this exe?
Thanks
Vijay
Hi Vijay, I have added a download link to the end of the post.
Regards,
Mike
hi.. im very interested in your idea. May i try it in my own app?
Cant access the request form.
Thank you for the opportunities.
Hi Dwi,
I have added a download link to the end of the post.
Regards,
Mike.
HI Mike,
Thanks for sharing link, i am trying this application and i am getting error Process Failed.
can you please let me know if you have also found this problem?
Thanks
Vijay
Hi,
The error must be related to either the document not being found or the user running it not having a licence. Are you loading an app published on a QlikView Server?
Mike
Hi Mike,
Thanks for the reply. Actually my application is more than 5GB and it gives error. If i run smaller application it runs perfectly. i would be grateful if you can give me some hint about pre-calculating QlikView objects.
Thanks
Vijay
Hi Mike,
I have tried to use this app but like Vijay I just get “Process Failed”. I have copied the path from BiX App into a local QV client and tested the Open in Server and that works fine. The path has parenthesis in it – could this be the problem?
Thanks,
Jason
PS – I am currently reading through your book for a review. Very nice!