Tuning a Qlik application: back end

from Ptarmigan Labs

 

Tuning a large, complex Qlik application can be an intimidating prospect. How do you add order to the madness? 

Start with the data model. Shrinking the footprint of the data model will make everything in the interface run better. After you finish your work on the back end, the app will already be smaller, more stable, faster to save progress, and more responsive as you continue your work on the front end.

 Below is the most logical order of operations for accomplishing this, in a vacuum*.

1. Reduce the number of columns

• Remove fields not used in the interface that are also not needed for the app to function, like key fields. The Qlik Sense Document Analyzer helps find these.
• Remove system fields, like updated timestamps
• Remove system-managed fields that are not being used, like keys that were used to join but are no longer needed or IDs for which text descriptions are preferred. Feel free to keep them in the QVD, for debugging, but don't load them in the front-end data model.
• You may also realize after removing so many columns that it makes sense to combine/eliminate tables, like if a dimension is reduced to just the key and a text value that could be joined or mapped.

2. Reduce the footprints of remaining columns

• Autonumber keys and other fields whose distinct values are needed for the app to function, but aren't meaningful to humans. Autonumbering prevents Qlik from creating symbol tables for those fields and even helps with fields that are already numeric, like IDs from a database.
• Round to reduce the number of significant digits, saving symbol table storage and reducing number of distinct field values and thus bytes required for pointers.
• Remove time from timestamps or at least fractions of seconds, if not needed for analysis
• Reduce number of distinct values by cleansing and standardizing data values

3. Reduce the number of rows in tables 

• Dimensions: remove rows from dimensions with no associated facts, often using the Exists function and loading dimension tables last in the script
• Facts: aggregate the fact data based on the columns and values remaining after the first two steps
• Remove rows that aren't needed for the app to function, ex. rows generated in a security table granting users access to values that don't exist in the fact data

* in a vacuum = without attempting to understand the business use case