Scripting

QVD Questions and Answers

15 Comments
On Feb 2 I presented a live webinar titled “Understanding & Best Practices with  QVD Files”. You can download the slides as a pdf. 

Many questions were submitted during the presentation, too many to answer at that time. I’ve tried to answer those questions and others below. If you have further questions about QVDs, post a comment here or  in the QlikCommunity Forum. 

Q: ­Does the QVD data get stored in an RDBMS like Oracle, or is it in a file system? ­
QVD files are stored in the file system.
Q: ­what is the compression factor for QVD’s­?
QVD files are stored uncompressed. A QVD contains the physical representation of an in-memory Qlikview Table. This “RAM image” format is what allows an optimized QVD load to be so quick. The physical blocks of disk are read directly into RAM, “ready to go”. Because QVD is the RAM image, there is no compression.
Q: ­Can we trace back QVD to its source?
As of QV10SR2, the XML header in a QVD file contains the name of the QVW that created the QVD as well as file sources and database connections/SQL statements.
Q: ­Why is sorting not possible while loading QVD?­
Sorting (ORDER BY) is only possible with Resident (already in memory) files. Sorting is not possible when reading from files.
Q: ­Could you go over again the concept of “forcing” un-optimized load for the MAPPING function, respective to the qvd?­
MAPPING tables may be loaded from a QVD, but it must be an un-optimized load (this is sometimes called “unwrapping”).
MyMap:
MAPPING LOAD F1, F2 FROM sometable.qvd (qvd);
The above mapping table will be created but it will appear to be empty when used in MAP USING or ApplyMap().  No error, just no resulting mapping.  One workaround is to create a condition that will cause an un-optimized load.  We want all the rows, so we create an always-true condition that will return all rows.
MyMap:
MAPPING LOAD F1, F2 FROM sometable.qvd (qvd)
WHERE 1=1;

Note: In QV10+, the MAPPING prefix will trigger an unoptimized load. The 1=1 trick is not necessary.

A corollary to this is that the target of a mapping operation cannot be an optimized QVD.
MAP Country USING MyMap;
// Optimized load, Country will not get mapped.
LOAD Customer, Country FROM customer.qvd (qvd);
Q: ­The use of the Where 1=1 is something that will be good for mapping fields in the future or is possible that qlikview will determine that where 1=1 will allow optimization?­
Good question. We use WHERE 1=1 to force the un-optimized load required by MAPPING LOAD. I’m hopeful that if Qlikview were changed to consider 1=1 as an optimized load, they will also recognize that MAPPING LOAD should be non-optimized.
(Note: In QV10, MAPPING LOAD is automatically non-optimized).
Q: ­How would you handle the need to load multiple models (ie multiple qvws)?? I don’t think you can do multiple binary loads, so what do you recommend.­
You can generate QVDs from each model and then load all the QVDs to form the larger model.  You can generate all QVDs from a qvw with a simple loop. You can add this code to each of your model qvws.
FOR i = 1 to NoOfTables()
  LET vTableName = TableName($(i)-1);
  LET vOutfile = ‘$(vTableName).qvd’;
  STORE [$(vTableName)] INTO [$(vOutfile)] (qvd);
NEXT i
Q: ­How are QVD refresh scheduled?­
QVDs are created by script in a QVW executed by the reload process. Schedule the reload as you would the reload of a user facing qvw, using the Qlikview Enterprise Management Console (QEMC) or a batch file.
Q: ­Is QVD Optimized load really worthwhile since it is fairly limited? In other words, should we load data to the memory striving for QVD optimized and then work with the memory tables within the script?­
Optimized vs non-optimized load has two impacts: Load duration and Server RAM usage. If your application is relatively small or you do not have concerns about the impact, don’t spend time trying to maintain an optimized load. Some of the script techniques used to maintain optimized can make your script harder to follow.
If, for a given document, you have concerns about load duration or RAM usage, then making the effort to maintain an optimized load would be worthwhile.
Q: ­Can a QVD be accessed from a AS400 DB2 database to get some data?­
Nothing but Qlikview can read from QVDs, so no, DB2 cannot read directly from a QVD.
In the same script that creates the QVD with the store statement:
STORE mytable INTO mytable.qvd (qvd);
You can also create a CSV copy for other consumers:
STORE mytable INTO mytable.csv (txt); 
The csv file can be read by any number of programs, including an ODBC text driver or a bulk database loader. You can use QV to do the ETL and then push csv files back into a Data Warehouse, using something like SQL Server DTS or other data pump.
Q: ­I’m pulling data from a database over a slow WAN link.  Would using a qvd speed this up?  If so, would the qvd file reside on the same side as the database or at the end of WAN Link (client side)?­
Using QVDs could speed up your overall process by allowing multiple reloads to load from the qvd instead of going to the database over the slow WAN link. The QVD should live at the client end of the link – where the qvw is reloading.
Q:  If the data source is constantly changing (such as portfolio management software) can we refresh qvds frequently? Will this overburden the process?
QVDs may be refreshed frequently. Exactly how frequently depends on your data volumes and architecture. Refreshing every 30 minutes is common, and I have seen intervals of 5 minutes.  Frequent refresh of large volumes usually requires incremental load, which is covered in the Reference Guide and the Forums.
Q: ­If add the BUFFER command before each load statement pulling from DBMS, the first execution pulls from the DB but all after are incremental loads pulling from a file system created batch of QVDs?­
The BUFFER prefix does not provide incremental load when loading from a DBMS.  Subsequent reloads will load from the buffered file system QVD, but new rows will not be fetched from the database.
When used with a load from txt files, BUFFER will provide automatic incremental load. Subsequent reloads will add new data from the file to the buffered QVD.
Q: I am running SBE Server so documents are reloaded right from the Documents folder.  What is your recommendation for location of the QVD generator documents?  In other words, do you place them in the Documents folder alongside your production QVW’s? 
I recommend putting the QVD generators in a separate “Loaders” folder. Make this a mounted folder in QVS and schedule reloads as needed. Use NTFS permissions to hide the folder from standard AP users
A number of questions were asked about the QVX format. I haven’t had much experience with QVX yet. Rob Patterson has indicated he will schedule a QlikLearn webinar specifically on the topic of QVX.
Q: Does ­QVX also have two types of load, optimized and not optimized?­
No optimized load only applies to QVD.
Q: ­What are the other differences between QVD and QVX?­
QVD is a proprietary file format provided by QlikView for storage. Only QlikView software can read and write to QVD files.
The QVX is in an open format performance file for storage of QlikView data. A customer or third party can create QVX files on any platform, without needing Qlikview software.
QVD files will typically load faster than a QVX file.
Q: ­Is QVX used as a source to other source systems or is it used to pull the data from source systems which has no ODBC provider?­
The use cases for QVX are still being discovered, and I’m sure we’ll see some interesting uses.  The scenario I currently understand is to provide data to Qlikview when there is no ODBC provider.
Q: ­How can I create a QVX?
Q: ­How do you write out to a QVX?­
Q ­How can you read QVX from other software than Qlikview .­
Documentation of the internal QVX format is available in the Qlikview SDK. The SDK can be installed from the Qlikview Server installation package. Also look for examples in the “Share Qlikviews” section of QlikCommunity.
You can also create a QVX with a script STORE statement:
               STORE mytable INTO mytable.qvx (qvx);
