Skip to Content
0
Jun 05, 2020 at 05:04 AM

Tricky aggregation requirement in SAP HANA

89 Views

Hi All,

Need some creative ways of solving a requirement that I've come across.

So the data consists of WBS levels (3 levels laid out in this example for simplicity). Data exists in the tables in below format:

So you have 3 level WBS structure where budgets can be assigned at each of these levels.

For example, we have an overall budget of 200 which is distributed between levels A.1 and A.2.

Note that budgets may not be fully allocated and the sum of budgets of child nodes may not equal the overall budget of the parent.

Also, we have actual postings which are only posted at the level 3. If someone wants the actuals at level 2, it would be a sum of level 3 actual postings - standard aggregation at play in this case.

Now, the report needs to look as the below in Webi and Analysis for Excel. Deriving this layout is not really a problem as data exists at the lowest levels.

The problem starts when user starts removing lower level dimensions. For example, if the user removes level 3 from the report, expected output is as below:

But this won't happen as Actuals will turn out fine since actuals at level 2 are the sum of their level 3 postings but the level 2 budget can not be derived as a sum of level 3s. A.1 would show up as 50 and A.2 would be 60+10 = 70 - sum of their lower levels instead of the expected values shown above.

The same problem would exist if I remove level 2.

Below is my expected output but I would get overall budget as 110 instead due to the sum of all lower levels.

Now, I am stuck in a position where I want one of my KPIs - Overall budget to be derived from it's corresponding level at the source but I want my other KPI actuals to be calculated as the aggregation of lower levels as it only exists at the lowest node.

Any ideas here?

Regards,

Shyam

Attachments

1.png (20.3 kB)
2.png (9.6 kB)
3.png (6.3 kB)
4.png (4.7 kB)
5.png (4.5 kB)