3 Replies Latest reply: Aug 16, 2016 4:07 PM by Martin Pohl RSS

    QVD

    Govind Ramchetty

      HI

      I am using Qlik Sense.What is the use of QVD and How to create give me steps pls.

       

      Thanks

      Govind R

        • Re: QVD
          Anand Chouhan

          Read some of this threads.

           

          https://community.qlik.com/docs/DOC-6932

           

          Regards,

          Anand

          • Re: QVD
            Miguel Braga

            Hi there,

             

            From these reference:

            http://www.quickintelligence.co.uk/qlikview-qvd-files/

             

            Checkout information bellow. It explains very well what it is:

             

            "QlikView QVDs play a crucial part in the majority of QlikView implementations. As such I have blogged about their use many times previously; such as in this post on Incremental Loads and this one on Optimised Loads. Now that I am going Back To Basics in this series of posts I felt it was a good point to go back to first principles with QVDs.

             

            What Is A QVD?

            At a basic level a QVD is a flat data file with the extension .qvd. It can store a single table of data (is. each row shares the same column list) and is typically created in the 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. One of the beauties of a 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 from QVD back into memory is blindingly fast as the format of the file mirrors how QlikView addresses data in RAM.

             

            Why Should You Use QVDs?

            One of the points that is often noted when pitching QlikView is that it does not need a data warehouse or predefined data cube to work off. This is completely true, but the requirement to have a logical data layer often remains. This is particularly true 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.

            Personally I would go as far as saying your implementations should always be built on QVDs – except perhaps where the data source is a simple Excel spreadsheet that is stored locally. There are many reasons I would suggest this, some of these are:

            • Decoupling data extract from data presentation
            • Ability to do parallel data extracts
            • Easier unit testing of parts of the load process
            • Incremental loads
            • Sharing of extracted data between presentation apps
            • Ability to scale the solution when data volumes grow
            • Ability to delegate responsibility for different parts of the data load to different teams

            The question you should be asking when designing your data load strategy is not why you should be using QVDs – but rather are there any reasons why you shouldn’t be using them (the reasons here are very few).

             

            How Do I Create A QVD?

            Typically QVDs are created during the execution of your QlikView load script. 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);

            Note that the STORE command can also be used to write data into a comma or tab delimited file – but that is a topic for another blog post.

            QVDs can be created during the execution of any QlikView load script, but best practice is to have separate applications which deal solely with the creation of QVD files. This “extraction layer” then handles all interaction with source databases. Depending on the size and complexity of the data being loaded I may create one app for each table being extracted or a single app may create all QVDs for the solution (or anything else  in between). What is important though is that the front end can be reloaded quickly from locally stored data files. This ability to refresh the presentation layer quickly can massively speed up development.

             

            How Do I Use My QVDs?

            Once you have created a data layer consisting of a number of QVDs you simply load from each QVD file in the same way you would a CSV or Excel file. The syntax is:

            TableName:
            LOAD
            FieldList
            FROM ..\Data\FileName.qvd (qvd);

            Note that where with CSV or Excel files the load statement contains a chunk of information about file formats this is not required with a QVD load. You can also use the wizard in the load script by clicking the File button and locating the QVD in the folder browser.

            When loading from a QVD you can apply a number of transformations, such as renaming columns, excluding rows and adding derived columns. Be aware though that many of these transformations will cause your QVD load to be non-optimised (which will make the load up to 100 times slower) please see this blog post for more information: QlikView Optimised Loads.

            By loading from multiple QVD’s into a single application you can build up your associative data model. This could involve some data from QVDs (perhaps originally from different data sources) and maybe some small lookup type data values from yet another source.

            As well as being a very quick way of loading data into QlikView apps QVDs can also be useful for archive – due to the excellent compression which is used in their creation. You could, for example, store dated copies of a data set into QVDs; your front end would then typically work off the latest version, but it would be possible to point it to an archive version of the data if required.

             

            Conclusions

            QVD Files

            QVD’s are QlikView’s proprietary way of staging and storing data. They can be used to provide a logical data tier in your solution and in some cases could remove the need for a data warehouse. Using QVDs you can share data between multiple applications without having to keep going back to the source data, you can also chunk up the data load process into manageable steps.

            If you are presently using QlikView but not using QVDs then I strongly recommend that you consider the advantages of doing so."

             

            Hope this is helpful,

             

            Regards,

            MB

            • Re: QVD
              Martin Pohl

              there are many advantages with using qvd-files:

              - once load from your data source and stored you don't need to reselect them from your system (e.g. the material master, used in diffenrent data models

              - you can realize a delta load for your datas. "old datas (e.g. invoise lines from last year) won't never Change, so why load them all days from source? Realize a delta load and collect all qvd files into your daat model.

              - you can realize a calculated data difference view to your data. Most of your datas you have a create date and a last changed date. But how about all changes between first date and last date? While store your datas e.g each date to qvd you can compare these datas each date - not only one

              How to:

              while selecting your datas from your source name the loaded table:

               

              Data:

              SQL select fields from source;

               

              then you can store your datas by using store statement:

              store Data into path\Dataname.qvd (qvd);

               

              after that you can unload the datas in this script:

              drop table Data;

               

              To use a delta load almost  I'm using a loop for actual and last period because most the previous period is closed during actual period.

              Example:

              for vYear = (year(today())-1) to year(today())

               

              Data:

              SQL select fields from source where Year = '$(vYear)';

              store Data into path\Dataname_$(vYear).qvd (qvd);

              drop tabel Data;

              next

               

              for initial load you have to setup the range to all periods wanted.

               

              Then you can load your datas in script with wildcards

               

              load * from path\Dataname_*.qvd (qvd);

               

              Regards