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.