Need help! Have gone through different postings on BOB and didn’t find any appropriate scenario.
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.
Yes, I have read the posting and tried aliasing the table ‘A’ which is where the measures are getting aggregated and created two contexts as below per universe context check,
A->B->C
A -> Alias of A
and also check the options for multiple SQL for ech measure in universe parameter tab.
Still the measures are getting duplicated from table ‘A’. Any suggestions?
Your measures from table “A” must come from the alias table. Only dimensions will be built on the original table A. There should be a context that includes A + Alias of A, and another context that includes A, B, and C.
I tried exactly the same as you suggested. Crated two contexts (one on A and Alias A and another on A,B,C) and the measures from the alias of ‘A’ table.
The measures from ‘A’ are getting globally sum and giving the wrong results,
The measure from Table ‘C’ (lower level ) is still correct but measure created on the new table (alais of ‘A’) is summing up the results globally. I did make sure I set all the setting in the universe parameters (multiple sql statements for each measure and multiple sql statemetns for each context) as required.
Any other suggestions on what could be the potential problem?
Have you joined table A and the alias of table A (‘A-lias’) on all key fields, so that a record in table A is joined to one and only one record from A-lias?
Are all the joins between A and A-lias in the context?
If everything is defined correctly for the 2 contexts, you don’t have to “check the options for multiple SQL for ech measure in universe parameter tab”.
I understand where I was going wrong. Actually one of the dimension filed I’m populating in the report is NOT from table ‘A’, which is causing the amount summation globally. After correctly populating the fields into the report fan trap issue get fixed automatically.
(BOB member since 2002-06-06)
(BOB member since 2006-02-06)