Fan Trap
A Fan Trap is a one - many relationship.
This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply.
Fact table that contains aggregated facts are called summary table
Scenario of Fan Trap
when you select dimensions from D1 and measures from F1 and F2. Then you may have fan trap that needs to be resolved.
The universe has the below situation,
Table A - Dimensions table ( contains few measure values)
Table B - Fact (No reporting objects - contains only key fields - measure less fact)
Table C - Fact (detailed fact - Transaction amounts)
A -> B -> C all are one to many joins between the tables.
Scenario 1: Dimensions, Measures from A and measures from C report returns correct results and BO generates sql for each measure.
Scenario 2: Dimensions/Measures from A and measures/dimensions from C. Then the measures from 'A' aggregating wrongly (doubled / tripled) , where as measures from 'C' is fine. I beleive this is due to the reason that Table 'C' is more detail data.
Is this a Fan Trap situation? If YES how to resrtict? Tried the regular scenario of aliasing the table 'A' and creating context but no success.
http://www.forumtopics.com/busobj/viewtopic.php?t=93351
Business Objects resolves this transparently by using 2 queries.
It is resolved automatically as long as the measure objects have aggregrate functions that means, (query will split in 2).
Also, you just need to make sure the Multiple SQL Statements for Each Measure box is checked in Designer. File -> Parameters->SQL-> Check “Multiple SQL Statements for each measure”
Another way to solve this problem is,
1. Create an alias for the table (Original table) that is producing multiplied aggregation
2. Create a one to one join between the alias table and the original table
3. Build the object that is causing the aggregation on the alias tables
4. Define a context for a original table and a context for the alias table
5. File -> Parameters->SQL-> Check “Multiple SQL Statements for each measure”
6. Modify the select statement for the columns that are summed so that the columns in the alias table are summed and not the columns in the original table
A Fan Trap is a one - many relationship.
This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply.
Fact table that contains aggregated facts are called summary table
Scenario of Fan Trap
when you select dimensions from D1 and measures from F1 and F2. Then you may have fan trap that needs to be resolved.
The universe has the below situation,
Table A - Dimensions table ( contains few measure values)
Table B - Fact (No reporting objects - contains only key fields - measure less fact)
Table C - Fact (detailed fact - Transaction amounts)
A -> B -> C all are one to many joins between the tables.
Scenario 1: Dimensions, Measures from A and measures from C report returns correct results and BO generates sql for each measure.
Scenario 2: Dimensions/Measures from A and measures/dimensions from C. Then the measures from 'A' aggregating wrongly (doubled / tripled) , where as measures from 'C' is fine. I beleive this is due to the reason that Table 'C' is more detail data.
Is this a Fan Trap situation? If YES how to resrtict? Tried the regular scenario of aliasing the table 'A' and creating context but no success.
http://www.forumtopics.com/busobj/viewtopic.php?t=93351
Business Objects resolves this transparently by using 2 queries.
It is resolved automatically as long as the measure objects have aggregrate functions that means, (query will split in 2).
Also, you just need to make sure the Multiple SQL Statements for Each Measure box is checked in Designer. File -> Parameters->SQL-> Check “Multiple SQL Statements for each measure”
Another way to solve this problem is,
1. Create an alias for the table (Original table) that is producing multiplied aggregation
2. Create a one to one join between the alias table and the original table
3. Build the object that is causing the aggregation on the alias tables
4. Define a context for a original table and a context for the alias table
5. File -> Parameters->SQL-> Check “Multiple SQL Statements for each measure”
6. Modify the select statement for the columns that are summed so that the columns in the alias table are summed and not the columns in the original table
No comments:
Post a Comment