The Magic of link Table
In Data warehousing world, many a times you come across a situation where you have to handle multiple Fact tables.
Star schema usually have a single Fact in the center and dimensions surrounding it but some times you have a situation where you have more than one Fact table.
If you want to learn more about Facts and Star schema, please visit my earlier blog https://www.learnallbi.com/dimension-and-fact/.
In this blog i will discuss about how to handle multiple fact tables in QlikView data modeling.
Link table vs Concatenate
When you have multiple Fact tables in QlikView, it can be handled in 2 ways, by using concatenate or by using Link tables. If the granularity and columns in the fact tables are same then you can use Concatenate which will merge the tables into one and resulting table will have the sum of rows of the two tables.
Use Link table, when the granularity of the facts tables are different and when they are joined to different dimensions.
Concatenate and Link Tables are ways to resolve synthetic keys which formulate due to the presence of multiple fact tables.
Link Table example
To explain the concept of Link table, I will load few fact tables and dimension tables.
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.
Resolving Synthetic Keys using Link table
To resolve these synthetic keys we will :
– Create a composite key. Link table can connect to the original fact tables using this composite Key
– Load all the common fields in one table called Link table
– Drop these fields from the original tables.
Let’s see these steps in screen shots below:
Creating composite keys
Creating Link table. Loading common fields from the original Fact tables.
Drop fields as we no longer need these fields.
After following the above steps and loading the data , you will get the following data model. As you will notice, Link table is in the center. Link table contains all the common fields and Link table is linked to other fact tables using the Key field.
Link Table is a very useful way to resolve synthetic keys when multiple fact tables are present in the data model.
You should always load Distinct records while creating Link table. Also if the Key field/composite field contains several individual fields then it is good to create Key field using Auto number ( ) function like the one below
AutoNumber(YEAR_MONTH&’|’&CUSTOMER_KEY&’|’&HIERARCHY_KEY&’|’&LOGO_KEY) As [%Key field]
:
Nice Article
Hi ,
Thank you for the example .
I have the same Data model and I used Link Table to resolve my issue. Just in my model i don’t have the Field Year_Month and I have LOGO_Key.
I have an issu, can you advise me.
for example if you want to get Revenu , Sales and Detail Amount in the same Pivot Table, you will just get Revenu and Sales but no Detail Amount because there is no relation betwwen there is no relation between Fact 1,2 and Fact 3.
Regards
Link table is required to resolve the synthetic keys.
Synthetic keys get created because you have common dimensions between the Fact tables and thus common fields in the Facts.
If your fact tables are not sharing the dimensions and there are no common fields then you will have multiple independent/disjointed schema.
You don’t need link table in such a case and you will not get fields from other schema in your visualization.
Hi , Can u just clear me one thing , in your example,
U have used Common 4 Field in your fact1 and fact2 and fact3 have 3 common field.
so i how it will associate the value where , Yearmonth&HierarchyKey&CustomerKey&LogoKey ( 2015Mar&222&A231&123) in fact1 and fact2 table with
Yearmonth&HierarchyKey&CustomerKey(2015Mar&222&A231) in fact3 table.B
Yes Brijesh. Even me too have same question.
In this scenario, we have to make another key field lets say %key_1 in the link table and have to connect the fact table with %key_1. Hope this is the correct way to do it.
I used the same solution and it work .
I create Key1 in Link Table in loading from Fact 1 and 2 . it concatenate the fields exist in Fact3.
the only disvanatage is the number of line if your link table is big beacuase you xill have 2 key with the same number of line
Hello,
The datamodel seems to look good. But it does not mean that it actuallly performs as you would expect!
You should have 2 keys, one for fact1 and fact2 and one for fact3.
You should also use “where not exists” to limit you concatenations.
,KR
Nice article but a question.
How would link tables work with slowly changing dimensions and historical data/incremental loads?
Say we have a customer whose address changed. The link table would need both versions, right?
Thanks.
Hi Chandraish Sinha,
Let me just start by saying that I’m new with qlikview and thus my question may be completely out of this scope, anyway, checking your example I believe I can use something similar to solve the issue I have in hands.
Below described.
In qlikview script I have something like:
table1:
SELECT
COUNTRY,
SALES,
EXPORT_GROUP1,
EXPORT_GROUP2,
EXPORT_GROUP3,
EXPORT_GROUP4
FROM TABLE_A;
table2:
SELECT distinct EXPORT_GROUP1 as EXPORT_GROUP from TABLE_A
union
SELECT distinct EXPORT_GROUP2 as EXPORT_GROUP from TABLE_A
union
SELECT distinct EXPORT_GROUP3 as EXPORT_GROUP from TABLE_A
union
SELECT distinct EXPORT_GROUP4 as EXPORT_GROUP from TABLE_A
The behaviour I’m looking for would be:
When I select any of the values coming from EXPORT_GROUP field (from table 2), I would be able to get the country from table 1 where table2.EXPORT_GROUP = table1.EXPORT_GROUP1
or table2.EXPORT_GROUP = table1.EXPORT_GROUP2
or table2.EXPORT_GROUP = table1.EXPORT_GROUP3
or table2.EXPORT_GROUP = table1.EXPORT_GROUP4
Already tried doing this with a simple join on the sql query but then I realised it was not working as it was duplicating the results.
Any comments on what approach should I use would be more than wecome.
Thanks in advance,
Valter Bastos
Thank you for your post. It is very informative. However, there is one thing in the post which confuses me.
At the step of creating a composite key for each fact table, I noticed that table 1 and 2 have the same composite keys, whereas table 3 does not since it does not include Logo_Key. How can this composite key be treated as the same one with the ones from the other two tables?