i am new to universe.
Currently i try to design a simple univer but the data i fetch is multipled.
i doubt it is a fan traps. i have research to solve this matter but still cant work out. the figure still in accurate.
the query that i fetch from webi is as below:
SELECT
FACT_SALES_ID.VALUE,
FACT_SALES_ID2.VALUE,
Derived_Table.PRODUCT
FROM
FACT_SALES_ID,
FACT_SALES_ID FACT_SALES_ID2,
(
Select FACT_SALES_ID.PRODUCT from FACT_SALES_ID GROUP BY FACT_SALES_ID.PRODUCT
) Derived_Table
WHERE
( Derived_Table.PRODUCT=FACT_SALES_ID.PRODUCT )
AND ( Derived_Table.PRODUCT=FACT_SALES_ID2.PRODUCT )
AND ( FACT_SALES_ID.TRX_MTHDATE <=‘2011-04-30’) and (FACT_SALES_ID.TRX_MTHDATE > ‘2010-04-30’ )
AND ( FACT_SALES_ID2.TRX_MTHDATE <=‘2010-04-30’) and (FACT_SALES_ID2.TRX_MTHDATE > ‘2009-04-30’ )
the corretc figure shud be around 40 mil instead it come out 200+ mil
any idea on it?
very appreciate if any helps.
Yes it seems to be a Fan Trap.
Try creating an alias for the Derived Table and join it with the Derived table itself on Products. then set context one for each path.
Also enable the option - Multiple SQL statements for each context in the Universe Parameters->SQL.
Now depneding on your Query requirements you can choose the context.