Model a 1:N relation

[paolo_cordini]

Hi, 

I have a database designed in this way. The main "fact tables" has ID's fields pointing to "bridge" tables, in turn pointings to "subdetail" tables.

The relationship between the "main" table and the "subdetail" tables is 1:N

I am trying to figure out whether there Is a way to model such a relationship with Compose.

I cannot link the main table to "bridge" tables because the relation fields are not primary keys. I cannot define them as primary keys because they are not unique.

So I cannot use this relationship in Data Mart definition.

Thanks

Paolo

[Nanda_Ravindra]

Hello @paolo_cordini ,

           I am not seeing any direct way to model such a relationship with Compose. If you are still looking for a solution, please open a support case, and the team should be able to assist you if there are any workarounds.

Thanks,

Nanda

[TimGarrod]

Hi,  Can you provide some info on what you would want the "fact" table to look like?  

This feels like a scenario where pivoting the data set to flatten the multiple values in the DW model may support what you are looking for - but would be good to understand output to be able to provide possible solutions. 

[paolo_cordini · Accepted solution]

Hi

actually, I could say there are many fact tables. The table I called "Main Table" is a fact table but "Detail1", "Detail2", are fact tables too, with 1:n relationship with the main one.

I cannot pivot the data set because I don't know in advance how many rows are in "Detail" tables for one record in the Main Table.

The solution I adopted for the moment is to leave the tables unrelated in the data model. In the Data Mart I defined as "dimensions" (even if they aren't) the detail tables, so I could flatten "Bridge" and "Detail" but without linking them to the fact table.

It's ugly and non-canonical, but it works for the final users because the Main Table and Detail tables must remain separated.

Thank you

Paolo

[lyka]

Hi Paolo, 

It looks like you have found a solution based on your last comment. We suggest submitting a feature request thru our Ideation  for your intended use case.

Thanks

Lyka