Data Volume - Calculation performance

[Former Member]

Hi,

We are experience degrading calculation performance as data volume increases.

We are implementing BPC 7.5 SP05 NW (on BW 7.0. EHP1).

An allocation script that ran in 2 minutes when the database contained only 800,000 records, took over 1 hour after the database was populated with a full year of data.

All logics have been written to calculate on a reduced defined scope but it does not seem to improve the execution time. When I check the formula log, the scope is respected.

The application is not that large either: 12 dimensions, the largest containing 300 members and 3 hierarchical levels.

We optimize the database but to no avail.

What can be done to optimize performance? Are there any technical settings in BPC or BW that can be fine-tuned?

Thanks,

Regis

[Former Member · Accepted solution]

Hi Regis,

In BPC usually, if we increase the volume of data then performance definitely degrades. I do agree it is taking much more time for you to make any calculations. I would request you to implement the folowing steps:

In BPC Admin:

1. Full Optimize the applications (This will compress data base).

2. Process all dimensions. (Take system offline and then take it online).

3. Refresh client side dimensions.

4. Logout and login once again.

Hope this resolves you issue. If not please let me know the exact version of BPC which you are using.

Rgds,

Poonam

[Former Member · Accepted solution]

Hi Poonam,

Actually we have performed these housekeeping tasks and to some extent, it does improve performance a bit but not significantly.

Thanks

Regis

[Former Member · Accepted solution]

Hi Regis,

Performance issues can arise due to many reasons. i would suggest you to check with SAP Note: 1424170. Here are the excerpts:

1. Log on to ABAP system.

2. Type in Transaction code SE91

3. Type in "UJK_EXCEPTION" in Message Classes

4. Choose "Change" button

5. Go to line 92, change the message to "Dimension set:"&DATAVALUE&" not assigned in Data Manager"

6. Click "Save" button on the toolbar.

Hope this resolves your issue.

Incase it doesnot, i would again like to know the exact version of BPC on whic you are working.

Rgds,

Poonam

[esjewett]

Hi Regis,

How many records are there in a full year? Can you share your allocation script with us? This kind of information might help you get a better answer.

I'd also recommend taking a look at Gersh's RUN_LOGIC BADI, which you might be able to use to run your allocation in parallel - [http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/b0ad38cf-9e0e-2e10-9d9a-fbf57e69cd40].

Cheers,

Ethan

[Former Member]

Hi Ethan,

I'd like to share the script with you but it does fit in this text area.

We are actually looking at using the RUNLOGIC BADI to improve performance or even write an entire BADI to perform the same calculation.

I was just wondering whether there could be some technical settings that could be applied in BW to at least control the performance as data volume grows.

Thanks

Regis

[esjewett]

Hi Regis,

