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.
Quote from: ry1633 on 12 Oct 2017 11:04:53 AM
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.
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
Quote from: ry1633 on 12 Oct 2017 03:10:41 PM
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.
Just to confirm, have you set the usage property of Hold_Reason table to be a bridge table?
Quote from: bus_pass_man on 13 Oct 2017 07:16:21 AM
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!
Quote from: Invisi on 18 Oct 2017 06:50:39 AM
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:
(http://image.ibb.co/kaRFvm/DQM_Usage_FM.png)
Cheers!
MF.
QuoteYou 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 (https://www.youtube.com/watch?v=Uo3XEF6g3EM)