Key-Table in Qlik

Greetings,

As I previously mentioned,

Snowflake and Star Schema comes often in data warehouses, but what happens when your model contains multiple facts?

In Qlik products, there are 2 techniques:

Concatenate fact tables
Key-Table

Concatenate and Key-Tables are ways to resolve synthetic keys, which formulate due to the presence of multiple fact tables, which I know I implement only in the PNL model (which we will mention in the future).

Between those 2 techniques, I always work with the key table, even when there is no need because, in the future, it’s much easier to connect another fact table to Key-Table.

Anyway, I will explain when you need to use any of them:

If the granularity and columns in the fact tables are same then you can use Concatenate, which will merge the tables into one, and the resulting table will have the sum of rows of the two tables.

Use Key-Table, when the granularity of the facts tables are different and when they need to be joined to different dimensions.

Concatenate and Key-Table is ways to resolve synthetic keys that formulate due to the presence of multiple fact tables.

So, how do we implement this Key-Table?

I will show through example:

So what happens if we just loaded as is?

If you load these tables, you will get synthetic fields in your data model. These synthetic keys are formed since we have multiple Fact Tables and these fact tables are sharing the same dimensions. Since these fact tables are joining to the same dim tables they have duplicate/common fields.

To resolve these synthetic keys we will :

– Create a composite key. (I use AutoNumberHash256 to make the key more simpler and less heavy ) Key-table can connect to the original fact tables using this composite Key

– Load all the common fields in one table called Link table, I use temp table to first bring all dimensions and only then create composite key (to save unnecessary rows ), and make sure you bring them using distinct

– Drop these fields from the original tables or just use them in composite key

Let’s see these steps in screen shots below:

After following the above steps and loading the data , you will get the following data model. As you will notice, Keytable is in the center. Keytable contains all the common fields and Link table is linked to other fact tables using the Key field.