Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
any one please explain what is a link table and what is use of link table and when it will use
please tell me any one with examples and any one have document or example on link tables please send me.
Hi,
Link tables are generally used for linking the two table or the fact tables. With the use of link tables, it’s possible to keep the fact tables separated from each other. The advantage of this solution for choosing this method is to keep the data model a logical one. With the use of link tables, it’s possible to keep the fact tables separated from each other
1.It is a table that stores all possible combinations of values called link table
2.It is used When there is more than one field in common between tables.
3.To maintain integrity of your application it is used.
Link tables are what is often outside of QlikView in other BI-environments/DWH and in general called bridge tables. The purpose and the concept is the same. These tables maintain a many-to-many relationship between two or more tables. Link tables should be evaluated in respect to another approach that is CONCATENATED fact tables.
Excerpt from QlikView material about link tables:
When to use a Link Table
Let's look at when a link table might be required. Generally if you have a data model with multiple fact tables, that contain different measures and only minimal common dimensions, then you will likely need a link table. If your fact tables store their data at different levels of granularity i.e. a transactional table with daily data and a targets table with monthly data, then you will almost certainly need a link table. The purpose of the link table is to provide a seamless lik between all data in your application so that QlikView's (or Qlik Sense's) associative technology can be used to it's full potential. A badly implemented Link Table will have a significant and negative impact on the user's experience.
Henric Cronström in several of his blog posts and discussions on this forum recommend using concatenated fact tables in most cases instead of link tables. Link Tables are a necessity however when you need something called an alias dimension or role-playing dimension. Henric Cronström's paper on CANONICAL DATE shows in detail how a link table is created and maintained for this specific purpose: Canonical Date
A special case of link table is the automatic creation of synthetic keys which QlikView does in the load script if it encounters two or more tables with more than one field (based on field-name) in common. Then it has to resort to this technique and create an intervening table it calls [$Syn <n> Table]. Also the use of INTERVAL MATCH will create a key and table like that which is in this case absolutely valid and necessary.
Here is a couple of useful links that might clarify the subject: