Key-Table in Qlik

November 4, 2018

 

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.

 

In my opinion ,KeyTable is the optimal solution for multi fact tables model in Qlik products.

 

Feel free to contact me on any of those channels:

Email

Linkedin

YouTube

 

 

Please reload

Featured Posts

Key-Table in Qlik

November 4, 2018

1/3
Please reload

Recent Posts

November 4, 2018

October 7, 2018

September 18, 2018

Please reload

Search By Tags
Please reload

Follow Us
  • Facebook Classic
  • Twitter Classic
  • Google Classic