May 27 2010
Everything About Shortcut Joins
Global Versus Successive Shortcut Behavior
I mentioned earlier that a shortcut join will only be used if it eliminates tables. The default behavior is to examine one shortcut at a time. In my snowflake schema this is a problem because I need to take a more global view. Here’s what is happening.
First the standard query path is selected based on the objects in my query panel.
Next the query pathfinder evaluates the first shortcut join from state to fact. If it used that join, it would be able to eliminate the outlook table from the query. The problem is, the outlet table is required to join from the region table to the fact table, and since I can’t eliminate the outlet table the state shortcut join is not used.
Finally the query pathfinder evaluates the second shortcut join from region to fact. If it were able to use that join, it could eliminate the outlet table… but the outlet table is required for the state to fact relationship. See what is happening here? If the shortcuts are evaluated one at a time (the parameter value is Successive) neither will be used. However, if the pathfinder were allowed to look at both shortcuts at the same time, it would be able to realize that the outlet table can truly be dropped.
In order to have this happen, I need to open my universe parameters list, find the SHORTCUT_BEHAVIOR entry, and change it. Here is what the default value is.
Note that the help text for this entry incorrectly states that Global is the default in some earlier versions. In 3.1 the help file is correct. Successive is the default value.
Once I change the parameter value from Successive to Global I can regenerate the SQL for my earlier query and see both shortcut joins being used.
SELECT REGION_LOOKUP.REGION_NAME, STATE_LOOKUP.STATE, sum(SHOP_FACTS.Amount_sold) FROM STATE_LOOKUP, SHOP_FACTS, REGION_LOOKUP WHERE ( STATE_LOOKUP.STATE_ID=SHOP_FACTS.STATE_ID ) AND ( REGION_LOOKUP.REGION_ID=SHOP_FACTS.REGION_ID ) GROUP BY REGION_LOOKUP.REGION_NAME, STATE_LOOKUP.STATE
Now I have a query where two shortcuts are used at the same time.
What About Contexts?
The general rule is that if I have contexts in my universe, each and every join has to exist in at least one context. This is true of normal joins, but not with shortcuts. Remember that shortcuts are used only after a context has already been selected. Until I have selected a context, I won’t know which tables are going to appear in the FROM clause. And until I know which tables appear in the FROM clause, I don’t have any idea which tables can be dropped via the application of shortcuts.
Conclusion
My intent with this post was to show examples of each of these statements:
- Shortcut joins do not provide an alternate path.
- Shortcut joins do provide a shorter path.
And also to clarify what I mean when I make these two statements:
- A shortcut join will only be used if it eliminates tables from the query.
- A shortcut join is applied after the SQL has been generated (meaning after a context selection has been made, if required).
Hopefully I have done that. I can summarize everything by stating the following:
Shortcut joins will never fix a universe, but they can improve the universe.
In other words, before you add any shortcut joins to your universe make sure everything is working correctly first. Then you can look for opportunities to add shortcut joins to improve performance. If you have a snowflake schema with multiple shortcut joins you may need to change the SHORTCUT_BEHAVIOR parameter as mentioned earlier. Just don’t try to fix universe problems by adding shortcuts; it won’t work. 🙂
As always, there is the comment form at the end of the post for you to ask questions related to this post.
Great post, Dave, keep the good work up, please!
That was a very informative post. Thanks
Hi Dave, thanks for this wonderful post, very useful.
Thanks, folks, this one took a while to pull together, so I’m glad y’all appreciate it.
Thanks Dave!!
Its pretty informative.
Dave, there is never a post from you we don’t appreciate
Great post, I must say that I’ve always found it a bit sketchy working out if a shortcut join would actually be used though from your post I think I have been using them correctly so far. In this case, would the join you’ve demonstrated be a good way to help get a cascading prompt for Showroom by country without having to have a second context?
Dave,
Great post and excellent work. After reading this post one can clearly understand the difference between normal and shortcut join.
You blog makes me interested in BO career.
Thanks
Great post..keep up the good work!!
Great Post, DAVE !!
Thanks for sharing..
Can you please cover Contexts and Aliases also with such details?
Thanks
Nirav
Nniraw, there are a number of posts already available on my blog that cover contexts and aliases. The most summarized version is Context Versus Alias Overview which was part of a presentation from 2008.
Great post Dave. Very informative.
Great post Dave.
But If in your query you only need something from Country, Showroom and Sales then shortcut join does give you the information you need. If you want to include client then its will take the longer path. Doesn’t it also depends how you define your query?
Also is it a good idea to join dimension tables with shortcut join all the time?
Thanks in advance.
Hi Dave,
Perfect post …Wish to be your Student …
Regards
Prashant
Thanks !! Thanks !! for such a wonderful post
Regards,
Vrushank
Hi Dave,
I deeply appreciate the amount of painstaking efforts that you put to make each of your posts so thoroughly detailed and yet the easiest to undestand.
Many Many Thanks!!
Rig 🙂
Thanks Dave.
Fantastic Work Dave!!.. I am delighted the way you explain the concepts… Hats off to you… Please keep continuing… Do explain the concepts of incompatibilitis… when it comes into picture… what to keep in mind when objects are incompatible..etc
Thanks a ton!!
Prashant
Dave. Could you direct me where to find a copy of the Prestige Motors database and maybe the universe you base your example??
Thank you.
Prestige Motors is (or at least was) used for the Universe Designer training course. The course materials come with the database so that you can work on the problems at home later.
believe it or not I am taking the eLearning version of that course now (BOU315), but I can’t get access to the materials. I did sent an email to their help desk and the person answering said that she did not know where to get this DB or universe file. Would you know of anyone at SAP BI that I could ask for the materials.
I expect it would need to be someone in the education or training department. I don’t have any experience with the eLearning versions of these courses.
Try contacting: Training@SAP.Com directly
Training@SAP.Com did not work.
I called their support number and they said that if I was to get their instructor based class then I would get the DB materials.
I don’t get what is the big deal about this freaking DB. Especially when the course (BOU315) cost $1300. The other courses I got from them work with the eFashion DB that was not a problem to find.
Anyway thanks for your help.
Great Post. I am an Business Objects administrator and I dont understand anything about Universe Development. By going through your post I am getting to understand the concepts of Universe Design.
Great Post, Dave! Thank you!!
It seems odd to have Successive as the default for this setting. I have a similar situation to the example shown and this must be common.
I also found that the choice of which join is the shortcut can also change the behaviour. It seems that the standard join path needs to follow the hierarchy through successive 1-N joins. In the example the standard joins could have been set from the fact table to region, state and outlet with shortcuts from outlet to region and state. This leads BO to choose the ‘wrong’ joins and include the fact table when you just need a list of outlets.
Matt, your scenario should never occur. My general rule is that a universe should work perfectly before any shortcuts are added. That means the join path you described should never occur because the universe doesn’t workout the join from outlet to region.
The reason for the setting is at least in theory to avoid cartesian product joins. However I tend to agree that the choice is unfortunate.
Thanks for highlighting the cartesian problem. I found that the Global setting was giving unwanted (and unexpected) cartesians so I have gone back to the Successive setting and introduced a RegionState entity and then joined that to the Outlet and ShopFacts table. The join to the ShopFacts becomes the shortcut join and everything works as required.
Hi Dave,
Would it be possible to share a copy of the Prestige Motors database as well please?
Thanks,
John
I cannot share the Motors database as it’s used (or formerly was used) for the Designer training course. If you attended the course (or taught the course as I did) then you got a copy of the database.
I just happened to use it to demonstrate this particular concept.
Very informative and knowledgeable post. Thanks Dave keep up the good work.
What concerns me more is that you have the fields available to create a shortcut on Client. By denormalising this field you are now relying on application logic or db constraints to ensure that Client.CountryID matches Region.CountryID where Client.RegionID = Region.RegionID. If this referential integrity is broken, then the shortcut will give you the wrong (or at least different because who is to say it is wrong..) results to the ‘proper’ join How would the universe determine whether the shortcut changes the actual results set rather than just eliminating intermediate tables. I suppose the Fault – in the most generous meaning of the word – would lie with the universe designer for either not clarifying the reasons and constraints on the denormalisation or not providing aliases for the country table (1 for use with client and 1 for use with showrooms).
As an aside, using the original joins, it would be possible to identify clients who have purchased vehicles from outside their country, an acitivity which is quite common in Europe as different tax rates apply.
very informative post.
my doubt being:
what is the use of short cut joins?
will there be any performance improvement if short cut joins used?
how to say the query/webi to use the short cut join?
when do we need to use short cut join?
regards,
swetha
I think most of those questions are already answered in the post, are they not? Certainly the first one is. The improvement comes from the fact that fewer tables are included in the query so the database does less I/O. You as a user don’t force Webi to use a shortcut join, it makes that determination automatically. And you never need to use a shortcut join. You should always be able to get the answer going the longer route, a shortcut is just for convenience and performance.
Hey Dave I am currently working on this universe and was wondering if there is another book for webi based on the same database so I can have better understanding of the report since my universe is based on the motors logic. If there is plz refer .
The Motors database comes with (or used to) the standard SAP training material. It’s not likely that there will be any 3rd party books that use this universe since they don’t have the rights to republish it.
Hi Dave,
This post defines the perfect ways to the concept of shortcut joins . It amazingly defined and expalined to be remembered always.
Very elaborate information. Showing a good example where a user can understand very easily. Simply U R Gr8.
Hi Dave,
I tried to implement the shortcut join in Island Resorts universe between the Sales and Invoice_Line tables but to no avail. Query generator is not recognizing the (only)shortcut join and several tables are being included in the query, even though i have taken only two objects Year and Number of guests. I had also removed the additional tables included in the object properties.
Please resolve my issue.
The question is, why are you trying to use a shortcut between Sales and Invoice Line? That join is not a candidate for a shortcut.
Hi Dave,
To get the Number of guests based on Year dimension.
Year Number of guests
FY2004 10,947
FY2005 11,364
FY2006 12,432
The query generated for the above result is:
SELECT
‘FY’+Format(Sales.invoice_date,’YYYY’),
sum(Invoice_Line.nb_guests)
FROM
Sales,
Invoice_Line,
Service_Line,
Service,
Reservation_Line,
Reservations,
Customer
WHERE
( Customer.cust_id=Sales.cust_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Service.sl_id=Service_Line.sl_id )
AND ( Customer.cust_id=Reservations.cust_id )
AND ( Reservation_Line.res_id=Reservations.res_id )
AND ( Reservation_Line.service_id=Service.service_id )
AND ( Service_Line.service_line = ‘Accommodation’ )
GROUP BY
‘FY’+Format(Sales.invoice_date,’YYYY’)
so my question is that, can’t we remove the intermediary tables from the above query using shortcut join.
Your query is using both sides of the loop (invoices and reservations). There is something wrong with your universe.
what is impact if I use shortcut joins every where in universe instead of normal join?
Simple answer: don’t do it. 🙂 It’s certainly not an appropriate design technique, and I have no idea how the SQL would be generated.
Hi Dave… Very useful post.
I understand that every join must exist in at least one context. Do we also put short cut joins in contexts or leave them as neutral join (I am considering IDT in stead of Designer)?
I have not thoroughly tested this, but my general practice from UDT was to leave shortcut joins out of a context. Shortcut selection should not result in a different context as it could then generate an infinite loop…
Gracefully explained!!