BPC 10 MS: Script failing

[Former Member]

Hi, has anyone ever seen this message before in a script that failed and know what will fix it?  We have been using this script all along and have never had this message.  Now when we run it, it fails for this reason each time.  Any help is appreciated, thanks, Tracey

Error writing data in step 4: Failed to post data to database:sgData

[former_member186498 · Accepted solution]

Hi Tracey,

verify that your sg tables are empty, probably not after a package or send data in error.

If not empty rebuild the OLAP with a modify model and check again, if not empty delete these tables manually with SSMS.

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto, I modified model and it was empty, but unfortunately when I ran the script it happened again.  Other scripts can run and when I run this script in another environment it works.  I can see in the log that it is doing the correct calculations but it is just not writing/posting it.  Any ideas? Thanks, Tracey

[former_member186498 · Accepted solution]

Hi Tracey,

this means that it was not a previous error caused by other script but the issue is on step 4 of your script, check this step for some issues maybe it contains a huge number see please this note 1876916 - BPC: Error "Failed to post data to database:sgData"

Regards

     Roberto

[Former Member · Accepted solution]

Hi Tracey,

Please try restart BPC Send Governor service and IIS.

Thanks & Regards,

Rohit

[Former Member · Accepted solution]

Hi Roberto, thanks, I'll look again...I saw that note and noticed it is now happening on other scripts and there is no number/decimal that long.  Same scripts are running fine in a test environment, thanks, Tracey

[Former Member · Accepted solution]

Thanks Rohit, we restarted BPC Send Governor service but not IIS. 

[Former Member · Accepted solution]

Hi, this did not fix our issue either, but thanks, Tracey

[JohnL · Accepted solution]

Hi Tracy,

What error do you see in the TblLogs?

Also are there any issues in the eventviewer of the App or DB servers around the time the issue occurred ?

Thanks,

[former_member186498 · Accepted solution]

Hi Tracey,

maybe you have a corrupted dimension, try to export all your dimensions and process full all the dims and after do a modify model with process model and reassign Index.

If doesn't work copy the fact tables and dimensions from prod to test and see if there works.

Regards

     Robverto

[Former Member · Accepted solution]

Hi John, this is part of the log.  The calculations are correct.  It is just not posting.  This same script works in test  environment.  We did try to abort this script run the first time and then the problem came up but I don't know if that is a coincidence?  The running of the scripts causes a lock but we clear it and process model before we tried again. Nothing was going on with server though we have rebooted......any ideas? Thanks, Tracey

BASE,FTE_EC_828,ACTUAL,INPUT_BASE,SSIDE_26665220,JC_102121,2015.MAY,2015.WK19,0.090909090900000000000
BASE,FTE_EC_828,ACTUAL,INPUT_BASE,SSIDE_26665220,JC_102121,2015.JUN,2015.WK23,0.090909090900000000000
BASE,HRS_SM_EVEN_IN,ACTUAL,LABOR_DIST,30860000_09425,JC_102121,2015.MAY,2015.WK21,12.9999960000000000
BASE,FTE_EC_829,ACTUAL,INPUT_BASE,30860000_09425,JC_102121,2015.MAY,2015.WK21,0.1600000000000000000000000008
BASE,FTE_EC_829,ACTUAL,INPUT_BASE,30860000_09425,JC_102954,2015.APR,2015.WK17,0.109090909080000000000
Time to validate records: 0.14 sec.

call 1 completed and data posted in 10,874.70 sec.

Run completed in 10,875.22 sec.
****************************************************************************************************
End time --->00:15:38  -  Date: 2015-05-18
****************************************************************************************************
Error writing data in step 4: Failed to post data to database:sgData

[Former Member · Accepted solution]

Here is log

‘’ <RESULT>

<MESSAGE><![CDATA[Execute formulasError

Error writing data in step 4: Failed to post data to database:sgData

]]></MESSAGE>

<STATUS_STEP><![CDATA[Total Step:@1

Execute formulas:@ Failed in 4,581 seconds

Default Formulas:@ completed  in 4,581 sec

]]></STATUS_STEP>

<LOGICLOGFILES>

<LOGICLOGFILE><![CDATA[DebugLogic_4478_0198104155.Log]]></LOGICLOGFILE>

</LOGICLOGFILES>

</RESULT>

[Former Member · Accepted solution]

Hi Roberto,  we tried that and no luck.  Test environment still works.  but thanks, Tracey

[former_member186498 · Accepted solution]

Hi Tracey,

try to resume, after you abort a package starts the problems in prod, now several packages stop to work having always the same issue, isn't it?

Have you checked the work status settings of the model that maybe someone changed for mistake?

when you run the package are other users working?

the default logic runs fast? i.e. could be a collision with other send data?

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto, once that package was aborted it seemed to have corrupted prod.  Even though we've cleared everything and reset the send governor....our work status has everyone locked out but we could always run scripts with this also with people using that do have access.  Test environment has work status locked too and is running the same scripts.  Even the data we are using is the exact same as in the test environment so I'm not sure what this could be, thanks, Tracey

[former_member186498 · Accepted solution]

sorry a doubt, have you remembered to clear also the lck<Models> tables?

[Former Member · Accepted solution]

Hi Roberto, yes we cleared those.  Interestingly we got scripts to work if we put in

*XDIM_MAXMEMBERS ENTITYST=25.  They took longer to run but results were good.  Why would this have helped it and why don't we need in test environment?  I don't know what changed...dims, data or signed data....seems the same in the two environments.  Thanks for your help, Tracey

[former_member186498 · Accepted solution]

Hi Tracey,

this instruction reduce the time for the select extracting each time a subset of the data in the query so diminuish the probability for a concurrent lock, maybe some parameters are changed on the server, check the ThreadPool\Process\MaxThreads, ThreadPool\Query\MaxThreads etc

see please http://wiki.scn.sap.com/wiki/display/CPM/BPC+7.5+and+BPC+10+Best+Practice+Performance+Tuning

http://help.sap.com/SCENARIOS_BUS2008/helpdata/EN/d4/0d80cce12a4c89b99110de1c7b22e0/frameset.htm

and these notes

1674814 - Parameters to measure performance of BPC Database and Application Server

1387343 - BPC: SQL Database Tasks

check ther settings.

John Leggio have you some other ideas?

Regards

     Roberto

[Former Member · Accepted solution]

Thanks so much Roberto, I am going to review these with IT and see if it helps.