Number tripling after adding a custom hierarchy attribute

Question

Hi,

Sounds like a rookie mistake, but we have many custom hierarchies built in using W_INT_ORG_D and W_INT_ORG_DH tables all are working fine except for one, I tried to compare it with those working normal several times but couldn't find the issue or what's wrong. Adding any level attribute from this hierarchy folder triples the measure numbers in the report. Any input to diagnose the issue is highly appreciated. Thanks.

[Deleted User] · Answer

https://community.oracle.com/tech/apps-infra/discussion/4498468/number-tripling-after-adding-a-custom-hierarchy-attribute

Did you give up on this?

Gianni Ceresa · Answer

If your numbers are 3x the original, the issue comes from the join happening.

It can be an issue of granularity: you select your attribute but forgot that this attribute isn't unique but exist multiple times in the table and therefore aggregate rows multiples times.

The easiest way to fix the issue is to look at the physical query generated, and start working on it: a WHERE condition is maybe missing somewhere, or the join condition is too generic and match rows of the fact table multiple times.

But the physical query will tell you exactly why you see the number multiplied by 3x, and will point you in the direction of the fix once you did correct the query (an extra filter required in the analysis, a condition set in the logical table source WHERE clause, a better join condition etc.).