spcopyopening not working anymore

[Former Member]

Hi,

This morning I ran a copy opening balances and it worked fine. A bit later I tried to run it again and though the package status was again success, no data was copied into the new period (a different period from the inital run).

I did not change the script nor the package, so I would not have expected a problem.

I then checked the log and found a difference. This is from the first, successfull run:

****************************************************************************************************
Start time --->9:21:38 AM  -  Date:8/29/2012  (build code:7.5.109)
****************************************************************************************************
User:INTRA\arwarh
Appset:LIVE
App:CONSOLIDATION
Logic mode:1
Logic by:
Scope by:
Data File:
Debug File:D:\BPC\Data\Webfolders\LIVE\CONSOLIDATION\PrivatePublications\arwarh\TempFiles\DebugLogic_643_01293551611.Log
Logic File:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\\..\AdminApp\CONSOLIDATION\Copy_Opening.LGX
Selection:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\PrivatePublications\arwarh\TempFiles\FROM_643_.TMP
Run mode:1
Query size:0
Delim:,
Query type:0
Simulation:0
Calc diff.:1
Formula script:
Max Members:
Test mode:0
Is Modelling:1
Number of logic calls:1
----------------------------------------------------------------------------------------------------
Call no. 1, logic:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\\..\AdminApp\CONSOLIDATION\Copy_Opening.LGX
----------------------------------------------------------------------------------------------------
signeddata is YTD
-------------------------
Building sub-query 1
-------------------------
Query Type:0
Max members:
Executing SPCOPYOPENING [CONSOLIDATION],[BUDGET],[<NULL>],[SPSCOPE_2904],[SPLOG_2903]
...

This is from the second run, that did not copy an data:

****************************************************************************************************
Start time --->10:44:46 AM  -  Date:8/29/2012  (build code:7.5.109)
****************************************************************************************************
User:INTRA\arwarh
Appset:LIVE
App:CONSOLIDATION
Logic mode:1
Logic by:
Scope by:
Data File:
Debug File:D:\BPC\Data\Webfolders\LIVE\CONSOLIDATION\PrivatePublications\arwarh\TempFiles\DebugLogic_645_462877229.Log
Logic File:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\\..\AdminApp\CONSOLIDATION\Copy_Opening.LGX
Selection:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\PrivatePublications\arwarh\TempFiles\FROM_645_.TMP
Run mode:1
Query size:0
Delim:,
Query type:0
Simulation:0
Calc diff.:1
Formula script:
Max Members:
Test mode:0
Is Modelling:1
Number of logic calls:1
----------------------------------------------------------------------------------------------------
Call no. 1, logic:D:\BPC\Data\WebFolders\LIVE\CONSOLIDATION\\..\AdminApp\CONSOLIDATION\Copy_Opening.LGX
----------------------------------------------------------------------------------------------------
signeddata is YTD
-------------------------
Building sub-query 1
-------------------------
Query Type:0
Max members:
Region:
DIMENSION:CATEGORY
BUDGET
DIMENSION:ENTITY
1104
DIMENSION:TIME
2012.JAN
...

For some reason, the first time round, the sub query seems to execute the stored procedure, the second time not.

Does anyone have an idea why this would happen? Again, the only thing I did between the two runs was to write a couple of EVDRE reports.

Thanks,
Arnold

[Former Member · Accepted solution]

Arnold,

Are you able to run any other SP, if yes then you are good from system and server side. You may also like to check the logic file...please check if the sub-query 1 in your lgf or lgf is misplaced from what was in past. Thanks

[Former Member · Accepted solution]

Hi,

yes I can run other stored procedures.

The one that causes the problem is this one:

// Anfangsstände kopieren
*RUN_STORED_PROCEDURE = SPCOPYOPENING([%APP%],[%CATEGORY_SET%],[%CURRENCY_SET%],[%SCOPETABLE%],[%LOGTABLE%]) 
// Script für die Account transformation rules ausführen
*INCLUDE Calculation.LGF
// Script für die Berechnung der Validierungskonten ausführen
*INCLUDE Validation.LGF

The two other logic files called are

CALCULATION.LGF
// JÜ aus der Guv in den EK Spiegel übertragen
*RUN_STORED_PROCEDURE=SPRUNCALCACCOUNT([%APP%],[%CATEGORY_SET%],[%CURRENCY_SET%],[%SCOPETABLE%],[%LOGTABLE%],[NETINCOME])
// Endwert aus Spiegel in Bilanz für Anlagevermögen, Eigenkapital und Rückstellungen
*RUN_STORED_PROCEDURE=SPRUNCALCACCOUNT([%APP%],[%CATEGORY_SET%],[LC],[%SCOPETABLE%],[%LOGTABLE%],[EK])
*RUN_STORED_PROCEDURE=SPRUNCALCACCOUNT([%APP%],[%CATEGORY_SET%],[LC],[%SCOPETABLE%],[%LOGTABLE%],[PROV]) 
*RUN_STORED_PROCEDURE=SPRUNCALCACCOUNT([%APP%],[%CATEGORY_SET%],[LC],[%SCOPETABLE%],[%LOGTABLE%],[ASSETS])
// Summe EK für ERSTKONS Daten
*RUN_STORED_PROCEDURE=SPRUNCALCACCOUNT([%APP%],[%CATEGORY_SET%],[%CURRENCY_SET%],[%SCOPETABLE%],[%LOGTABLE%],[ERSTKONS])

