Aggregating data with Data Actions

[chaznosterous]

Hi experts,

I am currently trying to solve a simple problem for a planning solution, where I have a hierarchy (parent/child) on a dimension, and I want to use a data action to copy values to a different version. The problem arises because I want to aggregate on hierarchy node above the leaf members.

Below is a dummy data version of what I am trying to achieve, but follows the same pattern.

I have been using the following script:

On the following material dimension:

In the script I am using the property “test” to assign the data. But when I run the data action, nothing is assigned and examining the data action monitor, I can see no rows are affected.

I would expect to see the value of amount being assigned to the hierarchy levels TOTAL, ZFURNITURE and ZHIFI.

I have tried to copy all the Member ID’s to the “test” property, which works, but assigns all the data to the leaf levels. I have also tried only keeping the values for TOTAL, ZFURNITURE and ZHIFI in the “test” property, but to no effect.

Does anyone have some pointers in terms of aggregation with Data Actions?

Best regards,

Rasmus

[marcelo_portella · Accepted solution]

Hi

You need to have an alternative hierarchy where the members you want to use as the aggregated level are actually leaf members.

Then you have to set this hierarchy on the data action script, using the global definition CONFIG.HIERARCHY = [d/Dimension].[h/Hierarchy].

This is the only way to write in a node hierarchy member, turning it into a leaf member in a secondary hierarchy, and making sure the data action recognize it as such.

Cheers

[N1kh1l · Accepted solution]

marcelo_portella

I have tried this approach but for some reason it still does not work. I had seen this example in OpenSAP course but could not get it to work.

Also explained here:

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/1679129715024574b7bbb...

Product Dimension Setup:

AF Data Action:

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.HIERARCHY= [d/NA_SAP_CEP_PRODUCT].[h/H2] // set to alternate hier

MEMBERSET [d/Measures] = "AMOUNT"
MEMBERSET [d/Date] = "202201"
MEMBERSET [d/NA_SAP_CEP_PRODUCT].[p/BRAND]="P0"

DATA([d/NA_SAP_CEP_PRODUCT]=[d/NA_SAP_CEP_PRODUCT].[p/BRAND]) = RESULTLOOKUP([d/Version]="public.Actual")<br>

Output:

Data Action Log: It shows 0 records affected.

Nikhil

[chaznosterous · Accepted solution]

Hi Marcelo,

I have now tried what you suggested, but to no effect:

Material dimension:

DA script:

CONFIG.HIERARCHY = [d/Material].[h/H2] //AGG_hierachy in material dimension
MEMBERSET [d/MEASURE] = "Amount"
DATA([d/Material] = [d/Material].[p/P2D]) = RESULTLOOKUP([d/Version] = "public.Actual") //P2D = AGG_PROP2 property in material dimension

Resulting story with changed hierachy:

Again this does not fit the case where you want to aggregate to the node above the leaf members and use another dimension to be the driver for distribution for the budget for instance.

Can you give an example of a dimension setup where this would work?

Best regards

Rasmus

[marcelo_portella · Accepted solution]

It seems the base level members that have data are not on you secondary hierarchy at all, so they are not read by the script when you define to step to use that particular hierarchy.

They should be at least root. You can make sure you are picking them by using the memberset filter. You can define a hier node to hold all basemembers as well.

[N1kh1l · Accepted solution]

marcelo_portella

I added the P1, P2, P3 members also as root in the second hierarchy but for some reason it does not write anything. Even scoping the products explicitly did not work. Not sure if I am still missing something.

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.HIERARCHY= [d/NA_SAP_CEP_PRODUCT].[h/H2]

MEMBERSET [d/Measures] = "AMOUNT"
MEMBERSET [d/Date] = "202201"
MEMBERSET [d/NA_SAP_CEP_PRODUCT]=("P1","P2","P3")
MEMBERSET [d/NA_SAP_CEP_ACCOUNT]="GROSS_SALES"

DATA([d/NA_SAP_CEP_PRODUCT]=[d/NA_SAP_CEP_PRODUCT].[p/BRAND]) = RESULTLOOKUP([d/Version]="public.Actual")

Nikhil

[marcelo_portella · Accepted solution]

Hi friends.

It works here like this:

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.GENERATE_UNBOOKED_DATA = ON
CONFIG.HIERARCHY = [d/Product].[h/AltHier]

MEMBERSET [d/Measures] = "Value"
MEMBERSET [d/Date] = "202301"
MEMBERSET [d/Product] = BASEMEMBER([d/Product].[h/AltHier] , "Leaf")

DATA([d/Product]=[d/Product].[p/Group]) = RESULTLOOKUP([d/Version]="public.Actual")

Cheers!

[chaznosterous · Accepted solution]

Hi Marcelo,

Got a chance to test this today and this works, thanks for clarifying this.

Kind regards,

Rasmus

[N1kh1l]

chaznosterous

The reason its not affecting any row is because DATA() and RESULTLOOKUP() only work for base members of dimension. The test attributes has values like ZHIFI and ZFURNITURE which are parent nodes and hence cannot be written back to. If you maintain a base member as attribute value in test it will work.

If you maintain a base member (ZHIFI_D, ZFURNITURE_D etc. ) as attribute value in test it will work.

You need to create this property values also as member first.

Below is the syntax of DATA() and it only accepts leaf members as id/property/parameter/variable member.

DATA([<measures/dimension>=<member_id>,...],[<date_dim>=<date_scope_function>])

<member_id> ::= { <leaf_member> | <#VARIABLEMEMBER> | <parameter_single_leaf> | <dimension> | [<linked_model>.]<dimension> | [<linked_model>.]<dimension>.<property> | ELIMMEMBER() }

Hope this helps !!

Nikhil

[chaznosterous]

Hi Nikhil,

Thanks for the advice, I tired implementing what you suggested, but I think I might be misunderstanding you (and the documentation)?

Do you mean updating the members of the Material dimension with the ZHIFI_D, ZFURNITURE_D and ZTOTAL_D? Like so:

This makes the script aggregate on the ZHIFI_D, ZFUR_D and ZTOTAL_D for my budget version. However this makes it unviable for disaggregation on the original leaf members, plus they do not display on the rows in a story.

My objective is to aggregate to the ZTOTAL, ZHIFI and ZFURNITURE level and leave the underlying leaf members blank.

Best regards

Rasmus