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.
Good luck!
