Problem loading and storing QVD files at the same time

[Anonymous]

Hi all,

I have an QlikView application, let's call it "QVD Generator", that loads a lot of data and finally in the script stores my only fact table into a QVD file. I load it through the QlikView Publisher with a tight schedule that reloads every half hour.

Then i have another application, let's call it "Consumer Application", that loads data from the QVD file in the load script. I load this application through the QlikView Publisher once a day, every morning at 7 am.

To my problem: I have noticed that the load of the "QVD Generator" application fails once a day, at 7 am of course. Other times, no problem. I have generated several log files, and all of them shows that my script fails when trying to STORE the QVD file (that is being loaded from another application).

There must be more that have experienced the same kind of problem, are there any clever solution to this issue? I have searched though blog posts, discussions and topics, without finding this problem. If you know any reading to this topic, please refer to it here...

Thanks for your help.

Best regards,

Filip

[Anonymous]

Hi,

I had the same problem some time ago: my qvds "were using" by another application, while the QVD Generator was triyng to store new data in them , so it failed!

[Gabriel]

Hi,

What I would look to do in this case is have the second task which is "Consumer Application" to start upon successful completion of the "QVD Gnerator."

So go to publisher and under Triggers tab edit the task.

Start the task = On event from another task

Start on = Successful

Completion of = "QVD Gnerator."

This should resolve the issue

[stevedark]

I had a similar problem where a database load was happening frequently, but it failed each morning when some admin task was carried out on the database.  I added code to skip over the load in the QVD Generator between 7am and 8am.  It reads like this:

let vTimeNow = (now() - today());

if vForceLoad = 0 and (vTimeNow >= (1 / 24 * 7) and vTimeNow <= (1 / 24 * 8)) then

  // skip reload due to database maintenance

  let vLoaded = 0;

else

  // do the load

  let vLoaded = 1;

The vLoaded variable is used to show a message on the first sheet of the QVD Generator if the load has not taken place.  There is a toggle button on the front page to set vForceReload, so if someone really wants to run the task at 7:15am (from the desktop) they can.

Regarding your problem, I would be pretty certain that the issue is that the QVD is trying to be written at the same time it is being read by the other process.  Can you not chain these two loads in the scheduler so that they run one after the other, rather than at the same time?

Hope that helps,

Steve

[Anonymous]

Thank you for a helpful response.

I have a lot of data. My "QVD Generator" has a running time of about 15 minutes... My "Consumer Application", that actually is a several, has a loading time of about 30 minutes. In that sense it wouldn't help me to organize the reload jobs to have a successive schedule.

Is it possible to sync a QVD file to another, in some way?

Are there any other solutions to this? Feels like this should be a quite common problem...

Best,

Filip

[stevedark]

What you could do is write your QVDs to a staging filename, and then issue an execute statement in the load script to rename them at the last minute of the generate routine.  This way the amount of time the file is locked due to writing will be minimized.

Having a 30 minute load time for a front end app sounds like you have a problem in the load script.  If all QVD loads are optimised you can bring in a serious amount of data in a much shorter time.

To have an optimised load you need to ensure that all data manipulation is done in the QVD generate and your load into the front end app follows a number of simple rules.

This article gives more detail on this:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/‌

Hope that helps.

Steve

[Peter_Cammaert]

Yes there are many solutions to this problem.

One could be to let your QVD Generator perform a double save of the required Facts QVD (simply STORE twice). The second save goes to a separate folder, to be written only during the 7 o'clock run (adjust your QVD Generator script accordingly), and to be used by the "Consumer Application" only (change the "Consumer Application" schedule to run only after 7:15).

This way you create some sort of data isolation depending on who will consume the data and how often and at what time. The original Facts QVD will still be available in updated form every other half hour for anyone who can read the data within 10 minutes or so (whatever is remaining of the 30 minute time slot). The copy of the QVD is available for 24hrs to the "Consumer Application".

Best,

Peter

[Anonymous]

Actually I went further with my last thought:
- Is it possible to sync a QVD file to another, in some way?

I created a .bat file witch copies the file to same location but another file name. I run this external task upon a successful reload of my "QVD Generator". Then my "Consumer application" will load data from the new file.

I will try this first..

[Peter_Cammaert]

Nice solution. However, your Customer Application may now try to run every half hour by itself, which creates the same problem in a different spot.

Best,

Peter

[Anonymous]

Actually not

I have two different load chains...

Chain 1

Job 1: Load of "QVD Generator" - every half hour
Job 2: Load of "BAT job" (external task) - every half hour if step 1 is successful

Chain 2
Job 3: Load of "Consumer application" - other schedule, once a day

That will make me copy the QVD file every half hour, but not start the load of "Consumer application".