Advanced Join Property

Advance join property in universe is present , when you click a join between any two tables and click on Advance button.

You need to have ANSI92, set to “Yes”, to enable this feature.

So, the question is, what exactly this feature do?

First of all let’s look what is the execution order of  a SQL statement:

When a query is submitted to the database, it is executed in the following order:

FROM clause

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

ORDER BY clause

So if I use my conditions in FROM Clause instead of WHERE Clause, it will be calculated first and can help in performance improvement.

Take for example in my universe:

If I select c_name object from customer table and sale_value from sales table and use c_name as a filter, the query will be: 

It can be seen that the filter condition is coming in WHERE clause.

The Advanced Join Property allows to the conditions to be used in “FROM” clause.

Again as I said previously, you need to have ANSI92=”Yes”  in your universe parameters for this to work.

Now we will set the advanced join conditions.

Click on the join between the two tables:

The drop down option says it what kind of behavior you want from objects selected in query filter.

Lets say I selected “Selected objects in FROM” in drop down and checked on C Name dimension object.

Now I will create the query again:

See how the filter which was coming in WHERE clause is coming in FROM clause.

I went through number of forums that tells how conditions in FROM clause helps in query performance compared to WHERE clause.

Although both types (i.e. filters in FROM clause and filters in where clause) generate the same results but there are scenarios specially when you use Left Outer joins in your query, that can lead to difference in results, which needs to be taken into consideration while using this option in your universe.

Fan Trap

Fan Trap in Business Objects

This is also a join path problem created in universe when below scenarios are met:

Consider table A, B and C.

• A   --<   B   --<   C   i.e.  A to B is “One to Many” and B to C is “One to Many”   AND
• Measures selected from Table B and any kind of object from Table C  AND
• Table B holds aggregated values from Table C.

This trap can also be created when we are having only two tables say B and C with “One to Many” relationship, where we are using dimension and measure(which is aggregation of a column in C) and an object from C table.

The results will be inflated like Cartesian product.

For eg.

I have three tables

These tables are connected in below manner: 

The relationship is something like this:

A customer say ‘Gaurav’ purchased a car and accessories. He paid total amount(car cost + accessories)=4000.

Now table Sales contain 4000 as the amount and sales detail captures the breakup of that amount i.e.(car=3000 and accessories = 1000). So Sales tables hold aggregated value from sales_detail table.

Classes and objects:

Now I will try to create a webi report using  objects:

Query 1

Id Sales	C Name	Sale Value
213	Gaurav	4,000

Correct result

Query2

Id Sales	C Name	Sale Value	Item Value
213	Gaurav	8,000	4,000

Incorrect result

Adding another object i.e. Item type from table C. 

C Name	Id Sales	Item Type	Item Value	Sale Value
Gaurav	213	Accessories	1,000	4,000
Gaurav	213	car	3,000	4,000

Observe that when we added Item Type to get the detail , item value is properly displayed but sale value still shows two rows with same value and hence when we remove the item_type, it gets aggregated and show 8000, which is a wrong value.

How to resolve this:

Again like Chasm trap you have to create two separate queries to display correct result.

1. Create two sql statements by checking on the option of “Multiple sql statements for each measure”.
2. Create alias and context.
3. Use aggregate awareness to avoid this scenario.

1. Setting the option in universe parameter:

This will generate 2 sql statements, if measures are selected from 2^nd and 3^rd table. 

C Name	Item Value	Sale Value
Gaurav	4,000	4,000

This gives correct results. But will not work in case you are selecting only dimension objects and not measure.

2. Using Aliases and Context.

We will now try to solve the trap using combination of aliases and context.

Create an alias of middle table , in our eg. It is Sales table.

Join Customer with alias table and set cardinalities of the same.

Now we will create two context lets say C1 and C2 like below.

Now we have to take the measure object from sales table i.e. sale_value from the aliased table rather than sales, so repoint the sales value object to point to sale_value column of sales2 table. 

Also check the below option in universe parameters: 

Now, if you use the measure objects from two different context in one query, it will generate multiple sql statements and the results will be correct. 

C Name	Sale Value	Item Value
Gaurav	4,000	4,000

Note:  There is another scenario also where this kind of trap can be created.

Lets say for example, in our structure of universe:

Lets say I ignore Customer table. 

Now If have objects like C_id(dimension Object) , Sale_value(measure) from sales table and item_value from sales_detail table in webi rich client query, it will again create a trap and will show inflated results.

C Id	Sale Value	Item Value
102	8,000	4,000

So it’s showing inflated results again i.e. wrong value of sale value.

To resolve this, we will create alias of sales table, same as above case.

Created two contexts C1 and C2.

