In june 2013, had been using QlikView for about a year. During those days, I was working on a QlikView project where I had to show the number of sales across multiple channels over multiple periods (as an example, daily, monthly, annual) and compared to last year.
I had two transactional tables, three mapping tables (available in Excel). I had successfully completed this project within a defined time frame. Despite this, was 2 problems with the model i used:
- Whenever there was any change to any of the mapping files, I had to repeat the reload procedure which involved not only reloading the Excel, but also reload the entire transaction table. If there is a problem with the network or the database, the recharge would fail.
- When I used to report the number of daily sales, recharged full boards daily, while the data until yesterday was already with me. It was too slow a procedure.
Image source: qlik.com
This is when using a QVD file (QlikView Data) made a big difference. You can think of these QVD files as an intermediate layer, so that your front-end queries don't run directly on the databases. The creation of these QVDs can be done during off-peak hours (less network and database load). The QVDs are also essential to load only the incremental data (In other words, additional or modified records).
What is a QVD file?
QVD is a QlikView format and can only be written to and read from QlikView. A single QVD file can store a single table and is created in the load script in a QVW file.
A QVD file consists of three parts:
- XML header to describe table fields, the layout of subsequent information and other metadata.
- Symbol tables in byte-filled format.
- Actual table data in a bit-padded format.
Advantages of using QVD:
QVD files offer many benefits to your QlikView applications, including the following:
- Faster charging time: QVD data reading is 10 a 100 times faster than other sources, In other words, reduce loading time. Because QVD creation uses the same algorithms that QlikView uses to store data in memory, the result of that load from QVD is faster, compared to other formats.
- Less load on databases and networks: Once the data is exported in QVD, you don't need to reconnect to an external database. Reduces the workload on databases and external networks. At the same time, when multiple QlikView scripts share the same data, only need to be loaded once from source database. The other applications can also use the same data through a QVD file.
- Incremental QVD uploads: Incremental load (loading only new details / modified from a database) can be done only using QVD. Significantly reduces charge time compared to full charge.
- Consolidate data from multiple data sources and databases: Multilayer QVDs are used to create a robust model, when data is obtained from multiple data sources.
How to create a QVD?
QVDs are most frequently created during the execution of the QlikView load script using the STORE command:
Syntax: TO STOCK
It is good practice to have a separate application to create QVD files. This layer is used to handle all interaction with source databases. And the result of this interaction can be quickly reloaded from QVD files.
Incremental loads: As mentioned previously, incremental loads are defined as loading only new or updated records from the database in an established QVD. Incremental loads are useful because they run much faster than full loads, specifically for large database data sets. (We will discuss it in detail in the next post.)
How to load data from QVD?
To read data from QVD, we need to write a LOAD statement (equivalent to the command to load a CSV or Excel file).
Syntax:
TableName: LOAD FieldList FROM <PathFileName.qvd (qvd) >;
QVD files can be read in two modes, standard (Quick) and super fast. The selected mode is automatically determined by the QlikView script engine. Ultra-fast mode can be used only when all fields or a subset of them need to be read without any manipulation (as an example, formula-based field generation), even when renaming the fields is possible.
Final notes:
QVD administration is one of the most important parts of any QlikView application project. It can be used to provide a logical data layer and, therefore, can act as a data warehouse in the absence of a (one of the great benefits of Qlikview compared to other BI tools on the market).
QVD creation helps reduce network and database load. Once a base QVD is generated, an incremental load script can be executed to load only the new or modified records in the QVD. Incremental loading also removes deleted records. As a result of this, your application consumes less space and requires less loading time.
If you have used QVD in the past to create multi-level data models or plan to use one in your application now, feel free to share your experience with me through the comments below..
