How to build long-running task chains in Qlik Application Automation

Emile_Koslowski · Jan 5, 2022 5:46:44 AM

This article explains how a reload task chain can be built that will run longer than the maximum automation run limit of 4 hours.

This is an advanced article, if you’re new to automations or task chaining, I suggest you first read this basic article.

Content:

General idea
Preparation
Automation 1: Task Chain Initiator
Automation 2: Task Chain Manager
Results
Bonus: reducing total executions of automation

General idea

The maximum run time of an automation is 4 hours. This can be a problem when you want to build a task chain of multiple reloads of big apps that could take longer than that. To solve this, we'll build a system of two automations that run together:

Automation 1: Task Chain Initiator, this automation will run once and start the task chain. It will trigger the first app reloads.

Automation 2: Task Chain Manager, this automation will be scheduled to run every 5 minutes. It will verify if any reloads have finished, whether they were successful and if any new reloads should be triggered.

The Task Chain Manager automation will need to know which reloads were triggered by the Task Chain Initiator and will need a way to store which reloads it already processed in previous runs. In other words: these automations need some sort of state to track the progress of the reload task chain. We’ll use Google Sheets for this, but of course, feel free to use any other cloud storage tool.

Preparation

The state will be stored in one file and divided over 4 sheets. Go ahead and create a new file in Google Sheets.

Sheet 1: Apps for initial reload, this sheet contains the ids and names of the apps that should be reloaded in the first automation. They will be executed by the Reload Initializer automation and run in parallel. Add a new sheet to the file you created, give it the name “Apps for initial reload” and add each app's id and name as in the below example. The order of the apps in the sheet is ignored.

Sheet 2: Queued reloads, this sheet will keep track of which apps were already triggered for reloading.

Add the sheet to the file and name it “Queued reloads” but don’t fill in any fields, the content of the sheet is autogenerated by the automations.

Sheet 3: Finished reloads, this sheet keeps track of all reloads that have finished, it contains their id, name, the status of the reload, and the reload log. Add the sheet to the file and name it “Finished reloads” but don’t fill in any fields, the content of the sheet is autogenerated by the Task Chain Manager automation.

Sheet 4: Chained reloads, this sheet dictates which apps should be reloaded after certain apps finished reloading. Add a new sheet to the file and name it “Chained reloads” specify the order in which apps should be executed.

The order of the rows in the sheet is ignored, the sheet is processed as follows:

If a reload has finished for an app from column “First_id”, then trigger a new reload for the app that’s specified in the column “Next_id” on the same row.

Add this sheet to the file and specify the apps as in the example below:

Automation 1: Task Chain Initiator

This automation will only run once at the beginning of the full task chain process, it will execute the following steps:

First, it will clear the Queued reloads & Finished reloads sheets, this will remove all data from previous task chain executions.
Next, it will add headers to these sheets
1. Examples of the generated headers in the Queued reloads sheet:
2. Examples of the generated headers in the Finished reloads sheet:
3. Your automation should now look like this:
After initializing these sheets, the automation will get all apps from the Apps for initial reloads sheet and trigger a reload for each of them in parallel (by setting the Run Mode in the Do Reload block to ‘Start reload and continue’).
1. Each app’s id, name, and reload id are written to the Queued reloads sheet:
2. This part of the automation looks like this:

Note: an export of this automation is attached to this article as a JSON file named "Task chain initiator.json".

Automation 2: Task Chain Manager

This automation will run on a schedule and will be the engine of our task chain process. Feel free to adjust the schedule according to your needs. On each run of the automation, the following steps will be executed:

Get all records from the Queued reloads sheet, the Finished reloads sheet and the Chained reloads sheet.
1. Tip: we won't use the loops on these blocks, right-click these blocks and choose "hide loop" to make your automation smaller.
Compare queued & finished reload records to find all apps that are in the Queued reloads sheet but not in the Finished reloads sheet. This will return a list of all apps from the queue that haven’t yet finished. Make sure to configure the settings of the Compare Lists block as follows. (List 1 should contain that queued reloads and list 2 should contain the finished reloads)
For each record returned by the Compare Lists block (= unfinished reloads), execute a Get Reload Record block to get the reload status and use a Case block to verify if the reload has finished:
1. The reload finished successfully (status == SUCCEEDED):
  1. Write the app & reload information to the Finished reloads sheet.
  2. Use a filter list block & filter the records from the Chained reloads sheet. Filter it to get all records where First_id == the id of the app of the reload that succeeded.
  3. For each record returned by the Filter List block, trigger a new reload with the Do Reload block and write the reload id to the Queued reloads sheet.
  4. This part of the automation should look like this:
2. The reload failed (status == FAILED):
  1. Write the app & reload information to the Finished reloads sheet.
  2. Optionally, send an email or a Slack message with the reload log.
  3. Configure this part like this:

Note: an export of this automation is attached to this article as a JSON file named "Task chain manager.json".

Results

The reloads in the Finished reloads sheet will look like this:

Bonus: reducing total executions of automation

When scheduled every 5 minutes, the "Task Chain Manager" automation will generate 288 daily runs. This bonus paragraph explains how the number of total runs can be reduced. Instead of having an automation that has many executions that run short, we'll build an automation that has only a few executions that run for a longer time.

Take all blocks from the Task Chain Manager automation and place them inside a Loop block. Use the explode formula to create a loop in the Loop block's List input field. Specify a comma-separated list of 30 items. For example: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30.
Then add a Condition block at the end of the automation right after the blocks from the original Task Chain Manager automation. Configure the block to validate the number of items returned by the Compare Lists block (use the Count formula for this). If this equals 0, the Task Chain has finished. Add an Exit Loop block to stop the automation.
Now add a sleep block after the condition to sleep for 60 seconds.
Finally, change the automation's schedule to run every minute. This will reschedule the next run of the automation 1 minute after the previous automation has finished. The automation will now run for 30 minutes (30 loop executions x 1 minute from the sleep block) + the total execution time of the other blocks in the loop.

Notes

You can easily modify this automation to send messages to Microsoft Teams or emails.
If multiple apps should be reloaded after a certain app’s reload is finished, just add multiple rows to the Chained reloads sheet with each time a different Next_id
In the Task Chain Manager automation, if the output of the Compare Lists block is empty, the task chain has finished. Feel free to add a condition block to the end of this automation to verify this and alert your team.

The information in this article is provided as-is and to be used at your own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Related Content

How to task chaining with Qlik Application Automation
How to import and export automations

FranciscoQ · ‎2022-01-21

Hi Emile, Thanks for this article ! Very Helpfull.

I have some questions and comments:

1) The case block will have a list of the apps that are in the queued sheet and not in the finished sheet in order to see if they are pending to reload.. and then will see if the status of those apps is succeeded or failed. What what happens if the app is still reloading ? It won't have a failed or succeded status... I don't understand very well this part. It will go again throuh the loop until the app has any of those 2 status?

2) The original automation of the task chain manager will be running 288 times a day which is 8,928 times a month, which is way more than the 5,000 limit with the original SaaS subscription. So definitely not scalable..

With the Bonus, there is a loop that will be running for 30 times (I assume you put 30 as a random number), The purpose is to reload 30 apps, and then end the loop and start again reloading more apps, so it never reachs the 1 hour limit ? Assuming that 30 apps take less than one hour..

Anyway, if all the apps are reloaded, when the automation see that there are none records that exist in queued sheet but doesn't exist in finished sheet, That means that the task chain has finished, but I don't want that after one minute the automation starts again, it will be running the loop (30 times), without doing anything because all the apps are reloaded, and then again, and again... So I will take less than 288 runs a day, but it will take quite a lot... and I don't want to run out of the 5,000 jobs (automation runs) that I have, especially beacuse I want to use more automations for other purposes.

Maybe there is a way that when all the apps of the task chain are reloaded, the automation doesn't run again.. I will also explore but in order to make this scalable, I think is important to figure out how to do it without have the automation running all the time, every day.

3) What's the purpose of the sleep block ? I didn't understand it.

4) Would be great to trigger the Task Chain Manager Automation, from the Task Chain Initiator Automation, maybe when the apps from the task chain initiator are completely reloaded or when they start reloading, call the Task Chain Manager Automation.

5 ) Do you know if Qlik is planning to include something like the tasks chain in the QMC of Qlik Sense Enterprise, in Qlik Sense SaaS? It would be way easier to reload chained apps, which is very common when you are creating BI proyects, with a lot of big apps, with tons of information, and different stages of development (apps to load data from DB and storing tables as qvds, other apps that read those qvds and transform data, other apps to join/concatenate/applymaps, etc to create the data model, and at the end the final app with visualizations that only read qvds already transformed in order to have a better performance).

Emile_Koslowski · ‎2022-01-24

Hi Francisco,

Thanks again for the thorough feedback, this is much appreciated!

I did my best to answer everything below:

Indeed, if the app is reloading, the Case block will ignore the app. In the next execution of the loop, the case block will check that app's reload status again. This will repeat until the status is either FAILED or SUCCEEDED.
(and 3) No, the purpose of the loop block is not to reload an app 30 times, it is to check 30 times if any app has completed reloading and to take action accordingly.

That automation does the following in a loop:
1. Check which apps have been triggered to reload but not finished
2. Check if any apps from step 1 are finished
3. If any apps are found in step 2: take action (update the sheet with finished reloads and start a new one if required)
4. Sleep for a minute. This gives ongoing reloads some time to complete. Without the Sleep block, the automation would execute the loop 30 times in a few seconds and probably won’t catch any finished reloads.
It is not possible to disable the schedule from within the automation

4. Indeed, that’s a possibility but then the Task Chain Manager Automation could only run for one hour max.

5. I have no news on the roadmap for the QMC, is this already requested through Ideation?

Kind regards,
Emile

Emile_Koslowski · ‎2022-05-03

FYI, we've increased the max run time of automations from 1 hour to 4 hours.

Ken_T · ‎2023-03-23

is there any chance Qlik will increase this so that reloads can run longer than 4 hours?

This is a lot of work to do something that worked flawlessly in enterprise without any extra work....

Sonja_Bauernfeind · ‎2023-03-23

Hello @Ken_T

I'd recommend posting this in our ideas forum. Previously, it was set to one hour, and an idea was logged to provide feedback to our product team (which is why it's now at four hours). Let us know the business requirements for longer-running automations through an idea, and our product team will review it.

All the best,
Sonja

msan · ‎2023-11-12

Very interesting.
There is an alternative approach, might be easier for those knowing Qlik Scripting.
It doesn't use any external resources or automations.
Just call the QliK API from Qlik Script with Rest.
Details here
https://qlikblog.eu/trigger-app-reload-from-qlik-script-saas-task-chaining-solution-continuous-reloa...

How to build long-running task chains in Qlik Application Automation