Aggregation based on field value : Analytical view or Calculation view

[Former Member]

Dear All,

    I have my analytical view as below (system landscape: ERP data replicated over HANA DB; modelling using Studio)

EBELN	EBELP	VGABE	BWART	BEWTP	DMBTR
4500005836	00010	1	101	E	286.32
4500005836	00010	1	101	E	687.18
4500005836	00010	1	101	E	2004.26
4500005836	00010	2		Q	291.44
4500005836	00010	2		Q	707.84
4500005836	00010	2		R	1964.18

I would like to aggregate(sum) the DMBTR(amount) based on VGABE, mean first(measure m1) to add all the DMBTR with VGABE = 1 and then another (m2) for VGABE = 2.

I tried creating a calculated measure using the m1= IF("VGABE"=1,DMBTR,0) but HANA Studio doesnot allow me.

Can any body suggest me how I can proceed further?

Thanks in advance!

Vittali

[Former Member · Accepted solution]

Hi Vittali,

Have you tried using restricted measures? That perfectly suits your use case here. Restrict measure  DMBTR by a specific attribute value of VGABE.

Your way of creating a calculated measure should also work but when you say HANA Studio doesnt allow me, is it that you are getting a syntax error? If so, I would change the expression to IF("VGABE"='1',"DMBTR",0) and also ensure the type of the calculated measure is the same as that of DMBTR.

Having said that you should be using "restricted measures" for this. Please let me know if you have more questions.

Thanks,

Anooj

[Former Member · Accepted solution]

Hi Anooj,

    Thanks a reply!

You were right, my syntax was wrong. I ended up using calculated measure using the IF condition, and its working fine for me. I believe it should work the same for restricted measures.

Then why use restricted measure specifically?

Best Regards,

Vittali

[Former Member · Accepted solution]

Good question around the use case for restricted measures as opposed to achieving the same with calculated measures (as stated above).

The way I see it, calculated measures have no influence on the select statement that hits the underlying database. They are processed once the data is all brought back, record by record. So something like this (on a very high level in simplistic terms):

Select from DB -> Aggregate result set based on selection -> Process calculated measure for each record

If you have the calculate before aggregation option set, the above changes to:

Select from DB -> Process calculated measure for each record -> Aggregate result set based on selection

However, the difference with restricted measures could be that, the restrictions are incorporated into your select statement that hits the DB and hence avoiding the need to do record by record processing & consequently should be more faster than the calculated measure approach in this case. The process flow for restricted measures would be something like this:

Select from DB (with Restrictions on measures) -> Aggregate result set based on selection

You may not notice the performance hit for a few 100s of records but for significant data volume I am guessing there should be some difference.

A little caveat to add: The above is based on my theoretical understanding & assumptions on the topic. You might want to verify this from documentations/material available out there if you are interested.

Thanks,

Anooj

[Former Member · Accepted solution]

Thanks Anoop ,

Couldn't ask for a more logical answer..

Best Regards

Vittali