OAS - Sum of 2 parent agg from different levels

Question

Imagine I have the next hierachy (simplified for this explanation)

A1 - A11 - A111 - A1111

A1 - A12 - A121 - A1211

B1 - B11 - B111 - B1111

MY fact table:

Id Class Amt

1 A1211 10

1 A1111 20

1 B1111 40

I want aggregate B1 (level 1) and A12(Level 2) en one value

Id sumAmt

1. 50

I’m working with ORACLE ANALYTICS SERVER AND RPD.

So far no result.

Normaly, The aggregation ocurrs at the same lavel and don’t know what to do

Answer 1

Your aggregate in the above example is basically supposed to happen on a substring. So you can implement this with using the LEFT function and trim things down to your levels. "A1", "A12", "A123" would be like 3 levels. That said even if it's possible to do it in the RPD the best place to do this is obviously the database level. If you think about it - every logic you implement in the RPD gets interpreted row by row. For every single row you parse. So that means performance loss. tl;dr possible but not necessarily the optimal point