The problem with the concatenate option is that it works well when the granularity of two tables are same, but when the granularity of the tables is not same, it is not suitable. So in such cases, the link table option is used to remove the synthetic keys.

In this method, we create a logical table also called as link table. The link table contains keys which will link the fact tables and the common fields from the fact tables.

Let us see an example. We take the following image:

As you can see in the previous screenshot, there are three tables, that is, sales, budget, and purchase. The granularity of all three tables is different, so concatenating them doesn’t make sense, and thus a link table approach should be used to remove the synthetic keys.

The logic to create a link table is as follows:

1. Create a unique composite key in each table and comment the common field.
2. Create a link table which contains the similar composite key and common fields from each table and then concatenate them.
3. Remove the fields from the table (if you have used resident load in step 2).

The script is as shown in the following  image:

The data model is as shown in the following image: