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:

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 SELECTSQL 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

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 SELECTSQL 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

Feedback

Did this information help you?

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