BPC Script Logic Tripling Values

[Former Member]

We're using BPC 10.1 SP2 for NW and seeing odd behavior with BPC Script.  I've taken my actual more complicated script and stripped it down to a very simple form to confirm that the values are still tripled.  Note that the value is tripled on the first run.  Subsequent runs do NOT increase the value.

I have found a work-around by using a FOR loop, but this changed the run time from my script from 55 seconds to 18 minutes.  My code will eventually reside within a package and in Default logic, so 18 minutes is unacceptable.  I have hard-coded a few of the members for test purposes.  Any suggestions as to what might be happening?

Test Results:

1.  Multiple embedded WHEN statements did not change the behavior

2.  Omitting the MEASURES scoping did not change the behavior

3.  Limiting scope to a single ENTITY produced correct results but this is not a viable option

Code that triples:

*SELECT(%PROFIT_CENTERS%, "[ID]", "ENTITY", "[INTCO]<>''")

*XDIM_MEMBERSET ACCOUNT  = 701003

*XDIM_MEMBERSET AUDIT_ID = ALLOCATION_ENTRY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY   = %PROFIT_CENTERS%

*XDIM_MEMBERSET FLOW     = F_CLO

*XDIM_MEMBERSET INTERCO  = I_1000

*XDIM_MEMBERSET SCOPE    = S_NONE

*XDIM_MEMBERSET TIME     = 2012.03

*XDIM_MEMBERSET VERSION  = PLAN

*XDIM_MEMBERSET MEASURES = YTD

*WHEN CURRENCY

*IS LC

   *REC(EXPRESSION=(%VALUE%),VERSION="ACTUAL",AUDIT_ID="ALLOC_CALC_MGMT")

*ENDWHEN

Code that works but takes too long:

*SELECT(%PROFIT_CENTERS%, "[ID]", "ENTITY", "[INTCO]<>''")

*XDIM_MEMBERSET ACCOUNT  = 701003

*XDIM_MEMBERSET AUDIT_ID = ALLOCATION_ENTRY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY   = %PROFIT_CENTERS%

*XDIM_MEMBERSET FLOW     = F_CLO

*XDIM_MEMBERSET INTERCO  = I_1000

*XDIM_MEMBERSET SCOPE    = S_NONE

*XDIM_MEMBERSET TIME     = 2013.03

*XDIM_MEMBERSET VERSION  = PLAN

*XDIM_MEMBERSET MEASURES = YTD

*FOR %CENTER% = %PROFIT_CENTERS%

*WHEN ENTITY

*IS %CENTER%

   *REC(EXPRESSION=(%VALUE%),VERSION="ACTUAL",AUDIT_ID="ALLOC_CALC_MGMT")

*ENDWHEN

*NEXT

[Former Member]

We encountered a similar issue during an upgrade last month.  Take a look at note 2031804. We are on 10.0 release 801.  But I think release 810 is 10.1?  If so, it looks like this note was in SP03.  The description in the note doesn't make it apparent that it would resolve the situation, but it did for us.

[former_member5472]

ok.

lets try to narrow down the problem. Instead of using the select statement, can you hard code the 3 entity's instead of  Select ..%PROFIT_CENTERS% . and see whats the output ?

Also for this can you paste the UJKT log ?

Prat

[Former Member]

Code that appears to work UJKT log:

*XDIM_MEMBERSET AUDIT_ID = BAS(DIRECT_POSTING_MGMT)

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY = PC3050

*XDIM_MEMBERSET FLOW = F_CLO

*XDIM_MEMBERSET INTERCO = I_1000,I_1170

*XDIM_MEMBERSET SCOPE = S_NONE

*XDIM_MEMBERSET TIME = 2012.03

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET MEASURES = YTD

*WHEN ENTITY

*IS *

*REC(FACTOR=-1,AUDIT_ID=ALLOC_REVERSAL_MGMT)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2014-11-10 16:10:45

FILE:\ROOT\WEBFOLDERS\CMC_GLOBAL \ADMINAPP\CONSOLIDATION\TEST.LGF

USER:P00061693

APPSET:CMC_GLOBAL

APPLICATION:CONSOLIDATION

[INFO] GET_DIM_LIST(): I_APPL_ID="CONSOLIDATION", #dimensions=10

ACCOUNT,AUDIT_ID,CURRENCY,ENTITY,FLOW,INTERCO,MEASURES,SCOPE,TIME,VERSION

#dim_memberset=10

ACCOUNT:225029,225030,701003,701007,4 in total.

AUDIT_ID:ALLOCATION_ENTRY,CALC,ENTRY,GL_01,GL_02,...9 in total.

CURRENCY:LC,1 in total.

ENTITY:PC3050,1 in total.

FLOW:F_CLO,1 in total.

