All topics
19 / 55
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.

Two facts joined to a shared dimension can produce wrong numbers if the query is generated naively. These two shapes have names, and OBIEE's logical layer exists partly to handle them.

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: one-to-many between the two facts.
    Orders            Order Lines
    order 1, 1000     line A, 600
                      line B, 400
2
Join both to Customer in one flat query and the order header repeats once per line:
3
    order 1, 1000, line A, 600
    order 1, 1000, line B, 400
4
    SUM(order amount) = 2000      <- wrong. It is 1000.
5
The order total is counted once per line. Nothing errors; the total is simply inflated by the number of lines.
6
A chasm trap: two facts sharing a dimension with no relationship to each other.
    Sales by product     3 rows
    Returns by product   4 rows
7
Joined through Product in one query, you get a Cartesian product within each product — 12 rows — and both measures are multiplied.
8
What the BI Server does about it. Because facts and dimensions are modelled in the logical layer, the server does not generate one flat join. It issues a separate query per fact, aggregated to the common grain, and stitches them together on the shared dimension:
9
    query 1: SELECT product, SUM(sales)   FROM ... GROUP BY product
    query 2: SELECT product, SUM(returns) FROM ... GROUP BY product
    -> full outer join on product
10
Each measure is aggregated before the join, so neither can multiply the other. That is why an OBIEE report combining two facts usually just works, where the same thing hand-written in SQL does not.
11
What you must get right for it to work. The facts must be modelled as separate logical facts, not merged into one. The shared dimension must be conformed — the same logical dimension used by both, not two dimensions that happen to describe products.
12
Model them as one logical fact over a joined view, and you have put the flat join back and the server can no longer protect you.
13
The question interviewers ask. "Why not just join the tables in a view and model that?" Because you would have reintroduced the trap, and the reason the logical layer exists is to avoid it.
14
What the interviewer is probing.1. "What is a chasm trap?" Probing: the classic. Stalls: "A join problem." Moves up: two facts joined through a shared dimension at the same grain, producing a cartesian product that inflates both measures.
15
2. "What is a fan trap?" Probing: the companion. Stalls: "The same." Moves up: a one-to-many join from a fact to a detail table, so the parent measure repeats and sums too high.
16
3. "How does OBIEE avoid them when modelled correctly?" Probing: the mechanism. Stalls: "It does not." Moves up: conformed dimensions with correct content levels let it query each fact separately and stitch results on the shared dimension.
17
4. "How would you detect one in testing?" Probing: the method. Stalls: "Review the model." Moves up: compare a measure alone with the same measure in a multi-fact analysis — if it changes, the stitching is not happening.
Use cases:multi-fact-modelingchasm-trapfan-trap