FM Shortcut Join Path

[c8guru]

All,

Do you know how FM determines the join path when there are multiple shortcuts from the same object that are joined to different query subjects?  For example, I have a Dealer shortcut that is joined to an Account query subject and another Dealer shortcut that is joined to an Account_Balance query subject.  The reason for this is that we want the join from Dealer to Account when no balances are chosen but we want the join from Dealer to Account Balance when a balance is chosen so it doesn't have to include the Account table.

When you include query items from all three query subjects, Cognos decides whether to join the Dealer table to the Account table or the Dealer table to the Account_Balance table.
In our case, it joins Dealer to the Account_Balance table every time so there must be a definitive method for why it chooses this join versus creating a join to the Account table.

Thanks in advance,

Steve

[MFGF]

Hi,

At a guess, it may be using the first alphabetical choice where the join path is ambiguous.  If you look at the names of the relationships, is this the case?

MF.

[c8guru]

MF,

You are correct, Cognos is determining the join by alpha sort.  I've tested a number of scenarios and determined that this is the case.

Steve

[MFGF]

Hi Steve,

Thanks for the update.  I guess now you need to revisit your modelling strategy and try to figure out a way to break apart the multiple join paths.  Not an easy task, but good luck!

Best regards,

MF.

[c8guru]

MF,

Yes, I updated our modeling strategy but ran into another snag.  When multiple joins are involved, for some reason C8 defaults one of the joins to the dimension table instead of the fact.  I can't figure out the methodology it uses to determine which relationship will be chosen to join to the Account table instead of the Account Balance table.  Any ideas?
Steve