using measures from different contexts: chasm Trap

[system]

I have 4 tables in my universe.
CR, RA, CA, PP- is a look up table.

I have the joins as follows-
inner join -CR.RA_ID = RA.RA_ID (Many to one)
complex inner join-(RA.P1_ID = PP.P1_ID and RA.P2_ID= PP.P2_ID) - (many to one)
complex inner join- (CA.P1_ID =PP.P1_ID and CA.P2_ID = PP.P2_ID) - (many to one)

Packages are shipped to sites, some packages may be returned back.

CA contains shipment information, and shipped quantity, and the date shipped. It is also the table that has all the possible Package IDs.
CR contains packages returned back by the customer and the returned quantity. Not all Package IDs will always have returns.
RA contains the date when the package returned by customer was received.
PP is the look up table having the package IDs.

I have to display both the shipped quantity and the returned quantity for each package id in one report. The issue is in the datacube, i see the shipped quantity broken by shipped date correctly, but when pulled on the report along with returned quantity, it aggregates and repeats for each line of the returned info. eg.

[list=]
Dt_Recvd Shipped_qty Return_qty PP_ID
11/1 8 1 D334
11/5 8 3 D334
11/7 8 4 D334
[/list]

Correct result for shipped_qty is = 8, but since it is repeating on the report it seems to be 24.

I have 2 seperate queries to bring shipped and returned info., and hence there are no contexts and loops. I then link the common dimensions between the 2 queries, but cannot link the 2 dates- dt_shipped, dt_recvd.

I am going against oracle application table and hence these are normalized tables (no fact tables).

How do I resolve this issue?

---

Don840 (BOB member since 2005-03-14)

[system]

Did you have a look at the Designer FAQ first?

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…

---

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

[system]

Hi Dave,
Thanks for the links. I read through the material, and think what I have is a chasm trap and not a fan trap.

The RA table has a many-one join with PP, and the CA table has a many to one with PP table. RA(many-one)-PP(one-many)-CA.

Now, my problem is when if I create 2 contexts-
CR-RA-PP = gives units returned information.
context PP-CA = gives the units shipped information.

The issue is I have to show both units returned and shipped in the same report (same table).

In the datacube I get the correct quanitities. But on pulling the measures on the report, I get 3 unique rows for shipped_qty but repeating rows for Return_qty as shown in my list in the earlier post.

The aggregation is correct if I remove the date_rcvd column from the table, but I have to display all the 3 columns in the same table.

How do I resolve that issue?

---

Don840 (BOB member since 2005-03-14)

[system]

HI
Wanted to update my post.
SO I do have a chasm trap in my universe design. I ahve created 2 contexts for shipements and returns information.

My requirement is to show both the ship and return quantity in the same report block (they belong to different contexts now), but this is difficult as these are at different levels of granularity and I am unable to force any context (no other linking table in the DB).

Using the multicube function in deski solves this problem and I am able to get the correct result (based on another field - LOT_ID present in CR and CA tables)

What would be the alternative of multicube in webi XI R2?

---

Don840 (BOB member since 2005-03-14)