1:n relationship and Multiple sql statements for each measur

[system]

In my universe I have a 1:n-relationship:

SALES 1-n SALES_ITEMS

I created a measures SALES_AMOUNT from table SALES and a measure SALES_ITEM_VALUE from SALES_ITEMS.

Of course I get wrong results for SALES_AMOUNT, because it get multiplied by the number of rows in SALES_ITEMS. I thought I can solve that problem by checking the option “Multiple sql statements for each measure”, but unfortunately only 1 SQL statement is generated for both measures. I set all cardinality correctly.

Any ideas what I am doing wrong?

---

hanshupe (BOB member since 2011-03-19)

[system]

Hi,

What you have is called a fan trap. It needs to be solved accordingly. Here is how:

FAQ: Designer

What is a fan trap, why is it a problem, and how do I resolve it? Dave Rathbun describes it well in his presentation, ZEN and the ART of Universe Design. Which is located here: http://www.dagira.com/conference-presentations/ Here is a quick synopsis of Fan Trap from his presentation: A Fan Trap is a one - many - 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. Business Objects resolves this t…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

@hanshupe

In my universe I have a 1:n-relationship:

SALES 1-n SALES_ITEMS

I created a measures SALES_AMOUNT from table SALES and a measure SALES_ITEM_VALUE from SALES_ITEMS.

Of course I get wrong results for SALES_AMOUNT, because it get multiplied by the number of rows in SALES_ITEMS. I thought I can solve that problem by checking the option “Multiple sql statements for each measure”, but unfortunately only 1 SQL statement is generated for both measures. I set all cardinality correctly.

Any ideas what I am doing wrong?

I believe, in the first instance, that your data model is wrong. There is a schema of mine, in this post, concerning Orders and Order Lines (both fact tables). I suggest you have a look.

https://bobj-board.org/t/166900

---

Mak 1 (BOB member since 2005-01-06)

[system]

Multiple SQL statements for each measure worked great in the Universe Designer in Version 3.x.

In IDT 4.0, the Multiple SQL Statements for each measure actually does not create multiple SQL… I believe that it is a bug in the software but we would need to check with SAP…

Has anyone else encountered this issue

Thanks

---

Brigparadise (BOB member since 2011-10-12)