and

CALCULATION.LGF
// Validierungskonten in lokaler Währung berechnen
*RUN_STORED_PROCEDURE = SPRUNVALID([%APP%],[%CATEGORY_SET%],[LC],[%SCOPETABLE%],[%LOGTABLE%])
//--------------------------------------------------------------------------------------------------------------------------------
//  Recording the total number of blocking errors on VALFLAG (Work Status account)
//--------------------------------------------------------------------------------------------------------------------------------
*SELECT ("%DATASRC_LIST%","[ID]","DATASRC","DATASRC_TYPE='I'")
*XDIM_MEMBERSET DATASRC=%DATASRC_LIST%
*XDIM_MEMBERSET CONSOSCOPE=G_NONE
*XDIM_MEMBERSET CURRENCY=LC
*XDIM_MEMBERSET INTCO=I_NONE
*WHEN SIGNEDDATA
*IS <>0
*WHEN ACCOUNT.DIMLIST_VR
*IS VR_VALREL
*REC(ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL,EXPRESSION=1)
*ELSE
*REC(ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL,EXPRESSION=0)
*ENDWHEN
*ENDWHEN
*COMMIT

So essentially it is mostly stored procedures and only a little bit of code.

The query 1, which is the stored procedure to copy opening balances has not changed between the package running and not running.

Thanks,
Arnold

[former_member186498 · Accepted solution]

Hi Arnold,

there is something wrong in the last lgf, the expression must be the first parameter in the REC, i.e.

...

*REC(EXPRESSION=1,ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL)

*ELSE

*REC(EXPRESSION=0,ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL)

...

Try maybe ...

Kind regards

Roberto

[Former Member · Accepted solution]

Hi Roberto,

I have changed it, doesn't make any difference though.

Thanks,

Arnold

[Former Member · Accepted solution]

Hello Arnold, your SP looks good to me and it should work fine. Please take a backup of current lgf file and remove all logic except this SPCOPYOPENING. And then validate and process, see if this works fine. In addition also please validate your carry forward BR and process your FLOW DIM.

[Former Member · Accepted solution]

Hi,

thanks, turns out it was this bit of code

//--------------------------------------------------------------------------------------------------------------------------------
//  Recording the total number of blocking errors on VALFLAG (Work Status account)
//--------------------------------------------------------------------------------------------------------------------------------
*SELECT ("%DATASRC_LIST%","[ID]","DATASRC","DATASRC_TYPE='I'")
*XDIM_MEMBERSET DATASRC=%DATASRC_LIST%
*XDIM_MEMBERSET CONSOSCOPE=G_NONE
*XDIM_MEMBERSET CURRENCY=LC
*XDIM_MEMBERSET INTCO=I_NONE
*WHEN SIGNEDDATA
*IS <>0
*WHEN ACCOUNT.DIMLIST_VR
*IS VR_VALREL
*REC(ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL,EXPRESSION=1)
*ELSE
*REC(ACCOUNT=VALFLAG,ACCTDETAIL=F_BAL,EXPRESSION=0)
*ENDWHEN
*ENDWHEN
*COMMIT

which I copied from the SAP IFRS starter kit.

[former_member186498]

Hi Arnold,

after

DIMENSION:CATEGORY

BUDGET

DIMENSION:ENTITY

1104

DIMENSION:TIME

2012.JAN

is the Executing SPCOPYOPENING? (because I see ...)

There aren't messages related to the stored proc.?

Check if the COM+ services are ok, if the DB server is responding, try to execute other stor.proc., if they doesn't work try to modify application (eventually proess the cube on the db server) or process all dims or if possible to restart the server.

Kind regards

     Roberto

[Former Member]

Hi Roberto,

in the log of the failed run there are no references to stored procedures. How do I check the COM+ services?

I have run other stored procedures and they work okay.

I have optimized all applications and re-started the send governor.

I will try to re-start the server.

Thanks,

Arnold

[former_member186498]

Hi Arnold,

open the "windows server manager\diagnostic\event viewer and check if in "Local" or in "custom view\Administrative Events" there are errors,
in "applic and services log\microsoft\osoftCPM" if there are recent errors

"windows server manager\configuration\services" see if a COM+ is blocked (but restartint the server they will restart all again).

Check also in "bpc server manager" the server diagnostic to be sure all is working fine.

Have you a single server? If noth check both (DB and Application).

Try eventually to check the lgf as Rohit wrote for some error and also to "validate" again the lgf maybe is only the lgx is corrupted.

Kind regards

     Roberto

[Former Member]

Hi Roberto,

we are working with a single server environment, so it is only the one server. COM+ seem to be okay, I can't see any blocked ones.

I do have frequent time out errors for MSSQLOLAPService but I not at the same time as problems with running the scripts in BPC.

I have compared the lgf and lgx files and they are what I would expect.

Thanks,

Arnold