Handling Large Data Volumes with QlikView

Qlik Technical Article

Article sections

    Summary: An article to advise users on the considerations for handling large data volumes and optimising QlikView document performance
    Article Type: Information
    Related Product(s): This article relates to the following products:

    • QlikView
    Related Articles:

    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

    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:

    QVD Management

    • 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

    Benefits:

    • 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.

    QV Model/Script

    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
    Related Information:
    in GeneralQlikview
    Share This Post
    Share on facebook
    Share on linkedin
    Share on twitter
    Share on email
    More To Explore

    Ready to Talk about your Project?
    Book a free Discovery Call

    A no obligation call to discuss your business needs and project requirements. During the call, our consultant will explain how we can help you to achieve your goals and answer any questions you may have.