|Summary:||An article to advise users on the considerations for handling large data volumes and optimising QlikView document performance|
|Related Product(s):||This article relates to the following products:|
Considerations for Handling Large Data Volumes and Optimising QlikView document performance
There are many ways to manage data within a QlikView environment, especially when dealing with large data volumes. It is advisable to consider how to:
- Plan and make use of the many ways to speed up load times
- Reduce queries against active source systems
- Ensure the best performance of the QlikView documents.
Below is a checklist of some of the methods to ensure Qlikview will perform at its best.
QVD files are exports of tables from a QlikView file. The format is native to QlikView and optimised for rapid load times (see “QVD Files” in QlikView help section). Some of the ways to deploy QVDs and the resulting benefits are:
- Pulling generic source data tables into QVD files to act as source files for multiple QV documents
- Using incremental loads to extract only new/changed records from source systems
- Ensuring the load from QVD is optimised by not filtering the load and avoiding the use of functions to carry out any transformations during the load
- Organising QVDs into annual sets eg. Sales2013.qvd, Sales2014.qvd
- Create a QVD with aggregated historical data
- Reduce queries against source systems which may be in use by users
- Speed up reload times of QV Documents
- Provide a consistent data source across the QV Documents
- Where historical data is not going to be analysed to a granular level, an aggregated QVD provides the historic high-level figures for comparison and reduces the need to store large files.
Note: Whilst QVD files are in a native QlikView format they are not encrypted. Security on folders containing QVDs should be established.
The structure of the QlikView model impacts the performance of the final document for users. Establishing a model that suits QlikView is important. Here are a few considerations when setting up the load script which will help to optimise the performance of the final QlikView model/document.
- Design the QV model into a compact start schema
- Avoid link tables by use of joins or the applymap function
- Use integers as link fields, making use of autonumber function if necessary
- Check that source database fields are indexed if referenced as a filter in a load statement
- Use ones and zeroes as flags – in front end a sum aggregation can be applied which will be more efficient than a count
- Only load fields required
- Only load date range required
- Split recurring pattern varchars into smaller distinct parts eg. https://company.com into “https://” and “company” and “.com”
QlikView Front End
Having established a structured setup for the QVDs and an optimised QlikView Model the final area to review is the user interaction with the QlikView document. In order to provide a responsive QlikView document, there are some key things to keep in mind when designing the sheets and objects.
- Remember, all objects visible on a sheet have to be calculated by QlikView. Therefore, as far as possible, keep the number of visible objects down to a minimum eg. by using container objects
- Remove any test sheets and test objects from your document before publishing
- Minimise use of memory hungry functions, for example:
- Using if or nested if statements
- Using Distinct Count
- Overuse of Set Analysis
- The object of Sheet Show/Hide conditions