INTERCO:I_1000,I_1170,2 in total.

SCOPE:S_NONE,1 in total.

TIME:2012.03,1 in total.

VERSION:ACTUAL,1 in total.

MEASURES:YTD,1 in total.

REC :%value%*(-1)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 2  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :1.00 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDIT_ID CURRENCY ENTITY FLOW INTERCO SCOPE TIME VERSION SIGNEDDATA

225030 ALLOC_REVERSAL_MGMT LC PC3050 F_CLO I_1170 S_NONE 2012.03 ACTUAL - 3.00

701003 ALLOC_REVERSAL_MGMT LC PC3050 F_CLO I_1000 S_NONE 2012.03 ACTUAL - 8.00

2  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2014-11-10 16:10:45

Code that doubles the value:

*XDIM_MEMBERSET ACCOUNT = 701003,701007,225029,225030

*XDIM_MEMBERSET AUDIT_ID = BAS(DIRECT_POSTING_MGMT)

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY = PC1000,PC1001,PC1002,PC1003,PC1004,PC1005,PC1006,PC1007,PC1008,PC1009,PC1010,PC1011,PC1012,PC1013,PC1014,PC1015,PC1016,PC1017,PC1018,PC1019,PC1020,PC1021,PC1022,PC1023,PC1024,PC1025,PC1030,PC1032,PC1040,PC1045,PC1050,PC1052,PC1055,PC1057,PC1058,PC1060,PC1061,PC1062,PC1090,PC1091,PC1092,PC1093,PC1094,PC1095,PC1096,PC1097,PC1098,PC1155,PC1160,PC1170,PC1199,PC1200,PC1202,PC1205,PC1210,PC1220,PC1221,PC1229,PC1230,PC1231,PC1232,PC1233,PC1235,PC1240,PC1241,PC1242,PC1243,PC1244,PC1245,PC1250,PC1260,PC1261,PC1270,PC1271,PC1272,PC1280,PC1290,PC1300,PC1310,PC1320,PC1325,PC1326,PC1330,PC1331,PC1339,PC1340,PC1341,PC1342,PC1343,PC1360,PC1361,PC1362,PC1370,PC1371,PC1372,PC1390,PC1391,PC1392,PC1405,PC1406,PC1407,PC1408,PC1409,PC1410,PC1411,PC1412,PC1413,PC1414,PC1429,PC1430,PC1431,PC1432,PC1433,PC1434,PC1435,PC1436,PC1437,PC1438,PC1439,PC1440,PC1441,PC1450,PC1451,PC1452,PC1453,PC1454,PC1455,PC1459,PC1540,PC1541,PC1542,PC1543,PC1544,PC1545,PC1546,PC1547,PC1548,PC1549,PC1550,PC1551,PC1552,PC1553,PC1554,PC1556,PC1557,PC1558,PC1559,PC1560,PC1562,PC1563,PC1564,PC1565,PC1566,PC1567,PC1568,PC1569,PC1570,PC1571,PC1572,PC1573,PC1574,PC1576,PC1577,PC1578,PC1579,PC1580,PC1581,PC1582,PC1583,PC1584,PC1585,PC1586,PC1970,PC1980,PC1990,PC1991,PC1992,PC1994,PC1995,PC2000,PC2020,PC2030,PC2040,PC2050,PC2060,PC2070,PC2080,PC2085,PC2090,PC2091,PC2092,PC2110,PC2112,PC2122,PC2132,PC2142,PC2152,PC2153,PC2154,PC2155,PC2162,PC2172,PC2181,PC2182,PC2183,PC2184,PC2185,PC2186,PC2187,PC2188,PC2189,PC2190,PC2192,PC2193,PC2194,PC2195,PC2212,PC2213,PC2214,PC2215,PC2222,PC2232,PC2233,PC2234,PC2235,PC2236,PC2242,PC2252,PC2262,PC2272,PC2282,PC2292,PC2302,PC2312,PC2322,PC2332,PC2342,PC2352,PC2362,PC2372,PC2375,PC2405,PC2410,PC2420,PC2430,PC2440,PC2450,PC2460,PC2470,PC2480,PC2490,PC2500,PC2510,PC2520,PC2530,PC2540,PC2570,PC2575,PC2580,PC2600,PC2610,PC2670,PC2671,PC2672,PC2673,PC2674,PC2675,PC2676,PC2677,PC2678,PC2679,PC2680,PC2681,PC2682,PC2683,PC2684,PC2685,PC2686,PC2687,PC2688,PC2689,PC2690,PC2691,PC2692,PC2693,PC2694,PC2695,PC2696,PC2697,PC2698,PC2699,PC2700,PC2701,PC2702,PC2703,PC2704,PC2705,PC2706,PC2707,PC2708,PC2709,PC2710,PC2711,PC2712,PC2713,PC2714,PC2715,PC2716,PC2717,PC2718,PC2719,PC2720,PC2721,PC2722,PC2723,PC2724,PC2725,PC2726,PC2727,PC2728,PC2729,PC2730,PC2731,PC2732,PC2733,PC2734,PC3000,PC3001,PC3002,PC3010,PC3011,PC3012,PC3013,PC3030,PC3031,PC3032,PC3033,PC3034,PC3035,PC3040,PC3050,PC3060,PC3070,PC3080,PC3090,PC3091,PC3095,PC3100,PC3101,PC3102,PC3103,PC3104,PC3105,PC3106,PC3107,PC3108,PC3110,PC3111,PC3112,PC3113,PC3117,PC3118,PC3120,PC3121,PC3131,PC3132,PC3150,PC3151,PC3152,PC3153,PC3154,PC3155,PC3156,PC3157,PC3158,PC3159,PC3160,PC3161,PC3162,PC3163,PC3164,PC3165,PC3170,PC3171,PC3172,PC3173,PC3174,PC3175,PC3176,PC3177,PC3178,PC3179,PC3180,PC3182,PC3183,PC3184,PC3185,PC3186,PC3190,PC3191,PC3192,PC3193,PC3200,PC3201,PC3202,PC3900,PC3950,PC3999

