It’s all in the universe – handling chasm and fan traps

Recently I worked on an assignment where the Business Objects universe was designed by a business analyst. Nothing wrong with that, as long as we get the correct results in reports or queries on the universe. But it was not to be.
We encountered incorrect results primarily due to two reasons:
1. Mixing of grains in fact tables by not using proper contexts or the aggregate navigator
2. Unresolved chasm traps
It was obvious that the business analyst was not trained in data modeling, nor did the universe go through a proper QA cycle where it is reviewed for data modeling errors.

What is a chasm trap?

The Chasm trap occurs when 2 “many-to-one” joins converge on a single table, which has not been resolved by any context. The most common problem caused by a chasm trap is fetching more data than expected.
Below is an example showing this many-to-one-to-many relationship:

Chasm Trap
How does a chasm trap inflate results?
The chasm trap causes the query to return every possible combination of rows for one measure (fact table 1) with every possible combination of rows for the other measure (fact table 2), thus effectively creating a Cartesian product. Under normal circumstances we would receive as many records as fetched from the tables. However due to the way fact tables are designed, measures have aggregate functions like SUM() or COUNT() applied on them, and this leads to an aggregated and inflated value for either measure.

Solution
The best way to resolve a Chasm Trap is to define a context for each fact table at the “many” end of the joins. This creates two SQL statements and two separate microcubes which Business Objects synchronize to return the correct output.

Another way to resolve a Chasm Trap which aplies only to measure type of objects is to select the option “Multiple SQL Statements for Each Measure” from the Universe Parameters dialog box. However this does not generate multiple SQL statements for dimensions and doesn’t resolve chasm traps in such cases.

Apart from chasm traps and the very familiar loops, there is another possible join problem encountered while designing Business Objects universes – the fan trap. This is however much infrequent compared to the other two.

What is a fan trap?
A Fan Trap is a join between 3 tables where a one-to-many join links the first table to the second and another one-to-many join links the second table to the third. Inflated results are obtained when fields from all 3 tables are included in the query.
Below is an example:

Fan Trap

How does a fan trap inflate results?

A fan trap “fans-out” the one-to-many relationships thus multiplying the result by the details at one end of the one-to-many relationship. An incorrect result is returned as you are performing an aggregate function on the table at the “one” end of the join, while still joining to the “many” end.
Solution
The best way to solve a fan trap using dimension objects in the query is to use an alias and contexts. Create an alias for the table producing the initial aggregation. Create a join between the alias table and the original table.
Next modify the object that is causing aggregation by using the alias table. Then, create separate contexts – one each for the alias table and the original table. Select “Multiple SQL Statements for Each Context” in the universe parameters.

Another way to resolve a fan trap only for measure objects is to use the universe parameters
option “Multiple SQL Statements for Each Measure”. This forces the generation of separate queries for each measure, but doesn’t ensure the same for dimensions.

Potential chasm and fan traps can be detected by visually inspecting the schema by suitably arranging the tables in the Structure pane, so that the “many” ends of the many-to-one joins are to one side of the pane, and the “one” ends to the other.

If you wish to read in more detail about SQL traps in Business Objects Designer and don’t mind a little drama or suspense, I highly recommend checking out Dave Rathbun’s posts on this topic.

About these ads

13 responses to “It’s all in the universe – handling chasm and fan traps

  1. Hi

    Can you explain me how incorrect result is 18 and 900 in detailed or if u can explain me example given in book which is there on business object website or if u can create any simple example for me my email id is sahilseashore17@gmail.com

    Like

  2. this is really very up to the mark and very help full material.

    thankz a lot

    Like

  3. Look for yourself.
    There are three records on other fact(Order).
    $300 * 3 = $900

    Like

  4. How would you solve an issue when two fact tables need to use the same dimension except that the granularity of the dimension is different.
    In this case, the dimension i am talking about is the Calendar Dimension, this is maintained for every single calendar date with month id, wtr id, year id etc..
    We do have two fact tables, one of them can have data only at Month ID level. One of them has data at a calendar date level.

    The first fact table ends up in a Many to Many relationship with the calendar join since the join is at Month ID level (a given calendar month has around 30 rows of data with the same Month ID)

    Like

    • you must create a (view, or indexed (aka materialized) view, or similar) object at month level to work with the monthly fact table; it’s called ‘role-playing’

      Like

  5. Hi ,

    This looked pretty useful list, but I m a bit confused when Dave mentioned in his post http://www.dagira.com/2008/03/10/what-is-a-context-anyway/ that “The setting “Allow selection of multiple contexts” is off by default and probably should stay off. It doesn’t really seem to help anything. ” (please see summary section of the linked post). While you mentioned that while countering Fan trap “Multiple SQL Statements for Each Context” in the universe parameters would be useful.

    Would you please clarify:
    Thanks
    Arif

    Like

  6. can any one help me..
    how to find traps in a universe?

    Like

  7. My scenario is that my fact is at the week level.
    My time dimension is a star schema(Calender table).month to week is many to many..so i broke up the table into snowflake…
    My time dimension is Year-<Quarter-<Month-<date and week-<date seperately.

    If i want to c my data in the fact at quarter level it shd agg. accordingly…. but it is not happening..
    How shd my context be….

    Like

  8. really very useful

    Like

  9. Can you please provide some examples on these traps…Since I am unable to connect to these traps through theoritcally

    Like

  10. Pingback: fan trap and chasm trap | No Reports

  11. edifying info. Thanks a lot.

    vreddy.chr@gmail.com

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s