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

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