When a “one to many” join linked by another “one to many” join in a serious of tables called as Fan Trap. Like the below example, Customer is having multiple Loan account and each Loan having Multiple Collateral which is “one to Many” & “One to Many” join in a series of tables.
Let see how the data looks like in the above tables.
Objects are created based on the above tables as below.
1. Create a report based on Customer Name and Loan Amount objects and your report would return correct result. For example,
2. Create a report for Customer John, based on Customer Name, Loan Amount and Collateral Amount objects and your report would return incorrect result. Result set is multiples of each row with other fact tables For example,
Result set in the back end execution.
3. 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 fact table which is common for both dimension and another fact table. Create a join between the alias table and the original table and create a Measure object from CUST Loan Account instead of Loan Account.
Create contexts as mentioned below.
4. Once you created the context for each fact tables along with alias table as mentioned above and enable the “Multiple SQL Statements for each Context” in universe designer parameter, SQL tab. This is common solution for Chasm Trap.
Now you can create the report and it will create separate SQL for each context and data will get synchronized based on common dimension Customer.
Please read the post and write me back your comments.