TM1 Forum

I have the following error :
5188 ERROR 2009-11-01 10:59:33,417 TM1.Process Process "Export Forecast or Budget to HFM in csv for multi-load": System stack overflow, process terminated


There is not much details on why this process abort
The scope of the process is to export data for all month for which number is greater or equal to selection value
But the text file exported is always the same with same number of lines and columns
Only the values are 'zero' for period less than the period selected
the process always work fine when period is greater than period 3 and abort each time when lower or equal to period 3

When slicing from a cube in Excel never get a problem .

Anyone have such type of error message ?




The process code in the Advanced Data is the following:

Counter=Counter+1;
If(Counter=1);
###############
# set up the header
###############
# 1. origin of data
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma','TM1');

# 2. scenario
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma',ATTRS('Scenario',pscenario,'HFM')
);
# 3. date
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor by
' | NUMBERTOSTRING(pScaleFactor) | ' .cma', SUBST(pPeriod,2,2) | '/' | NUMBERTOS
TRING(CELLGETN('Param_FIN','Calendar Days',pPeriod,pScenario,pYear)) | '/' | pYear);

# 4. Number of month sent

# if(PSCENARIO@='ACT');

if(PSCENARIO@=' ');
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma','1');
ELSE;
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor by
' | NUMBERTOSTRING(pScaleFactor) | ' .cma',NUMBERTOSTRING(13-STRINGTONUMBER(SUBS
T(pPeriod,2,2))));
ENDIF;

# 5. Hyperion upload information
if(pperiodstatus@='Monthly');
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma','R,R,0,,,,Y,N,,PER');
else;
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma','R,R,0,,,,Y,N,,YTD');
endif;

# 6. Hyperion upload information
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma',';A;UD2;UD4;;;;;;C;UD1;V;V;V;V;V;
V;V;V;V;V;V;V');

# 7. blank
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma','');

# 8. header
vLine=pQuote | 'cube;account;account;account;cur;view;iter;year;scen;unit;unit';

# if(PSCENARIO@='ACT');
if(PSCENARIO@=' ');
vLine=vLine | ';' | pPeriod;
ELSE;

# first we do the Mxx parts
i=STRINGTONUMBER(SUBST(pPeriod,2,2));
WHILE(i<=12);
IF(i<10);
vLine=vLine | ';M0' | NUMBERTOSTRING(i);
ELSE;
vLine=vLine | ';M' | NUMBERTOSTRING(i);
ENDIF;
i=i+1;
END;

ENDIF;

# output to text file
ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor
by ' | NUMBERTOSTRING(pScaleFactor) | ' .cma',vLine);

Endif;

vLine='polyone:PolyOne Reporting;' | Accounts | ';' | Accounts | ';' | Accounts | ';' | CurrencyReporting | ';' | PeriodStatus | ';' | Iteration | ';'
| pYear | ';' | pScenario | ';' | Units | ';' | Units;


# if(PSCENARIO@='ACT');

if(PSCENARIO@=' ');

vLine=vLine | ';' | NUMBERTOSTRING(pScaleFactor*CELLGETN('PolyOne Reporting',Iteration,PeriodStatus,CurrencyReporting,pScenario,VYear,pPeriod,Units,Ac
counts));

ELSE;

i=STRINGTONUMBER(SUBST(pPeriod,2,2));
WHILE(i<=12);

IF(i<10);
vMonth='M0' | NUMBERTOSTRING(i);
ELSE;
vMonth='M' | NUMBERTOSTRING(i);
ENDIF;

vLine=vLine | ';' | NUMBERTOSTRING(pScaleFactor*CELLGETN('PolyOne Reporting',Iteration,PeriodStatus,CurrencyReporting,pScenario,VYear,vMonth,U
nits,Accounts));
i=i+1;

END;

ENDIF;

ASCIIOUTPUT('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' | pPeriodStatus | ' Created on ' | DATE(Now,1) | ' Factor by
' | NUMBERTOSTRING(pScaleFactor) | ' .cma' ,vLine);

#Asciioutput('\\POCTSE02\tm1\4. Output\' |pScenario| ' ' |pYear| ' ' | pPeriod | ' ' |
#pPeriodStatus | '.cma','polyone:PolyOne Reporting',Iteration
#,PeriodStatus,CurrencyReporting,Scenario,VYear,Period,Units
#,Accounts,Numbertostring(Value));

#endif;

Hello Daniel

What happens when you cut the code in the Data tab and the Epilog tab to a text file?

And then you execute the process for M04 (so for one year, one version; you can change these test settings afterwards to check other views).

Is the view called ExportHFM correct when you open it in the cube viewer? Does it display all needed elements? Can you slice to Excel (note, it will not be a small view).

Also, what happens if you replace the full data tab code with:
Wim

By the way, this is the code in the Prolog tab:
The variables with p... are parameters, pPeriod is the most important one since for P05 all works well and for P04 it gives an error.

Wim

"What happens when you cut the code in the Data tab and the Epilog tab to a text file?
And then you execute the process for M04 (so for one year, one version; you can change these test settings afterwards to check other views).
>>>The TI process generate a file
Is the view called ExportHFM correct when you open it in the cube viewer? Does it display all needed elements? Can you slice to Excel (note, it will not be a small view).
>>>Yes the view is working fine and can be sliced to Excel
Also, what happens if you replace the full data tab code with:
>>>generate a file

By the way, this is the code in the Prolog tab:
>>> generate a file also

All proposed changes let the TI goes through and export a text file , this file is not in the format designed in the initial process having month in columns and reporting units and accounts in rows , but worked out

Hello

So this means that the error(s) should all be due to the Data tab, in which we are going to export the data of the view to a text file (with a custom layout).

I would suggest to continue the tests only with M04 for a year and scenario that you know to give errors.

Then, take out the code for the header (if Counter = 1 and so on...)

Does the error still exist? I would think yes. Rather unprobable, but if no errors here, test only the part for the header.

Then, decrease the loops over i (WHILE(i<=12); ... END;)

For instance, only loop WHILE(i<=4); such that you output month 04 and not the other months 05 to 12.

Another idea is to use less accounts (change the attribute to have fewer accounts in the view). It might perhaps help to spot an account that gives a problem for months M01 to M04.

Wim

I did the following
Try to export only one account by changing attributes to blank in dimension account
No difference , one or all account give the same error at same period level (M04 or less) , and goes through for period M05 to M12

I did test to export on C:\ drive or on Server and no difference

I copy the database and processes on another server and get same errors on same selection

Suppressing the header loop does not change the error

I compare the various export file , none are blank , never stop on same line or records (account - units) ...

Period M04 is always working
M03 - M02 - M01 never working INDEPENDANT OF YEAR or SCENARIO

OK, good.

Let the header be suppressed.

In the Data tab, insert a statement like this to cut down on the number of lines exported:
And don't forget to increase Counter to as to reach 100 (Counter=Counter+1);

Change the 100 to see if you get it to work, or that it does not export a single data point.

Do the test only on M01 or M02 or MO3, the rest seems to ok.

BTW, thank you for testing.

Wim

the export abort around counter = 141 in year 2009
with M01 and 142 with M02
140 seems to work for all scenario - period - YEAR 2009
but for year 2006 counter need to be 110 for ACT M01 !!

This error has been solved

The ASCIIOUTPUT function was generating one or more lines with length longer than 255 Characters
This did not happen in more recent version than the one I am using 9.1 SP3
The solution was to round to 2 decimals all sent numbers rather than having full precision of numbers to limit the total length below 255 characters
The function is called ROUNDP

Thanks to Wim for is solution

You're welcome. In the end I wrote the export process, so I should also make sure it works