How to calc accumulate rate in a script logic?

[Former Member]

Hi experts,

We are trying to generate a monthly accumulate rate in a script logic. We have a Unit rate in 12 months and we want UnitAccum rate (like YTD) in the same 12 months

Finally, we need to use this new rate in a multiplication.

How can we do it with a script logic? Any idea out there?

Kind regards

Albert Mas

[esjewett]

Maybe something like the following:

[ACCOUNT].[#UNITACCUM] = ([ACCOUNT].[UNITS],[MEASURES].[YTD])
*COMMIT

You could use the ([ACCOUNT].[UNITS],[MEASURES].[YTD]) tuple in a *REC(EXPRESSION= ... ) statement or a dimension member formula as well, in order to multiply the YTD value by something else without actually writing the UNITACCUM account value to the cube.

Ethan

[Former Member]

Hi Ethan,

Thanks for your help.

You can create the dimension member formula that you have written, and it works well when you build queries at Excel, but if you try to use that member in a script logic, when you execute the script logic with a simple example with few records, it takes too much time and at the end, it's cancelled.

Some more ideas?

Thanks in advance,

Albert Mas

[esjewett]

Hi Albert,

I might be misunderstanding you, but I think you said you created an account UNITACCUM with dimension member formula:

([ACCOUNT].[UNITS],[MEASURES].[YTD])

You can do that, but the account won't work in script logic. What I meant (but probably didn't say clearly) is that you should create a base level account UNITACCUM (not a formula account) and then populate it from script logic using the logic:

