Very new to both Cognos and Framework manager and am attempting to build a model. My current software queries directly on the data source and I have 3 joins on the tables (without all 3 joins the data is incorrect). I have looked online and through the forum and I don't quite understand if this can be done at the model level or not and if so how to accomplish that. I have a simplified SQL segment below showing how the tables are joined in my current query. I'm hoping someone will be able to point me in the right direction to accomplish this in FM if it can be done. Thank you!
FROM
GLDBFA.GLPTX GLPTX
LEFT OUTER JOIN ARDBFA.ARPGT ARPGT
ON
GLPTX.TXPAGE = ARPGT.GTPAGE AND
GLPTX.TXSN2 = ARPGT.GTGLCN AND
GLPTX.TXSN1 = ARPGT.GTAUGJ
Quote from: bren1519 on 19 Jul 2019 10:43:49 AM
Very new to both Cognos and Framework manager and am attempting to build a model. My current software queries directly on the data source and I have 3 joins on the tables (without all 3 joins the data is incorrect). I have looked online and through the forum and I don't quite understand if this can be done at the model level or not and if so how to accomplish that. I have a simplified SQL segment below showing how the tables are joined in my current query. I'm hoping someone will be able to point me in the right direction to accomplish this in FM if it can be done. Thank you!
FROM
GLDBFA.GLPTX GLPTX
LEFT OUTER JOIN ARDBFA.ARPGT ARPGT
ON
GLPTX.TXPAGE = ARPGT.GTPAGE AND
GLPTX.TXSN2 = ARPGT.GTGLCN AND
GLPTX.TXSN1 = ARPGT.GTAUGJ
Hi,
When you select two query subjects, the right-click menu gives you the option to create a relationship between them.
In the relationship dialog there is an "Add link" button at the top that allows you to create as many links within the relationship as you require. In this case you would add all three to the relationship.
Cheers!
MF.
So I was misinformed I guess as I understood that Cognos only took the first join into consideration if there were multiple joins. If it takes all joins into consideration then that makes it easier. Thanks!
Quote from: bren1519 on 19 Jul 2019 12:21:40 PM
So I was misinformed I guess as I understood that Cognos only took the first join into consideration if there were multiple joins. If it takes all joins into consideration then that makes it easier. Thanks!
Hi,
No and yes :) You weren't misinformed, but I suspect the details were not fully explained to you...
When you create a relationship between two query subjects in Framework Manager, that one relationship can include links for as many different items as you need. You can even use the ellipsis at the bottom to convert it to an expression-based relationship and use functions if you really need to. The important consideration is that it is all encapsulated in one relationship - however you decide to define it.
If you create more than one relationship between two query subjects, this is where you run into trouble. The query engine will only use one of them - the first alphabetical one based on the name you specify for the relationship. Specifically, this would mean selecting both query subjects a second time and using the "Create relationship" option a second time. Sometimes when you ask FM to determine relationships automatically you can end up with multiple relationships between query subjects too. I'm guessing this multiple relationships scenario is what was described to you, but not fully explained?
Cheers!
MF.
That makes it much clearer. Thank you very much!