QVDs form a crucial part of many Qlik implementations, and are an ideal way of persisting data in both Sense and QlikView. What you may not be aware of though is there is an XML header to these files containing useful information. This post explores this header, and has free downloads to load them into Sense and QlikView.
Why Use QVDs?
This is something I have covered before on this blog, so I won’t go into detail here. Just to say that they are a best practice method of separating out ETL and data presentation tiers in an implementation, they are also essential for incremental loads. The advantages of chunking up bits of ETL into separate tasks, with QVDs as the interim file, is that parts of your code can be unit tested and run separately (which can speed up development). The advantages of incremental loads are more obvious, with you only needing to pull recent or changed data from your data source and the rest coming from pre-stored QVDs.
One thing to watch with QVD usage, that can have a major impact on performance, is whether your QVD loads are optimised, look out for this as you load from QVDs and see our article for details on how to fix problems.
What is in the XML Header?
The simplest way to discover what is available to you in the header of a QVD is to take a look. If you simply load a QVD into a text editor you will find the header at the top. Be warned this may not be so sensible for very large QVDs though!
As you look down this header you will find much useful information, including the app which created the QVD, information on all of the fields in the QVD, the content of those fields and the number of rows.
Loading The XML Header
In a previous post I detailed a bit more about the content of this header and how to load QVD XML headers into QlikView. Loading the header into Sense is done in a similar way.
Simply create a Library pointing to where your QVDs reside (or you will probably have this already). Click the Select Data icon, exactly as you would to load the contents from a QVD. Sense will then correctly identify this file as a QVD and offer that as the File Format. Using the drop down you can override this though, and tell sense you want to load the XML portion of the file. Once you have done this you will see the XML header in the preview.
When you click Insert Script the code to load the QVD header will be inserted into your load script.
I’ve noticed that Sense can get the preview and code wrong on larger QVDs, but the code is the same on any QVD – generate the code on a small QVD and then point it to the larger one you need to read from.
Tools for Looking at QVD Headers
One tool that I always recommend our clients licence and install is QViewer, this allows you to double-click a QVD file and view the contents. It also has a menu item to open up the metadata of that QVD, seeing the values I’ve mentioned above. Another tool that has recently come to my attention is NodeGraph, thanks to Rob Wunderlich’s recent blog post. This looks like a totally awesome tool, and is something I am hoping to have a much better look at soon.
What I find can be very useful though is having a tool which I can view the QVD structure data with directly in QlikView or Sense. Here the QVD header data can be loaded in alongside other data and be made available to users through the same Hub or AccessPoint they are already used to. It is also useful as a consultancy to have something we can leave with clients with no additional installation or licencing. To that end I built a QlikView app to do just that, and this has also been ported across to Qlik Sense.
Both parse a repository of QVDs and then display the header information for those QVDs. The script also persists the current state to QVD, and keeps an archive QVD, so you can see how the content of your QVDs have changed over time.
Both apps include the QI Qlik Data Profiler components, which can be copied and pasted into other applications.
If you have any questions or comments on the apps please leave these below, or in Qlik Community. If you find the apps useful then please rate them and like them in Qlik Community also. Thanks!