Best practices for data modeling

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

In general, the way you load data into the app 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 Qlik Sense.
   • 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 app.

Your goal should be to create a data model that enables efficient handling of the data in Qlik Sense. 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 Qlik Sense.
• 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