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.

Fan Trap1

Let see how the data looks like in the above tables.

Fan Trap2

Objects are created based on the above tables as below.

Fan Trap3

Report Execution:

1. Create a report based on Customer Name and Loan Amount objects and your report would return correct result. For example,

Fan Trap4

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,

Fan Trap5

Result set in the back end execution.

Fan Trap6

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.

Fan Trap7

Create contexts as mentioned below.

Fan Trap8

Fan Trap9

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.

Fan Trap10

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.

Fan Trap11

Please read the post and write me back your comments.

Advertisements

8 thoughts on “Working on Trap Issue – Fan Trap

    • 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

  1. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s