Reload Looped & Reduced (L&R) Application

[sunny_talwar]

Hey Experts -

Don't know if I have the title correctly specified or not, but here is my situation:

We have 150 million rows of data which is going to be L&R based on different client. But the issue I am facing is that Server is terribly slow in handling that many rows of data (Group By Statement taking a lot of time on the Server).

So then we thought what if we can break the QVW into different client versions and then load each of them separately. The problem is that a change to one application (front end or back end) will be needed to carry over to 45-50 different instances.

So finally we thought (just a thought, you guys tell me if it is possible or not) that if there is a way to reload an application without any data except the client id and L&R it. Once it is L&R, we trigger another reload and this time we are able to specify (via a variable may be) which client's L&R version it is and load only the data for that specific client id. This way we will truly have 1 application to maintain, but every client specific qvd will reload in different instances of L&R application.

Any suggestion is greatly appreciated.

Best,

Sunny

[sunny_talwar]

Any ideas or suggestions? MRKachhiaIMP‌ jagan‌ pcammaert‌ swuehl‌ hic‌ rwunderlich‌ MarcoWedel‌

[marcus_sommer]

I don't have a publisher and couldn't say if it could configured in this way. But "manually" it should be possible like this way:

- within a distribution-application with a table of clients, then loop through these table and create execution-statement with a copy-command which copied your master-file (data-reduced) into a client-file

- in a second step all these client-files will be reloaded (the client-part within the filename is your where-part)

Is it possible to create a batch file in the Qlikview Script

Re: Is that Possible to Specify Path of the "Generate Logfile"?

It's not very nice but it could work.

But maybe you could change application datastructure respectively optimize your original load. This meant do you really need the group by? I have recently removed some group by statements which often reduced the amount of records only 1:2 or 1:3 and handle the bigger amount of data within the gui quite easily and the time-saving by the reloads is enormously. Another point is the load itself and if you have group by loads with where clauses then split this. Even if you have then more loadings the overall time will be reduced significantly.

- Marcus

[sunny_talwar]

Thanks for your time Marcus.

Reference to the previous post was just to show the specs of client's server. The issue I am trying to tackle here is a different one. It is not related to the Group By statement.

Coming back to the first part of our email. I have very little familiarity with the server and was hoping if you can give a step-by-step way of doing it.

Marcus Sommer:

- within a distribution-application with a table of clients, then loop through these table and create execution-statement with a copy-command which copied your master-file (data-reduced) into a client-file

- in a second step all these client-files will be reloaded (the client-part within the filename is your where-part)

Mainly what how would we create execution-statement with a copy-command which copied my master-file into a client-file?

Thanks,

Sunny

[marcus_sommer]

Hi Sunny,

I meant to create an execute-statement like this one:

Clients:

Load Client From ClientTable;

for i = 1 to noofrows('Clients')

     let vClient = peek('Client', $(i) - 1, 'Clients');

     let vSource = 'YourFolder' & 'YourMasterFile';

     let vTarget = 'YourFolder\temp\' & '$(vClient)' & '.qvw');

     execute cmd.exe /C copy $(vSource) $(vTarget);

     sleep 1000;

next

- Marcus

[sunny_talwar]

Thanks Marcus -

I am going to bother you further to understand it better since I have never done anything like this before.

Focusing just on this -> execute cmd.exe /C copy $(vSource) $(vTarget); what exactly is going on here? Seems like we are copying a file from one location to another?

So If I am right my load statement will be within the For loop? and with each loop a new file will be created for each instance on the server. Are we still employing Loop and Reduce or this is an alternate to L&R.

[marcus_sommer]

Yes the command will copy the master-file for each client by renaming it while the copy. Within these routine could run a reload on each app also per execute triggered (it will need a certain delaying) but if the reloads are quite long respectively differently it would be better to run another task afterwards - triggered per batch, vbs or qmc.

If this won't be handled from publisher I'm not sure if the security of loop and distribute logic will work - in this case you would need to implement section access.

I think my workaround-suggestion will work but believe it's rather a worse case - maybe the others have further ideas. But if not I would nevertheless look if the load could be optimized - maybe splitting the task in a further stage which could run in a ealier time-window and you could then load only qvd's and/or the final distribute is without a real reload. Also incremental approaches could be useful.

- Marcus

[#8]

As per my understanding you have L&R task is too slow on the server. Ideally, you can do Loop & Reduce with in the script outside of the publisher but how we smartly to do that work because it involves lot of maintenance required.

You may introduce new qvw between to your qvd loader to application and handle most of the group by function. or modify your qvd loader to handle some part of group by requirements. The other smart way is create the transformation qvd's incrementally so you can avoid the group by statements on the larger data sets.

Are you doing any data roll up on time based ? what is your data source ? Is it possible roll up the data out side qlikview ?

[rwunderlich]

You can do a separate reload for each ClientId value. The technique I would use is to set the list of ClientIds on the "Script Parameters" section of the Reload tab.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com