Checked the option of ‘Multiple SQL for each contexts’ in universe parameters.

Change the sql of sale value object to point to sale_value column of alias table i.e. sales2.

Now, if you select the objects c_id, sale_value and item_value. It will display correct result.

Chasm Trap

Chasm Trap in Business Objects :

This is a join path problem that is created in a Universe.

The Chasm trap occurs when two “many to one” joins converge on a single table.

Like for the example below, where a customer can have multiple sales and also have multiple purchases.

Now below conditions need to be met for a trap to occur:

1. There should be “many to one to many” join relationship between the three tables.
2. There should be objects selected from the many end of join tables (For eg.  Objects from sales and purchase should be present in query)
3. And as the cardinality says there should be multiple values of Customer table present in sales and purchase table.(for eg. Customer 1  should have 2 or more rows of sales or 2 or more rows of purchases)

The trap causes inflation of results and you basically get a result which is more than the expected one(Like a Cartesian Product).

Lets first see, what exactly happens:

I have created three tables:

These tables are connected in the following way:

Below are the classes and objects created from this table list:

Query 1                                                                                      

Id	C Name	Purchase Value
101	Ashish	11,000
102	Gaurav	7,000

Correct Results

Query 2

Id	C Name	Sale Value
101	Ashish	6,000
102	Gaurav	4,000

                                                                              

Correct Results 

Query3

Id	C Name	Purchase Value	Sale Value
101	Ashish	33,000	12,000
102	Gaurav	7,000	4,000

Wrong Results

Note:

In Query 1 purchase value of Ashish is coming correctly; in Query2 sale value is coming correctly.

In Query 3 , I used both purchase value and sale value in one query and it can be seen that the results are inflated for Ashish.

But the result for Gaurav is still correct.

Now we will bring more objects in the query to see where exactly the problem is:

Query1                                                                               

Id	C Name	Purchase Dt	Purchase Value
101	Ashish	2010-01-10	5,000
101	Ashish	2010-02-10	6,000
102	Gaurav	2010-01-10	7,000

Query2

Id	C Name	Sale Dt	Sale Value
101	Ashish	2010-01-01	1,000
101	Ashish	2010-02-01	2,000
101	Ashish	2010-03-01	3,000
102	Gaurav	2010-01-01	4,000

Query3

Id	C Name	Purchase Dt	Sale Dt	Purchase Value	Sale Value
101	Ashish	2010-01-10	2010-01-01	5,000	1,000
101	Ashish	2010-01-10	2010-02-01	5,000	2,000
101	Ashish	2010-01-10	2010-03-01	5,000	3,000
101	Ashish	2010-02-10	2010-01-01	6,000	1,000
101	Ashish	2010-02-10	2010-02-01	6,000	2,000
101	Ashish	2010-02-10	2010-03-01	6,000	3,000
102	Gaurav	2010-01-10	2010-01-01	7,000	4,000

I included purchase dt and sale dt in query 1 and query2 respectively.

In query 3, I included both of them.

Now the difference can be visualized in Query 3 number of rows which is coming for Ashish is more than the expected one. Reason is it is created all possible combinations of sales dt and purchase dt that makes(2*3=6 rows) of data for Ashish. But for Gaurav data is coming correct because he has only one entry in both table so 1*1= 1 row.

So when we remove the purchase dt and sale dt from query 3, it aggregates and display wrong results.

Lets see how to overcome this scenario.

How to detect chasm trap.

•  Mostly by analysis. When you are creating the universe, you should look for join conditions(n:1:n) and definetly the inflation of results in reporting.
•  Detect Context in universe designer can help to analyse possible contexts, which says there could be a trap, which needs to be resolved.

Now if I select sale value and purchase value in same query, it will generate  below sql.

And the report data will be :

Id	C Name	Purchase Value	Sale Value
101	Ashish	11,000	6,000
102	Gaurav	7,000	4,000

This is absolutely correct.

Note: This option will not generate multiple sql for each measure , if you have not defined aggregate function in the measure definition.For eg.,

If the definition of measure is like above than it will not create two sql, even if option is checked.

We need to have the deifintion as:

Note: this method will also not work if we select dimension objects from many end of the query as two select will not be generated and will still display inflated results.

2. Another method which will resolve this issue is by Context, which is the most acceptable one.

    Create two context like below:

Also we need to click on the check box in parameters:

If you don’t click on this check box, using objects from purchase and sales in a single query will fetch”Incompatible objects” error.

By clicking on this check box, will give two queries for each context, both in case of measure or dimensions coming from many end of tables.

Id	C Name	Purchase Value	Sale Value
101	Ashish	11,000	6,000
102	Gaurav	7,000	4,000

This will resolve Chasm trap and will correct results in query.