This is useful to generate a sample QVX for examination or testing.
-Rob 
Share

15 thoughts on “QVD Questions and Answers”

  1. Thanks, Rob – I picked up a few new bits of information in that one.

    Regarding the comment “Nothing but Qlikview can read from QVDs, so no, DB2 cannot read directly from a QVD.” – this is not accurate. Some third-party tools (for example, the Excel plugin Vizubi) are able to read data from QVD sources. Of course, this doesn’t help whoever asked the original question, since they want to read it from their DBMS..

  2. Thanks for the clarification. I wasn’t aware of Vizubi. There may be other third-party tools as well that can read QVDs. If you know of some, please post a comment here.
    -Rob

  3. Hi. Very interesting and good presentation.
    You mention the need to do periodic snapshots for delta analysis. This is a very common requirement but I have not found any good documentation or how-to information on this subject. Eg. Inventory trending month by month, orders on hand this month compared to last month etc.

    Will it be possible for you to elaborate on how to best do this in Qlikview?

    1. Create the qvd file(s) that store periodic snapshots
    2. If several files are needed – how to load into Qlikview.

  4. Rob, looking at your presentation on Understanding QVds, regarding the 3 tier model I believe there is an inconsistency on the Transform operation from the Extracted Qvds. Earlier in the presentation you showed the Qvd Optimized Load limits ( mainly No transformations and No new fields allowed )…so what transformations can be applied to the extracted Qvds ? ( sorry if I missed something, maybe the transformations are still possible..but you simply don´t get an Optimized Load ? ..if this is the case, should I understand the qvd files don´t add value in terms of loading speed increase ?
    Thank you
    Enrique

  5. Enrique,
    It’s correct that QVD loads in the transformation step will probably be un-optimized.

    The transformed QVDs may be read optimized by multiple user facing qvws, which will benefit from the increased load speed of a QVD.

  7. QlikView can read the files .cub (local cube) of analysis services?

    You can then generate QVD files from the files .cub?

  8. Hi Rob,
    on your commment
    Q: ­Can we trace back QVD to its source?
    There is nothing inherent in a QVD that would provide source lineage.

    In a QVD there actually are Tags which contain the script leading up to the store (or so it seems)

    an example from a qvd of mine :

    Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=***;Extended Properties=””
    Bu:
    SQL SELECT * FROM S278.CCTBU where LANGID = 2

    Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=***;Extended Properties=””
    Contract:
    SQL SELECT * FROM S278.CCONTRACT

    …

    greetings,

    Michael

  9. @Michael: Rob’s presentation and blog post predate QV10SR2, but now QVDs store in an XML header the name of the QVW that created them as well as file sources and database connections/SQL statements.

  11. Rob

    Not sure that you are aware but Advanced ETL processor can now read and create QVD files

    Mark

  14. Thank you Rob for sharing your QV skills and techniques!

    Perhaps you can explain the following QVD behaviour:

    My environment date setting:
    SET DateFormat=’DD.MM.YYYY’;

    If I reduce my QVD data unoptimized by Where-Clause (e.g. Where Country = ‘US’) the date field will be displayed as YYYY-MM-DD. If I instead use “Inner join” or “Where Exists” (=optimized) in order to reduce my data the date fields still have the prefered format DD.MM.YYYY…

    Thanks,
    Daniel

  15. i read the question about merging multiple datamodels into one..

    We write the code as you mentioned….
    But what about the joins in the new qvw file? We need to do it again is it??

Comments are closed.