Shortcut Join

building semantic layer
1

I have created a shortcut join between two tables by checking the ‘Shortcut join’ box on the Edit Join window. This created a dotted line between the tables on my data model.

When I create a query using objects from these two tables the generated SQL does not use the shortcut join. Instead, the SQL takes the long way around and contains joins for three other tables which are otherwise not needed.

I’ve spoke to Business Objects Tech Support but so far they cannot tell me why the shortcut join is not being used. Is there something else I need to do to get this to work?

Thanks.
Jane Kelly

Listserv Archives (BOB member since 2002-06-25)

2

I have successfully gotten shortcut joins to work the way you describe, but have run into the limitation of trying to use multiple shortcut joins. Essentially, only one shortcut join may be used for any given query. So, in your case, verify that you do indeed only have one shortcut join you are trying to utilize and only select objects from the two tables related via the shortcut join. If this is your case and it still doesn’t work, then I too am confused.

In my case, I attempted to create shortcut joins from several dimensional detail tables to a single fact table and selected objects from more than one dimension table and that does not work. Any single dimension table will utilize a single shortcut join, but once multiple shortcut joins potentially come into play, any use of them is abandoned. I’ve been told by tech support to not look for that upgrade any time soon.

HTH,

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 98-07-07 14:32:19 EDT, you write:

I have created a shortcut join between two tables by checking the
‘Shortcut join’ box on the Edit Join window. This created a dotted line between the tables on my data model.

When I create a query using objects from these two tables the generated SQL does not use the shortcut join. Instead, the SQL takes the long way around and contains joins for three other tables which are otherwise not needed.

I’ve spoke to Business Objects Tech Support but so far they cannot tell me why the shortcut join is not being used. Is there something else I need to do to get this to work?

Thanks.
Jane Kelly

Jane:

Here are a few things to check regarding BusObj’s refusal to use a shortcut join…

This may be an obvious thing, but it has happened to me before so I will ask: you are sure that you are using the updated universe (with the shortcut join) on the pc where you are creating the query? In other words, you did not forget to save and / or export the universe? As I said, this is probably not the problem.

Another issue that could be a problem is that somewhere along the line one of your objects is referencing more than one table, and that is forcing BusObj into the “long path”. The best way to test this is to create a new object from each table involved in the shortcut join and test your query with just those two objects. If your query now works, then one of the other objects you were trying to use is the culprit; check the “Tables” button for each object and you may find where this is the case. Alternatively, test your query by adding one object at a time and checking the SQL that is generated. Once the shortcut join is no longer used, the most recent object to be added is likely to be the candidate for the problem.

Is there a security setting in the supervisor module that is adding an additional table to your query?

I have not experimented with this nor actually seen this as a problem, but do you have cardinality information for all of your joins? I don’t know if that is supposed to affect short cut joins or not, but it is something else to check, perhaps.

Finally, does one of your other join clauses reference multiple (ie more than two) tables? If so, then that join may be forcing the “long path” instead of an object.

These are all things that came off the top of my head. If you can’t solve the problem, I suspect that you will need to post more information about your universe and object design to get more detailed help.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)

4

David Rathbun Integra Solutions schrieb:

In a message dated 98-07-07 14:32:19 EDT, you write:

I have created a shortcut join between two tables by checking the
‘Shortcut join’ box on the Edit Join window. This created a dotted line between the tables on my data model.

When I create a query using objects from these two tables the generated SQL does not use the shortcut join. Instead, the SQL takes the long way around and contains joins for three other tables which are otherwise not needed.

I’ve spoke to Business Objects Tech Support but so far they cannot tell me why the shortcut join is not being used. Is there something else I need to do to get this to work?

Thanks.
Jane Kelly

one more thing to consider:
Check, that you/your designer included the short-cut join into the corresponding context(s) (if there are contexts defined at all)!!! This is not done automatically (unfortunately) as it was in an earlier version of the designer with the “detect contexts-menue”…

Walter

DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)

5

Hi,

May be you ran into the same bug(?) I did with shortcut joins. I noticed that they are left out when you let BO come up with contexts. Alternatively, it could also be that you created the join later then the context or just forgot it when manually creating the contexts.

Anyway, check that the context your query will want to use contains the shortcut join.

Success!
Marianne Wagt
IDETA, the Netherlands

Listserv Archives (BOB member since 2002-06-25)

6

Hi All,

Can any body please explain me the Shortcut Join and how it works is possible with an example ? How it defers from the normal equality join ?

Thanks a lot
Sarat.

Listserv Archives (BOB member since 2002-06-25)

7

All,

I know I am asking too much from BO!

I have two fact tables F1 and F2. There is a join between F1 and F2 (Yeah Im crazy). I have two dimension tables D1 and D2.

Now I have following joins:

D1->F1, D2->F1, F1-F2

Since I have dimension keys copied over to F2 also, I have added following ShortCut joins.

D1->F2, D2->F2

Now This doesn’t work the way I expect. When I include only one dimension everything works fine. BO is chossing short-cut join when I don’t include objects from F1. But if include both dimensions, it is not using ShortCut joins. It is always going thru’ F1.

Is there any other way I can overcome this?

Thanks
Vasan

Listserv Archives (BOB member since 2002-06-25)

8

Don’t make them shortcut joins, contexts should be able to sort this out for you.

Ken.

Listserv Archives (BOB member since 2002-06-25)

9

Thanks Ken.

If I use context, when user selects objects from D1 and F1, there will be two possibilities. D1->F2->F1 and D1->F1. So BO will display a screen asking user to select context. We want to avoid that.

Any more ideas… please.

Thanks
Vasan

Listserv Archives (BOB member since 2002-06-25)

10

In a message dated 01-06-14 12:38:38 EDT, you write:

I have two fact tables F1 and F2. There is a join between F1 and F2 (Yeah Im
crazy). I have two dimension tables D1 and D2.

Now I have following joins:

D1->F1, D2->F1, F1-F2

Since I have dimension keys copied over to F2 also, I have added following ShortCut joins.

D1->F2, D2->F2

Now This doesn’t work the way I expect. When I include only one dimension everything works fine. BO is chossing short-cut join when I don’t include objects from F1. But if include both dimensions, it is not using ShortCut joins. It is always going thru’ F1.

Is there any other way I can overcome this?

I don’t remember if this is “official” or not, but I think that I remember finding out that only one shortcut join will be used per query. So since you are trying to use two shortcut joins at the same time, it won’t work.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

11

Hello,
1.Is there a limit on the number of shortcut joins used in a query?

2.There are three objects A,B and C. C is a combination of objects A and B. It is A/(A+B). For a query that selects all the three objects, BO generated three sql statements. Queries for objects A and B use the shortcut join while the query for C is not using the shortcut join. I need to define C in the universe, since user would use adhoc querying also.

Thanks in advance,
Anuradha

Listserv Archives (BOB member since 2002-06-25)

12

Yes, you only get one shortcut join.

-Durgesh

Listserv Archives (BOB member since 2002-06-25)