Full Optimize Problem

[Former Member]

Hi Guys,

I am getting the following error when doing a full optimize with database compression:"Error message:Arithmetic overflow error converting numeric to data type numeric.

The statement has been terminated."

I am guessing this has a lot to do with the performance issues i am having with system at the moment. Everything is slow at the moment and I am getting SendGovener issues as well.

Any help will be appreciated.

Regards,

AvdB

[Former Member · Accepted solution]

Hi Andries,

Just try to perform full optimization on each entity first (mainly to Account and Entity Dimension). Then perform optimization to the application. Also check your both these dimensions.

just let me know if the problem doesnt resolve.

Ajay

[Former Member · Accepted solution]

Hi,

Do you mean that i should do a full process of each of the dimensions that are used in the Application?

Regards,

AvdB

[sorin_radulescu]

Hi,

You need to know what it will be the with aproximation the biggest number insert into database.

In this way you will know what kind of format you should have into database.

By the way deleting the record from tbldefaults is not the right approach.

You have to use webadmin page - Application Parameters and you can set there the format use for your application.

Knowing what kind of numbers you have to handle you will be able to set the correct format for your signeddata.

Kind Regards

Sorin Radulescu

[Former Member]

Hi Guys,

I organized dome down time for the system tomorrow morning and I would just like to confirm the steps with you before potentially breaking the system.

1.Backup the tblFactFinance table

2.Create a new table with a signeddata column size of decimal(25,6) (they need 6 numbers after the comma)

3.copy all the datafrom the tblFactFinance into the new table

4.Delete the old table and rename the new one to tblFactFinance

5.Perform a Full Optimize and hope that is actually works

Is this the best way to do this fix?

Regards,

AvdB

[Former Member]

Hi guys,

I implemented my proposed fix above and it is still giving me the same error when I try to optimize the application. Any other ideas? I have also logged a call now with SAP, this is becoming a big issue for me.

Regards,

AvdB

[Former Member]

Hi All,

I finally got it working. I had to follow the following steps:

1. Used the following query to determine what the decimal precision had to be (just change the app specific stuff)

SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO], CONVERT(DECIMAL(25,10),SUM([SIGNEDDATA])) AS [SIGNEDDATA],[SOURCE],[ACCOUNTL] FROM ( SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFAC2FinanceStatutory]
UNION ALL
SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFACTFinanceStatutory]
UNION ALL
SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFACTWBFinanceStatutory]
) T
GROUP BY [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SOURCE],[ACCOUNTL]
HAVING ABS(SUM(SIGNEDDATA)) > 0.0000001

2. Changed the signed_data_format in the APPLICATION parameters (not appset parameters)

3.modified the application

4.optimized the application

Thanks for all the help!

Regards,

AvdB

[sorin_radulescu]

Hi,

It seems you are goindg over of max number into default format.

Sam provide you a right thread.

Actually the sum of your numbers are going over 15 digits which is the maximum into default format and that's the problem.

the format is 25,10 but you can changed into 25, 4 which means will allow you to use max 4 decimals but you will be able to use 21 digits numbers.

Regards

Sorin Radulescu

[Former Member]

Hello,

Please read note 1369160 - Arithmetic overflow error converting nvarchar to data type numeric.

Maybe it could hepls.

We had the same problem here and now it's solved.

Regards

[Former Member]

Hi all,

We are having that same problem and really need to solve it.

So, reading the thread and the note, it seems that we can have that error due to 2 different problems:

1) numbers too big in the fact tables.

2) a wrong entry in the tbl.Defaults table (using a comma notation in a "version" entry instead of a dot notation).

So, we would need help with the following:

1) about tbl.Defaults, in our case we only have values with commas in an entry like this:

UserId        Type        App         KeyID                Value
_GLOBAL     SYSTEM      PARAMETROS    SIGNED_DATA_FORMAT      20,15

All the ones about version look like this:

UserId        Type        App         KeyID          Value
_GLOBAL    VERSION      PARAMETROS                    1.022

So, is the entry about the Key "Signed_Data_Format" supposed to be causing the problem? Should we either change it or erase that entry?

2) we don't have numbers too big in the database (in this case), but how are we suppose to prevent that from happening?

Thank you,

Rafael

[Former Member]

Hi all,

as an update, we solved the problem. It was indeed caused by the SIGNED_DATA entry in the tbl.Defaults table. It was there because we have a parameter created in the "Application Set Parameters" of BPC Administration as SIGNED_DATA_FORMAT (25,10). This caused the error when executing "Modify" over the application. By simply deleting it, everything works fine now.

Still, for the sake of future references, how are we suppose to prevent too big numbers from being inserted in the fact table?

Regards,

Rafael

[sorin_radulescu]

Please check if you have big numbers into database.

Did you perform any stress test or did you try to input random data into database.

It seems that you have some big numbers into db which sum of them is going over max of decimal number.

Can you check this please.

The installation can be corupted or reg setting as well can be corrupted by an windows update.

Did ypu perform any windows update?

Regards

Sorin

[Former Member]

The biggest number in signeddata is "991084035708471.0000000000"

The smallest number is "-987525210338215.0000000000"

The sum of all the data is "1038830088274756.3931219156"

What is the maximum that is allowed?

I see automatic updates are enabled on this server. It's asking me to install 20 updates, which I have not done. How do I determine whether a previous update corrupted something?

Thank you!

[Former Member]

see abopve thread, May be the errror is due to your Signed Data format.

Sam

[sorin_radulescu]

Hi,

It seems that one of your dimension is not correct process with ful process or you have bad data into fact table .

Did you have working this system before?

One of other problems can be that installation was not correct or you applied a QFE for windows which change the regional settings for one of users from HKEY_USERS - ...

These reg settings are not anymore Englisg US how it is required.

Regards

Sorin

[Former Member]

(Hi, I'm helping Gert Andries)

Thanks Sorin,

Yes, we successfully 'Full' Processed all the dimensions within the Application.

Yes, the system was working before so I doubt it's an installation problem.