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.