The Qlik Sense® data model

Data used in Qlik Sense needs to be in a tabular form, very much like a table in Excel. A column in the table is referred to as a field and every row is a record.

When data is loaded into Qlik Sense, it is stored in the QIX internal database. In the simplest case, the data is just one single table. However, more commonly, the data model consists of several tables with links between them. These define how the different tables relate to each other. It is, hence, a relational model.

In the previous chapter, we saw an example where four tables were used: Customers, Orders, Order lines, and Products:

The Qlik Sense® data model

A simple data model made from four tables

This is in fact the core of a very common business application—a sales analysis based on the registered orders.

The structure is not a coincidence. Rather, the reason why it looks the way it does is that it is a reflection of the real business processes. The relations these four entities have in reality dictate the data model:

  • A customer may over time place several orders. Hence, customers and orders should be stored in different tables, and the customer ID should be stored in the Orders table.
  • An order may contain several order lines. Hence, orders and order lines should be stored in different tables, and the order ID should be stored in the Order lines table.
  • Several different order lines can refer to the same product. Hence, products and order lines should be stored in different tables, and the product ID should be stored in the Order lines table.

You could add a number of additional tables, and for each table you will need to ask yourself what its relation is with the already existing tables. The new table could be a list of countries to which the customers belong, of product groups, of shippers, of suppliers, of invoices, and so on. The business processes define whether a customer can belong to more than one country, whether a product can belong to more than one product group, and whether a single invoice can refer to more than order.

Hence, you should look at the real-life entities and their relationships to understand how they should be modeled in the Qlik Sense data model.

Creating a multitable data model

Loading several tables is technically just a matter of using several Load or Select statements in the script. Joins, in the way a database manager knows it from SQL, are usually not needed.

Normally, each Load or Select statement creates one table in the data model. Hence, if you want to load four tables, you should have four different Load or Select statements, each defining the appropriate table.

There are, however, some exceptions to this rule:

  • If a loaded table contains exactly the same set of fields as a previously loaded table, the new table will not be created as a separate table. Instead, the loaded records will be appended to the existing table, which most likely is what you want. This way you can add more data to an existing table.
  • If the Load or Select statement is preceded by the Concatenate or Join keywords, the loaded table will be merged with an existing data table. The Concatenate keyword is used if you want to add records to the table, just like in the previous bullet, but the two tables have slightly different sets of fields. The Join keyword is used to create the product between two tables, that is, the same as a JOIN in SQL.
  • If the Load or Select statement is preceded by the Generic keyword, the loaded table will be transformed into several data tables. This is a keyword you need to use if your table is a generic database, that is, if the second to last column contains an attribute name and the last column contains the attribute value.
  • If the Load or Select statements are preceded by the Mapping keyword, the loaded table will not be used as a normal data table. The table will be used for other purposes.
  • A previously loaded table can be deleted using the Drop command. This is especially useful if you make many transformations and need temporary tables.

Linking tables

Further, when loading multiple tables, the links between the tables are defined by the field names. At the end of the script run, the existing tables will be evaluated. If the same field name is found in more than one table, this field will be considered to be a field that links both the tables. This way, a data model is created. The logic in the script evaluation is hence identical in Qlik Sense and QlikView.

This means you need to make sure that the fields you want to use as links between the different tables, the key fields, are named the same in all tables you want to link. You can do this using the Profiling dialog (refer to the previous chapter) or you can rename them yourself by editing the script.

For instance, if the key is called CustomerNo in one table and CustomerID in the other, you could rename them like this:

Load CustomerNo as CustomerID, … From Table1 … ;
Load CustomerID, … From Table2 … ;

Also, it is important that you make sure that you don't have any unwanted links. For example, you may have a field called Description in two different tables. This is of course not a key, but rather just a short name for something that in one table may be a product description and in another a customer description. Also, here you need to rename the fields:

Load Description as Description1, … From Table1 … ;
Load Description as Description2, … From Table2 … ;

The goal is to create a script that defines a logical, coherent data model that corresponds to the business situation.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset