Invalid Real Number Issues

[normanbobo]

All,

A piece of code that has been working for months is all of a sudden broken and I can't isolate what change occurred to break it. Here is the code:

Code: Select all

nbrTotalPutCommitments = CELLGETN(strCubeNm, strEmplOrgSectionAllElem, strProjOrgSectionAllElem, strTaskOrgSectionAllElem, strActvCtrAllElem,
       strProjTaskAllElem, strFWkAllElem, strAgmtAllElem, strExpTypeAllElem, strProjTypeAllElem, strDeptSoftshellAllElem, strDivSoftshellAllElem,
       strCenterSoftshellAllElem,  strSBUSoftshellAllElem, strTIProcessElement, strActualsElement, strCommitmentElement);
nbrTotalPutRecordCount = CELLGETN(strCubeNm, strEmplOrgSectionAllElem, strProjOrgSectionAllElem, strTaskOrgSectionAllElem, strActvCtrAllElem,
       strProjTaskAllElem, strFWkAllElem, strAgmtAllElem, strExpTypeAllElem, strProjTypeAllElem, strDeptSoftshellAllElem, strDivSoftshellAllElem,
       strCenterSoftshellAllElem,  strSBUSoftshellAllElem, strTIProcessElement, strActualsElement, strTxnCntElement);

TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutCommitments' , NUMBERTOSTRING(nbrTotalPutCommitments));
TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutRecordCount' , NUMBERTOSTRING(nbrTotalPutRecordCount));

The TEXTOUTPUT commands return an error: "Invalid Real Number". It's almost like there is a string value or some other data corruption in the cube.

I used TEXTOUTPUT commands to display the values of the cube name and the dimension elements prior to the CELLGETN statement and all of them are correct. When I open the cube viewer, the cube returns normal, non-zero numeric values in the cells being read. Just to make sure nothing weird had happened to the cube somehow, I deleted and re-created the cube. Still the same issue. There are no string elements in any of the dimensions in the cube -- they are all simple/N. Any thoughts/ideas on how to hunt down this issue?

[tomok]

Have you tried using the rules function, STR, to convert the numeric value to a string, instead of NUMBERTOSTRING, to see if that works?

[jstrygner]

1. If you put:

Code: Select all

nbrTotalPutCommitments = nbrTotalPutCommitments + 1;

before TEXTOUTPUT will this line return error (try to figure out if the variable is not numeric or the NUMBERTOSTRING itself has problems).

2. Is the value with decimal separator? Any chance some settings with decimal separator changed anywhere?

[Steve Rowe]

It might be a false error message and it's actually complaining about the file path and name? Have you tested that to see if it's valid and the user the TM1 instance is running under still has access to this location?
HTH

[normanbobo]

thanks for the ideas....

The NUMBERTOSTRING function does not require that you take a guess at the size of the number and has been working for many such statements and, in fact, is working in other processes right now. But just to see if it would help uncover the root of the issue, I changed the code to use the STR function:

Code: Select all

TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutCommitments' , STR(nbrTotalPutCommitments,20,2));
TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutRecordCount' , STR(nbrTotalPutRecordCount,20,2));

The statements executed withot error, but returned the following lines in the output file (clearly not numbers):

"Epilog: nbrTotalPutCommitments","...................."
"Epilog: nbrTotalPutRecordCount","...................."

I then added statements to increment the values by 1:

Code: Select all

nbrTotalPutCommitments = CELLGETN(strCubeNm, strEmplOrgSectionAllElem, strProjOrgSectionAllElem, strTaskOrgSectionAllElem, strActvCtrAllElem,
       strProjTaskAllElem, strFWkAllElem, strAgmtAllElem, strExpTypeAllElem, strProjTypeAllElem, strDeptSoftshellAllElem, strDivSoftshellAllElem,
       strCenterSoftshellAllElem,  strSBUSoftshellAllElem, strTIProcessElement, strActualsElement, strCommitmentElement);
nbrTotalPutRecordCount = CELLGETN(strCubeNm, strEmplOrgSectionAllElem, strProjOrgSectionAllElem, strTaskOrgSectionAllElem, strActvCtrAllElem,
       strProjTaskAllElem, strFWkAllElem, strAgmtAllElem, strExpTypeAllElem, strProjTypeAllElem, strDeptSoftshellAllElem, strDivSoftshellAllElem,
       strCenterSoftshellAllElem,  strSBUSoftshellAllElem, strTIProcessElement, strActualsElement, strTxnCntElement);

nbrTotalPutCommitments = nbrTotalPutCommitments +1;
nbrTotalPutRecordCount = nbrTotalPutRecordCount +1;

TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalInputCommitments' , STR(nbrTotalInputCommitments,20,2));
TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutCommitments' , STR(nbrTotalPutCommitments,20,2));

TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrInputRecordCount ' , STR(nbrInputRecordCount,20,2));
TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutRecordCount' , STR(nbrTotalPutRecordCount,20,2));

The statements executed without errors and returned the same results in the output file.

Hmmmm.....very strange ....

[Alan Kirk]

thanks for the ideas....

The NUMBERTOSTRING function does not require that you take a guess at the size of the number and has been working for many such statements and, in fact, is working in other processes right now. But just to see if it would help uncover the root of the issue, I changed the code to use the STR function:

Code: Select all

TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutCommitments' , STR(nbrTotalPutCommitments,20,2));
TEXTOUTPUT(strTextOutputFileName, 'Epilog: nbrTotalPutRecordCount' , STR(nbrTotalPutRecordCount,20,2));

The statements executed withot error, but returned the following lines in the output file (clearly not numbers):

"Epilog: nbrTotalPutCommitments","...................."
"Epilog: nbrTotalPutRecordCount","...................."

You have three different versions in your profile; what's the exact version that you're using?

In 9.5.1 (no hot fixes applied and I have to get around to putting 9.5.2 on that machine), if I have a value which has more significant figures than TM1 can store then Str(dbl, 20,2) is giving me an empty string. Of course that may be something else in a different version of 9.5x. The value isn't throwing an error when I use NumberToString() though it's truncating back to 123456789012345 (followed by all 0's). Again this may be different in another version.

You said that the number is non-zero, but is it possible that the number in the cell is huge and that it could be triggering an error in the version that you're on?

[Martin Ryan]

Before numbertostring came around I used trim(str(myNumber, 100, 2)). This may help in your case.

Martin

[normanbobo]

[quote="Alan Kirk]
You said that the number is non-zero, but is it possible that the number in the cell is huge and that it could be triggering an error in the version that you're on?
[/quote]

Alan, the number is large -- in the hundreds of millions -- not "huge".

This is quite the stumper....

[normanbobo]

All ... an update to this issue.

After initially reporting this as a bug, IBM development has now responded with a different answer, which actually makes a lot of sense.

We recently added BatchUpdate in the TI processes. If you have ever used BatchUpdate, you know that the BatchUpdateFinish (or BatchUpdateFinishWait) command must be in the Epilog and can only be the last statement in the Epilog. Without thinking about the interactions involved, the batch update commands were added to our TI processes in an effort to implement concurrency during our batch loads (using the new TM1RUNTI.exe command line to execute simultaneous loads). Well, one of the commands that the processes have long had in them were CELLGETN commands to read the total amounts posted to the cubes during the TI processes. These control totals were then saved to a control totals cube (which we intend to use in an automated process to verify our loads against external control totals). The problem was, though, the data in the cube had not been committed. Rather than returning an intelligible error message, the CELLGETN commands returned an internal "not committed" error message value -- as the value! This value then, caused all of the number to string conversion commands to fail. None of them would work. This all makes sense now. BTW, the value shows up when you turn on debugging.

The solution to this puzzle is to write a separate TI process to obtain the control totals.

What is interesting is that this process sometimes worked and the CELLGETN commands returned accurate values, even though the data had not been technically committed yet because the batchupdatefinish command had not yet been executed. The error started appearing when we began using TM1RUNTI.exe. We demonstrated this interaction in a series of controlled tests. So there is some connection to using TM1RUNTI.exe, but what that connection is will probably not ever be known.

Simple Lesson Learned: When using Batch Update commands, don't attempt to read a value out of a cube that was updated in the TI process. Seems kind of silly in hindsight now, but I am sure someone else will run across it.

[Alan Kirk]

All ... an update to this issue.

After initially reporting this as a bug, IBM development has now responded with a different answer, which actually makes a lot of sense.

We recently added BatchUpdate in the TI processes.

Soooo... not so much a case of "A piece of code that has been working for months is all of a sudden broken", more "We changed the code and now it's broken". A somewhat relevant piece of information seems to have been omitted from the original question. However it's good that you posted the resolution; it might save someone else some grief.

It's probably safe to assume that this Technote was published in response to that service request:
https://www-304.ibm.com/support/docview ... wg21512708

[normanbobo]

Soooo... not so much a case of "A piece of code that has been working for months is all of a sudden broken", more "We changed the code and now it's broken". A somewhat relevant piece of information seems to have been omitted from the original question.

It did work for many weeks with the batch update in it. That is what's so weird about this. It was when we started using TM1RUNTI.exe that the problem cropped up. So, no adding the batch update did not immediately break it, but now we see how that contributed to the problem and how we now need to remove the CELLGETN commands out to another process.