Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

MVP
MVP

Re: QVD size doubling when concatenating 2 identical fact tables

Could you post the meta data (XML-part) of your three QVDs?

If your field values are quite different in both QVDs (so it needs more bits to represent the distinct values in the facts and maybe as well more bytes to represent the symbols, like for mixed number / text representation), this may explain some of your size increase.

Partner
Partner

Re: QVD size doubling when concatenating 2 identical fact tables

Lots of %IDs there. Any chance they were created with Autonumber during the QVD creation steps? Because if so, concatenating the tables will break consecutive integer optimization. Maybe

MVP
MVP

Re: QVD size doubling when concatenating 2 identical fact tables

In addition, you are creating some of the fields in your load with a constant value.

Are you comparing your numbers using the original QVD sizes or after transformation, but not concatenated?

Also note that the constant field value optimization (QV does not need to store a bit pointer for a constant field value) doesn't kick in if you are concatenating the table with records with distinct values. Since you are using quite a lot of these fields in either table, this might explain very well the increase in fact table size.

chinnakatikisg
Valued Contributor

Re: QVD size doubling when concatenating 2 identical fact tables

No,we haven't created the %IDs using autonumber. Those are direct key values from dimensions.

However, These QVDs are not straight forward ones. These are transformed QVDs.

without constant values also it is giving same size. I have put the constant values just to enforce auto concatenate .

chinnakatikisg
Valued Contributor

Re: QVD size doubling when concatenating 2 identical fact tables

Dear Stefan,

Thanks for your time.

How to extract the XML part??

Br,

Chinna

MVP & Luminary
MVP & Luminary

Re: QVD size doubling when concatenating 2 identical fact tables

Open the file with a text editor. And copy the xml part. Basically the part that's readable.


talk is cheap, supply exceeds demand
Highlighted
Partner
Partner

Re: QVD size doubling when concatenating 2 identical fact tables

I think the key point about the constants is that you are not only concatenating the tables, you are also making each of them wider (by adding more fields). Especially the second, larger table, where you add over 10 columns. This widening has its own cost in space, no matter if you fill the added columns with zeros, empty strings, nulls (which Concatenate would add automatically if you did not assign them manually), or whatever else - at least that's how I understood Stefan's remark.

You could not test it by adding the columns to each QVD and storing the results separately, because QlikView would wise up to the fact all values in the column are for example 0 and use nearly no space because of that. After you actually concatenate the tables, it's no longer the case (QlikView cannot use this single value in column compression trick anymore) and the size grows.

MVP
MVP

Re: QVD size doubling when concatenating 2 identical fact tables

The part is the text part at the beginning of your file, e.g. when opening in a decent editor like notepad++.

chinnakatikisg
Valued Contributor

Re: QVD size doubling when concatenating 2 identical fact tables

PFA QVD XML

MVP
MVP

Re: QVD size doubling when concatenating 2 identical fact tables

You would need to extract that for the two original QVDs and the final, then compare the meta data for the fields.

For example, have a look at field #ACT_SALES_AMT_RATE_A

  <QvdFieldHeader>

       <FieldName>#ACT_SALES_AMT_RATE_A</FieldName>

       <BitOffset>96</BitOffset>

       <BitWidth>16</BitWidth>

       <Bias>-2</Bias>

       <NumberFormat>

         <Type>UNKNOWN</Type>

         <nDec>0</nDec>

         <UseThou>0</UseThou>

         <Fmt></Fmt>

         <Dec></Dec>

         <Thou></Thou>

       </NumberFormat>

      <NoOfSymbols>36088</NoOfSymbols>

       <Offset>113581</Offset>

       <Length>324788</Length>

       <Comment></Comment>

       <Tags>

         <String>$numeric</String>

       </Tags>

     </QvdFieldHeader>

You see that there are a lot of distinct symbols, and QlikView uses 16 bits to represent.

But in one of your input table, this field is missing or constant, so QV does not need to use any bits for this field in the fact table - but only until you concatenate the table with the 16 bit symbol pointer.

If your first table has a lot of records and some more fields like this, this could explain the unexpected increase in size.

(and using the meta information of all three tables, you can actually double check this by calculating the sizes manually).