Chasm and Fan Trap
How to solve Fan Trap in Business Objects Universe
However having one-many-many relation in universe does not always cause fan trap. You get fan trap issue only when reporting query have
- There is a measure object based on middle table – Table B
- Measure object from last table. – Table C
- Table B (Middle table) holds values which is aggregate of values from ,Table C
e.g. Consider following example.
It satisfies the first condition of Fan trap i.e 1-N-N relationship between three tables.
Now if user creates a query to see how many models were sold by customer Sham and show his sale quantity and sale total.
Query will include following object.
CustName, SaleTotal, ModelId, Sale Quanity
which would certainly give a wrong result as below due to existing fan trap issue.
If you could observe, Sham has sold total two models and its sale total is 100, however in above report it would come as 200 as for each instance of model sale total is repeated in report.
So what’s really happening in Fan Trap
When you run the query with following objects
CustName, SaleTotal and SaleQty, measures are correctly aggregated, However when also want to know modelid, you get saletotal for every model which makes result looks inflated.
There is now way can detect Fan Trap automatically, you would need to visually analyze the relationship between table and result in report.
How to solve Fan Trap
There are three ways to resolve fan trap.
- Changing the universe parameters to generate different SQL for each measure.
This method works only for measure objects. Though it works to solve CHASM trap and Fan trap it’s not recommended.
- Use combination of alias and context based on scenario in Fan traps.
Scenario 1
- When three tables are joined by 1-many relationship
- Report query contains dimension from first table and measure from rest of the two tables.
Solution
1. Create an alias for a middle table which is at many ends.
2. Create a join between new alias table and first table which is at one end.
3. Set the cardinality and set context
4. Change the SELECT of SALE_TOTAL object to refer to alias table.
`
Now if you create a create query by implementing above solution you would see two queries for each measure and in turn result gets merged at the report cube level. This should solve the fan trap.
Scenario 2
- When two tables are joined by 1-many relationship
- Report query contains dimension and measure from first table and measurefrom last table.
Solution
- Create an alias for table A
- Crate join from the alias Ax to table A and set cardinalities
- Set context B and A
- Edit object Y so that it refers to columns in the alias Ax instead of table A
How to Avoid Fan Trap
While designing a universe you can avoid the Fan trap altogether by creating measurefrom the table which is at the end table of 1-M-M relationship however it also depends on if you have those aggregate columns present in end table.
SQL Traps in Business Objects Universe : How to Solve CHASM Trap
A Chasm trap is a join path type problem between three tables where two many-to-one join path converge on a single table and there is no context to separate the converging path.
However even if we have above type of joins in universe we experience chasm trap problem only when
- There is many-to-one-to-many relationship between three tables
- Reporting query have objects on tables from many end
- There is more the one value for a single dimensional value.
Let’s see it in detail. Consider below diagram.
Now in above case when a query includes object from table B and Table C and objects from table A, the CHASM trap causes a query to return every possible combination of one measure with other. This result gets multiplied by number of rows in result set and output is similar like a Cartesian product.
This CHASM trap can be resolved by executing separate query for each measure and then merging the results.
How to detect CHASM trap in a universe
CHASM trap can be detected automatically; you would need to use multiple ways to identify a possible CHASM trap issue.
- Make sure you arrange one-to-many table from left to right in universe and analyze one-to-many relationship to detect possible CHASM trap issue.
- Use detect context automatic tool to detect possible context in universe and use them in order to avoid CHASM traps.
- Test many-to-one tables by creating reports using object from table at many end. Try adding additional dimension object in report. If there is a CHASM trap aggregated values will be double which might help you to detect possible CHASM trap.
Lets see a practical CHASM trap example in a universe
Let consider a following joins in universe. In following diagram three tables are joined by many-to-one-to-many join relationship.
If I want to see number of guest for a sports service, report returns following result
| Service | Number of Guests |
| Sports | 145 |
If I want to see number of future guest for a sports service, repot returns following result
| Service | Number of Future Guests |
| Sports | 8 |
However If I include moth the measure together in the same query.
| Service | Number of Guests | Number of Future Guests |
| Sports | 188 | 96 |
Result seems to be inflated due to CHASM trap issue.
How does CHASM trap inflate the result of a query?
CHASM trap causes query to return every possible combination of a measure with other measure, which makes query to return Cartesian product result and since result is grouped against single dimension value its gets aggregated.
In above example
- Number of guests transactions *Number of future guest transactions
- Number of future guest transactions*Number of guests transactions
Lets go deeper to understand what has happened.
In order to examine what all rows are included in aggregation we would need to split the aggregated data into granular level.
For “Number of Guest’ we would include additional dimension “Days Billed” to see granular data.
| Service | Number of Guests | Days Billed |
| Sports | 4 | 3 |
| Sports | 133 | 4 |
| Sports | 8 | 6 |
For “Number of Future Guest’ we would include additional dimension “Days Reserved” to see granular data.
| Service | Number of Future Guests | Days Billed |
| Sports | 7 | 1 |
| Sports | 1 | 2 |
Now lets combine the result (Disable generate separate query for measure fromuniverse parameters)
| Service | Days Billed | Number of Guests | Days Reserved | No. of Future Guests |
| Sports | 3 | 4 | 1 | 3 |
| Sports | 3 | 4 | 2 | 1 |
| 4 | 129 | 1 | 75 | |
| 4 | 35 | 2 | 9 | |
| 6 | 8 | 1 | 6 | |
| 6 | 8 | 2 | 2 | |
| Sum | 188 | Sum | 96 |
You could see, query returns every possible combination of future guests with numberof guests and when result is aggregated it gives wrong numbers.
How to solve CHASM Trap?
You can solve CHASM trap using context. In above example you can create context.
- Analyze many-to-one-many relationship to detect possible CHASM trap.
- Use Detect Context to create contexts
- Select the contexts and click on Add.
- Select File->Parameters to launch universe parameter box.
- Click SQL Tab
- Select Multiple SQL for each contexts option.
- Click OK
Now when you create query, two separate queries will be generated and result will bemerged on common dimension. This is how CHASM trap gets resolved using contexts.
Using “Multiple Statement for Measure” to solve CHASM trap.
If you have only measure objects defined for both fact tables, then you can use theUniverse Parameters option Multiple SQL statements for each measure. This force the generation of separate SQL queries for each measure that appears in the Query pane.
This solution does not work for dimension and detail objects.
0 comments:
Post a Comment