Qlik Sense documentation and resources.
The simple attached applications (and sample code) will allow you to setup your Qlik reload processing (in QlikView Server, Publisher or Qlik Sense) to kick off directly on completion of a process on a source system (e.g. an ETL process on a DW).
A simple process.
Tip 1 - Add in a pause to the loop so that your application doesn't spam the DB with requests.
A sleep command is added which pauses the reload following an unsuccessful pass. This could be set to every 10 or 60 secs to reduce a constant barrage of requests being sent to the DB. When the condition is met the script will exit the loop before the sleep is activated.
Tip 2 - Multiple triggers to set off different reload streams.
If you have a more complex environment where reload windows are constrained you could setup multiple triggers based on different event outputs from the DW. For example, where the DW completes a sales transactions fact but has many other dependent tables still to process you could have an event written to allow one stream of your reload to commence. The other streams could rely on different event triggers when all table reloads are complete.
Tip 3 - Keep it simple!
I have seen many different ways of setting up processes such as this, most of which IMHO are over-complicating something quite simple. One of the pleasures of working with Qlik products is the relative lack of complexity and the ease of understanding each implementation you come across. This is something we should promote rather than the tendency in IT to introduce/play with another bit of code or tool which makes debugging issues more technical. It also results in hidden steps in the process when someone new is introduced to the implementation, and documentation rarely gets the message across. When I say 'hidden' I would suggest that an initial trigger application in the reload schedule is very clear to someone new to the specific Qlik implementation, actions in source DBs or other programs are not immediately clear and require some digging etc to find and understand.
The example code based on a .txt file in Qlik Sense - change the LIB reference to a defined folder location for QlikView.
LET vDWDate = null();
LET vToday = num(today());
// Start loop script that will continue until condition is met.
Do until vDWDate=vToday
LOAD num(@1) as DWDate
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
LET vDWDate = peek('DWDate');
// where condition is met, exit and complete.
if vDWDate=vToday then
// where condition is not met, drop that table ready to restart the load process.
DROP TABLE Trigger;
// The sleep means that we can control the frequency of calls to the source database (or file).
Sleep 10000; // 10000 milliseconds = 10 seconds.
// where condition is not met then restart loop.
Try it yourself!
The attached files can be used to test this process.
It is important to match the formats of the dates in both files. Check your global variables in the main tab to understand the settings you are assigning to the vToday variable as this will need to match the data being read from the source to complete the process. The screenshot below shows where to check in Qlik Sense, the same applies in QlikView.
thanks for sharing this, but I have a question! Was there a (real world) reason you didn't do this using EDX triggers?
Hi Marcus, just a personal preference and I'll often look for a simple solution in Qlik before bringing other tools into the mix. I have also had to debug too many implementations where people have over-complicated things with too many handovers etc so I aim for simple solutions that are easy to maintain and easy to understand by people new to the implementation (on the Qlik side at least)... Cheers.
Thanks for sharing, this is a very useful piece of work, it could help many of us on a daily basis
We used this configuration today and it's pretty amazing. I know EDX triggers are the recommended way to trigger refreshes in QMC, but the overhead for EDX is way more than this setup requires.
For EDX setup you need to setup at least an EXE and have a setup EXE to ocnfigure for the task that you want to fire. Then you also need to setup a scheduled task or BAT file to run the EXE file based on a trigger from the source database anyways. In most cases, the configuration for this is many more steps than what is outlined above.
Is there any other reason besides the slight hit to performance and the 'constant' connection to the source database that is of concern for this configuration? What other benefits do EDX have over the above?
I'll let others comment on the prowess of the EDX approach as I'm more of a fan of simplicity. From my perspective the only benefit would be a direct handover from one to the other at the exact time the DB processing competes. However if we're looping through every 10 seconds then that is a delay I would probably be comfortable with.
With regards to the hit to the database of this loop statement. I cannot see any evidence of resource consumption of this simple select statement querying a table with a single column and a single row on my database instance but others will have their views on the pros and cons of this approach.
If you have any other feedback please add it to this conversation as I'm not advocating one or the other. My aim with this is to enable solutions without too much technical expertise. We can't be masters of everything IT!
A useful script that is an alternative to EDX.
One option rather than looping continuously with a delay could be tobset the app to reload at a frequent interval on the server, say every 2 or 5 mins.
Absolutely correct. There are not really good or bad approaches, just options... The sleep command in this script allows you to customize the time delay to whatever setting you like so 5, 10 mins are perfectly acceptable and logical depending on the tightness of your reload window...
The qvf is to be used with Qlik Sense to see how this process works in practice, the qvw when using QlikView.
Thanks a lot for your sharing,
very interesting and powerful