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
App Id - Use do lookup to find the Dimensional QVD App and set it as the value.
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.
App Id - Use do lookup to search for Parallel QVD Generator Template App and set it as the value.
Space Id - Use do lookup to find the space where you want to copy the temporary Apps.
If you wish to add debugging to the Do Reload block you can add a condition that checks if the reload succeeded or not:
Should the reload fail you can generate an error and include the log from the failed reload:
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.