What is a chasm trap? How can it be solved.
In a dimensional schema based universe, we may have one dimension table joined with two fact tables such that both of them are one-to-many joins(F >- D -<F ). In such a scenario, if we drag a measure each from both the fact tables along with dimensions from dimension table, the value of the measures in the fact tables are inflated. This condition is known as chasm trap.
A chasm trap can be solved using 2 methods:
- In the universe SQL parameters, the option, generate multiple queries for each measure needs to be selected. This will generate separate SQL statement for each measure and give the correct results. However, this method would not work, if a dimension (for example date) occurs multiple times in the result set due to chasm trap.
- A better approach is to put the two joins in two different contexts. This will generate two synchronized queries, thus solving the problem.