Thursday 9 February 2012

QlikView Binary Loads

Anyone who has worked with QlikView for long will likely have used at least one binary load. For those that haven't, a binary load allows one QVW to inherit the entire model and data from another. Because the data doesn't need to be reprocessed, binary loads are really quick, simply copying the 1's and 0's from the disk to RAM.

A binary load must be the very first statement on the first tab of your script and you can only perform one binary load per script; that's all there is to know about them. The syntax couldn't be simpler either:

BINARY report\myreport.qvw;

There are basically two main uses for binary loads. The most common is to share a data model between 2 QVWs. This can be as simple as copying the model from an existing QVW when adding a new reporting app to an existing solution. Whilst his is a perfectly valid technique, it assumes you want the exact same data model in both files. But this doesn't have to be the case, there is nothing to stop you dropping tables from the model or loading additional tables once you've performed a binary load. The benefits of this are straight forward, it saves development time and speeds up the reload process by avoiding having to load a complete new model from data source or QVD.

Fig.1: Basic use of binary load
 The other main use of a binary load is within advanced incremental build scenarios. When performing an incremental build, the loading of modified data from the data source may be very quick, but if you have large volumes of history data, loading this from QVD can still take a long time. A binary load can solve this problem shaving precious minutes and even hours off reload times. The technique can be tricky to understand at first but once you have your hear around it, implementing it is relatively simple. As with any incrememental build, be sure to consider if managing deletions is neccessary which can make things a little trickier to implement. The best way to understand the process is as follows:
  1. Start with a standard incremental build process using a QVD to store the transaction table and a reporting interface which loads in the QVD.
  2. A new QVW is created which runs each night or even weekend. This loads in the contents of the QVD and adds it to any history it already has. Once complete it empties the QVD. The incremental process creating the QVD will therefore mean the QVD stores only records added since the last reload of this new QVW.
  3. The reporting interface is modified to first binary load the new QVW to get the history data before then concatenating the QVD. 
Fig.2: Binary as part of an incremental build
Hopefully you can see how a binary load can be a powerful tool in your QlikView arsenal. Using a combination of these two techniques, I have in the past managed to reduced a customer's reload time from 18 hours down to well under an hour. That alone can mean the difference between a successful or failed implementation.

12 comments:

  1. I'm wondering if you could expand on the incremental build. I'm a little confused. Why would the reporting interface QVW at the end of the process have to concatenate the QVD with the new data? Wouldn't the "new QVW" already have all the history data AND the new data from the latest run in step 2? I'm assuming this "new QVW" does a self binary load and then tacks on the incremental data from the QVD.

    Wouldn't the final interface QVW then simply have to binary load the "new QVW" which now has all the data you need?

    Am I missing something?

    Thanks for any help

    -Nate

    ReplyDelete
    Replies
    1. Hi Nate

      In a normal incremental build using only a QVD, the QVD stores all history and on every reload the QVW gets the new data and adds it to the QVD. So you are correct in thinking the QVD would normally hold all of the data.

      However, in an incremental build using a binary load the QVD only stores the data from say midnight until now. The extra QVW stores all the history data up until midnight, and so to get all data up to now the reporting interface first performs a binary load of the QVW containing the history (up to midnight) and then loads the QVD in (to get the data from midnight until the last reload) and then finally gets from the database the new data (since it last ran) giving a complete set of data.

      Hope that makes it clearer.
      Matt

      Delete
  2. Hello,
    very interesting approach! What about the size of the QVW containing all history? Which size is possible? Today we have big QVD files (biggest one 60 GB) and the incremental load is getting slower and slower. Which kind of load do I have to use in the Hist QVW? Add only load... ?
    Thanks!
    Amigo

    ReplyDelete
    Replies
    1. Hi
      There is no limit to the size of the history QVW when using this method. Really your problem is data volume and dealing with such large data volumes in QlikView is a specialist subject in itself. Without being able to investigate your solution in details its difficult for me to advise if there is anything that can be done to help. Drop me a message using the "Contact Me" page if you think their is an opportunity for me to take a look.

      Any incremental build will always get progressively slower as the data volume increases, there is no way around that fact other than to reduce the data being included. But this method will be much faster than an incremental build using just QVDs.

      I'm not 100% sure what you mean by "Add only load.." but to include the history QVW in another QVW you need to use a Binary load as shown in the post above.

      Regards
      Matt

      Delete
  3. Hi Matthew, This was very interesting...
    I believe, this approach was applicable to only Data Inserts.
    How to handle Update and Delete Rows in the Existing History QVW?

    Thanks,
    Sree

    ReplyDelete
  4. i m also having the same doubt.How to handle Update and Delete Rows in the Existing History QVW?

    ReplyDelete
  5. Hi Matthew,
    what if i have data_hist.qvw which some data matching with recent data data_now.qvw...As per discussion if it concatenates then it will create dupliacte values.
    Can you explain incremental load approach for binary load in more better way?
    And again same doubt as before what about update and deletion of data?

    ReplyDelete
  6. Hi Matt,

    I'm trying to implement your proposal with binary .qvw;
    We do use a qualify '*'; and unqualify '%*'; for each table in the data model. If then I'll load a binary.qvw and want to implement new records from concatenate (TABLE1) load * from TABLE1.qvd (qvd), fully new rows are created and called TABLE1-1 -> is there any possibility to avoid it??

    My scenario is: I want to reduce loading time and prepare old data in old.qvw, load this binary old.qvw into new app and just add new records load * from table1.qvd (qvd); ....

    Or is there a better way to solve this?

    Regards



    Stan

    ReplyDelete
  7. hi actually i want to merge two slightly different qlikview data model into one.. Is there any way to it.I tried one mothod of binary load one data model into another data model but it is not giving proper output..

    ReplyDelete
  8. Very good. Thanks for the info.

    ReplyDelete
  9. A little tip: Although BINARY statement must be the first executable command in a QVW, it can make use of variables to allow the target folder/filename.QVW to be dynamic. You cannot do this via a LET or SET statement though since those are executable commands which cannot precede the BINARY statement.

    e.g.
    BINARY '$(v_some_variable)' ;

    Here's a few options:
    1. Use QV.EXE's commandline "-v" option. See QVW manual for that one.

    2. Use Publisher's RELOAD PARAMETERS to supply a value to the variable. That's all Publisher parameters really are i.e. just variables

    3. Use DESKTOP/SETTINGS/VARIABLE OVERVIEW/Set variable value

    4. Or any other variable setting mechanism that does not involve executing a command in the same execution that triggers the BINARY command

    ReplyDelete