topic mega loading in QlikView

mega loading

Tue, 13 Mar 2012 08:55:08 GMT

Hi,

I'm creating a mega project, and i'm using 3 tiers architecture, meaning, the data will be loaded into QVD by different QVW.

For the loading of the QVW, i don't want to use publisher to schedule them because it's just too many of them, and hard to maintain.

So i use the idea of BW process chain logic, and created something similar for QV.

There's a main dashboard, to monitor the load of each QVW, it will tell if it's sussccesfful or fail or when was it last run, etc.

and each QVW, we use batch file to trigger the qv process to execute the qvw in background,

and it has the ability to do daily load, weekly, monthly, or monthly on a specific date, works like wonder.

BUT sometimes it run and sometimes it doesn't, and i'm not really sure what is the error, although i can get it from the system log, but it didn't tell much.

and if i kill all the processes and did a manual trigger, it works, this solution by batch is really hard to troubleshoot.

has anyone of you done anything like this? maybe not the batch approach?

Please share .... Thx.

mega loading

Tue, 13 Mar 2012 11:41:37 GMT

I'm actually working on a project very similar to this. I have a QVW that reloads and creates 10+ QVDs and then there are several other QVWs that read from those QVDs - 3 to be exact. However, one of those 3 actually executes a chain of reloads - it fetches a list of IDs from a database and then reloads a 4th QVW once for each ID fetched from that table, thereby creating (loop on field) one new QVW for each ID in that field. I also decided not to use publisher, the "master" that actually handles the reloading is a QVW itself.

On a technical note, I realized that qv.exe was too unreliable for this. It might work in testing, but as soon as you try to start using batch files, or the windows scheduler, things get weird. Since we did have QlikView Server, I chose to use qvdistributionservice.exe for reload as described in Section 25.2 of the QlikView Version 10 Reference Manual (page 195). It works very well.

Is that how you are doing the reloads? What version of QlikView are you working with? Is it QlikView Server? What is the error that you mention that comes up?

mega loading

Tue, 13 Mar 2012 12:13:59 GMT

Hi Ralph,

Interesting info you are sharing.

Yes, i'm using qv.exe, like you said, very unreliable.

i use a window scheduler to tirgger the batch to execute the qvw, then when it's done, send the edx message to publisher to finished up the rest of the loading.

But i have never heard of qvdistributionservice.exe, could you provide more info?

I'm on V10 SR2, i tried to look into section 25.2, and it talked about the following.

Could you do a screen copy of the doc and send to me?

and where can i find qvdistributionservice.exe?

Thanks.

25.2 The QVD file Format

A QVD file is an attempt to strike a compromise between excellent QlikView performance

both reading and writing the files and a compact representation. A QVD file

contains exactly one table. Conceptually it is quite similar to any typed file (e.g. csv,

dif, biff or fix). A QVD file consists of three parts:

1 A well formed XML header (in UTF-8 char set) describing the

fields in the table, the layout of the subsequent information and

some other meta-data.

2 Symbol tables in a byte stuffed format.

3 Actual table data in a bit-stuffed format.

mega loading

Tue, 13 Mar 2012 12:37:15 GMT

Hi Ralph,

I just tried to execute with QVDistributionService.exe, but it doesn't work.

It says - "Canot start service from the command line or a debugger .... "

How did you do it?

i also tried QVB.exe, but it didn't work, and in the end i had to use qv.exe.

Re: mega loading

Tue, 13 Mar 2012 12:46:44 GMT

Sorry! Driving. There is a .config file of the same name in that directory. Edit it and change the directive "enablebatch" or something like that, to TRUE. That will let you run it from the command-line. As soon as I can I will post some examples.

mega loading

Tue, 13 Mar 2012 13:12:22 GMT

Thanks Ralph, good tips, i need your sample code, i tried to execute but it's asking for username and password. werid ...

Re: mega loading

Tue, 13 Mar 2012 13:35:54 GMT

what is asking for a username and password? qvdistributionservice.exe? could you post a screenshot?

basically I use the EXECUTE function in the load script:

