Performance Tuning

After implementing a data model with vast amount of data, the demand to optimize the data model rises in order to fulfill following requirements:

  • Increase response time per selection
  • Faster application reload / opening and script runtime
  • Hard drive size (footprint)

The first steps for a faster and optimized data model, following actions can be performed on the data model:

  • - Reduce fields: Delete unused fields, not loading them into the data model
  • - Reduce records: Reduce the amount of rows per table, filter only relating records
  • - Increase cardinality: Reduce number of unique values
  • - Create surrogate keys by using the autonumber() function

Reduce fields


By reducing unused fields, the application gets smaller, script loading time increase and the response time of the application increases to due lower RAM requirement of the data model. Therefore it is utmost important to delete / ignore fields that are not required. There are good tools such as the Document Analyzer (found under Recommendations), which help to identify the unused fields.

Reduce records


Unnecessary records that are not required for analysis should filtered out from the data model. If e.g. a fact table contain years of data, which are not required for analysis, filtering them out decreases the size of the data model. The relating dimension (attribute) tables can also be filtered to only read the existing facts data by usage of the exists() function.

Increase cardinality


  • Qlik stores data indexed and in lists. A fields containing 10 million values with just 2’000’000 distinct values is stored in a list containing 2’000’000 entries with pointer pointing at those records. If e.g. decimals of numbers or unnecessary combined fields can be avoided, the size of the data model will decrease. If we have date field, but we only require monthly data analysis, having the date field in a monthly basis will for example decrease the different day entries to just one monthly values.

    Create Surrogate keys


    • Combined key fields and alphanumerical keys result with bigger field contents, which takes more space by comparison to numerical values. In data models with hugh amount of data, using the autonumber() function will make the data model smaller, but also faster due to smaller key field sizes.

    • These are a small but effective way to make a data model smaller and faster. There are way more possibilities to optimize data models and applications. A good way to analyze an application is to make use of the Documnent Analyzer, created by Rob Wunderlich available from qlikviewcookbook.com.
    blog comments powered by Disqus