Script Logic is running on too large a selection; try using MAXMEMBERS exception

[Former Member]

Hello Experts,

Below is my BPC script for converting YTD data from consolidation model to a Periodic data in a periodic Model FLSPLANNING.

I am on SAP BPC 10.1 on HANA Service Pack 2.When I run this code from DM, I am getting an exception "Logic is running on too large a selection; try using MAXMEMBERS" I tried using MAXMEMBERS too but still getting the error.Please let me know the issue

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MAXMEMBERS ENTITY=10

*XDIM_MAXMEMBERS ACCOUNT=100

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE

*SKIP_DIM = FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*FOR %T1% = %TIME_SET%

//*XDIM_MEMBERSET TIME = %T1%

*WHEN_REF_DATA = MASTER_DATA

*WHEN TIME.PERIOD

*IS JAN

           *WHEN ACCOUNT.ACCTYPE

           *IS INC, LEQ

                     *REC(EXPRESSION = -%VALUE%)

           *ELSE

                     *REC(EXPRESSION = %VALUE%)

           *ENDWHEN

*ELSE        

           *WHEN ACCOUNT.ACCTYPE

           *IS INC, LEQ

                   *REC(EXPRESSION = -%VALUE% + [TIME].[TMVL(-1,%T1%)])

           *ELSE

                   *REC(EXPRESSION = %VALUE% - [TIME].[TMVL(-1,%T1%)])

           *ENDWHEN

*ENDWHEN

*NEXT

Thanks,

Senoy

[former_member186338 · Accepted solution]

Hi Senoy,

Just to confirm: you want to convert from YTD to PERIODIC for some number of months selected by user in %TIME_SET%?

Then I will provide some better script.

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Yes Users needs flexibilty to add time in DataManager Package.They may enter for few months or  entire months in a year

Thanks

[former_member186338 · Accepted solution]

In general, there is no 100% perfect solution for this simple task (may be only BADI). The issue is that *WHEN_REF_DATA = MASTER_DATA statement affects ALL dimension and as result dramatically degrades performance (too many members are scoped).

1st solution - using MEASURES in REC - also not very fast but straightforward:

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MAXMEMBERS ACCOUNT=100

*XDIM_MEMBERSET TIME = %TIME_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE

*SKIP_DIM = FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*WHEN TIME

*IS *

*REC(EXPRESSION=[MEASURES].[YTD])

*ENDWHEN

Please, pay attention to the fact that in BPC 10 you don't need to reverse signs for INC and LEQ as in BPC 7.5.

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thanks for your expert comments.My consolidation model is YTD based and FLSPLANNING model is periodic.So do we have to write *REC(EXPRESSION=[MEASURES].[PERIODIC]) instead of *REC(EXPRESSION=[MEASURES].[YTD])?

Thanks,

Senoy

[former_member186338 · Accepted solution]

Yes, typo mistake! For sure [MEASURES].[PERIODIC]

2nd option is to use FOR/NEXT - also not very fast solution, but you have to compare:

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MAXMEMBERS ACCOUNT=100

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=TMVL(-1,%T%),%T%

*WHEN TIME

*IS %T%

  *REC(EXPRESSION=%VALUE%)

*IS TMVL(-1,%T%)

  *WHEN TIME.PERIOD

  *IS <>DEC

    *REC(EXPRESSION=-%VALUE%,TIME=%T%)

  *ENDWHEN

*ENDWHEN

*NEXT

Vadim

[Former Member · Accepted solution]

Hello Vadim,

When I try the 1st method in UJKT, I am getting below error.

REC :[MEASURES].[PERIODIC]

UJK_EXECUTION_EXCEPTION:MEASURES not specified

Thanks

[former_member186338 · Accepted solution]

Please try to add the line before WHEN/ENDWHEN

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE

*SKIP_DIM = FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*XDIM_MEMBERSET MEASURES=YTD //looks useles, but...!

*WHEN TIME

*IS *

*REC(EXPRESSION=[MEASURES].[PERIODIC])

*ENDWHEN

At least in BPC 7.5 this line was required to use MEASURES in REC

Vadim

[former_member186338 · Accepted solution]

Please remove line:

*XDIM_MAXMEMBERS ACCOUNT=100

From the 2nd option code!

[Former Member · Accepted solution]

Hello Vadim,

Thanks a lot for your help.I tried option two and was giving correct results for dummy data.

Which option should I go with?

