Innovate your data – castle of DWBI

Menu

Working on Trap Issue – Fan Trap

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.

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 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).