Power Up your Big Apps with Binary Index Expansion

September 11, 2011 at 12:16 am 11 comments

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.

About these ads

Entry filed under: Goodies, Server. Tags: , , , , , .

A New Choice of Armor: The Flag Matrix Idea: Distributed Resources for Development

11 Comments Add your own

  • 1. Baber  |  January 18, 2012 at 8:55 am

    Hello,
    would be interested in trying this out but both the instruction video and the request form is not accessible.

    BR
    /Baber

    Reply
    • 2. Mike  |  January 18, 2012 at 10:38 am

      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.

      Reply
  • 3. Eamonn O'Brien  |  July 12, 2012 at 11:14 am

    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

    Reply
  • 4. Vijay  |  August 3, 2012 at 7:25 am

    Hi ,

    can you please share this exe?

    Thanks
    Vijay

    Reply
    • 5. Mike  |  August 19, 2012 at 7:02 pm

      Hi Vijay, I have added a download link to the end of the post.

      Regards,
      Mike

      Reply
  • 6. Dwi Sulistiyo  |  August 17, 2012 at 8:56 am

    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.

    Reply
    • 7. Mike  |  August 19, 2012 at 7:02 pm

      Hi Dwi,
      I have added a download link to the end of the post.
      Regards,
      Mike.

      Reply
  • 8. Vijay  |  August 19, 2012 at 11:07 pm

    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

    Reply
    • 9. Mike  |  August 20, 2012 at 7:38 am

      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

      Reply
  • 10. Vijay  |  August 21, 2012 at 12:14 am

    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

    Reply
  • 11. Jason Michaelides  |  April 17, 2013 at 9:44 am

    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!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,304 other followers

Recent Posts

Twitter


Follow

Get every new post delivered to your Inbox.

Join 1,304 other followers

%d bloggers like this: