Creating QVD files from source system

[sangland2]

We have found that some QV reloads using ODBC are very slow. This appears to be a problem with the qvconnect layer not QV or the database server delivery data. We have decided to bypass this by dumping database files to csv which is very fast and loading csv files into QV. Taking this one step further we are considering creating qdv files, rather than csv. There appears to be a slight performance gain loading qvds rather than csv. Would this be correct? That being the case we can easily create the HTML format of QVDs. Teh problem is the syntax for the raw data (attached). I know there are ETL tools in the marketplace, but we have to develop this on the database server side. Is there a definitions for the qvd fle structure?

As an example :

Transactions:

LOAD * INLINE [

    product,branch,Transdate,qty

    F100,100,'31/10/14',104

];

STORE Transactions into 'c:\temp\Transactions.qvd' (qvd) ;

Then the QVD file XML looks like this up to the first field:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<QvdTableHeader>

   <QvBuildNo>12664</QvBuildNo>

   <CreatorDoc>C:\temp\test.qvw</CreatorDoc>

   <CreateUtcTime>2016-04-13 03:54:27</CreateUtcTime>

   <SourceCreateUtcTime></SourceCreateUtcTime>

   <SourceFileUtcTime></SourceFileUtcTime>

   <SourceFileSize>-1</SourceFileSize>

   <StaleUtcTime></StaleUtcTime>

   <TableName>Transactions</TableName>

   <Fields>

     <QvdFieldHeader>

       <FieldName>product</FieldName>

       <BitOffset>4</BitOffset>

       <BitWidth>4</BitWidth>

       <Bias>0</Bias>

       <NumberFormat>

         <Type>UNKNOWN</Type>

         <nDec>0</nDec>

         <UseThou>0</UseThou>

         <Fmt></Fmt>

         <Dec></Dec>

         <Thou></Thou>

       </NumberFormat>

       <NoOfSymbols>2</NoOfSymbols>

       <Offset>0</Offset>

       <Length>12</Length>

       <Comment></Comment>

       <Tags>

         <String>$ascii</String>

         <String>$text</String>

       </Tags>

     </QvdFieldHeader>

Then code as attached follows the XML. Understanding this would be useful ?

[Peter_Cammaert]

No there is not. The QVD file format is proprietary and undocumented. Only QlikView Desktop or the reload engine in QlikView server can generate QVDs.

There is however an SDK library that reads/writes QVDs. AFAIK you can license it from Qlik. If you can either offer them a lot of money or a lot of future business.

Peter

[marcus_sommer]

If your database could process the queries quite quickly into csv-files but is per odbc-connection a lot slower (and you could exclude the performance of the network as reason) then it might be helpful to search for another odbc- or oledb-driver or are there any proprietary custom-driver available (or rather to invest in them instead of creating qvd's from outside of qlikview).

Beside them you should consider to use incremental load-approaches (either on sql- or on csv-level) to reduce the amount of data to load and speed up thw hole load-process. Here: Advanced topics for creating a qlik datamodel you will find various links to these topic and some more.

- Marcus

[sangland2]

Thanks, We'll stick with csv files. We are already doing incremental loads but only add/update. Add/update/delete is as slow as a full reload. A full reload using csv files will much faster than and incremental load over odbc. Unfortunately we are stuck with the ODBC driver we have which is proprietary.