RPDadvanced
Multi-Fact Modeling & Chasm/Fan Traps
Explain how combining multiple logical fact tables at different grains in one query can produce chasm or fan traps, and how OBIEE's BMM design avoids them.
This is one of the most conceptually important — and most misunderstood — topics in dimensional modeling, and OBIEE interviewers love it because it directly tests whether a candidate understands *why* the BI Server's logical modeling exists, not just how to click through the Administration Tool.
It's like asking for "total sales" and "total support tickets" per customer in one breath — a careless assistant might try to join both lists directly and end up quoting nonsense totals, while a careful one tallies each list separately by customer first, then lays the two tallies side by side.
Key Concepts
1
A fan trap occurs when a query joins a dimension to two fact tables that have a one-to-many relationship with each other via that dimension, causing rows to multiply incorrectly if joined naively (e.g., joining Orders and Order Line Items both to a Customer dimension in a single flat join path can multiply order totals by the number of line items). A chasm trap occurs when two fact tables share a common dimension but have no direct relationship to each other, and a naive join produces a cartesian-like explosion of unrelated combinations (e.g., joining a "Sales Facts" table and a "Support Tickets Facts" table, both linked to Customer, without any shared grain between them).
fan trapchasm trap
2
OBIEE's BI Server handles this correctly by design, provided the BMM is modeled properly: because logical fact tables are distinct logical objects joined to shared conformed dimensions, the BI Server recognizes when a single logical SQL request spans multiple fact tables at incompatible grains and automatically generates separate physical queries per fact table, then performs the join/union in memory at the *dimension* level — effectively avoiding the trap by never generating one giant multi-fact physical join. This is why OBIEE documentation refers to "multiple fact table" subject areas as a supported, common pattern, not an anti-pattern, as long as the conformed dimensions (same grain, same keys, consistent across both facts) are correctly shared in the BMM.
by designseparate physical queries per fact tableconformed dimensions
3
A strong interview answer explains that the *safety* comes from the BI Server's own query-splitting behavior, not from clever SQL the developer writes by hand — but that this safety net still depends on dimensions being properly conformed and logical grains being correctly declared; a poorly conformed dimension can still produce wrong (though not usually catastrophically exploded) results.