Cognos FM selecting a different path to query data

[brandi]

Hi folks!

I have a question.

I have 2 paths to get to a dimension table.

Path A : Table 1 -- > Table 2 --> Table 3
Path B: Table 1 --> Table 4 --> Table 5 ---> Table 3

The query contains data from Table 1 and Table 3. Cognos is using Path A and it's not generating the right result.

We WANT Cognos to use Path B. Even if I remove the joins from Path A... cognos will not use that path B. It will actually do a full outer join and not do the where clause in the Sql at all.

If I query data from Table 1, Table 4, Table 5 and Table 3, it then works. It's almost like it needs query items from the "middle" dimensions in order to return the correct data.

What is the best way to force Cognos to down a path. Is it shortcuts?

Any help would be appreciated.

[blom0344]

First look at the setting of cardinalities. Cognos marks a query subject as fact/dimension based on the cardinalities set. It will always generate a full outer join if 2 facts converge upon a dimension. Basically which query subjects are what?

[brandi]

Thank you.

We d have 1.1-0.n relationships on 2 different tables. And from I can see, Cognos is treating this as a Multi Fact query based on that.

The issue is resolved when we pull from all the middle dimension tables as they are uniquely identifying the row. I understand that the cardinality is creating that fact dimension relationship.

The issue I have is obviously resolving AND the fact that this information is provided as a data structure for the business.  Anyone find ways to resolve this?

Thanks!

[cognostechie]

Disconnect Table3 from Table5. Create and alias shortcut for Table3 and join that to Table5.

That's how I would do it in your situation.

[brandi]

Thanks for the tip. Can you explain why an Alias shortcut would work and not just a regular join? Also do you have it as a reference?

[cognostechie]

Look at the picture in entirety. This is what it will look like -
                           
    |------------------------------------------------------------|
    |                                                                           |
Table 5 ----- Table 4 ----- Table 1  ---- Table 2 ---- Table 3
    |                                                                           |
    |------------------------------------------------------------|

See the ambiguity ? There is more than one way to reach from one point to another. This is called a 'Loop Join' (in Cognos official terminology a 'Transitive Trap').

Normally when you write a SQL by hand in the DB and you have to use the same table twice, you would use it by giving the same table name 2 diff aliases, correct? That's exactly what you will be doing by using an alias shortcut - telling Cognos to use the same table but by joining the alias to only one other table, not to two other tables at the same time. This is what it should look like -

Table 3 alias
    |
    |                                                                           
Table 5 ----- Table 4 ----- Table 1  ---- Table 2 ---- Table 3
                                                                               
This way, the granularity of the data does not mess up the query and the alias join will come into effect only if your report requires a column from that. So essentially, there will be two diff. model query subjects in the Business Layer, one using Table3 and another using the alias of Table3 and you will use only what is required in your report.

So the granularity in Table2 and Table 5 could be different but it will not mess up your query because cognos will still use a different join than using a common join so the results will be better.

This problem with Loop joins (going in a full circle) is a very common topic in Cognos world.
I would suggest, take a look at all 4 diff. traps in FM and how to resolve them. FM can change the data depending on how the moselling is done.