David Lai's Business Intelligence Blog

In this article I would like to talk about Chasm traps and Fan traps. These are problems that we often experience while building universes and reports. When encountering these traps, one may wonder what is going on? How come my sum statements arent adding up correctly? Or why am I missing some rows? A properly designed universe will help avoid these problems. In addition, a good understanding about measures and contexts from report designers will help as well.

Chasm Traps

Let’s talk about Chasm traps first. In short, a Chasm trap can be imagined as a bottomless pit where some rows may unknowingly fall in and never come back out. So when viewing a report caught in a Chasm trap, one may ask “Hey where did Record X go??”.

Looking at the eFashion Universe (note that this is a modified version of the efashion universe), we see that there are 2 fact tables that join to 2 of the same dimension tables. If we do not set a context for each of the fact tables in the universe, we will only get 1 query when building a report that involves measures from both fact tables.

SELECT
Article_lookup.Article_id,
Shop_facts.Amount_sold
FROM
Article_lookup,
Shop_facts,
product_promotion_facts,
Calendar_year_lookup
WHERE
( product_promotion_facts.Week_id=Calendar_year_lookup.Week_id )
AND ( Article_lookup.Article_id=Shop_facts.Article_id )
AND ( Article_lookup.Article_id=product_promotion_facts.Article_id )
AND ( Shop_facts.Week_id=Calendar_year_lookup.Week_id )

Since the promotion fact table and shop fact table are joined in the same query, we will only get results that are in both the promotion fact and shop fact table. In reality we want all available products even if they are not on promotion. The products that are not on promotion will fall down the bottomless pit and report designers will be wondering where they have gone.

Fan Traps

Fan traps are another common problem and occur when a measure is overstated. So using the same example above, the promotion cost measure is a “sum of promotion costs”. Let’s say for one product we might have 5 entries in the shop fact table and 2 entries in the promotion fact table. Instead of a one-to-many join for the promotion fact table, we now have a many-to-many join which will cause a cartesian product. The promotion cost for that product will be 5 times higher than what its supposed to be since we have 5 entries in the shop fact table.

Prevention
To prevent falling into these traps, we must properly set our contexts in the universe. Here we set a context for both the promotion fact and shop fact. As a rule of thumb we should always set contexts for facts.

Setting contexts for these 2 facts will now produce 2 queries that will be synchronized thus returning the correct results because now we have 2 seperate joins instead of 2 joins in the same table.