I am facing a serious performance problem with exception aggregation in formulas. The query contains such a big number of key figures that have to be calculated as weighted average, that the query execution failes after a long time of execution. Now I am asking myself how to avoid the exception aggregation and to use alternative approaches.
Here comes a basic example. There are several countries with different quantities and values.
Country Qty Value
------- --- -----
DE 1 5
FR 2 20
To get the weighted value for the whole world depending on quantity we need to sum each country product of quantity and value (1*5 + 2*20 = 48). The result will be divided by the sum of quantities (45/3). So we get 15. Generally this works fine using exception aggregation SUM on Country.
Country Qty Value Qty*Value WeightedValue
------- --- ----- --------- -------------
DE 1 5 5
FR 2 20 40
Sum 3 45 15
Searching for alternatives I'd like to define something like a view, that is firstly doing the multiplication between quantity and value on country level. Then this view should be the data basis (like persistent ADSO or InfoCube) for a query. In that query I could avoid the exception aggregation as all products (Qty*Value) are already available and default aggregation will always devliver valid sums for the weighted value calculation independent from drill down level. But how can such a view be easily realized? What do you think? I am thinking about external HANA views for queries, so that a second query can consume this basis query. What about calculation views in the HANA layer? Are there further possibilities?
In the meantime I also thought about persisting the result of Qty*Value in the database. But as there are many different value key figures and additional several quantitiy key figures on that the weighted average calculation has to be processed, this is no way.
Do you have further ideas to avoid exception aggregations?