Shortcut Join Problem - Revised

building semantic layer
1

X-cc: “Davis, Vicki L.” VICKI.L.DAVIS@usa.conoco.com

Thanks Luis and Robert for your responses however I think you misunderstood the problem so let me try to explain it again.

First of all the key that something is not working right was my point that if it is defined as a shortcut it is not included and I get a Cartesian product. If I simply uncheck the shortcut box then it includes the join and gives the proper result. Regular join - included in query no problem. Shortcut join - not included and Cartesian product.

Now I simply cannot understand regardless of how many joins, tables, contexts or whatever that you would get that behavior. If it is the ONLY join path to the table why would it work as a regular join but not as a shortcut?

So let me describe in more detail the Universe set up. Forget my first post.

Table A is joined to table B. Table A is joined to Table C. Table C is joined to table D. These are regular joins. Now a shortcut join is created from Table A to table D to bypass table C if data is not selected from that table. All of these joins are in a context.

Now I create a query and Include objects from A, B, and D. Since Table C was NOT included in the objects the shortcut join should have been used. I should have had A–B, A–D. However the SQL had A–B, A–C, C–D. I simply cannot get it to use A–D shortcut join.

No here is the strange part. If I choose items from A and D only (leave out B) then I get the A–D shortcut join in the query. I put and object from B back in and the A–D join goes away.

In order to force it to use it I actually deleted join C–D. Then the SQL had A–B, and a Cartesian to D. Where is A–D??

Next I changed A–D from a shortcut to a regular join a guess what? The SQL had A–B, A–D.

Help!

Greg Mills
Conoco Inc.
(580) 767-5597 ETN 442-5597
s-greg.mills@usa.conoco.com

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

2

Greg,

You wrote:

If I simply uncheck the shortcut box then it includes the join and gives the proper result. Regular join -
included
in query no problem. Shortcut join - not included and Cartesian product.
I know it looks silly but this behaviour is what I have come to expect of BO, the shortcut join will only be used by BO if the joins for which it is a shortcut are present in the context as well. Must be the way BO registrates the shortcut join is different from a regular join.

What you described with tables A-C-D and the shortcut A-D is what I must have used a thousand times, never had a problem.

This doesn’t help you out of your real problem though, therefore just a few thoughts:

  • Are you absolutely certain ALL joins you described are present
    in one context? You’re not missing one of the regular joins? B.t.w. Did you know shortcut joins are not included in ‘detected’ contexts? * Isn’t there another context present that is causing the problem?
  • Are you using the correct version of the universe?
  • Can you recreate this behaviour if you start the universe from
    scratch?

I certainly hope you will be able to get BO to do what it should.

Greetings,
Marianne Wagt-van Loenen
IDETA
The Netherlands

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

3

My understanding was that the shortcut join was only supposed to be used when there are TWO possible paths between two tables (one long, one short) and you want to force the query to take the short path. Any other use of the shortcut join would cause problems.

An example - Tables A, B, C, D, E
Valid joins exist between A & B, B & C, C & D, D & E and there just so happens to be fields in common between A & E as well.

If your create a regular join between A & E, in this case, you could produce a loop, depending on the cardinality. So a reguler join will likely cause problems.

But what if I want a query with results from A & E only? I don’t want the query to go through the other tables to get there. So I create a shortcut join between A & E. Shortcut joins are only noticed when they are one of two possible paths. Otherwise they seem to be ignored by BO. So in this case, if my query hits A & E only, shorcut join is used since it’s the quicker path. If the query hits A, C, & E, BO uses the long way join.

Note, this is a slightly different case then a Context. Context is two possible paths that should yeild different results when used properly. Like Reservations for one path, Sales for another (as in the example database). With a shortcut, I should get the same results both paths, one is faster and I’m telling BO which one is.

Note also the loop aspect. If I had used a regular join here, any query would have resulted in all five tables being used since BO did not know which was more valid. So a query with A & C only STILL would have used ALL JOINS. Thus slowing everything down again (and probably giving you the wrong answer to boot!).

In your case, after reviewing your second post, you ARE dealing with a context problem. From the stated set-up you have, you could/should have two contexts.

One context is A-B only, the other contains all the joins between A, C, and D (keep the shortcut in this case).

-OR-

Go with a Context for A, B, C (And D but only with the join to C), and a context for A, B, D. Have the A-D join be a regular join in this second context. This setup may cause the query to ask you which context to use in a A-D only query so it may not be the best solution.

Either way should solve the problem and speed up the query.

Shortcuts are very specialized. I’ve only found ONE case in two years of universe projects at multiple clients where it was warranted. Maybe this can give you a better perspective. Contact me directly if I can be of more help.

Hope that helps!
David Jelinek
DAVID.JELINEK@opc.com

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

4

Thanks everybody for the information. It all helps. Using that information and some further testing on my own, I have found some things out about how BusinessObjects is using the shortcuts, and I am going to share it in case someone finds it useful.

However first I wanted to address David and his very good post. I have to disagree that I have a context problem. I do not think I should have two contexts. These are all the same context and we were getting the same results either way. We want the shortcuts to eliminate unnecessary joins. Also the context includes more tables and joins but I limited the description for simplicity sake. We do not want the users prompted by building unnecessary contexts when it should be using the shortcut joins within the existing context. I thought that was the whole point of shortcut joins.

Believe it or not we have been able to solve the problem we had by re-ordering the shortcut joins in Designer! I have been able figure out that BusinessObjects is picking shortcut joins in a rather strange manner. It chooses the FIRST join path it encounters that contains a shortcut join, regardless whether that is the most direct path to the table or not. I think this is a flaw and it should choose the most direct path, but that is the way it seems to be working.

I wanted it to use the shortcut join A–D. It however was picking the two joins A–C, C–D. I couldn’t figure out why. It turns out that C–D was a shortcut join. C–D was listed in the joins BEFORE A–D. I deleted the C–D shortcut join and then rebuilt it and added it back to the context. It was now at the bottom of the list of joins. The shortcut join A–D was now found first and it was selected!

I did a lot of testing where there were many different possible join paths. It ALWAYS worked at that whatever join path that included a shortcut join it found FIRST, was the one selected. We have now been able to work out an ordering of the shortcut joins at the bottom of the list of joins that allows BusinessObjects to always select the minimum number of joins needed to satisfy the query. I don’t know if this will help out anyone else, but it might.

Thanks again everyone!

Greg Mills
Conoco Inc.
(580) 767-5597 ETN 442-5597
s-greg.mills@usa.conoco.com

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

5

Hi Greg,

There was on outstanding issue on your Shortcut Join problem which was the carthesian product. Did you solve that as well?

I know out of experience that if a table is only connected to a structure using a shortcut join (so ther’s no fixed join that connect’s it to any table) and this join is used in a query you get the carthesian product message. This is because BO somehow doesn’t see the shortcut join as a real join. The SQL generated and the query result however is perfectly OK. Therefore in my opinion it isn’t a carthesian product.

Thanks,

Jeroen Gerritsen

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