Unwished outer joins

[d_idaho]

Hi,

In a framework with three tables A, B and C :
1) B is joined to A with an inner join 1..1 to 1..N
2) C is joined to A with an inner join 1..1 to 1..N

When taking fields from tables A and B, the generated query uses the 1) inner join.
When taking fields from tables A and C, the generated query uses the 2) inner join.

When taking fields from tables B and C, I would expect it  uses inner joins 1) and 2).
Anyway, the result I get seems like the use of full outer joins.
The resulted lines contain data from table B or table C but no line with both of them.

Same result with request using fields from A, B and C :
Lines contain data from A and B or from B and C, but never from A, B and C.

How can I force Cognos to use inner joins ?

Thanks for helping

[Lynn]

Are you familiar with how Cognos uses cardinality and the impact on generated SQL? You might want to review the cardinality section in the FM user guide ( chapter 8 ).

The full outer join behavior you describe is what happens for multi-fact queries.

If you describe what A, B, and C represent you might get better advice as to modeling best practices for your situation.

Fact tables belong on the n side of relationships while dimensions belong on the 1 side of relationships. Sounds to me like you've got B and C identified as facts where A is a conformed dimension.

[wyconian]

Hi

Lynn is absolutely right, it sounds like you have all these set as facts i.e. with all incoming relationships being on the n (many) side.

In a multi-fact query Cognos will break the request into multiple queries (based on a fact) and then try to 'stitch' them together.  Look at the generated SQL, if you can see coalesce or have columsn called 'sc' then this is what's happening.

Are these actually facts?  If they are it would be 'unusual' to be trying to join facts directly together without some kind of conformed dimension between them.

I've found that the cardinaluty of the relationships is really only important when you want to roll records up.  If this is something you need to do then look at adding determinants (check out the documentation for this).  If you don't need to roll records up just change the cardinality to be 1:1.  Cognos will then not treat these as facts and won't try to stitch.

[cschnu]

I've found that the cardinaluty of the relationships is really only important when you want to roll records up.  If this is something you need to do then look at adding determinants (check out the documentation for this).  If you don't need to roll records up just change the cardinality to be 1:1.  Cognos will then not treat these as facts and won't try to stitch.

Besides determinants and cardinality is there some type of setting in framework manager that determines the mulit-fact scenario you have outlined? I have the exact same problem outlined above and we are using a relational datasource.  I still need it to aggregate some fields in "table A" so is determinants the only route? We have other models that i didn't develop that have 1:n or 0:N relationships in them that seem to be working just fine.  Do all the columns that we are joining on need to be marked as "identifier"?

[blom0344]

Cognos will create 'stitch' queries - no matter what -  when 2 facts converge upon a dimensional subject  ( N --> 1 <-- N)  This does happen even when no determinants are set, though setting them for the dimensional subject is a good idea and mandatory if the grain of the facts is not the same (otherwise no correct results are returned)

Setting the usage to the proper type for columns that involve joins is a good idea, but the usage is there for other purposes. However the column that is used for a join would never be a fact. I doubt you will get other - wrong - results between attribute and identifier settings