Monday, 9 January 2017

Two Link Table Design Strategies for multiple fact tables

Background:
In Qlikview, using link table is a typical solution for  the scenario that multiple fact tables share several conformed dimensions.
While in the projects I have experienced, I see two major design strategies regarding the link table:

1. Classic Design :
     1.1 using the same key column across all the fact tables
     1.2 in the link table, store the individual dimension key column;

The advantage of this approach is that the whole diagram is simple, the performance is good, the link table size is small;

The disadvantage of this approach is that we need to have a whole idea about the

2. In several of the projects I experienced, I also saw another design strategy for the link table, I called it QuickAndDirtyWhileEasy approach:
   2.1 For each fact table, we create a unique key for that fact table;
   2.2 Link table is a result of concatenating the individual fact table's unique key and individual dimension table unique key column.
The advantage of this approach is that it is very easy to grasp and implement. The result of the model is quite reliable. It is easy to handle different granularity among different fact tables.
The disadvantage of this approach is that the diagram, the link table is a bit ugly. Because the record in the link table will have more columns is NULL. And the dashboard performance is not as ideal comparing to the option 1. Especially if we have more data in the fact tables.

Conclusion:
I used to be a fan of option 1. While through several projects, I found option 2 is not a bad idea in the situation of small-medium size project, especially when data volume and performance is not a problem, in that case, the modelling and development is much faster and easier.





1 comment:

  1. I feel Qlik is the most useful and wonderful aspect through which one easily looks out and finds about the right kind of tools needed.

    Qlik Rest Api Connection

    ReplyDelete