Maybe Pastebin it? [http://pastebin.com/]

Can you tell us how much data we are talking about? If your script runs in 2 minutes on 800,000 records and takes over an hour on 25,000,000 records, that would be expected and you need to look into optimizing and parallelizing your calculation - there is not much to be done on the BW side of the house. If it takes over an hour on 2,000,000 records, then something is probably wrong!

Cheers,

Ethan

[Former Member]

Hi Ethan,

Take a look at one of the allocation script: http://pastebin.com/TA16xCd3

We are testing RUNLOGIC but we are facing some problems in two situations:

- passing the DM package variable to the RUNLOGIC script

- using a passed variable in the called script

The DM prompts for 3 selections: ENTITY, TIME and CATEGORY.

The RUNLOGIC script:

*SELECT(%DIVISIONS%,"[ID]",DIVISION,"[LEVEL]='DIV' AND [STORECOMMON]<>'Y'")
*SELECT(%BRANCHES%,"[ID]",BRANCH,"[BRANCHTYPE]='STORE'")

*START_BADI RUNLOGIC
     QUERY=OFF
     WRITE=ON
     LOGIC=ALLOC_DIV_ACTUAL_S.LGF
     DIMENSION ENTITY=C1000
     DIMENSION TIME=FY10.MAY
     DIMENSION CATEGORY=ACTUAL
     DIMENSION DIVISION=%DIVISIONS%
     DIMENSION DIVISION=%BRANCHES%
     CHANGED=ENTITY,TIME,CATEGORY,DIVISION,BRANCH
     DEBUG=ON
*END_BADI

In ALLOC_DIV_ACTUAL_S.LGF, we are using a %DIVISION_SET% variable. At the time of validating, we get a message "Member "" does not exist".

When we run the package, it fails with the same error message:

An exception with the type CX_UJK_VALIDATION_EXCEPTION occurred, but was neither handled locally, nor declared in a RAISING clause
Member "" not exist

Thanks

Regis

[former_member200327]

Hi Regis,

A couple comments.

First of all, you can't use same DIMENSION more than once in RUNLOGIC like you are using it for DIVISION.

Second, I'd suggest starting from less dimensions in CHANGED: 5 dimensions will create a lot of separate tasks and overhead can be more than gain.

Third, can you first split you long script into a sequence of smaller once, like separate WHEN/REC/ENDWHEN, separate RUNALLOCATION, etc. Than check which script takes most of the time and try to optimize that script first.

After we know that it will be easier to help.

Regards,

Gersh

[Former Member]

Hi Gersh,

Actually, that was a typo. Now, after removing TIME, CATEGORY and ENTITY (which would have been reduced to one member each anyways, the members selected in the prompt), I simplified to the following

*SELECT(%DIVISIONS%,"[ID]",DIVISION,"[LEVEL]='DIV' AND [STORECOMMON]<>'Y'")
*SELECT(%BRANCHES%,"[ID]",BRANCH,"[BRANCHTYPE]='STORE'")

*START_BADI RUNLOGIC
     QUERY=OFF
     WRITE=ON
     LOGIC=ALLOC_DIV_ACTUAL_S.LGF
     DIMENSION DIVISION=%DIVISIONS%
     DIMENSION BRANCH=%BRANCHES%
     CHANGED=DIVISION,BRANCH
     DEBUG=ON
*END_BADI

But I still get the same error message at run-time.

As for the part of the script that is most impacting the performance, following your suggestion, we narrowed it down to the RUNALLOCATION sections.

Thanks

Regis

[former_member200327]

Hi Regis,

This message looks like some member set is empty. Can you please try running this script with %DIVISION% and %BRANCHES% hard-coded to some values just to see if the error is still there?

Also, I think you have 4 allocations in that script. Do you know which one is the slowest? And in some of the RUNALLOCATION I can see FACTOR = USING/TOTAL/USING. is it a typo as well?

Regards,

Gersh

[Former Member]

Hi Gersh,

The error actually comes from the callee script rather than the calling script. If I hard code members instead of using %DIVISIONS% and %BRANCHES%, it does not make any difference. The problem seems to be caused by the %DIVISION_SET% in the callee script.

As for the part of the script that takes time, all sections using RUNALLOCATION take a larger amount of time than the WHEN/ENDWHEN sections.

The FACTOR=USING/TOTAL/USING is not a typo, that's actually how it needs to calculate. You would think it should be equivalent to 1/TOTAL but actually it's not and it generates the result I expect.

Thanks,

Regis

[former_member200327]

Hi Regis,

A couple more question regarding your scripts.

First, you mentioned %DIVISION_SET% in ALLOC_DIV_ACTUAL_S.LGF. I can't find it there.

Second, you redefining DIVISION and BRANCH in RUNLOGIC call; at the same time you defining those Dimensions in ALLOC_DIV_ACTUAL_S.LGF with XDIM_MEMBERSET. This will overwrite sets passed by RUNLOGIC. If you need Dimensions as defined inside ALLOC_DIV_ACTUAL_S.LGF then remove DIMENSION statements from RUNLOGIC; if you need those as defined in the main scrip then remove XDIMs from ALLOC_DIV_ACTUAL_S.LGF.

Third, if you want to run scripts in parallel on some Dimension do not redefine that Dimension with XDIM_MEMBERSET in ALLOC_DIV_ACTUAL_S.LGF.

Hope this helps,

Gersh