When you are building an app in Qlik Sense, the first thing to consider is the desired result of the data model. The data model is how the data loaded into your app is understood by Qlik Sense. This article will discuss the various considerations that need to be thought of when you design your app’s data model.
When you load data into an app, this data can come from multiple sources. Each ‘chunk’ of data is held in the app in a data table. For example, if you upload an Excel spreadsheet, the data within will be stored in the app in a data table. Equally, if you load some data from a database via a data connector, this data will also be stored as a data table. Unless all your data is contained in one single database table or spreadsheet, you are likely going to end up with multiple data tables in your Qlik Sense app. For your app to work correctly when you create visualisations and your users interact with the app, you need to consider how Qlik should associate these tables together in the model.
Importance of a solid data model
The reason why you need to design your model early on is to avoid any erroneous results being served to your app’s users when they interact with the data. Qlik Sense is incredibly dynamic in that users can use filters and interact with visualisations to narrow down the data shown in the app. If the data model consists of multiple tables, there is a risk that when a filter in table A is applied, it will lead table B to show unexpected results. Additionally, if a visualisation is using data from both, tables A and B and they are associated incorrectly, this can cause incorrect results to be displayed in a visualisation without any filters being applied.
Data table associations
Traditional relational databases
Think of your Qlik Sense app’s data model as a type of a relational database. Tables within a relational database are usually associated via keys. Each table will have a primary key, usually a field called some variation of the word ID. If another table is to be associated with that table, the other table will have its own primary key but it will also have a foreign key to the table it wants to be associated to. The values in the foreign key field in this table will match the values in the primary key of the first table. For example, consider these 2 database tables:
The ‘ICECREAM_ORDERS’ table has a primary key called ID. The table contains all the ice cream orders from an ice cream van. The table ‘ORDER_DETAILS’ contains the details for each order such as the item that was brought and the flavour. This table also has a primary key field called ID. However, it also has a foreign key to the ‘ICECREAM_ORDERS’ table in the field ORDER ID. Note that ORDER_ID 3 appears 3 times, once for each item in the order. If you were to retrieve data from these two tables, this is how you would ‘join’ them in a relational database such as SQL Server.
Qlik Sense associations
Qlik Sense works a little differently. Instead of a primary key and foreign key concept, Qlik works with a single key. The way that Qlik knows that tables are ought to be associated using a specific key is by checking the field names. If the names are the same, Qlik assumes those fields are keys. This would pose an issue with the ice cream example above because the identical field names are the ID fields. Consider these 2 tables formatted slightly differently:
The primary key field in the ‘ICECREAM_ORDERS’ table and the foreign key in the ‘ORDER_DETAILS’ table have been renamed to the same name. Qlik will now associate the tables using the correct field. If your resulting data model needs to contain multiple data tables, you need to make sure that your key fields to be associated have the same names. You will need to make sure that other fields in the model DO NOT have the same names to avoid incorrect associations. Renaming of fields can be done in Qlik.
Once you have added data to your model, you can use the data model viewer to check that associations are being made correctly. Here is the ice cream example. Note that the fields ORDER_ID are connected with a line and have a key icon next to them. This indicates that they are associated.
Associations can be formed in the data manager or the data load editor. In the data manager, you aren’t able to have identical field names. Instead, you have to associate tables in the associations view. You can choose which fields are used for associations.
Joining tables
Sometimes your data may be stored in separate places but in your app’s data model, you want to store it in a single, wide data table. This can be achieved by using joins. Joins can be made in the data manager or the data load editor.
Joins in Qlik Sense are different to joins in a relational database. In a traditional database, using SQL, you can join tables on specific parameters by specifying an ‘on’ clause. In Qlik, this can’t be done. Instead, Qlik will attempt to join tables on identical field names. If you are joining tables, you need to make sure the fields you want to join on have the same name and other fields DO NOT have the same names. Consider the following 2 tables:
The table ‘ORDER_DETAILS’ contains the details of ice cream sales from an ice cream van. The ice cream flavour is stored in a separate table. Note that for the join to work correctly, the fields FLAVOUR_ID have the same name and no other fields have this name nor any other matching names. Joining the 2 tables would result in a table like this:
Note that Qlik Sense does permit all the normal types of joins: inner join, outer join, left join and right join.
Concatenating tables
Sometimes, you may want to join tables by stacking one on top of the other so that the result has the same fields but more rows. This is called concatenating and can be done in Qlik Sense in the data manager or the data load editor. If you are familiar with SQL, concatenate is the same as a union clause. Consider these 2 data tables:
The ice cream orders are stored in a new table each month and you want the resulting table in the Qlik Sense data model to contain all the combined data. Concatenating these tables will result in a single table as such:
These are the basic considerations for building a data model. It’s much easier to think about this before starting the app build as you can format your data to meet requirements as you go.