LET QVDistributionEXE = 'C:\Program Files\QlikView\Distribution Service\QVDistributionService.exe';

LET QVWLocation = 'D:\QlikView\Documents\';

EXECUTE cmd.exe /C ""$(QVDistributionEXE)" -r="$(QVWLocation)\Master.qvw" -variablename=vID -variablevalue=$(vID) -out=Runner_$(vID).log";

This is actually enclosed in a for loop and vID is what gets pulled from the database.

This would basically translate to the following on the command-line (for a batchfile or vbscript):

"C:\Program Files\QlikView\Distribution Service\QVDistributionService.exe" -r="D:\QlikView\Documents\Master.qvw" -out=Master.log

I omitted the variable part because you probably don't really need that part of it. You definitely want the -out parameter since that is the logfile.

I've attached the server reference manual - look at page 195.

You mentioned that you sent the EDX message to publisher - how are you doing that?

mega loading

Tue, 13 Mar 2012 15:45:16 GMT

Hi Ralph,

Thanks, i was looking at the wrong manual.

I tested, it works.

But do you know the differences between qv.exe and qvdistributionservice.exe?

For edx, i got it from http://community.qlik.com/message/135587#135587

you need to save - RequestEDX10.vbs, and then follow as instructed.

mega loading

Tue, 13 Mar 2012 15:55:46 GMT

the differences really revolve around the windows server environment. qv.exe is a front-end application that wants to display a GUI - even when you use "/r"

this becomes a major issue when you are trying to use the windows scheduler and batch files, where, technically, if no user is actually logged on, there are no "GUI sessions" available. Thats the simple explanation.

qvdistributionservice.exe does not need a GUI. It is truly a command-line app. It also happens to be the actual executable that runs the "QlikView Distribution Service." Because of this you can pretty much run it however you want, command.com, powershell, pstools, remotely, etc.

One other major difference: The user that you run the qvdistributionservice.exe as, MUST be in the local security group "QlikView Administrators." (this might be why you got a username/password prompt)

Also, for both qv.exe and qvdistributionservice.exe - if you are using any connection strings with integrated authentication, the user running the .exe must have access to the data (e.g. files, databases, etc).

mega loading

Thu, 15 Mar 2012 16:45:40 GMT

Hi Ralph,

Did you schedule your loading by publisher or still window scheduler?

Because i just tested to schedule the qvw directly with publisher and it's now working.

i think this will change a lot of things like those u explained.

I'm still in testing phase, because other than normal scheduled loading, i have the funtion to do selective manual loading by data source.

Thanks for your help.

mega loading

Thu, 15 Mar 2012 16:56:32 GMT

I've used this successfully in three different scenarios:

1. Publisher/QlikView Server Reload

2. Windows Scheduler Reload

3. External Scheduler handling the reload (e.g. EDX, remote batch file execution, pstools)

My suggestion is always the QlikView Enterprise Management Console (QEMC) for reloading. It makes it easier to see the jobs, and any errors the master jobs generate. But, you can pretty much do it any other way if you really need to.

Glad to help!

mega loading

Mon, 19 Mar 2012 13:04:11 GMT

Thanks Ralph, i converted all the loading to QVDistributionService.exe, it's much much more stable.

Thanks and Happy qliking..

Re: mega loading

Mon, 26 Mar 2012 13:36:06 GMT

Hi Ralph,

Just a quick check with you if u faced the following error before?

i'm setting up another machine, and trying to use QVDistributionService.exe, but when i execute it, i got the error message like below.

In fact, i copy paste what i have done just to test QVDistributionService.exe , the only difference in this machine is that it's Qlikview 11.

Does it matter?

I did modify the config file for EnableBatch = True.

3/26/2012 3:22:15 PM Error Exception=System.NullReferenceException: Object reference not set to an instance of an object.

at QDSMain.WorkorderStore.StartWorkorderStore()

at QDSMain.ObjectPool.InitObjects(ILogBucket i_LogBucket, eRunLevel i_RunLevel, Func`1 clusterIDListGetFunc)

at QDSMain.MainClass.BatchRun(eBatchReloadMode i_BatchReloadMode, String i_BatchReloadDocumentPath, String i_VariableName, String i_VariableValue)

3/26/2012 3:22:15 PM Information Setting exit code to the number of errors. ErrorCount=1

Thanks.

Re: mega loading

Mon, 26 Mar 2012 13:52:30 GMT

I have never seen that error. Is this happening for all your reloads, or just some of them?

Try restarting the QlikView Distribution Service and see if that gives you an error of some kind. I would also try and use the "Trace" directive in the config file - that might produce some more errors.

Re: mega loading

Mon, 26 Mar 2012 14:27:22 GMT

Hi Ralph,

Yes, i did restart the service after the modification.

with Trace on, i got the following, it doesnt seem bad?

3/26/2012 4:25:13 PM Information TRACE:Cluster file successfully opened. File=Notification.xml, Access=Read

3/26/2012 4:25:13 PM Information TRACE:Cluster file closed. File=Notification.xml, Access=Read, Duration=00:00:00.0470000

3/26/2012 4:25:13 PM Information TRACE:Cluster notification subscription added for notifiable object. Object=QDSMain.TriggerDetailsFile

3/26/2012 4:25:13 PM Information TRACE:Cluster notification subscription added for notifiable object. Object=QDSMain.TaskDetailsFile

3/26/2012 4:25:13 PM Information TRACE:Cluster notification subscription added for notifiable object. Object=QDSMain.TaskExecutionHistoryDetailsFile

3/26/2012 4:25:13 PM Information TRACE:Cluster notification subscription added for notifiable object. Object=QDSMain.TaskResultDetailsFile

3/26/2012 4:25:13 PM Information TRACE:Cluster notification subscription added for notifiable object. Object=QDSMain.ConfigurationFile

3/26/2012 4:25:13 PM Information TRACE:Initializing ThreadPool

3/26/2012 4:25:13 PM Information TRACE:Threadpool "XSTP" started

3/26/2012 4:25:13 PM Information TRACE:Cluster file successfully opened. File=Configuration.xml, Access=Read

3/26/2012 4:25:13 PM Information TRACE:Cluster file closed. File=Configuration.xml, Access=Read, Duration=00:00:00.0470000

3/26/2012 4:25:13 PM Information TRACE:Initializing QVBWrapper

3/26/2012 4:25:13 PM Error Exception=System.NullReferenceException: Object reference not set to an instance of an object.

at QDSMain.WorkorderStore.StartWorkorderStore()

at QDSMain.ObjectPool.InitObjects(ILogBucket i_LogBucket, eRunLevel i_RunLevel, Func`1 clusterIDListGetFunc)

at QDSMain.MainClass.BatchRun(eBatchReloadMode i_BatchReloadMode, String i_BatchReloadDocumentPath, String i_VariableName, String i_VariableValue)

3/26/2012 4:25:13 PM Information Setting exit code to the number of errors. ErrorCount=1

Re: mega loading

Mon, 26 Mar 2012 15:20:23 GMT

what is the command you are using?

mega loading

Mon, 26 Mar 2012 15:56:06 GMT

C:\Documents and Settings\Qlikview_ACCT>"F:\QlikView\Distribution Service\QVDistributionService.exe" -r="F:\test.qvw" -out="f:\log.txt"

mega loading

Mon, 26 Mar 2012 15:59:54 GMT

I'm not sure what it could be. Make sure that the username you are running the command as is in the QlikView Administrators group - AND that you log off and log back on if you recently added them to it.

You might have to open a support request with QlikTech. What version of 11 are you using 11154 (IR) or 11271 (SR1)?

mega loading

Mon, 26 Mar 2012 16:15:14 GMT

the account is already both admin and QV admin.

i'm using the latest, 11154. just installed, and setup to try the QVDistributionService.exe

I have opened a ticket for them, lets see if i have anything from them and i'll post it here.

Thanks for your help Ralph.

mega loading

Mon, 26 Mar 2012 17:08:38 GMT

They just released SR1 - which is 11271. For kicks download that and install, and see if you get the same error.