In the previous chapters, we looked at how to create a definition of which data to load. But we did not look at which considerations you should make on how to load and link different tables.
So, in this chapter, we will discuss the following topics:
Data modeling
How a data model reflects business processes
The QIX engine is the core of the logic in Qlik Sense. All evaluations and calculations are made by this engine.
Every user selection implies a new logical situation. Other field values than the ones used earlier are possible; summations need to be made, so the charts and the KPIs get different values than what we had before. Everything needs to be recalculated, and the data model defines how this is done.
When the user makes a selection, Qlik Sense first evaluates which field values of other fields are possible and which rows are possible. In this evaluation, the key fields are used to propagate the selection to other tables. This is the logical inference. The second step is to calculate all formulas based on the possible rows. In this step, all aggregations are made.
The data model defines how the QIX engine's logical inference and calculations will be made.
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:
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...
In a database, there are rules about where different entities are stored. For instance, everything about the customers should be stored in the Customers table. A customer identifier is stored in this table, which means that the necessary data can be retrieved by a simple lookup in the Customers table. So, if you need to refer to a customer from another table, you just store this identifier in the other table.
The identifier needs to be unique in the Customers table, so that every record can be addressed. Here the field is called a primary key. In other tables, duplicates may exist. For example, several records in the Orders table might refer to the same customer. If this is the case, the key is called a foreign key.
A consequence of this setup is that a database can have many tables, often thousands. One table for customers, one for orders, one for order lines, one for products, one for product groups, one for shippers, one for invoices, and so on. Each table...
The script defines the data model, but if you want to view it graphically, you should use data model viewer. This is opened from the toolbar menu, as shown in the following screenshot:
Clicking on the icon to the right in the menu will open Data model viewer in a new tab. Once this is open, you can visually see what the data model looks like. If you have more than one table, they should be linked by the key fields and should look something similar to the following screenshot:
The Data model viewer is an excellent tool to get an overview of the data model. It is also a very good debugging tool in the application development process.
Check whether the data model looks the way you want it to and make sure that you have no circular references. Circular references will be marked with red links and synthetic keys will be named $Syn:
© 2015 Packt Publishing Limited All Rights Reserved
