Fan trap issue

[system]

Hi All,

We are facing a Fan Trap problem. We are having 3 tables which are refered in the report. The relation between these tables is as:

CRT_CURRENCY ----< PRC_RATE-----< CAPACITY_DTL.

The data in these tables is as:

Table CRT_CURRENCY:

CCY_CD		LNG_DESC
--------------------------
GBP		Pound
USD		US Dollar

Table PRC_RATE:

RATE_LINE_ID  VER_NO   CCY_CD  TOT_PROMSD_REV
--------------------------------------------------------------
12345	         1            GBP		1000
12345                2	       GBP		900
66666   	         1	       GBP		750
66666	         2	       GBP		800

Table CAPACITY_DTL:

RATE_LINE_ID	VER_NO		ITEM_TYP_CD	
--------------------------------------------
12345		1		I-1	
12345		1		I-2	
12345		2		I-3	
66666		1		I-4
66666		2		I-5

In the report we are having Dimensions from Table CRT_CURRENCY, PRC_RATE and CAPACITY_DTL and Measures from table PRC_RATE.

The output we are getting in the report is as:

CCY_CD	LNG_DESC	     ITEM_TYP_CD	TOT_PROMSD_REV
---------------------------------------------------------------
GBP	Pound		I-1		1000
GBP	Pound		I-2		1000
GBP	Pound		I-3		900
GBP	Pound		I-4		750
GBP	Pound		I-5		800

			Total		4450

As it can be seen above the value 1000 for TOT_PROMSD_REV is repeated twice because of fan trap problem. We have created Alias of table PRC_RATE & used the formula @Aggregate_Aware(SUM(PRC_RATE.TOT_PROMSD_REV),SUM(PRC_RATE1.TOT_PROMSD_REV) in the object for TOT_PROMSD_REV.

We are not sure whether the report output above is corect or not i.e value 1000 appearing twice for TOT_PROMSD_REV. Also if ITEM_TYP_CD is deleted from the report (not the query) the total still appears as 4450 which is incorrect. Please suggest solution/comments to the above problem??

The query which the report has generated after using aggregate awareness is as below:

SELECT   
  CRT_CURRENCY.CCY_CD,
  CRT_CURRENCY.LNG_DESC,
  CAPACITY_DTL.ITEM_TYP_CD,
  SUM(PRC_RATE1.TOT_PROMSD_REV)
  
FROM
	CRT_CURRENCY,
	PRC_RATE PRC_RATE1,
	CAPACITY_DTL,
	PRC_RATE
WHERE
  PRC_RATE.CCY_CD=CRT_CURRENCY.CCY_CD(+)
  AND  CAPACITY_DTL.RATE_LINE_ID(+)= PRC_RATE.RATE_LINE_ID and CAPACITY_DTL.VER_NO(+)=PRC_RATE.VER_NO
  AND  PRC_RATE.RATE_LINE_ID=PRC_RATE1.RATE_LINE_ID and PRC_RATE.VER_NO=PRC_RATE1.VER_NO
  AND  CRT_CURRENCY.CCY_CD  =  'GBP'

GROUP BY
  CRT_CURRENCY.CCY_CD, 
  CRT_CURRENCY.SHORT_DESC, 
  CRT_CURRENCY.LNG_DESC, 
  CAPACITY_DTL.ITEM_TYP_CD

Thanks.

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. You might have to edit your post further to align the columns properly. Thank you, Andreas.]

---

diganta_goswami (BOB member since 2006-12-14)

[system]

Fan traps must be resolved using alias tables and universe contexts. Forget about using @Aggregate_Aware for now.

For example:
D1 = Dimension table
F1 = Fact table 1, Order header information
F2 = Fact table 2, Order line information

Relationships:

D1 --< F1 --< F2

In other words: D1 has a 1 to many relationship to F1, F1 has a 1 to many relationship with F2.

Fan trap:

F1 --< F2

Fan trap issue:
Measures from F1 will be multiplied (incorrectly), because of the 1:N relationship from F1 to F2, whenever objects from F1 are used together with objects from F2 as well.

Solution:

1. Alias the fact table F1 as “F1_alias_measure” and switch all measures from F1 to this new alias.
2. Create a new context for: D1 --< F1 – F1_alias_measure
3. Create a new context for: D1 --< F1 --< F2

Result:
When creating a data provider with measure(s) from F1_alias_measure and any object from F2, two separate SQL statements will be generated, resolving the fan trap issue.

Note:
Sometimes you can attack a fan trap by re-modeling/re-designing your datamart.

---

Andreas (BOB member since 2002-06-20)

[system]

Hi Andreas,

Thanks for very good explanation. I’ve a question.

after creating 2 contexts, one from D1 --< F1 – F1_alias_measure and other from D1 --< F1 --< F2 , if I select a measure from F2 along with some objects from D1 and F1(dimensions only) , will it fetch the correct results or not?

Please explain.

Thanks,

---

Sree Konduri (BOB member since 2005-07-14)

[system]

How about you try it yourself and report back, please hm?

Hint:
All dimensions related to F1 should still be sourced from F1 and not F1_alias_measure. Only measures related to F1 should be sourced instead from F1_alias_measure.

---

Andreas (BOB member since 2002-06-20)

[system]

Thanks Andreas,
We have implmented the solution recommended by you and we are getting the following result for the data as mentioned by us above:

CCY_CD   LNG_DESC    ITEM_TYP_CD   TOT_PROMSD_REV 
----------------------------------------------------- 
GBP   	 Pound        I-1      	            1900 
GBP   	 Pound        I-2      	            1900 
GBP   	 Pound        I-3                      1900  
GBP   	 Pound        I-4                      1550 
GBP   	 Pound        I-5                      1550

		                  Total   3450

It seems to be correct! (Whats your opinion )

Just few small clarifications.
a) For our scenario A —< B ----< C we have created a alias for B and instead of creating 1-1 from B — B Alias we have created B —< B Alias. This way different context were detected for A—< B ----< C and A—< B ----- B Alias automatically by Business Objects. Please sugggest whether this approach is correct or not.

b) Do we have to create only Sum & Count measure objects from B Alias instead of table B or is it that even the Max, Min measure objects should also be created from B Alias.

Thanks for your help.

---

diganta_goswami (BOB member since 2006-12-14)