Best practices for data modeling

This section describes a number of different ways you can load your data into the QlikView document, depending on how the data is structured and which data model you want to achieve.

In general, the way you load data into the document can be explained by the Extract, Transform and Load process:

  1. Extract

    The first step is to extract data from the data source system. In the script, you use SELECT or LOAD statements to define this. The differences between these statements are:

    • SELECT is used to select data from an ODBC data source or OLE DB provider. The SELECT SQL statement is evaluated by the data provider, not by QlikView.
    • LOAD is used to load data from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically
  2. Transform

    The transformation stage involves manipulating the data using script functions and rules to derive the desired data model structure. Typical operations are:

    • Calculating new values
    • Translating coded values
    • Renaming fields
    • Joining tables
    • Aggregating values
    • Pivoting
    • Data validation
  3. Load

    In the final step, you run the script to load the data model you have defined into the document.

Your goal should be to create a data model that enables efficient handling of the data in QlikView. Usually this means that you should aim for a reasonably normalized star schema or snowflake schema without any circular references, that is, a model where each entity is kept in a separate table. In other words a typical data model would look like this:

  • a central fact table containing keys to the dimensions and the numbers used to calculate measures (such as number of units, sales amounts, and budget amounts).
  • surrounding tables containing the dimensions with all their attributes (such as products, customers, categories, calendar, and suppliers) .
Note: In many cases it is possible to solve a task, for example aggregations, either by building a richer data model in the load script, or by performing the aggregations in the chart expressions. As a general rule, you will experience better performance if you keep data transformations in the load script.
Tip: It's good practice to sketch out your data model on paper. This will help you by providing structure to what data to extract, and which transformations to perform.

Each table in your data model usually corresponds to either a SELECT or LOAD statement. The differences between these statements are:

  • SELECT is used to select data from an ODBC data source or OLE DB provider. The SELECT SQL statement is evaluated by the data provider, not by QlikView.
  • LOAD is used to load data from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?