BI Performance Tuning

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

The application becomes smaller by reducing the number of unneeded fields. Both the script processing time and the reaction speed are increased by selection in the application due to the lower RAM requirement of the data model. Therefore, NOT reading or deleting unnecessary fields is one of the most important criteria for a faster application. With the help of Document Analyzer (by Rob Wunderlich, links see below) you can easily identify and delete the NOT needed fields.

Reduce records

The data volume has a large impact on the application size. Therefore, unnecessary data that is not relevant for data analysis should be deleted. This would be the second possibility to optimize the data model. For example, in a sales application, data older than 5 years could be excluded from the fact table in the data model. The same then also applies to the attribute (dimension) tables such as customer, product, calendar, etc.. By using the Exists() function, only the relevant master data is read, which is also available in the facts table.

Increase cardinality

Qlik stores the data indexed and in lists. A field containing 10 million values but only 2 million unique values is stored in a list with 2 million entries and 10 million pointers to these values. For example, rounding up unneeded decimal places, reducing the unique values (more identical field contents) or separating combined fields into several individual fields makes the data model smaller.

For example: With a time stamp where the time is irrelevant or the date is only displayed in months, the cardinality can be increased easily and effectively thanks to a monthly reduction.

Create Surrogate keys

Compound or alphanumeric keys result in larger field contents that require more space than numeric values. In data models with large amounts of data (10 million and up), using the Autonumber() function, you can create artificial keys that fit more efficiently and smaller into the field content.

These are small but effective ways to make the data model smaller and faster. There are further optimization possibilities. On the website qlikviewcookbook.com you can download for example the Document Analyzer by Rob Wunderlich: This tool is a valuable help to analyze and optimize applications.