The Query & Analysis tools from SAP BusinessObjects – Desktop Intelligence and Web Intelligence – are very powerful when it comes to analyzing corporate information. They give you the ability to add complex conditions without the need to know SQL language.
A problem that may arise when generating your query is that, when defining conditions with sub queries, the complexity (an indicator of the performance that the query will have when transformed into SQL language) escalates causing your query to take an enormous amount of time to generate, or not to generate at all. Let’s look at the root causes of these facts and a workaround that can be used to make your analysis quicker.
The root causes for a low performance
Let’s consider the following example: We want to show a list of Contacts that accomplish a certain condition, but we also want to exclude two groups of contacts from that list. Let´s call this operation {A} – {B} – {C}
Our query will be based on the contact codes, all their necessary attribute objects and some conditions that restrict the query, in addition to other two restrictions: “Codes Not In List Codes that accomplish B” and “Codes Not In List Codes that accomplish C”
If the number of items in these restrictions is too high, the SQL engine cannot handle the whole request and result is query does not finish.
Alternatives to a sub query
In the example above, we can load the restriction codes in different queries or data providers. Then the main query can be modified so that the sub queries are turned into calls to data providers: “Codes Not In Query that accomplishes B” and “Codes Not In Query that accomplishes C”.
This solution has the advantage that the sub query condition is already physical and it is not involved in query calculation, therefore complexity is reduced. Unfortunately there is a limit to the number of items in the sub query that can be handled by the SAP BusinessObjects engine. A pop up message will inform you about this.
Another solution that actually works is to load the three lists (A, B, C) and do the exclusion {A} – {B} – {C} directly in a report using report filtering… more on this below.
The solution
The solution with the best performance is the one where we load the data that we need into three different queries or data providers, combine this information in one single table and apply some filtering directly in the report.
Advantages of this method are that performance is optimized, as every single query has got the lowest complexity. Moreover, it is more visual as we can double check with examples to make sure what we are doing is correct. The one disadvantage is that the workaround may be too technical for an end business user, as we are using dimension merging or linking and non intuitive complex filtering.
Find below a demonstration of the performance gain by analyzing the cost of queries using the Oracle explain plan from a real example:
Cost index using a single query containing two sub queries is 146,091
Cost index using three single queries shows a dramatic improvement. The cost is 7 times better (maximum cost = 6,334 x 3 = 19,000).
If you are interested in trying this solution, you can follow these steps:
- Load the three queries or data providers (including main codes) as dimensions
- Merge or link or the three queries or data providers via their main code only
- Make sure to load a detail object type that has a 1 to 1 relationship with the main code in every query. If it is not available in the universe, a detail variable can be created emulating this feature. We will call these variables “filtering flags”
- Create a table that contains the main code, its attributes and their filtering flags. Only the codes that are in the main table {A}, but are not in {B} or {C} are the ones that are useful for us.
Table with main codes and attributes
- 1. Add three filters in the tab / Report:
Flag A: Not Null / Flag B: Is Null / Flag C: Is Null
Table with three filters added
With this example we have described a method to replicate complex queries when the number of items in the restrictions is too high. If the number of items in the restrictions is reasonable, then we can continue with the standard method.
Hopefully this post has given you a good example of how the merging/ linking of queries/data providers works and has demonstrated that a good understanding of them will help as a work around for real challenges and support the flexibility of SAP BusinessObjects Query & Analysis tools.
