Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!

How to split QVDs using an automation to improve reloads

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

How to split QVDs using an automation to improve reloads

Attachments

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

 

Environment

 

Background

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 have been 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 you want to base the QVD split on 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 you want the QVD splits based on. 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 Do Reload block you can add a condition that checks if the reload succeeded or not:

J_Lindberg_0-1668612985735.png

Should the reload fail you can generate an error and include the log from the failed reload:

J_Lindberg_1-1668613064604.png

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.

Labels (2)
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 II
Contributor II

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 II
Contributor II

@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:
‎2022-11-17 04:11 AM
Updated by: