Monday, July 14, 2014

QV2 QVD datafiles

QVD is a flat data file with the extension .qvd. It can store a single table of data. It is created at path same as load script of a QVW file.
The structure of the file is essentially an XML format, with various bits of header information stored at the top of the file and the rest of the data beneath. The property of QVD file is that it is compressed on creation using the same algorithms as QlikView uses to store data in memory – so the files can be incredibly small for the amount of data they contain. Loading data from QVD into memory is blindingly fast as the format of the file mirrors how QlikView addresses data in RAM.

A QVD file holds exactly one data table and consists of three parts:
  1. A well formed XML header (in UTF-8 char set) describing the fields in the table, the layout of the subsequent information and some other meta-data.
  2. Symbol tables in a byte stuffed format.
  3. Actual table data in a bit-stuffed format.


If the data being collected is coming from multiple systems or is being distributed via multiple QlikView applications. QVDs can fulfil this requirement admirably. QVDs are also essential when you want to adopt an incremental load strategy.

Increasing Load Speed
By buffering non-changing or slowly changing blocks of input data in QVD files, script execution becomes considerably faster for large data sets.
Decreasing Load on Database Servers
The amount of data fetched from external data sources can also be greatly reduced. This reduces work load on external databases and network traffic. Furthermore, when several QlikView scripts share the same data it is only necessary to load it once from the source database into a QVD file. The other applications can make use of the same data via this QVD file.
Consolidating Data from Multiple QlikView Applications
With the Binary script statement it is possible to load data from only one single QlikView application into another one, but with QVD files a QlikView script can combine data from any number of QlikView applications. This opens up possibilities e.g. for applications consolidating similar data from different business units etc.
Incremental Load
In many common cases the QVD functionality can be used for facilitating incremental load, i.e. exclusively loading new records from a growing database.
To learn more about the use of QVD files and Incremental Load, follow the link below:
Using QVD Files for Incremental Load

How to create QVD file

QVD files can be created by two different ways:
  • using STORE command
Typically QVDs are created during the execution of your QlikView load script (*.qvw file). A STORE statement writes the current contents of a single table in your data model to a file on the disk. The syntax for this is:
STORE TableName INTO ..\Data\FileName.qvd (QVD);

LOAD "ename","job",deptno;SQL SELECT *
FROM scott."public".emp;

Store dimemp into dimempQVD.qvd;

  • By preceding a load or select statement with the Buffer prefix, QlikView will automatically create a QVD file which under certain conditions can be used instead of the original data source when reloading data.

A BUFFER prefix on a LOAD or SQL statement creates and maintains an automatic QVD for that statement. executions of the LOAD/SELECT statement will read from the QVD, avoiding read data from database. A read from QVD is generally faster than fetching from database.
BUFFER LOAD "ename","sal","job","deptno";SQL SELECT * from scott."public".emp

On first execution, the SELECT will fetch rows from the database and the resulting dimemp will be stored in a specially named QVD on the local machine(path preferabley in user/appdata). After this dimemp will automatically be loaded from the local QVD.

If you make a change to the dimemp LOAD/SQL statement, QV Reload will detect the change and fetch from the database again and update the local QVD.

No comments:

Post a Comment

web stats