Would not using *WHEN_REF_DATA = MASTER_DATA create any issue?

Also, I had written a simple script to copy data from one model to another model in some other project The Script used to fail when the number or records increased to around 800000.

Will we face the same issue if the data volume is very huge?

Thanks

[former_member186338 · Accepted solution]

Script option 2 will work fine for the reasonable number of months (at least 12 months is ok). This script is created to avoid usage of *WHEN_REF_DATA = MASTER_DATA:

For each month in the loop we scope this month and previous month and write data as is for this month and negative value for previous month (with exception if previous month is DEC). It will work fine even if some records for this month or previous month are missing - just test!

The script with:

*XDIM_MEMBERSET MEASURES=YTD //looks useles, but...!

*WHEN TIME

*IS *

*REC(EXPRESSION=[MEASURES].[PERIODIC])

*ENDWHEN

Have to work also!

You have to compare performance of both (I don't have HANA to compare).

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thanks a lot for your time and efforts.I will get back to you with the results of the testing.I did a test for 100 records and it didnt took more than 10 seconds.

Thanks,

Senoy

[former_member186338 · Accepted solution]

Have you tested both?

[Former Member · Accepted solution]

Hello Vadim,

I am currently testing option two but Option two is not taking all time members which I am entering in the DM

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

//*XDIM_MEMBERSET TIME = %TIME_SET%

//*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

//*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=TMVL(-1,%T%),%T%

*WHEN TIME

*IS %T%

  *REC(EXPRESSION=%VALUE%)

*IS TMVL(-1,%T%)

  *WHEN TIME.PERIOD

  *IS <>DEC

    *REC(EXPRESSION=-%VALUE%,TIME=%T%)

  *ENDWHEN

*ENDWHEN

*NEXT

This is the selection

SELECTION = /ProjectName/FLSCONSOLIDATION/PRIVATEPUBLICATIONS/50023586/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:TIME|2017.01,2017.02,2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12

But its taking only two time members and populating values only for them

#dim_memberset=3

SCOPE:NOSCOPE,1 in total.

FLOW:FCLO,1 in total.

TIME:2016.12,2017.01,2 in total.

Thanks,

Senoy

[Former Member · Accepted solution]

Hello Vadim,

When I try Option 1, System gives an error that YTD does not match with storage type

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

// *XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

// *XDIM_MEMBERSET ENTITY=%ENTITY_SET%

// *XDIM_MEMBERSET TIME = %TIME_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE

*SKIP_DIM = FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*XDIM_MEMBERSET MEASURES= YTD

*WHEN TIME

*IS *

*REC(EXPRESSION=[MEASURES].[PERIODIC])

*ENDWHEN

Thanks

Thanks

[former_member186338 · Accepted solution]

Hi Senoy,

You are looking only on the beginning of the log. Each FOR/NEXT loop iteration will process single member from %TIME_SET% generating scope with 2 periods: one member from %TIME_SET% and another - previous time member calculated as TMVL(-1,%T%). If you look on the log you will see othe time periods processed.

To test script it's better to use UJKT:

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Sorry for the incovenience.I tested it again and found that the option 2 changes the source YTD application data itself.Only Jan Data is posted to Target application and remaining Months data is getting subtracted in the source application itself

Attaching the UJKT test results

Thanks

Push Data Testing.txt.zip

[former_member186338 · Accepted solution]

Ups, sorry, I forgot that in BPC 10 *DESTINATION_APP works only for the next single WHEN/ENDWHEN! (In BPC 7.5 it will work for all WHEN/ENDWHEN after *DESTINATION_APP):

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MAXMEMBERS ACCOUNT=100

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=TMVL(-1,%T%),%T%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*WHEN TIME

*IS %T%

  *REC(EXPRESSION=%VALUE%)

*IS TMVL(-1,%T%)

  *WHEN TIME.PERIOD

  *IS <>DEC

    *REC(EXPRESSION=-%VALUE%,TIME=%T%)

  *ENDWHEN

*ENDWHEN

*NEXT

Now *DESTINATION_APP is inside FOR/NEXT!

Vadim

[former_member186338 · Accepted solution]

P.S. Definitely it was not a clever decision to to change the logic of *DESTINATION_APP dramatically between BPC versions. If you upgrade from 7.5 to 10 you have to review all scripts

[former_member186338 · Accepted solution]

P.P.S. Tested both methods in my test BPC 10 system!

Second method works fine! *DESTINATION_APP has to be inside FOR/NEXT.

First method also works, but some strange thing have to be corrected in the code:

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE

*SKIP_DIM = FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*XDIM_MEMBERSET MEASURES=PERIODIC //looks useles, but...!

*WHEN TIME

*IS *

*REC(EXPRESSION=[MEASURES].[PERIODIC])

*ENDWHEN

Strange to set MEASURES to PERIODIC (may be to be compatible with target cube...)

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thanks again.

I too checked from my side.I found Option 1 is little slower around 19 seconds for 180 records as compared to 17 seconds from Option 2

But Option 2 I found an issue.I had an account BACKLOG.IC with AUDITSOURCE=HINP and TBINPUT values in Consolidation Model.The target Model has only AUDITTRAIL=HINP.So ideally it should reject those values with TBINPUT while loading the data into Plan Model.For such cases, Option 2 is giving wrong values in Periodic Model

Option 1 is giving correct results in all cases

Below is the Consolidation Cube

Below are the results of Option 1 and Option 2 in Periodic Model

Thanks,

Senoy

[former_member186338 · Accepted solution]

Hi Senoy,

If you are moving data between cubes with RENAME_DIM you have to be always sure that the members are properly scoped! Only members existing in both cubes have to be scoped (or some property conversion is used).

Just add line:

*XDIM_MEMBERSET AUDITSOURCE=HINP

to other XDIM's, and you will have same results!

Vadim

[former_member186338 · Accepted solution]

P.S. And if for some reason you want to combine figures from HINP and TBINPUT (just theoretically) use the following:

*XDIM_MEMBERSET AUDITSOURCE=HINP,TBINPUT

...

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW,AUDITSOURCE //Use single line for SKIP_DIM as mentioned in help!

*ADD_DIM AUDITTRAIL=HINP

In this case values in source for HINP and TBINPUT will be accumulated in target HINP!

B.R. Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thanks for your help.I kept the filter as you mentioned but I am not getting correct result.

I investigated further and found that Balance sheet Accounts with Account Type AST or LEQ when moved to Periodic Model, there values are not changed(Periodic values in Periodic Model are same as the YTD values in Consolidation Model).Profit and Loss Accounts with Account Type LEQ or EXP when moved to Periodic Model, there values changes based on monthly calculation(Month2-MONTH1 happens).This is the case when I use MEASURES option

In the Option where I use the For Loop, all the accounts are changed to periodic values

Thanks

[former_member186338 · Accepted solution]

O! Simply forget to tell you, that AST and LEQ are always YTD - in YTD and in PERIODIC cube. For AST and LEQ use separate simple WHEN/ENDWHEN without FOR/NEXT just to copy values.

Vadim

[former_member186338 · Accepted solution]

Something like:

*XDIM_MEMBERSET SCOPE = NOSCOPE

*XDIM_MEMBERSET FLOW = FCLO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MEMBERSET AUDITSOURCE=HINP

*SELECT(%PL%,"[ID]",ACCOUNT,"[ACCTYPE]='EXP' OR [ACCTYPE]='INC'")

*XDIM_MEMBERSET ACCOUNT=%PL%

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=TMVL(-1,%T%),%T%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*WHEN TIME

*IS %T%

  *REC(EXPRESSION=%VALUE%)

*IS TMVL(-1,%T%)

  *WHEN TIME.PERIOD

  *IS <>DEC

    *REC(EXPRESSION=-%VALUE%,TIME=%T%)

  *ENDWHEN

*ENDWHEN

*NEXT

*SELECT(%BS%,"[ID]",ACCOUNT,"[ACCTYPE]='AST' OR [ACCTYPE]='LEQ'")

*XDIM_MEMBERSET ACCOUNT=%BS%

*XDIM_MEMBERSET TIME=%TIME_SET%

*DESTINATION_APP = FLSPLANNING

*SKIP_DIM = SCOPE,FLOW

*RENAME_DIM AUDITSOURCE=AUDITTRAIL

*WHEN TIME

*IS *

  *REC(EXPRESSION=%VALUE%)

*ENDWHEN

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thanks a lot for your time and efforts

Regards,

Senoy

[Former Member · Accepted solution]

Hello Vadim,

Hope you are doing well.

I have created a sdn post with similar YTD to periodic conversion issue

I know why its giving wrong values but not sure on how to rectify it

Request your expert help in solving the issue

Thanks,

Senoy