Shortcut Join

[Former Member]

Hi,

I would like to know whether Shortcut join is required or not for the below table structure (Data Foundation):

I have joined the tables using equi-join.

There are more columns in each table and I am using the columns from all these tables in Business Layer to result in Webi report.

Should I join the table A and D for better performance? Can I join the other tables too like A and C, C and D?

Version: BO 4.1

Thanks in advance.

[Former Member · Accepted solution]

Hi,

Yes,  you can join the tables A----->D for better performance.

Suppose if we have four tables A, B, C & D.

A-B-C-D and there is a chance to A-D, in this case instead of linking A-D using normal join

We can link A and D by using short cut join and this will not create any loop and get the data in the path.

[Former Member]

You can join A - C and A - D if there are common columns to create the join.

Shortcut joins are evaluated after contexts to shorten the join path. So if you are using nothing from B in your query then the shortcut joins will kick in.

A good example in your situation would be that A is a Region Dimension and B is a Branch Dimension with C and D as two different fact tables. You would have two contexts, one containing joins A-B and B-C and the other containing joins A-B and B-D.

If you select objects/conditions from A C and D only then the shortcut joins will kick in - as soon as you take anything from B, then the shortcut join cannot be used.

Does that all make sense?

[Former Member]

Mark Prosser wrote:
...

If you select objects/conditions from A C and D only then the shortcut joins will kick in - as soon as you take anything from B, then the shortcut join cannot be used.

... 

Please explain this in more words.

Thank you.

[former_member4998]

Hi

If you joined short cut between the A –-> C and A –->D. and not selected any object from table B.

The query will use the short cut joins (A –-> C and A –->D) and skip the join B.

generates the query as below and fetch the data.

Select

Col1 TA

Col2 TC

Col3 TD

From

Table A TA

Table C TC

Table D TD

If you joined short cut between the A –-> C and A –-> D. and selected object from table B.

In this case shortcut join not  (A –-> D)used ….query use A to B and B to D

Below is the sample code.

Select

Col1 TA

Col2 TB

Col3 TD

From

Table A TA

Table B TB

Table D TD

[Former Member]

Say you have a chain of three tables, A, B and C that connect in that order, so your diagram looks like this:

A -< B -< C

There must be a key column on C that allows you to create a shortcut join of A -< C

So, A is Region Dim, B is Store Dim and C is Sales Fact.

If I want to know sales by region, I would pull objects from A and C.

As I have not pulled any objects from B, BO will change the join path to use the shortcut join.

As soon as you use anything from B, either as a condition or a result, then you MUST go through B.

A shortcut join is not needed, it is a tool that can be used if you can use it.

[former_member4998]

Hi

You can join Table A to Table D (If both the tables have common field) same as other tables.

Shortcut join act as normal join and not cause the loop. and it is a join that joins tables (A and D) by bypassing middle table (D) that exist in the universe.

Normally shortcut joins are used to generate more efficient query by reducing the joins in the query and improves the performance of the query by not taking into account intermediate tables.

Please find the below link for more info

http://www.dagira.com/2010/05/27/everything-about-shortcut-joins/

http://www.dagira.com/2010/05/27/everything-about-shortcut-joins/3/