Link tables are generally used for linking the two table or the fact tables.
Let us go with an example..
As we know that when we are designing the datamodel synthetic keys and circular loop are common.
For fixing this problems we use the link table concept.
You can also use concatenation ,but it always not give the appropiate result.
Concatenation works well enough when we have all the fields same.
With the use of link tables, it’s possible to keep the facttables separated from each other. The advantage of this solution for choosing this method is to keep the datamodel a logical one.
Please refer the following link. But its better to avoid link table.
Hope it helps u.
There are two main strategies to modelling data in QlikView to handle multiple fact tables:
- Append your fact tables into one single fact table - usually referred to as a CONCATENATED FACT as QlikView's syntax for appending data to tables is by use of the CONCATENATE prefix (the equivalent of a SQL UNION operation)
- Build a link table (what you have done so far) For a majority of implementations, option 1 is the appropriate method. Attributes of a CONCATENATED fact can be summarised as:
- Performs well due to the reduced number of large tables in the data model
- Simple to implement, just append all data to one generic fact table whilst ensuring common dimensions are referenced by common field names
- The different facts are NOT directly associated with each other. The implication is important to understand. It means that cross-analysis of facts is typically only achievable by the common dimensions. Any fact specific dimensions do not connect in any way to the records of the facts that do not reference these dimensions. Complex 'set analysis' syntax can to some degree mitigate this shortcoming, but if your core requirement is to do indirect analysis of fact A by fact B's fact specific dimensions then you may need to revert to a link table model instead.
How to construct Link Tables is a complex subject but relies upon traditional database linking table design techniques. It is easy to go wrong and produce linking tables that may seem to produce the correct results in the front-end but is excessively large, consuming memory and CPU resources.
In my experience, a poorly modelled QlikView data model is the most common culprit for causing poor performance.
I hope this quick, far from exhaustive, introduction to multi-fact modelling in QlikView proves of some help and sets you on the right course.