Objective
Sending parameters from a scheduled app to its script.
Summary
The post shares the approach used to send parameters from the app name to its script. The referred approach only makes sense if your source code is externalised in an include. Otherwise, the script will be duplicated, and definitely this would not be a sustainable approach.
Use Case
Massive data needed to be reloaded as fast as possible in order to allow the business to proceed with month-end routines. Time was the most critical aspect when this approach was developed.
Each day had between 70 and 80 million rows and the last three months needed to be reloaded every day to capture changes. The incremental approach was used to scan those three months to capture only the rows changed.
Solution
Concurrent reloads would reduce the total reload time.
Challenge 1 – How to reload several apps without duplicating the app script?
It is known that up to now (June 2020), Qlik Sense does not provide any way to send parameters from a task to the script, as QlikView does.
Here the main question is raised: How can I send a parameter from a scheduled app to its script? The answer is simple, the app name is the key. The app name is the source for your parameters and the post shares the approach developed.
Challenge 2 – Concurrent reloads can drain the resources from the server.
When you put in place concurrent reloads the queues will be taken by those apps and will be released only when the reload is completed. Each case must be evaluated to balance apps and server resources. Although, there is no “recipe”, some aspects should be considered such as if the environment has near real-time apps they should be moved to a dedicated scheduler. If that is not taken into consideration, the “near real-time concept” will be lost because the reloads won’t perform as expected.
Another point to be considered is that increasing the number of queues won’t solve the problem if the server resources remain the same.
The Approach
The most important thing to be defined and kept in mind is how the apps will be named to provide an easy way to recover the parameter name and parameter value from the name. I strongly recommend the use of delimiters. I decided to use “_” (underscore) once it keeps the app name readable and the parameters can be easily identified.
Example 1:
APP: EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION_MONTH_00_FROM_DAY_01_TO_DAY_05
App name: EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION
Parameter Name 1: MONTH_
Parameter Value 1: 00
Parameter Name 2: FROM_DAY_
Parameter Value 2: 01
Parameter Name 3: TO_DAY_
Parameter Value 3: 05
To get the app name in the script, use the DocumentTitle() function and then apply the necessary logic to retrieve the parameter names and parameter values from the app name. Keep it simple, make the logic reusable, define parameter names, and parameter values explicitly, no one must have to guess what the app name contains as parameters.
From the app name, it can be guessed that the script reloads month 00 (current month) in batches of five days per app, which means six extractors running in parallel. So, to extract three months the EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION app must be duplicated and only the app name needs to be changed. This is how the whole set of apps is:
Parameter Name 1 | Parameter Value 1 | Parameter Name 2 | Parameter Value 2 | Parameter Name 3 | Parameter Value 3 |
MONTH_ | 00 | FROM_DAY_ | 01 | TO_DAY_ | 05 |
MONTH_ | 00 | FROM_DAY_ | 06 | TO_DAY_ | 10 |
MONTH_ | 00 | FROM_DAY_ | 11 | TO_DAY_ | 15 |
MONTH_ | 00 | FROM_DAY_ | 16 | TO_DAY_ | 20 |
MONTH_ | 00 | FROM_DAY_ | 21 | TO_DAY_ | 25 |
MONTH_ | 00 | FROM_DAY_ | 26 | TO_DAY_ | 31 |
MONTH_ | 01 | FROM_DAY_ | 01 | TO_DAY_ | 05 |
MONTH_ | 01 | FROM_DAY_ | 06 | TO_DAY_ | 10 |
MONTH_ | 01 | FROM_DAY_ | 11 | TO_DAY_ | 15 |
MONTH_ | 01 | FROM_DAY_ | 16 | TO_DAY_ | 20 |
MONTH_ | 01 | FROM_DAY_ | 21 | TO_DAY_ | 25 |
MONTH_ | 01 | FROM_DAY_ | 26 | TO_DAY_ | 31 |
MONTH_ | 02 | FROM_DAY_ | 01 | TO_DAY_ | 05 |
MONTH_ | 02 | FROM_DAY_ | 06 | TO_DAY_ | 10 |
MONTH_ | 02 | FROM_DAY_ | 11 | TO_DAY_ | 15 |
MONTH_ | 02 | FROM_DAY_ | 16 | TO_DAY_ | 20 |
MONTH_ | 02 | FROM_DAY_ | 21 | TO_DAY_ | 25 |
MONTH_ | 02 | FROM_DAY_ | 26 | TO_DAY_ | 31 |
Attached is the full task chain to the end application and also the script for the extractor (open the docx file, copy, and paste the script in a text file, it is easier to read).
The approach is not failure-proof once it is based on the app name and any mistake done in the app name will cause the app to fail. However, checks can be put in place to prevent garbage in the parameters.
Note 1: Changes done in the script don’t need to be republished because the script is externalised in an include and you are not dealing with the QVF directly.
Note 2: The task chain is very sensitive, make sure that all dependencies were correctly set.
Note 3: Binary load is used in all end applications.
Conclusion
The target was successfully achieved, apps are reloaded faster, and business is able to start month-end earlier on the first day of the month compared to reports sent via e-mail.
An effective approach was created to send parameters from a scheduled app to its script, no duplicated code, easy to expand the concept to other months. What needs to be done is only duplicate the app for the month 04, rename accordingly, and schedule them. The script will take care of everything else.
As expected, the server resources are highly demanded when the task chain starts but it was able to handle all the tasks without errors. The virtual server has 384GB RAM and 16 vCPUs.
A small note goes to the scheduler server. By default, the timeout for queued tasks is 30 minutes. It means that when a task is triggered it waits for up to 30 minutes before it gets aborted automatically by the system. If your environment is configured to send e-mail alerts if a task fails, you might get some e-mails that you don’t want to receive. My recommendation is, before changing the parameter, make sure that everything else was done the guarantee the best performance possible. The Engine timeout can be changed here: QMC -> Schedulers -> select the scheduler server -> select Advanced (on the right) -> change the "Engine timeout (minutes)" parameter and save.
I will be more than pleased to go into details if I was not very clear in a specific aspect.
Comments and suggestions will be more than welcome.
I do hope this post can give you a new perspective to deal with massive data loads, time constraints, and improve the flexibility and adaptability of your Qlik Sense environment.
Huberto Pereira Haidemann
