Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

How to split QVDs using Qlik Application Automation to improve reloads

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
J_Lindberg
Support
Support

How to split QVDs using Qlik Application Automation to improve reloads

Last Update:

Mar 3, 2023 3:37:12 AM

Updated By:

Qlik-Lorena

Created date:

Nov 4, 2022 8:48:39 AM

Attachments

This article contains information on how to use the “Split QVD Generation” template.

Content:

 

While QVD files are optimized for loading speed, it is possible to further improve loading time during reloads by splitting a QVD file into several smaller ones. This will allow you to use Qlik Sense Cloud's ability to perform parallel reloads.

 

How it works

The template uses two Qlik Sense applications. Demo examples of what these applications may look like are attached to this article.

  • The Dimensional QVD App is for selecting the dimension used for the splitting in the automation. Dimensions configured in this application can be used for the splitting of the data into multiple QVDs. The dimensional data can either be static or reloaded upon each run depending on need.
  • The Parallel QVD Generator Template App is used to create a copy of the application for each dimensional value, resulting in QVD file splits that are based on the dimension selected from the Dimensional QVD App. Each copy will be prefixed with the same name as the dimensional value. After a reload, these temporary applications are deleted.

 

Configuring the apps

Dimensional QVD App

This app should have a table containing the dimensional values to be used for the splitting. Add the dimension on which you want to base the QVD split as a dimension in the application's Master Items. To visualize the values, you may also add the dimension to a table on a sheet. When considering what dimension to base the data split on, it is a good idea to select a dimension that has evenly distributed values, since this will improve performance. Having the data unevenly split, where most of the data is loaded into just one of the QVDs, would have minimal performance improvements compared to not splitting the QVD.

The attached demo uses sample data from Snowflake (TPCH_SF10) and "Region" is used as the dimension on which to base the data split.

 

Parallel QVD Generator Template App

This is the application that produces the data for the split QVD files. It is important that you filter on the dimension used for splitting in the WHERE clause in the load script of this application, as highlighted below in the example taken from the  attached demo application:

FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."CUSTOMER","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."ORDERS","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."LINEITEM","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."REGION", "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."NATION","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."SUPPLIER"
where Year("O_ORDERDATE")=$(vYear) and Month("O_ORDERDATE")=$(vMonthNumber) and R_NAME = '$(vQVDName)' and "L_ORDERKEY"="O_ORDERKEY" and "C_CUSTKEY"="O_CUSTKEY" and "N_NATIONKEY"="C_NATIONKEY" and "N_REGIONKEY"="R_REGIONKEY" and "S_SUPPKEY"="L_SUPPKEY";

 

Configuring the automation

Once the applications have been set up, they can be used in the automation template. The template requires the configuration of two blocks:

List Dimensional Values

  1. App Id - Use do lookup to find the Dimensional QVD App and set it as the value.
  2. Dimension Id - Use do lookup to find the dimension on which  you want the base the QVD splits. This should match the statement included in the WHERE clause of the Parallel QVD Generator Template App's load script.

Copy App

  1. App Id - Use do lookup to search for Parallel QVD Generator Template App and set it as the value.
  2. Space Id - Use do lookup to find the space where you want to copy the temporary Apps.

 

Debugging reloads

If you wish to add debugging to the reloads you can add this after the “Wait For Reloads To Complete” block.

  1. Add a list variable that takes the output from the “Wait For Reloads To Complete” block. The output will include information on the reload status per app. If a reload should fail you can also look at this output to find detailed information on the error in the log item.

    Picture1.png

  2. The variable “reloadLog” now contains a list generated by the list variable block, where only the first item is populated. Select this list item and check the status for each reload using a “Filter List”.
    Picture2.png

  3. Add a new list variable to store the App Ids of any failed reloads.

    Picture3.png

  4.  Add a “Condition” block to check if there are any failed reloads.
    Picture4.png

  5. Add an “Update Run Title” block and select a title that indicates that the run had failed reloads, for example, “Reload(s) failed”. This title can be used to filter out automation runs that failed in the automation’s history tab.

  6. Add an “Error” block to stop the automation to output information on which reloads failed by applying the implode formula to the reloadErrors variable, which contains a list of App IDs for failed reloads. Finally, apply the text formula to turn this into a string.

    Picture5.png

If you can’t identify the app that failed based on its id, use the "Get App Information" to retrieve the app name.

Note that automation logs are removed after 30 days. If you wish to keep older logs, then you must add blocks to write the failed reload logs to a cloud storage destination.

 

The information in this article is provided as-is and to be used at 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.

 

Environment

Qlik Application Automation

Labels (1)
Comments
dyy
Employee
Employee

What is missing here is the reload log files.  Because the apps are deleted after the reload is complete (fail or not), we do not have the error log for the reloads should they fail.  I had to disable the delete app block to understand why the reloads were failing.  Is there anyway to generate and store the reload logs should they fail? @J_Lindberg 

William_Wistam
Contributor III
Contributor III

Thank you for the article, maybe a short video will help with the implementation? Thanks.

J_Lindberg
Support
Support

Hi @dyy , 

Here you can find instructions on how you can add error handling for the reload block: https://youtu.be/PXjfilz_TPw?t=1521

J_Lindberg
Support
Support

@dyy Article has been updated to include information on how to log failed reloads for debugging purposes.

William_Wistam
Contributor III
Contributor III

@J_Lindberg This is powerful stuff, thanks for sharing and adding the log failed reloads for debugging. I was wondering for days why my update finished with no error message but some of my files weren't updated. I use parallel reload all the time in Qlikview using bat file however Qlik SaaS is new to me so this article is very helpful and it works! Thanks.

Version history
Last update:
‎2023-03-03 03:37 AM
Updated by: