Reload process trigger on external event (e.g. completion of DW load)

[richbyard]

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.

1. At the end of the ETL process the source database can be made to write a success value to a specific table in the DB for us to use as an indicator of a successful reload. For example, this might be today's date.
2. Create a Qlik application (QlikView or Sense) which will be scheduled at the start of the reload process and all other applications will be dependent on this, and any other dependencies they have. This initial application is a simple script that loops until a condition is met by the data being loaded. In this example, when the date loaded from the source DB is equal to today.
3. This application will be scheduled around the time of the earliest conceived completion of the source DB process or slightly before. This ensures that our reload process is ready and waiting for a completion record, in effect polling the source DB for a completion record.
   
4. As this application completes all subsequent reloads will start. This ensures that your reload process kicks off as soon as the source DB has completed processing and never before.

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

Trigger:

LOAD num(@1) as DWDate

FROM [lib://Trigger/Trigger.txt]

(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

  Exit Do

// where condition is not met, drop that table ready to restart the load process.

else

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.

end if

// where condition is not met then restart loop.

loop

Try it yourself!

The attached files can be used to test this process.

1. Download them and have them in the same folder.
2. Kick off the reload of the QlikView document and watch as it loops through as the condition is not met.
3. Open the Trigger.txt file and change the date to today's date (format D/M/YYYY unless you change your date settings in the script)
4. Save the .txt file and watch as the next time the application loops it will complete the reload.
5. Simple!

Date formats

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.

[ergustafsson]

Great idea!

Our take on this was to add a bit more complexity. This lets us finish the script if it takes too long, and we read the timestamp instead of date. The output from the database was generated in PowerShell to ensure correct timestamp format. Despite this we encountered some charset issues, ensure that you are using the right one (unicode / UTF-8 etc)

LET vDWDate = null();

trace sets the Data Warehouse date to right away, as it is null '$(vDWDate)';

LET vNow = num(now());

trace sets the variable for what time it is now, which is '$(vNow)';

//0.2 = 4.8 hours = 4 hours and 48 minutes

//0.167 = 4 hours

//0.0052083333333333 = 7.5 minutes

LET vFinishAnyway = num( now() + 0.167 );

trace allows us to finish earlier if the script is not met, which is '$(vFinishAnyway)';

//where we find the stuff

LET ConfigLib = '../Config/';

// Start loop script that will continue until condition is met.

Do until vDWDate >= vNow

    Trigger:

    LOAD

        num(left(@1,19)) as DWDate

    FROM [$(ConfigLib)\EDWLastLoaded.txt]

    (txt, unicode, no labels, delimiter is '\t', msq);

    LET vDWDate = peek('DWDate');

    trace vNow is '$(vNow)';

    trace vDWDate is '$(vDWDate)';

    // where condition is met, exit and complete.

    if vDWDate >= vNow then

        trace first loop;

        Exit Do

    // exits earlier if the loop has been running for too long

    ElseIf num(now()) >= vFinishAnyway then

        trace second loop;

        Exit do

    // where condition is not met, drop that table ready to restart the load process.

    else

        trace third loop;

    DROP TABLE Trigger;

    // The sleep means that we can control the frequency of calls to the source database (or file).

    Sleep 300000;  // 10000 milliseconds = 10 seconds.

    end if

// where condition is not met then restart loop.

loop

exit script;

If you have several more steps within the environment you can just output a similar take from QlikView/Qlik Sense:

LET vNow = now();

trace sets the variable for what time it is now, which is '$(vNow)';

timetable:

NoConcatenate load

     now() as time

AutoGenerate (1);

//where we find the stuff

LET ConfigLib = '../Config';

store timetable into [$(ConfigLib)QVLastLoaded.txt] (txt);

[Anonymous]

Thank you

[rohitk1609]

Hi ryy ,

I have implemented your solution and it is working fine, but my use case is , as number of times extraction process happens on server my reload task will get triggered. I have created a task on ETL layer as whenever extraction will happen it will put a time stamp in a csv file  which is 15 minutes more value than current time. So I have put a time stamp value in format : 20/06/2017 09:45:07 pm

Updated your code :

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

// SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

// SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vDWDate = null();

LET vToday = Timestamp(Now(),'DD/MM/YYYY hh:mm:ss tt');// + 0.25/24 ;

// Start loop script that will continue until condition is met.

Do until vDWDate < vToday

//last time stamp value is in vDWDate is less than vToday

set status = 'Loop';

Trigger:

LOAD

  @1 as  DWDate

FROM [lib://Sample Data (dr_rkumar)/Trigger.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET vDWDate = timestamp#(peek('DWDate'),'DD/MM/YYYY hh:mm:ss tt');//+ 0.25/24;

//where condition is met, exit and complete.

if vDWDate > vToday then

set status = 'Exit';

  Exit Do

// where condition is not met, drop that table ready to restart the load process.

else

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.

end if

//where condition is not met then restart loop.

loop

//////////////////////////////////////////////////

Problem is here, my condition are working fine and you may check it with variable Status but loop is getting exit even though conditions are working fine and not getting in loop with Do until vDWDate < vToday

Please tell me what exactly I am missing.

Consider this on priority basis.

Thanks in advance

Rohit

[richbyard]

Hi Rohit,

Without your example to debug my first thought would be to ensure your timestamp formats are being recognised correctly. transform them into numbers with decimals for the time of course, ensure they are both correctly transformed and and then do the comparison. I am on leave for the next 2 weeks (the first week I will not have access to the internet so may find it difficult to debug you example for a week or so) but if you want to attach your example then please feel free to do so and I'll look at it then.

Thanks

Richard

[rohitk1609]

Here no advanced editor option is coming, Can you share your email id so I can mail you my application please?

[arulsettu]

hi rohitk1609‌ did you solved the issue. my case also similar for a day number of extraction process will happen in DB. i need to reload number of times.

[arulsettu]

ryy can you tell me how to approach for multiple extraction per day and multiple reload

[richbyard]

Hi Arul,

You would need to define some logic for this based on timings (rather than date time). I will suggest one quick option for you which might work without you getting into APIs which is the more advanced approach.

For example.

1. Have your data source process (i.e. DW) reset the trigger file value as it commences each time. You could set this to the previous days date or to a NULL value.
2. When your Qlik application commences based on a scheduled task it will follow the process as above until the trigger file date is reset to today's date at the end of the data process. This can be repeated as many times as you like but it does rely on the timing of the DW loads being defined and consistent and the Qlik reload tasks scheduled accordingly.

There are many other variants that could be created to meet the need. However, the cleanest option in this case would be to have an API call (Qlik Sense) or EDX call (QlikView) to trigger the reload from the database process itself but this takes a bit more knowledge to set up.

[arulsettu]

Thanks for the reply ryy

i have the DW reload trigger date time stored in  a table in DW server itself like below

Reload_completion_time

22-08-2017 08:30:21

22-08-2017 10:00:55

22-08-2017 12:00:42

22-08-2017 04:20:10

i should set like this

LET vDWDate = null();

LET vToday = num(today()-1);

and how can i schedule this application run multiple time based on the Reload_completion_time

Thanks

[richbyard]

Let me clarify the approach I was thinking of when I responded as I'm not sure you fully understood it:

• You would only ever have a single date in the trigger table/file. This would not be date time. This date would be written over at the start of the DW process and re-entered at the end.
• The reload trigger script as written in the original post would work as is, it would wait for today's date to be written in the trigger table/file. NOTE: this approach will always assume that the timing of the Qlik scheduled reload is after the timing of the scheduled refresh of your DW. Therefore you could schedule your DW refresh and subsequent Qlik refresh multiple times a day.

If you need to do this differently there are plenty of ways but that was not the intention of this post. This is an example to be extended as per your needs.

With regards to scheduling your reload multiple times you will need to create a trigger for each time of the day you wish to refresh that is synchronised with your DW refreshes. These triggers can be set to repeat daily (and you can have as many as you like against a single application as logically allows, i.e they cannot overlap).