Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Woohoo! Qlik Community has won “Best in Class Community” in the 2024
Khoros Kudos awards!
This article contains information on how to use the “Split QVD Generation” template.
Content:
- How it works
- Configuring the apps
- Dimensional QVD App
- Parallel QVD Generator Template App
- Configuring the automation
- List Dimensional Values
- Copy App
- Debugging reloads
- Environment
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
- 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 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
- 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.
Debugging reloads
If you wish to add debugging to the reloads you can add this after the “Wait For Reloads To Complete” block.
- 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.
- 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”.
- Add a new list variable to store the App Ids of any failed reloads.
- Add a “Condition” block to check if there are any failed reloads.
- 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.
- 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.
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.
