February 21, 2016

QVD Metadata

Examining QVD metadata

Qlikview data files (extension .qvd, referred to as QVD files in this article) are used for intermediate data storage, history, incremental loads and so on. The file contains the full information of a single data table and the files are usually generated and consumed by the load script. The metadata is useful when developing and operating multitier Qlikview setups.

What you may not know is that the qvd file contains extensive metadata for the table. This includes:
  • The name of the qvw file that created the QVD file
  • The version of Qlikview when the load script that created the QVD was run
  • The time the file was created
  • Metadata for the fields (name, number of unique values, data type, width, symbol table offset etc
  • The number of rows in the table
  • Lineage data, including data sources, SQL queries and connection data
This metadata is found in an XML header in the first part of the file. There are several ways of accessing this metadata without loading the entire file

Windows Batch "More"

A simple way to look at the metadata (as raw XML) is to use the Windows batch "more" command running in a console window. To set his up, you need a simple batch file. These are the steps to set this up:
  • Create a text file on your desktop using a text editor (notepad will do, although I prefer Notepad++)
  • Add a single line:   more "%1"
  • Save the file.
If you saved the batch file on your desktop, you can drag a QVD file and drop it on the file icon. A command or console window will open showing the first part of the XML metadata header. Press space to get the next page full. One you get a whole bunch of strange characters, you have reahed the end of the header and are looking at the symbol table which follows the XML header.

This approach is simple, but not always the most convenient way to get the metadata you need.

Powershell Script

To simplify extracting the metadata and displaying it in a clear layout, I have created a Powershell script to extract the XML and unpack the XML elements. Powershell is a standard component of Windows since Windows XP/Server 2003.

If you are not already using Powershell, create a scripts folder: eg C:\Scripts or C:\Users\Fred\Scripts. Download the script ps1 file and copy to the scripts folder you created.

 qvdMeta.ps1

You can use this script using the following command line (type in CMD window):
>powershell C:\Scripts\qvdMeta.ps1 -source

You can add this to a batch file saved on your desktop:

@echo  off
powershell C:\Scripts\qvdMeta.ps1 -source %1

Now drag the qvd file and drop it on this file to see the fie metadata:

QVD Meta data extractor V1

Source file     C:\Data\QVDs\Balances.qvd
Created by      C:\QVDeploy\QVDCreators\Balances.qvw
Qlikview build  12664

No of Rows      231 372
Table name      BALANCES
Build time      2015-12-08 01:26:59

Fields:
  EffDate                                         1 282  values
  RelNo                                             389  values
  Company                                             8  values
  FundCode                                           29  values
  ClientName                                        362  values
  ClientBalance                                   9 855  values

XML lines       201 ; bytes =  6 499:



Note the script has been tested with QVD file produced by QV 11.20 up to SR13. I have not tested it with QVD files produced by Qlik Sense, or QV 12. It may need minor mods.

Open using Qlikview

If you are not interested in the metadata, but want to examine a qvd, you can read the qvd in Qlikview - just drag the qvd and drop on the QV icon on your desktop. This will create a load script. Now click File | Reload, and create some list boxes, and a table box for a quick view of the content.

To view the  metadata only, change the file type in the QV file preview to XML. Now you can load the XML into QV rather than the actual data.




















 

4 comments: