I have a situation in which a shortcut join is behaving very strangely. The bottom line is that it won’t use it. It always uses other joins involving other tables NOT in the query. In trying to isolate the problem I deleted all of these joins so the only join path to my table was the shortcut join. The query warned me of a Cartesian product. It simply won’t use it. So I went to Designer and unchecked the shortcut box to turn it into a regular join. Then everything works fine. I change it back to shortcut and it won’t use it again.
The query involves three tables A, B, C. There is a regular join between A and B. The shortcut join is between A and C. There is no join between B and C.
The funny thing is if I just pick objects from A and C it uses the shortcut join just fine. However when I add objects from table B it removes the join between A and C. I must turn it into a regular join to get it to work.
It sounds like the shortcut join is behaving properly.
When you have two joins between three tables (A–B–C), the purpose of the shortcut join is to use the shortcut between tables A and C when you’re not selecting anything from table B. However, if you are selecting from all three tables, there’s no reason why the shortcut needs to be used, since both joins must be used for the query.
Also remember that shortcut joins are not designed to replace regular joins.
Your join is failing for these two reasons.
Regards,
Luis Gonzalez
From: Mills, S. Greg [SMTP:S-GREG.MILLS@USA.CONOCO.COM]
I have a situation in which a shortcut join is behaving very strangely. The bottom line is that it won’t use it. It always uses other joins involving other tables NOT in the query. In trying to isolate the problem I deleted all of these joins so the only join path to my table was the shortcut join. The query warned me of a Cartesian product. It simply won’t use it. So I went to Designer and unchecked the shortcut box to turn it into a regular join. Then everything works fine. I change it back to shortcut and it won’t use it again.
The query involves three tables A, B, C. There is a regular join between A and B. The shortcut join is between A and C. There is no join between B and C.
The funny thing is if I just pick objects from A and C it uses the shortcut join just fine. However when I add objects from table B it removes the join between A and C. I must turn it into a regular join to get it to work.
Thanks for the recap on the Shortcut join. The shortcut join is working correctly in his scenario. I think the underlying problem came from the start of his description…: The query was using joins in tables NOT in the query.
This is most likely caused by a context that was not correctly defined. When Business Objects generates the query, it checks the following: 1) Is there a loop?
2) Is there a context?
3) What joins are used for the context?
4) What joins are needed for these two tables to be relational…? 5) What shortcut joins can replace existing joins?
The query was failing (due to a cartesian product) from the loops. The query brought in some unnecessary joins due to the definition of the context.
Just check to make sure that you are not using the tables A-B-C in a context, then your joins should work when they are all properly defined. They should only really be used in a context if your universe has LOOPs.
It’s worth remembering, if you have any plans to commit to using Shortcut Joins in your universe design that BO will not use more than one shortcut join in any given query. I understand that this is the way it was designed, for one reason or another and therefore does not qualify as a bug. I wonder whether someone from BO can throw in a suggestion as to when this may be supported?
A quick example:
We have 3 fact tables in what is effectively a hierachy which share common lookup tables. When aggregate awareness descends the levels it should use the defined shortcut joins so that the fact table at the top of the hierachy (the one with REAL joins defined) is no longer required.
This unfortunately, is not what happens and query performance in these cases suffer as a result.
Our only solution so far is to ensure the joins between the fact tables are optimised in the database, and that they are well indexed.
Thought it may be worth highlighting!
Phil Morris
Analyst Programmer
BITS dept.
Tarmac Heavy Building Materials UK Ltd.
PO Box 8
Ettingshall
Wolverhampton
West Mids
WV4 6JP