Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Tuesday, April 16, 2013

What is Fan Trap in Discoverer and how it handles them?


Fan Trap is a situation while running discoverer reports that return unexpected results due to a group of joined database tables. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently.
If you use a straightforward SQL statement to aggregate data points here, you may get incorrect results due to fan trap. Now, if you enable fan trap detection in Discoverer and if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results.
Example of Fan Trap:
Consider an example fan trap schema that includes a master folder (ACCOUNT) and two detail folders (SALES and BUDGET), as shown below:
Now let’s say we need to answer the question, “What is the total sales and total budget by account?
Straightforward SQL statement approach:
1
2
3
4
5
6
7
8
9
10
11
SELECT Account.Name,
       SUM(sales),
       SUM(budget)
FROM
      Account,
      Sales,
      Budget
Where
      Account.id=Sales.accid
      AND Account.id=Budget.accid
GROUP BY Account.Name;
Account    Sales Budget
Account 1   800   1200
Account 2   130    200
Account 3    600   750
Account 4    600   600
The above results are incorrect, because they are based on a single query in which the tables are first joined together in a temporary table, and then the aggregation is performed. However, this approach causes the aggregates to be summed (incorrectly) multiple times.
Discoverer Approach:
If we run the query in Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.
Here are the results from discoverer which is correct:
Account   Sales   Budget
Account 1  400      400
Account 2  130      100
Account 3  200      750
Account 4  300      200
How to enable fan trap in discoverer?
By default, fan trap detection is always enabled for you. If you want to disable it (however not recommended), you can logon to Discoverer Plus, go to Tools > Options >Advanced Tab and click on ‘Disable fan trap detection’.
How Discoverer handles fan trap?
If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.
In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:
  • If the detail folders use different keys from the master for the join
  • If there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
  • If non-aggregated values are chosen from more than one of the detail folders
  • If more than one detail folder has a separate join relationship to a different master folder
In the above circumstances, Discoverer disallows the query and displays an error message.

No comments:

Post a Comment