modelling with a many to many table

[ry1633]

Hi all,

I'm having an issue modelling a many-to-many table.   I have a parent table called Hold  - this goes to a many to many table called Hold_Reason that has Hold ID and Reason ID in it.  And that m/m table goes to a Reason table that has the Reason value in it.  I have them joined to each other like follows.

Hold table (1:1) <---> (0:n) Hold_Reason table (1:1) <---> (0:n) Reason table

But when I try to pull in anything from the Parent table and a few other related tables and add in the Reason field (having to go through the middle many-to-many table) Cognos doesn't like it and generates one of those nice RSUM1 stitch query that outputs somewhat strange looking data.

[MFGF]

Hi all,

I'm having an issue modelling a many-to-many table.   I have a parent table called Hold  - this goes to a many to many table called Hold_Reason that has Hold ID and Reason ID in it.  And that m/m table goes to a Reason table that has the Reason value in it.  I have them joined to each other like follows.

Hold table (1:1) <---> (0:n) Hold_Reason table (1:1) <---> (0:n) Reason table

But when I try to pull in anything from the Parent table and a few other related tables and add in the Reason field (having to go through the middle many-to-many table) Cognos doesn't like it and generates one of those nice RSUM1 stitch query that outputs somewhat strange looking data.

Hi,

The cardinalities you have included in your example don't seem to represent a many-to-many relationship? It looks like:

a Hold row can have 0 or many Hold Reason rows. A Hold Reason row will always have one Hold row.
a Hold Reason row could have 0 or many Reason rows. A Reason row will always have one Hold Reason row.

Following that logic, a Reason row can only ever relate to one single Hold row? (A Reason row can only have one Hold Reason row, which can only have one Hold row)? You seem to be contradicting yourself - can you explain?

MF.

[ry1633]

I talked to my system architect for the application whose database I am modeling in this case.  He called the middle table in my example a many to many table - in that a Hold can have many Reasons and a Reason can have many Holds.  The cardinality is just how I have it joined in Cognos

[MFGF]

I talked to my system architect for the application whose database I am modeling in this case.  He called the middle table in my example a many to many table - in that a Hold can have many Reasons and a Reason can have many Holds.  The cardinality is just how I have it joined in Cognos

Hi,

This tallies with what you described, but doesn't tally with the cardinalities you have indicated above? For the middle table to be a many-to-many table, the cardinalities would need to be:

Hold table (1..1) <---> (0..n) Hold_Reason table (0..n) <---> (1..1) Reason table

Can you elaborate on what exactly you have?

MF.

[bus_pass_man]

Just to confirm, have you set the usage property of  Hold_Reason table to be a bridge table?   

[Invisi]

Just to confirm, have you set the usage property of  Hold_Reason table to be a bridge table?

You can do that in Framework Manager? Where is it? I want it!

[MFGF]

You can do that in Framework Manager? Where is it? I want it!

Hi,

If your model is set to be DQM, it's as below:



Cheers!

MF.

[Michael75]

You can do that in Framework Manager? Where is it? I want it!

Was introduced with 10.2.2

https://www.youtube.com/watch?v=Uo3XEF6g3EM