SAP Analytics Cloud - Hierarchy Aggregation

[mtayibi]

Hello experts,

i'd like to know if there is a solution in SAC for the following problem:

H1 - 50 kg

H1.1 - 50 kg

H1.2 - 50 kg

I have articles which can be in H1.1 and H1.2 at the same time but in total i have 50 kg not 100kg. Currently the result for H1 is 100kg which is wrong. Hierarchy is build in SAC.

Technical situation:

• SAP HANA Studio
• Import Connection
• No SAP BW in place

Thanks in advance for any help. I hope the provided information is enough to understand my problem.

Kr

Benaissa

[William_Yu · Accepted solution]

Hi mtayibi,

Not really sure how you would like H1.1 and H1.2 to be aggregated into H1 node. SAC has by default aggregation behavior sum, you can define exceptional aggregation behavior with account formula or measure formula. Your example looks like average aggregation which can be achieved in this way.

Best regards, William

[mtayibi · Accepted solution]

Thanks for the quick reponse. I will try your approach.

Kr

Benaissa

[ebajema1 · Accepted solution]

Hi william.yu. Does your answer here mean that you cannot actually influence the behaviour of aggregation up a hierarchy?

Taking Benaissa's example, say you have a measure called "Weight", and you have a Product dimension with a hierarchy.

Product 1 has Weight = 50kg, Product 2 has Weight = 60kg. They have a common parent called Product Group 1.

The desired result would be to see 55kg (average of 50 and 60) appearing against the parent in the hierarchy.

What is the best way to achieve this?

At present, the measure definition window in SAC only gives you the "option" to set Sum aggregation. You can try setting Exception Aggregation to Average, referencing Dimension = Product but this doesn't actually do anything if you are using a new model with both measures and accounts. The change only takes effect if you additionally apply the exception aggregation in the Account dimension, even if "Prioritise Measures" is selected in the model preferences. This may be quite inconvenient if you want different behaviour for different measures across a single account member.

Looking forward to any clarification you can offer. Kind Regards

[William_Yu · Accepted solution]

Hi ebajema1,

Seems it's working quite well for me by having different aggregation types by measures,

Measure Weight is having Average aggregation while measure LC value is having normla SUM aggregation.

Best regards, William

[ebajema1 · Accepted solution]

Hi william.yu, Sorry I may not have been clear with my point.
In your example above, does your model have an Account dimension ?

When I perform the same test on my model, the measure setting alone will not result in the behaviour you have shown.
Instead, it only works when you also set every Account member to use Average exception aggregation, which is not necessarily desirable. This is true even when priority is given to measures, which seems wrong.
Any comments on this or are we looking at the wrong thing?

[William_Yu · Accepted solution]

Hi ebajema1,,

Yes, I have account dimension in the model, and the account I used in this case has no aggregation type at all. Actually doesn't matter if the account member having aggregation type null or SUM, it's always Average on this measure.

Not sure why it's not working for you.

Best regards, William

[ychernikov · Accepted solution]

Hi,

Is it possible to change the default aggregation behavior in hierarchies from other dimensions than Account ?

Can it be defined by each member in the hierarchy ?

For example :

Non Account Dimension:

N1 node should aggregate

- N11 (+) --> ADDITION

- N12 (-) --> SUBSTRUCT

Thanks