Tuesday 13 March 2012

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.




23 comments:

  1. Its True and common issue solved accordingly as mentioned above...Gr8 explaintion

    ReplyDelete
  2. Hi,

    You still do not explain how you manage to put in he same table ... Purchase Dt and Sale Dt ...considering that these two dimensions cannot be synchronized.

    fabrice Vaux
    vax17@hotmail.com

    ReplyDelete
    Replies
    1. Hi,

      Using object purchase date or sale date in same table or block wont be allowed by webi(this will give incompatible or Data sync message).
      You have to use a workaround for the same.
      Try below steps:
      Use objects:
      ID,CNAME,Purchase Date,sale value and purchase value in block.
      This will give data for purchase value corresponding to purchase date and sale values will be repeating.
      Now create a detail object (report variable) using sale date and associate this with ID.
      Use this detail object in the block, you may see #multivalue message. Go to Properties and check the option "Avoid Duplicate Aggregation".
      Now you have the report with both purchase and sale date with there correct values.

      Thanks..

      Delete
    2. In the comments provided why cant we use purchase and sale date in same block? These are not coming from different data providers. They share the same.

      Delete
    3. Hi Gaurav,

      Great Explanation.
      Could you please help me in understanding the issue here.
      When multiple query for each context is set, why would incompatible error will come if I drag purchase dt and Sale dt?

      Thanks

      Delete
  3. Finally I got very good explanation after a lot of search..Thanks bro..

    ReplyDelete
  4. Really very good explanation....one of the complex concept explained in easy way....thanks gaurav......

    ReplyDelete
  5. Really very good explanation...very complex concept was explained in easy way with very good examples.... want to see more and more topics from you.....thanks gaurav

    ReplyDelete
  6. Hi Gaurav,

    am learning bo,
    for practicing , can you tell from which DB & datasource, this tables are used in universe, both for chasm and fan trap,

    your inputs will be really helpful,
    i understood the concept, practicing it makes more perfect,

    thanks in advance

    ReplyDelete
    Replies
    1. Hi,

      I created this sample data on SQL Server.
      You can also create the same either in MS Access or any other database.


      Thanks

      Delete
  7. thank you so much for the info..!!!

    ReplyDelete
  8. Awesome explanation !!

    ReplyDelete
  9. Excellent explaination

    ReplyDelete
  10. Firstly thank you for the amazing explanation. I have tried to implement with same data with IDT 4.1 SP1 and Webi Rich client 4.1 SP1,
    I got every result as you got in Query1, Query2 & Query3 but when i removed "Purchase Dt and Sale Dt" from Query3 I got the correct Result without applying any context of changing any settings which you mentioned. I've got result as shown in the Last screenshot. how is it possible? how it got resolved without creating any contexts?

    Please help me i'm confused.

    ReplyDelete
  11. And sorry i forgot to mention i'm using MS Access 2007 database.

    Thanks

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

    ReplyDelete
  13. In the comments provided why cant we use purchase and sale date in same block? These are not coming from different data providers. They share thr same.

    ReplyDelete
  14. great explanation

    ReplyDelete
  15. Thank you, it's another kinda charity..it's very clear instructions n can remember

    ReplyDelete