Monday 19 March 2012

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 2nd and 3rd 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.
  1. 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.






16 comments:

  1. Thanks.. very helpful

    ReplyDelete
  2. Great explanation with very easy example. Really helpfull cheers
    keep posting such concepts with such easy examples

    ReplyDelete
  3. Good Great explanation thanks

    ReplyDelete
  4. Hi Gaurav,very nice explanation,but I have one quick question.
    As we said,using Aggregate awareness we can resolve this issue.
    Can you pls explain in detail how can we use AA for resolving fan trap?
    in above case i want to show item type,item value and sale value objects from both sales and sales detail tables.

    ReplyDelete
  5. very nice explanation,
    can you please tell from which DB & data source (connection in universe) should we select tables for practicing it?

    customer --< sales --< sales detail are tables of which DB

    ReplyDelete
  6. Hi Gaurav,
    Very neately explained !
    Would be great if you could add an example of dimensional fan trap in this example.

    Thanks

    Regards,
    Shantanu

    ReplyDelete
  7. Awesome explanation !!

    ReplyDelete
  8. If we can resolve it using "Multiple SQL for each context" then why do we use aggregate awareness at all? obviously I am talking about the scenario in which user is dragging Measure as well.Any help appreciated.

    ReplyDelete
  9. Crystal Clear explanation. Thanks

    ReplyDelete
  10. Very Good. Thanks for the wonderfull explanation...!!

    ReplyDelete
  11. Good one.
    Please add more topics

    ReplyDelete
  12. Can we sole this using multiple data providers..?
    In first query
    CName-IdSales-SaleValue
    In second query
    CName-Idsales-ItemValue

    And Merge the queries on Cname and IDSales . Will it work???

    ReplyDelete
  13. Good explanation of the concept with examples.....Thank you Gaurav:)

    ReplyDelete
  14. Thanks a lot Gaurav:) Very nicely explained...

    ReplyDelete
  15. very nice...clear explanation.Please try to add more topics in BO so that it will helpful a lot for us.

    ReplyDelete
  16. Hi Gaurav,
    Great explanation. can you please explain how to resolve this using aggregate awareness.
    Thanks.

    ReplyDelete