Building a link table

[joioan]

Hello experts,

I have the following fact tables, their only common -as is- field is SEA_ID. The cardinality between the tables is many-to-many for field SEA_id. For the first table the unique primary key is Questionnaire_ID and for the second is Collaboration_ID. I tried making a link table for these two as I need them for my app. 

From what I studied, I need to create the link table by using the common dimension field, but then how would I comment out the dim field SEA_id from the facts?

I thought about doing so:

Questionnaire:
LOAD
Questionnaire_ID,
//SEA_id,
//Donation_Date as Date
SEA_id&Donation_Date as Key,

(other fields that describe the fact table but are uncommon between fact tables)
from Questionnaire.qvd;

Collaboration:
LOAD
Collaboration_ID,
//SEA_id,
//Registration_Date as Date,

SEA_id&Registration_Date as Key,

(other fields that describe the fact table but are uncommon between fact tables)
from Collaborations.qvd;

Link:
LOAD Key,
'Donation' as DateType
resident Questionnaire;
Concatenate(Link)
LOAD Key,
'Registration' as DateType
resident Collaboration;

How does this look to you? Can you please help me out?

Thank you in advance 🙂

[BrunPierre]

The typical approaches to associate two fact tables with a single common field is using a CONCATENATE or a JOIN, with your specific use case and data model in mind.

Since you mentioned creating a link table to establish a many-to-many relationship between your fact tables, concatenating seems the obvious approach.

Questionnaire:
LOAD SEA_id,
Questionnaire_ID,
Donation_Date as Date,
...

Concatenate

Collaboration:
LOAD SEA_id,
Collaboration_ID,
Registration_Date as Date
...

[joioan]

Hello @BrunPierre thank you for your quick response. In fact, I want to handle this many-to-many relationship, not establish it (not sure if you mean the same 🙂 )

Can I concatenate the fact tables no matter the column number or the field names?

Also, the script you provided is for concatenation of both tables (right?). Do I keep the date fields with alias "Date" or as they were initially (eg Donation_Date).

[BrunPierre]

The Concatenate prefix combines two tables even if their columns aren't identical. Any fields that don't match between the tables will be filled with null values in the resulting table.

Renaming date fields the same is not a requirement but rather a good practice for consistency and clarity in your data model.

[joioan]

@BrunPierre 

Ok, but then  if I rename them the same,  I suppose I still have to create a DateType flag to distinguish Dates, right?

Also for this case, I would like to know -for educational reasons- why it could not work as an approach.

Many thanks 😄 

[BrunPierre · Accepted solution]

Including a flag (Type) to distinguish the origin of the fields can be particularly beneficial, especially when the two tables share identical structures.

Link table would equally work, given your scenario, Concatenate is a more simplistic approach with excellent performance.

Read more from here :Concatenate and Link tables 

[joioan]

I will study this material as well and I will return with comments 🙂

So it is better performance-wise to create a massive fact table instead of a link fact table with connections to the other fact tables (and maybe other dimension tables), noted.