topic Re: Building a link table in App Development

Building a link table

joioan — Fri, 15 Nov 2024 20:59:34 GMT

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 🙂

Re: Building a link table

BrunPierre — Mon, 22 Apr 2024 13:19:03 GMT

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
...

Re: Building a link table

joioan — Mon, 22 Apr 2024 13:15:27 GMT

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).

Re: Building a link table

BrunPierre — Mon, 22 Apr 2024 13:33:36 GMT

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.

Re: Building a link table

joioan — Mon, 22 Apr 2024 13:33:44 GMT

@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 😄

Re: Building a link table

BrunPierre — Mon, 22 Apr 2024 14:05:56 GMT

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

Re: Building a link table

joioan — Mon, 22 Apr 2024 14:10:25 GMT

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.