topic Re: QV Publisher, datamodel in QlikView

QV Publisher, datamodel

Mon, 26 Jan 2026 18:19:17 GMT

Hey,

I have built an application that will be updated overnight and reduced to different QVWs based on field value.

However, I'm now considering whether to create different QVD files (one per QVW) or one large QVD that will be split up in different QVW's using the task described above.

I'm afraid the QVD will become very large, and because the QVWs will be updated at different moments during the day it might be hard to schedule updating the QVD. At the moment I'm leaning towards creating multiple QVD source files.

Can anyone offer his thoughts?

If I opt for creating multiple QVDs, then how do I create one QVW that I can update for each file (as you can imagine I don't want to maintain 100 QVWs that are exactly the same except for their source data?

I have uploaded the model as I think fits my purposes best, however implementation on the right hand side (QVD->QVW) I could use some help on.

Re: QV Publisher, datamodel

Wed, 18 Sep 2013 14:17:21 GMT

Hi Daniel,

A QVD can contain only 1 table. So if you want to use 1 QVD per QVW you have to put your entire data model for that particular QVW in a single QVD. That will likely result in a very big QVD file and also in the front-end of the QVW it doesn't help performance because you need DISTINCT if you want to calculate unique values. That's is also not so well for performance.

What about loading multiple QVDs into a QVW and reduce that with Publisher? Depending on the size of the document, the data reduction and the number of users you could also opt for data reduction on opening using Section Access.

Regards,

Kjeld

Re: QV Publisher, datamodel

Wed, 18 Sep 2013 14:44:22 GMT

Sorry, I should rephrase my question.

At the moment we are loading 10 QVDs into a QVW file and reduce it based on field value. However the QVDs are growing exceedingly large (as we keep on adding new databases). Is it possible to create separate QVDs (one per database) and then load them into corresponding QVW files (again per database, so reduced on database ID)?

It seems like we should loop over each QVD, and then reduce on field value, but in doing so the QVW will still take up loads of RAM. Creating one QVW file per database on the other hand will result in increased version management.

So the question: how can I create QVD's per database and then the corresponding QVW (per database), while having one master QVW?

Re: QV Publisher, datamodel

Anonymous — Wed, 18 Sep 2013 15:07:10 GMT

Daniel

I presume for your 'one master QVW' you wish to load all the discrete database QVD's into it.

You can use a wild card when loading QVD's. So if you have 3 QVD's called say:

DATABASE_1
DATABASE_2
DATABASE_3

Then

load * from 'DATABASE_*.qvd' (qvd);

Should load all three of them.

Best Regards, Bill Markham

Re: QV Publisher, datamodel

Thu, 19 Sep 2013 07:29:00 GMT

Hey Bill,

the problem is I don't want to have a Master QVW, because of it's sheer size.

What I want is a set of QVD's per database, one QVW that loads one database, publishes it and then loads the next database..

Re: QV Publisher, datamodel

Thu, 19 Sep 2013 08:59:24 GMT

If you have a Publisher license you can create QVDs without the need to create a QVW.
In the QMC you can go to System > Supporting Tasks > QVD creation.

You assign a folder as both a mounted source folder and user documents folder containing the file(s) loading the QVDs. The file can be reloaded in place, so it will be visible on the AccessPoint.

This is a bit of an odd solution though. I don't consider the same reference of a source folder and a document folder to be a best practice.

Regards, Kjeld

Re: QV Publisher, datamodel

Anonymous — Thu, 19 Sep 2013 09:02:25 GMT

Daniel

I have some QVD generators that loop through various Oracle Users on various databases.

I use a spreadsheet to hold all the connect strings then read this into QlikView and loop round it.

In the example below you’ll see that I construct a variable vQVDFile that defines the name and location for the QVD file for each database in each loop.

////////////////////////////////////////////////////////////////////////////////////
Connects :
////////////////////////////////////////////////////////////////////////////////////
LOAD
DATABASE,
CONNECT,
TYPE,
ORACLEUSER
FROM
[..\..\Config\Connect.STWT.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Match ( ORACLEUSER ,
// 'US_M01_T5L' ,
                                              'WE_M01_T4L' ,
// 'UK_M01_T4C'
                                                       )
;
////////////////////////////////////////////////////////////////////////////////////
// Start of the Loop
////////////////////////////////////////////////////////////////////////////////////
FOR vCounter=1 to NoOfRows('Connects')
let vLastLoadStart = now() ;
let vDatabase= peek('DATABASE', $(vCounter)-1, 'Connects') ;
let vOracleUser = peek('ORACLEUSER', $(vCounter)-1, 'Connects') ;
let vConnect = peek('CONNECT', $(vCounter)-1, 'Connects') ;
let vQVDFile = '$(vSTWTFolderQVD)$(vDatabase).$(vOracleUser).$(vFileNamePrefix).qvd';
let vLogFile = '$(vFolderLog)$(vDatabase).$(vOracleUser).$(vFileNamePrefix).Log.qvd';
///////////////////////////////////////////////////////////////////////////
// trace variables
///////////////////////////////////////////////////////////////////////////
         Trace ;
Trace ******************** ;
Trace vDatabase: $(vDatabase) ;
Trace vOracleUser: $(vOracleUser) ;
Trace vFolderLog: $(vFolderLog) ;
Trace vSTWTFolderQVD: $(vSTWTFolderQVD) ;
Trace vFileNamePrefix: $(vFileNamePrefix) ;
Trace vQVDFile: $(vQVDFile) ;
Trace vLogFile: $(vLogFile) ;
Trace ******************** ;
Trace ;
//////////////////////////////////////////////////////////////////////////////////////

// BEWARE: The QlikView editor will red underline this, but it is actually ok.
$(vConnect)
//////////////////////////////////////////////////////////////////////////////////////

................... load your data from the database

.//..................... save the QVD, drop the table & wait 5 seconds to release latches

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
STORE MSO into '$(vQVDFile)' (qvd);
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
drop table MSO ;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
sleep(5000) ;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

.//..................... don’t forget to close the loop and drop the Connects table

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
next
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
drop table Connects ;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Best Regards, Bill Markham

Re: QV Publisher, datamodel

Thu, 19 Sep 2013 09:23:20 GMT

Hey Bill,

that's exactly what I was looking for, and how I considered creating the QVD files.

The second step is I have an app that will be exactly the same for all databases, but I don't want to load all the separate QVD files into one QVW.

How would one go about letting a QVW looping through the right subset of QVD's and then publishing the corresponding QVW with corresponding rights to the server?

Kr,

Daniel

Re: QV Publisher, datamodel

Anonymous — Thu, 19 Sep 2013 09:34:09 GMT

Daniel

I have used Section Access to have a single qvw dashboard, but only allowing specific AD Groups / Users to see their subset of data. This can reduce the data available to each AD User to what they are allowed to see, hence alleviating issues due the large volume of the total data.

See http://community.qlik.com/docs/DOC-1853

QV Publisher has its "Loop and Reduce" functionality. I have never used it though as Section gives exactly the results I need.

Best Regards, Bill Markham

Re: QV Publisher, datamodel

Thu, 19 Sep 2013 10:55:07 GMT

Interesting piece of information but still not quite what I'm looking for..

Lets say I have 1000 customers, each with a DB and corresponding QVD file of 10GB. That will result in 1000 * 10 * 1.25 =1250TB of RAM use; if all needs to be loaded in one document, and THEN restricted by using section access. Right?

That's the issue, we simply can't load ALL data in one file and THEN distribute. As far as publisher goes, I'll have a look again, I only saw a reduce funtion, which still needs all data loaded in one file at least once.

Re: QV Publisher, datamodel

Anonymous — Thu, 19 Sep 2013 12:06:37 GMT

Daniel

I am not convinced at all that your estimate of 1250TB of RAM, for your 1,000 Customers [i.e. a touch over a TB for a single customer] is remotely accurate.

How about for testing you take one customer and generate the QVD ~~for that customer.~~

Use that to populate your qvw dashboard and see how much RAM it consumes.

Then use something like Rob Wunderlich's Document Analyser Downloads - Rob Wunderlich Qlikview Consulting to optimise RAM usage, especially removing unused data fields and eliminating GUI tables displaying loads & loads of rows.

I would wager that RAM usage will be a lot, lot less than you expect. And with more tuning could be reduced even more.

Best Regards, Bill Markham

Re: QV Publisher, datamodel

Thu, 19 Sep 2013 12:44:51 GMT

Yeah obviously I meant 12.5 and just as an example, there could be more than 1000 customers. Some customers have small databases (less than 1 gig) and yet others are larger). The 1.25 multiplier was provided by QlikTech as a rough rule of thumb..

As for your suggestion to test for one customer, yeah we did. In fact, storing all customers in one combined file won't be a problem at all at first. However, it's best to be prepared for the future:)

I guess our situation is pretty uncommon. Thanks for the help.

Re: QV Publisher, datamodel

Tue, 24 Sep 2013 11:17:02 GMT

You can setup so you have one master qvw to load data into, then distribute master qvw by loop and reduce to X amount of qvws, they will then run another loop and reduce and provide one specific document per user.

And you dont have to publisher master doc as each user has it´s own file. Or split it down to working sizes in the second step and then use Section access to control access, and publish DataAA,etc

All comes down to what works and not.

DataA >

DataAA

DataAB

DataAC>

UsersA

Andy

Amy

UsersB

Bob

Bewoulf

etc