BusinessObjects Board

Fan Trap with Dimension

Good Day…

Grateful if any of the experts provides solution for my problem.

Problem Description:
How to resolve a fan trap when occurs with a Dimension.

Example:
I have series of one to many joins as in below.

Customer -> Order -> Order Line. ( Order and Order Line are fact tables and Customer is the dimension table).

I have created a report with objects Customer (from Customer table) , Sales Amt ( from Order table) and Item Desc ( from Order Line table). Here Customer and Item Desc are dimensions and Sales Amt is a measure. The report is generating single SQL as there is only one measure in the report. Hence the mesaures are aggregated incorrectly.

2 SQL is expected for the report.

  1. Customer -> Order ( for generating Sales Amt measure)
  2. Customer -> Order -> Order Line ( for generating item desc)

Please advise how to solve this problem.


dhayanithij (BOB member since 2007-06-26)

Hi, Welcome to B:bob:B!

This appears to be a standard fan trap. It’s covered in the Designer FAQ here. Do please read the FAQs in each forum and try searching before posting a question on BOB, thanks.


Dave Rathbun :us: (BOB member since 2002-06-06)

Dave,

Many thanks for your immediate response. Happy to be part of BOB forum.

I am aware of the regular Fan trap problem. Fan trap occurs when there is a series of Many to One joins. A -> B -> C.

Usually there will be mesuares from the “B” and “C” table. “Multiple SQL for each Measure” option in the universe parameter window will split the SQL and fan trap will be resolved.

In this scenario, there is no measure from table C instead there is a dimension object. So the SQL is not getting splitted and the measure are getting aggregated wrongly.

Is it feasible to resolve a fan trap with dimension object.

Thanks
Dhayanithi


dhayanithij (BOB member since 2007-06-26)

Please read the entire post in the FAQ. :wink: There is more than one solution for a fan trap. One is solvable if the fan is only measures, the other requires creating an alias and a context to split out the dimension values.


Dave Rathbun :us: (BOB member since 2002-06-06)

You would have to create alias of Order and join to the original Order table. Then define contexts for Customer->Order->Order Line and for Customer -> Order -> aOrder . Measure will come from alias table.


Marfi :poland: (BOB member since 2006-12-18)