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 of these applications are available for download from Qlik Community and our Qlik Downloads page.
QVD Header Viewer for QlikView
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!
This is just amazing. I have already found one use for this (finding the qvw used to create the qvd file) and I am going to show it to some of my other colleagues as I am sure they will like this information.
Thanks Steve!!
Thanks Sunny – glad you like the app. Thanks for sharing with your colleagues.
Thank you very much Steve very interesting and I greatly appreciate your interest and posting
This is very timely, we were just talking about a building qvd library yesterday in the office! One quick question (and I may have missed something), but is there a way to get the app to parse a directory structure? I.e. given the top level dir, it will work its way down the structure looking for qvds? Thanks!
Hi John, thanks for your comment. It is certainly doable, and I have code which will do it. It’s in a code that I’ve not made available, partly because the code needs tweaking to work in different environments. If you get in touch with me directly then I will be able to share this with you.
Hi Steve,
When you load those tables, is there no column that is common across all the tables to join them?
for e.g. QvdTableHeader and QvdTableHeader/Lineage/LineageInfo and other tables have no common field so that I could join them and get output as 1.QVD has these Columns then that QVD was generated via this statement.
Hi Anurag,
There is no key within the XML data itself, but you can add a key. If you are just loading from one QVD you can just have 1 as ID in the load. If you are looping you can keep a counter of QVDs and use that for the id, e.g. $(iID) as ID. If you look at the example application I have provided you will see that this is the way that I have dealt with it there. Hope that helps.
Steve