Inbound Process / Fact Table Calculation Issue in IDT

[Former Member]

Hey guys,

I'm trying to display the following inbound scenario.

PO# | PO# Item | Material# | PO Qty | Mat Movement Qty

Therefore I can see whether I have received the full amount I have purchased.

The PO# is based on Fact Table 1 and Mat Movement Items based on Fact Table 2 (plus various dimension tables for Material, Vendor etc.)

I join both fact tables through a bridge table but when I have 1 PO with 3 Material Movements it sums up the PO Qty times 3. How can I avoid this in the IDT?

Data after the join:

PO Number	PO Item	Material Number	PO Qty	Material Doc Number	Mat Doc Qty
1	10	abc	15	1	5
1	10	abc	15	2	5
1	10	abc	15	3	5

Goal:

PO Number	PO Item	Material Number	PO Qty	Received Qty
1	10	abc	15	15

What I get :

PO Number	PO Item	Material Number	PO Qty	Received Qty
1	10	abc	45	15

Hopefully you can help out here.

[mhmohammed]

Hi Phillip,

Ideally you should've 2 Fact tables, 1st at PO level, 2nd at Item Level, and  you should be creating 2 contexts in the Universe for these 2 levels of granularity.

If you have that already, and are pulling the data from both contexts in 2 queries in a report, users should know that they're not supposed to add the PO Qty (or any measure from PO Level) when they've data from both contexts in one table. Does that make sense?

I see that you've Item level data here (Material Doc Number, which are 3 different values), so, the PO Qty in this case, in this table, shouldn't be added (15*3=45), you should just get a snapshot (one of the values), might as well, create an additional objects in the Universe for this PO Qty (as dimension) and that should be used when combining data from 2 different contexts in one table.

Hope that helps & let me know how it goes / if you have any question(s).

Thanks,

Mahboob Mohammed

[Former Member]

Hi Jan,

Please try to share a snapshot of your universe schema. There seems to be an issue with the relationship among the tables.

Regards,

Yuvraj

[Former Member]

Hi,

Based on your input i feel, you are facing issue because of fan trap.

Please check below article to resolve the fan trap in universe.

https://rbelbahri.files.wordpress.com/2007/04/modeling_traps.pdf

Generally it is recommended to join two fact tables until they are at the same grain/level.

So if is it a trap then try to resolve it as mentioned in the article else create context to separate the query of each fact table.