Shortcut join

building semantic layer
1

In my Oracle universe I have four tables A, B, C and D. Normal joins A-B, B-C, C-D, and shortcut join D-A.

If I make a query 1 with the two objects:
D.column and A.column,
the shortcut join D-A appears in the where clause as expected.

If I make a query 2 with the three objects:
C. column, D.column and A.column,
the joins A-B, B-C and C-D appear, instead of C-D and D-A that I thought would be used. Am I wrong in thinking that it should have used my shortcut join to avoid using the extra join in the path?

(If I try and tweak it by changing the A-B join to also being a shortcut join, to see if that could make query 2 take the path I wanted, these three objects:
C. column, D.column and A.column,
give me the joins C-D and thus return a Cartesian product.)

Do I misunderstand the concept of shortcut joins, is this intended functionality?

FYI: no contexts here, using Oracle and BOXI 3.1 SP6.
Parameter SHORTCUT_BEHAVIOR = Successive

ebeth (BOB member since 2012-10-31)

2

Shortcut joins will be used if they eliminate joins. In you first scenario, the base SQL would be A-B, B-C, C-D. There are no result objects from B or C, so those tables can be eliminated from the query path, meaning the shortcut from A-D can then be used because all of the extra joins can be removed (A-B, B-C, and C-D).

In your second scenario you have objects from A, C, and D. In this case, the join from C-D cannot be dropped because you reference objects from table C. In this case, since all of the affected joins cannot be dropped, the shortcut is not a candidate.

I wrote a blog post several years ago that is all about shortcuts. I’m not sure I covered the specific scenario (and if not it would be a good follow-up) but you can read it here:

Everything About Shortcut Joins

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Thank you so much, Dave!

I had actually already read your post, but still felt (and feel) a bit confused. In my scenario 2, table B would be eliminated if the shortcut join was used, that’s why I expected the query to take the shortcut path. But you are saying that since all the joins from the normal join path wouldn’t be dropped, none will be dropped?

ebeth (BOB member since 2012-10-31)

4

All of the joins covered by the shortcut, yes. In the scenario you outlined, you have to be able to drop both B and C in order to use the shortcut, not just one.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Thank you and happy Christmas :slight_smile:

ebeth (BOB member since 2012-10-31)

6

Hello Dave,

I have a scenario in which there are following 4 loops in total as shown in screenshot below.

  1. DIM_BILLING → DIM_ACCT → FACT_TRRANSACTION → DIM_BILLING
  2. DIM_SUBSCRIPTION → FACT_TRANSACTION → DIM_FUND_CD → DIM_SUBSCRIPTION
  3. DIM_SUBSCRIPTION → DIM_ACCT → FACT_TRANSACTION → DIM_SUBSCRIPTION
  4. DIM_SUBSCRIPTION → DIM_ACCT → FACT_TRANSACTION-> DIM_FUND_CD-> DIM_SUBSCRIPTION

I am thinking can I resolve the loops using shortcut joins in any case?
Please help me.

Thanks ,

Preeti
Help.PNG

preeti_trp (BOB member since 2018-07-12)

7

These look like good candidates for shortcut joins:
fact_transaction - dim_subscription
fact_transaction - dim_billing

I assume dim_acct - dim_subscription would be a shortcut, but I can’t confirm without knowing more about the data.

joepeters :us: (BOB member since 2002-08-29)

8

Shortcut joins are not a loop resolution method. If your universe is not working before shortcuts, it won’t be working after either. :slight_smile: They’re only added as a performance measure.

If you haven’t read my blog post linked earlier, it might help. Good luck!

Dave Rathbun :us: (BOB member since 2002-06-06)

9

I would suggest that you need to resolve your loops with aliases or simply break some joins in this situation.

Why is DIM_BILLING joined to DIM_ACCT as well as FACT_TRANSACTION? What does that mean?

Why is DIM_SUBSCRIPTION joined to DIM_ACCT and to DIM_FUND_CD and directly to FACT_TRANSACTION?

I think a better understanding of the data model is needed before we can suggest an appropriate solution.