DynamicUpdateCommand: Top 10 Tips, Tricks, and Gotchas
We are a heavy user of QlikView's dynamic update functionality (i.e. DynamicUpdateCommand), and rely on it to create user defined dimension values (I won't go into the process of how users define a new dimension value, only how it is saved).
Dynamic Update is a very powerful QlikView feature since it allows document data updates in real time, which are made available to all QlikView document users. What follows is a list of tips, tricks, and gotchas:
Since QV9 SR4 it is possible to use QlikView desktop (as opposed to the QlikOCX) to perform dynamic updates on the server. This is preferred, since it is easily possible to disable Macro security warnings, by opening User Preferences (Ctrl-Alt-U) and under the Security tab, setting the "Module" option under "Always Override Security"
Use a message queuing service such as MSMQ to ensure that dynamic updates are performed one at a time. If you are using MSMQ Triggers, ensure the MSMQ Trigger service is running under the same Windows NT account as the QlikView services are running under.
When INSERTing batches of data via DynamicUpdateCommand, experiment to determien the optimal batch size. In our tests (for the batches we're INSERTing) we have found 5,000 rows is the optimal size.
Between INSERTing batches, ensure you Sleep for an adequate amount of time. This sleep time should be proportional to the size of the document. I.e. the bigger the document footprint, the longer the sleep interval time. That said, I've found that 5 seconds appears to be sufficient for most document sizes. If the sleep interval time is too short, this can cause problems with very large documents, since client connections can be lost and the time it takes to re-open the doc is longer than the interval, which in turn causes the connection to be lost. This can repeat itself until all DynamicUpdateCommand batches have completed.
DO NOT use WaitForIdle before or between DynamicUpdateCommand. This command has been too undpredictable for us. Better to use an explicit sleep interval, measured in seconds (see the last point).
Avoid DELETing large amounts of data via a single DynamicUpdateCommand. It's more efficient to use an UPDATE statement (in conjunction with data islands) to achieve the same goal.
I have encountered situations where data was INSERTed via DynamicUpdateCommand, but was not available through the OLE Automation commands (e.g. GetSelectedValues). If you need to know selected values, it's better to use the "P" function in set expressions to achive this goal.
In the controlling desktop app (responsible for DynamicUpdateCommand), ensure you first create and move to a blank sheet in the document before calling DynamicUpdateCommand. This will ensure that Chart re-calcs do not occur, and in general, that QlikView is not forced to do any work that's not required. If you forget to do this, your charts will re-calc between batches, and in effect the process will be competing with itself for resources.
Be aware that all field "Selection" triggers and the "AnySelection" trigger will fire each time DynamicUpdateCommand is called. Depending on where the clients are within the document, this can lead to unexpected behaviour. This is one of the biggest "gotchas" I've discovered.
In the controlling desktop app (responsible for performing the DynamicUpdateCommand), try to avoid firing "Select" triggers resulting in Macro execution (e.g VBScript macros). You can accomplish this by placing the macro name within a conditional expression, that does not fire when you're on the blank sheet you have [presumably] created. NB: It is not possible to explicitly remove triggers from a server loaded document.
we are facing problem in the use of Dynamic Update in a QV application that require 15 to 20 cuncurrent users on the same document: we are working to optimize the call of Dynamic Update command because at the moment with 3 users connected the document stop the operativity of QV server.
I seems that the Dynamic Update doesn't queue correctly the calls from different users on the same document, so to solve this problem we will probably need to appliy your suggestion to use a message queuing service such as MSMQ.
Shall you send us a few more details how to manage the calls of QV Dynamic Update on MSMQ trigger sevice?
thanks for your comprehensive listing of tips and tricks. I would also like to know, how "MSMQ trigger sevice" can be used. I cannot find good examples or explainations in the internet on that topic.
Be aware that all field "Selection" triggers and the "AnySelection" trigger will fire each time DynamicUpdateCommand is called. Depending on where the clients are within the document, this can lead to unexpected behaviour. This is one of the biggest "gotchas" I've discovered.
Yes, this is rather annoying side effect. I found that it can be compensated by adding Selection -> Back action immediately after dynamic update action.