*XDIM_MEMBERSET FLOW = F_CLO

*XDIM_MEMBERSET INTERCO = I_1000,I_1170

*XDIM_MEMBERSET SCOPE = S_NONE

*XDIM_MEMBERSET TIME = 2012.03

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET MEASURES = YTD

*WHEN ENTITY

*IS *

*REC(FACTOR=-1,AUDIT_ID=ALLOC_REVERSAL_MGMT)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2014-11-10 16:12:31

FILE:\ROOT\WEBFOLDERS\CMC_GLOBAL \ADMINAPP\CONSOLIDATION\TEST.LGF

USER:P00061693

APPSET:CMC_GLOBAL

APPLICATION:CONSOLIDATION

[INFO] GET_DIM_LIST(): I_APPL_ID="CONSOLIDATION", #dimensions=10

ACCOUNT,AUDIT_ID,CURRENCY,ENTITY,FLOW,INTERCO,MEASURES,SCOPE,TIME,VERSION

#dim_memberset=10

ACCOUNT:225029,225030,701003,701007,4 in total.

AUDIT_ID:ALLOCATION_ENTRY,CALC,ENTRY,GL_01,GL_02,...9 in total.

CURRENCY:LC,1 in total.

ENTITY:PC1000,PC1001,PC1002,PC1003,PC1004,...408 in total.

FLOW:F_CLO,1 in total.

INTERCO:I_1000,I_1170,2 in total.

SCOPE:S_NONE,1 in total.

TIME:2012.03,1 in total.

VERSION:ACTUAL,1 in total.

MEASURES:YTD,1 in total.

REC :%value%*(-1)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 1.00 ms. 8  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

4  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 4  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDIT_ID CURRENCY ENTITY FLOW INTERCO SCOPE TIME VERSION SIGNEDDATA

225030 ALLOC_REVERSAL_MGMT LC PC3010 F_CLO I_1170 S_NONE 2012.03 ACTUAL 0.00

225030 ALLOC_REVERSAL_MGMT LC PC3050 F_CLO I_1170 S_NONE 2012.03 ACTUAL - 6.00

701003 ALLOC_REVERSAL_MGMT LC PC3010 F_CLO I_1000 S_NONE 2012.03 ACTUAL 0.00

701003 ALLOC_REVERSAL_MGMT LC PC3050 F_CLO I_1000 S_NONE 2012.03 ACTUAL - 16.00

4  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:2.00 s.

LOG END TIME:2014-11-10 16:12:31

[former_member186338]

Hi,

The only possible issue with this code is:

You have

*XDIM_MEMBERSET AUDIT_ID = BAS(DIRECT_POSTING_MGMT) // 9 members!!!

and you write results to single AUDIT_ID member:

*REC(FACTOR=-1,AUDIT_ID=ALLOC_REVERSAL_MGMT)

Vadim

[former_member5472]

Hi,

I'm not sure why you getting the records in the scope when there is no transaction data.

Can you add,

*WHEN_REF_DATA=TRANS_DATA just to avoid any ambiguity and try ?

..

..

*ENDWHEN

Prat

[Former Member]

Good idea.  I just tried it, but it had no effect.

[former_member186338]

Hi,

If you are talking about default.lgf then please read my article

In general you have to avoid using scope changing operators in default.lgf. Use *IS instead.

*SELECT(%PROFIT_CENTERS%, "[ID]", "ENTITY", "[INTCO]<>''")

*WHEN CURRENCY

*IS LC

*WHEN ENTITY

