Pros and cons of shortcut join in universe?

[vijaymuniraj]

Hi,

I have a scenario like I have a universe tables which has joins like A->B->C-> D and also there is join b/w A->C, now to avoid loop i have created shortcut joins b/w A->C. now my question is, If I select a table A,C and D, then which path it will take to generate the result?

and what is advantage and disadvantage of shortcut join?

Looking for your valuable answers.

Thanks

[Former Member · Accepted solution]

It's not about pros and cons.

Aliases and contexts resolve loops, shortcut joins shorten join paths.

If you have Region -< Area -< Branch -< Sales Fact and have Region Key in Sales Fact then you can create a shortcut join from Region to Sales Fact.

If you select Region Name, Area Name and Sales Value, you'll use the original (long) join path but if you remove Area Name then BO will use the shortcut join. It evaluates whether it needs anything from the tables that you are looking to shortcut and if you don't, it uses the shortcut join. This is done after it has decided which context to use.

So in your instance, if you have Region, Area, Branch and Sales as A, B, C and D, then yes, a shortcut join is appropriate. One key point to it is understanding where your measures are sourced from. Another is understanding why you would look at joining A to B and A to C. If A is being used for two different purposes then it should be aliased.

For example, if A was Manager and you had Manager, Area, Branch and Sales Fact and the Manager table held all Area and Branch managers, then you'd create aliases for Area Manager and Branch Manager. You'd then have Area Manager -< Area -< Branch -< Sales Fact and Branch Manager -< Branch -< Sales Fact. All joins would be part of the Sales Fact context.

If your measures come from more than just table D, let's discuss other solutions that would be appropriate for your situation.

Does that make sense?

[Former Member]

short cut joins are not meant to work as loop resolution.

so in your case I would go for an alias for loop resolution.

advantage of short cut join is that it will skip tables and therefore you will have better performance.