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.
Report Execution:
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.
DWBI castle 
Hi Madhes, thanks for the nice article.
1 question is… where do the 2 empty Coll_Amt cells come from?
Thanks
Hi Dawei,
Those 2 empty Coll_Amt cells wont comes to report, it would happen in back end. I have just showed how the report execution would be at back end. Means its a cartesian product ( multiply by number or rows)
Thanks
Madhes
Thanks for the reply Madhes, still bit of confused…
After the first join (Customer, Loan Account), we will get:
1001 | John | 1901 | 7,000,000.00
1001 | John | 1902 | 2,500,000.00
Then we use this result set to join to Collateral, we get:
1001 | John | 1901 | 7,000,000.00 | House | 940,030.00
1001 | John | 1902 | 2,500,000.00 | Vehicle | 120,010.00
Can you tell me where the Cartesian product come from… thank you.
Thanks Dawei for catching up this issue. There was an data set mismatch in the example I have given. I have updated with correct data set (earlier the data set was not matching with Fan trap scenario, means the second fact table is not having one to Many relationship with first fact table.. that is where the confusion started).
Thanks again for your comments
Thanks
Madhes
Yeah this time it doesn’t confuse me. 🙂 Thanks for the nice article.
Based on the data, the 2 rows “1904 GOLD”,”1905 EDU CERTIFICATE” will not generate the wrong data, am I correct?
😊 yes! You are correct.
Thanks for the informative post. Quick question…What happens when you add “Collateral_CD” to the final block that has – Cust name, Coll Amt, Loan Amt? Does the Loan Amt get inflated?
Collateral_CD object wont impact anything in report amount.