*IS %PROFIT_CENTERS%

*WHEN ACCOUNT

*IS 701003

...

   *REC(EXPRESSION=(%VALUE%),VERSION="ACTUAL",AUDIT_ID="ALLOC_CALC_MGMT")

*ENDWHEN

*ENDWHEN

*ENDWHEN

...

Vadim

[Former Member]

Thank you Vadim.  Although I do have to create similar code in DEFAULT logic, I'm not at that point yet.  I'm currently just trying to get this simple script to work via a package.

I've never had this much trouble getting a script to work, so I think there is something misconfigured on the server or a bug, but I'm still testing.

[former_member186338]

Please show the screenshot of the admin screen of your ENTITY dimension!

Vadim

[Former Member]

[bishwajit_das]

Can you please try this :-

*SELECT(%PROFIT_CENTERS%, "[ID]", "ENTITY", "[INTCO]<>''")

*XDIM_MEMBERSET ACCOUNT  = 701003

*XDIM_MEMBERSET AUDIT_ID = ALLOCATION_ENTRY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY   = %PROFIT_CENTERS%

*XDIM_MEMBERSET FLOW     = F_CLO

*XDIM_MEMBERSET INTERCO  = I_1000

*XDIM_MEMBERSET SCOPE    = S_NONE

*XDIM_MEMBERSET TIME     = 2012.03

*XDIM_MEMBERSET VERSION  = PLAN

*XDIM_MEMBERSET MEASURES = YTD

*WHEN ENTITY

*IS *

   *REC(FACTOR=1,VERSION="ACTUAL",AUDIT_ID="ALLOC_CALC_MGMT")

*ENDWHEN

*COMMIT

Regards,

Bishwajit

[Former Member]

I tried that and got the same result.  It seems that I have 3 Entity members that are related to the problem.  Even though there is no data stored in those members, if they are included within the scope, then the data is tripled.  I removed one of the members because it was a test member and now my data always doubles.  I cannot remove the other two since they are required.

[former_member5472]

Hi,

Is your original code tripling the values for all entity values selected in "Select" statement or just the one entity ?

It looks like you have used the "When" condition in a wrong manner.  If you want to generate separate records for each entity with version as Actual and Audit id as ALLOC_CALC_MGMT then use when condition for entity dimension or use a *for loop

Prat

[Former Member]

Let me try a different entity member and then I'll respond.  I've been testing with the same entity, so I'm not sure.

A FOR loop solves the problem but turns my 55 second script into a 20 minute script and that is too long.

I don't know how I could be using the WHEN statement incorrectly.  I've written code exactly like this in 7.5 and 10.0 without this problem.

[Former Member]

FOR/NEXT loop calculates it right because it overrides previous postings.

In WHEN/ENDWHEN records are accumulated, so if you have same member multiple times it will add them up. In your script this can happen to ENTITY Dimension only.

[Former Member]

Right, that makes sense.  I've cleared the intersections for that ENTITY however, so there is only one intersection with data.  I placed 500 in that intersection via an input schedule.  Then I run the script and it places 1500 in the destination intersection.  Since there is only one intersection in the model for that Entity, I don't know what it would be adding.

[Former Member]

I tried another entity and the same problem exists.

It seems that I have 3 Entity members that are related to the problem.  Even though there is no data stored in those members, if they are included within the scope, then the data is tripled.  I removed one of the members because it was a test member and now my data always doubles.  I cannot remove the other two since they are required.

[Former Member]

I'm not sure I follow you.  I shouldn't have to use ENTITY within the WHEN statement.  In fact, I don't want to because I actually have some additional REC in the actual script (not the test script) that saves the value to a different ENTITY.  If I used *WHEN ENTITY, then it would sum all of the values and store in the target ENTITY which I don't want.

I have tried a FOR loop and it seems to fix the problem, however as stated in the original post, it makes my 55 second script take about 20 minutes.  That's a huge difference that the customer doesn't want.

[Former Member]

Update:  Through diligent testing of over 430 Entity members, I have found that the removal of 3 of those members from the scoping seems to solve the problem.  Unfortunately, those Entities are required.  I can't find anything different property-wise with these 3 members from any of the other members in the dimension, so I'm still unsure of the actual problem.

[Former Member]

Can you check if those members are Nodes and add CALC = 'N' in your select statement?

[Former Member]

They are indeed base members.  Only base members have the property set.  Actually the Select statement was an attempt to fix the problems since scoping <ALL> or BAS(some parent) for ENTITY had the same problem.

[Former Member]

Hi,

This might be a long shot, but we had a similar tripling problem recently that was resolved by restarting SendGovernor.  (7.5 MS.)  No problems since.

Good luck,

Jon

[Former Member]

Thank you.  I'm going to research this and see if I can try this.  I'll report back on whether it helped or not.