[ACCOUNT].[#UNITACCUM] = ([ACCOUNT].[UNITS],[MEASURES].[YTD])
*COMMIT

Alternatively, if you are at BPC NW 7.0 SP08 or above and you know that the UNITS account has a value in the current period, I believe the following script logic will be a lot faster because it will only run for intersections where there is a record in the UNITS account:

*WHEN ACCOUNT
*IS UNITS
*REC(EXPRESSION=([ACCOUNT].[UNITS],[MEASURES].[YTD]), ACCOUNT=UNITACCUM)
*ENDWHEN

The value in the account UNITACCUM will then be available to your script logic calculations.

Please let me know if I'm not understanding something here!

Thanks,

Ethan

[Former Member]

Hi Ethan,

You are understanding ok

We are working on SAP BPC NW version 7.0, SP05, and at the moment, it's impossible to update to SP08.

We have done all the following tests, trying to find some solution, under these conditions (we knew that some tests weren't going to work well, but anyway, we had to try them):

- always for only one product (*XDIM_MEMBERSET PRODUCT=Product1)

- UNITS and UNITACCUM are ACCOUNT base members

- UCostVtaAcumDimFor is an ACCOUNT Formula Base Member ([ACCOUNT].[UNITS],[MEASURES].[FYTD])

- FYTD is a Measures base member for Fiscal Year To Date (we use it because we want to see as Year to Date (YTD) as Fiscal Year To Date (first month of the year is September and the last one is August) (it works well)) (We have also tested on YTD instead of FYTD, with the same results)

(1)

*XDIM_MEMBERSET TIEMPO=2010.FEB

[ACCOUNT].[#UNITACCUM] = ([ACCOUNT].[UNITS],[MEASURES].[FYTD])

*COMMIT

Result: MEASURES not specified. Failed

(2)

*XDIM_MEMBERSET TIEMPO=2010.FEB

*WHEN ACCOUNT

*IS UNITS

*REC(EXPRESSION=([ACCOUNT].[UNITS],[MEASURES].[FYTD]), ACCOUNT=UNITACCUM)

*ENDWHEN

*COMMIT

Result: MEASURES not specified. Failed

(3)

*XDIM_MEMBERSET TIEMPO=2010.FEB

*WHEN ACCOUNT

*IS UNITS

*REC(ACCOUNT=UNITACCUM,MEASURES=FYTD,FACTOR=1)

*ENDWHEN

*COMMIT

Result: Unknown Dimension Name in Keyword "MEASURES". Failed

(4)

*XDIM_MEMBERSET TIEMPO=2010.FEB

*WHEN ACCOUNT

*IS UNITS

*REC(ACCOUNT=UNITACCUM,TIEMPO=TIEMPO.NEXTMONTH,FACTOR=1)

*ENDWHEN

*COMMIT

Result: it takes too much time (more than five minutes) without ending the process --> We stop this test

(for 2010.FEB, TIEMPO.NEXTMONTH=2010.MAR)

(5)

*XDIM_MEMBERSET TIEMPO=2010.FEB

*WHEN ACCOUNT

*IS UNITS

*REC(ACCOUNT=UNITACCUM,TIEMPO=TIEMPO.PREVMONTH,FACTOR=1)

*ENDWHEN

*COMMIT

Result: it takes too much time (more than ten minutes) without ending the process --> We stop this test

(for 2010.FEB, TIEMPO.PREVMONTH=2010.JAN)

(6)

*XDIM_MEMBERSET TIEMPO=2010.FEB

*WHEN ACCOUNT

*IS UNITS

*REC(ACCOUNT=UNITACCUM,FACTOR=1)

*ENDWHEN

*COMMIT

Result: Cancelled

(7)

*XDIM_MEMBERSET TIEMPO=2010.FEB

[ACCOUNT].[#UNITACCUM] = [ACCOUNT].[UCostVtaAcumDimFor]

*COMMIT

(UCostVtaAcumDimFor is a Formula Base Member ([ACCOUNT].[UNITS],[MEASURES].[FYTD]))

Result: Cancelled

Some more ideas?

Thanks in advance,

Albert Mas

[esjewett]

Hi Albert,

Your first two options look good to me, but you're getting that error (I think) because you need to specify a MEASURES memberset like

*XDIM_MEMBERSET MEASURES = PERIODIC
*XDIM_MEMBERSET TIEMPO=2010.FEB 

[ACCOUNT].[#UNITACCUM] = ([ACCOUNT].[UNITS],[MEASURES].[FYTD])
*COMMIT

I actually recommend trying your code using the YTD measure first instead of your custom FYTD measure. I think there are issues with validating script logic that references custom measures, though if you ignore the validation errors it should actually work with your FYTD measure. So first try the code below and see if it works, and then try the code above:

*XDIM_MEMBERSET MEASURES = PERIODIC
*XDIM_MEMBERSET TIEMPO=2010.FEB 

[ACCOUNT].[#UNITACCUM] = ([ACCOUNT].[UNITS],[MEASURES].[YTD])
*COMMIT

I'm not sure about what other dimensions you have in your application, but if you have a lot of dimensions or 1 or 2 large dimensions, then this script could be very slow. I recommend testing while restricted to a single record by having an *XDIM_MEMBERSET statement for each dimension. Only once you find a script that works properly, then un-restrict your memberset slowly and see what the impact is.

If you use the *REC option, it will be faster than MDX, but only after SP08 or the note [1463291|https://service.sap.com/sap/support/notes/1463291] is applied. As you say you cannot go to SP08, you may want to consider applying the note if you can't get good enough performance without it, but please keep in mind that this note can change the behavior of existing script logic.

Ethan

[Former Member]

Hi Ethan,

Thanks for your help, again.

We have tested your options, with the same result: Cancelled

All the tests have been done using *XDIM_MEMBERSET statement for each dimension (and only for one product and for one month). We have written more logic scripts with the same *XDIM_MEMBERSET restrictions, and they have run successfully,

At the moment, we cannot update the version or apply any note.

Some more ideas?

Thanks in advance,

Albert Mas

[esjewett]

Hi Albert,

In that case I think we must be running into a support pack incompatibility. I've put almost this exact code into production at a client on an SP06 system and I tested it on an SP07 system before recommending it to you.

For comparison purposes, the specific code I tested in a pretty small test application was:

*XDIM_MEMBERSET TIME=2009.FEB
*XDIM_MEMBERSET MEASURES=PERIODIC

[CATEGORY].[#PLAN] = ([CATEGORY].[ACTUAL],[MEASURES].[YTD])

I'm not familiar enough with SP05 (I skipped straight from SP03 - SP06) to be able to know if the SP-level is causing the problem with this code. Sorry it is not working for you

Unfortunately I don't have any other ideas as far as using script logic to do this. At this point, if you have no flexibility around upgrades I would recommend writing a BADI to do what the code above does (populate an account), then use the account that stores the result of the calculation in your subsequent calculations.

Ethan

[Former Member]

Hi Ethan,

Thank you very much for your help.

We'll try to solve this problem working on BADIs.

Albert Mas