Find below the model relationship created in the FM. Now as per requirement we need to select columns from (Table1) and (Table5) . But at runtime we are seeing that report is not detecting the path mentioned below. It is creating full outer join between the two sets of records.
Set 1. Table 4 & 5
Set 2. Table 1 & 2
It is not detecting table T3 automatically.
But if we add any column from T3, then it is detecting perfect path mentioned below.
Please let us know what modification is required in FM so that report should detect the proper path without inclusion of T3 column in report. Ideally this report should not create full outer join it should create inner join between these 5 tables.
Table1 (1:1) -- (1:n) Table2 (1:n) -- (1:1) Table3 (1:1) -- (1:1) Table4 (1:1) -- (1:1)Table5
Path detection can be a very vexing issue to solve. Sometimes you just have to include something from an intermediate table in the query (does not have to appear on the report). Is that a problem, or you are concerned users will not understand this and thus produce incorrect reports?
One thing I noticed is that you have 1:1 joins to all but Table2. Is that correct? A little unusual, and might be throwing Cognos off as it uses cardinality to distinguish facts and dimensions.
Hi,
could it be that there is another path leading from table 1 to table 5 or maybe some direct connection between tables 2 and 4?
regards
Fabian