Building Link table

[Qlik_William]

Hi,

lets say I have 3 fact tables F1,F2,F3 

F1 and F3 have common dimensions Year, Product, Customer

F2 have common dimensions Year, Product

I want implicit association so when a customer is selected, I want rows in F2 to be selected if the Year, Product exists in F2 for that customer in F1,F3

So I build a link table like below "L" and I have the F1-F3 tables and a couple dimensions tables

Now there is a many-to-one relationship between the "L" table and the "F2" table because of the "Fact2CompositeKey" field. Can this be a problem? Is the Link table wrong? I did some tests and didn't find any issue.

I can make the Fact2CompositeKey unique in the "L" table by not adding keys that already exists but that defeats the purpose, for instance, if a row gets added in the F3 table with Year=2023, Product=4, Customer=D I want this row associated with the F2 table. 

Is there another way to build the Link table in this case?

[Qlik_William]

I think i solved it by building the link table first, loading the 2 fact tables with all 3 common dimensions first, and for the last fact table F2 using this

WHERE NOT Exists(Fact2CompositeKey,Year & '|' & Product)

LinkTable:
LOAD
Distinct
Year & '|' & Product & '|' & Customer as Fact13CompositeKey,
Year & '|' & Product as Fact2CompositeKey,
Year,
Product,
Customer
FROM [lib://Excel_Data/Link_Examples.xlsx]
(ooxml, embedded labels, table is Fact1);

Concatenate(LinkTable)
LOAD
Distinct
Year & '|' & Product & '|' & Customer as Fact13CompositeKey,
Year & '|' & Product as Fact2CompositeKey,
Year,
Product,
Customer
FROM [lib://Excel_Data/Link_Examples.xlsx]
(ooxml, embedded labels, table is Fact3);

Concatenate(LinkTable)
LOAD
Year & '|' & Product & '|' & null() as Fact13CompositeKey,
Year & '|' & Product as Fact2CompositeKey,
Year,
Product
FROM [lib://Excel_Data/Link_Examples.xlsx]
(ooxml, embedded labels, table is Fact2)
WHERE NOT Exists(Fact2CompositeKey,Year & '|' & Product)
;

[ali_hijazi]

instead of concatenating the distinct values of the tables, make a full outer join instead, this way customer and product and year may fit in a common row for the 3 fact tables

[marcus_sommer]

Like hinted in my comment to your previous posting a link-table approach must not be the most suitable data-model - neither from any performance point of view nor to the needed extra efforts.

[Qlik_William]

You mean by using Join(LinkTable) instead of Concatenate(LinkTable) keeping the rest of the script the same? It produces the same result in my test. Also maybe joining tables like this will increase script run time, not sure though. Thanks for your comment

[Qlik_William]

Yes I am aware, just trying some different things. Is there a way to build the link table in my example without replicating the Fact2CompositeKey do you know?

In my second comment using the WHERE NOT Exists(Fact2CompositeKey,Year & '|' & Product)

I can still get duplicates for Fact2CompositeKey in the Link table from F3, for instance if F3 contains a row Year=2023, Product=2, Customer=D.

Then I will have Fact2CompositeKey 2023|2 repeated twice in Link table. So when I select Product=2 two rows will be associated with the F2 table. Not sure if it's a problem though.

[marcus_sommer]

I think it's the wrong logic/load-order to build at first the link-table and then the facts because at this point it's completely unknown which key-values exists or not.

There are a lot of good reasons to check the (not only by the key's) values to clear/remove, correct and/or fill/populate them before creating the final tables - regardless if a star-scheme or a link-table data-model is used.

The last where not exists() concatenate tries to fill two keys at the same time. To ensure that all adding/population is working as expected all steps should be done explicitly.

[ali_hijazi · Accepted solution]

yes use 
Linktable:
load * from F1;

Outer Join(Linktable)
load distinct common_key from F2;

outer join (LinkTable)
load distinct common_keys from F3;

this case you eliminated duplicates
and you would have common columns in same rows instead of having ones for F1, and F2 and another for F3

reload time increase depends on the size of each fact table
but usually it doesn't take so much time because you will be loading only the common columns for the linktable

You may optimize the load as follows:
outer join(LinkTable)
load key1, key2;
load * from F2.qvd(qvd);