CellGetN crashing a procedure

Post Reply
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

CellGetN crashing a procedure

Post by 20 Ton Squirrel »

My procedure makes heavy use of CellGetN to coordinate and accumulate data between several cubes. It grooves along just fine until the CellGetN hits an error, then it barfs on the dance floor.

Is there a way to safely ignore errors on CellGetN? Or maybe a way to detect errors so I could use AsciiOutput to report what iteration of elements is causing a problem?

I realize the REAL problem isn't CellGetN, it is the rules behind a particular area… but the rules in these cubes are a labyrinth and the trace function does not return anything useful. I'm hoping to just get a list of where my problems are and dissect the rules from there.

Any ideas?
War teaches us geography, getting old teaches us biology.
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: CellGetN crashing a procedure

Post by burnstripe »

A cellgetn will only error if the element doesn't exist, a syntax error, (eg a dimension missing) or its reading a string instead of a value.

If it's the element doesn't exist consider an if statement using dimix to check if the element exists in the dimension

If it's a string you could use the check the element type of the measure using dtype. And only do cellgetn on n and c types and cellget on string.

If you want to debug when it crashes consider using powershell to output the a text/csv file. If you run it this way the the file will represent the last moments before the crash and the file will always exist regardless of the crash. If you'd like some sample code let us know and I'll fish it out tomorrow. I find this great for debugging as by creating the file this way you can also append the file, rather than having it recreated every time the process is run with asciioutput/textoutput
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: CellGetN crashing a procedure

Post by burnstripe »

Regarding the barfing :) what's the behaviour, any messages created during the crash. It's not running out of memory is it? With the labyrinth of rules one never knows... There's no stack overflow messages occurring in the server message log are there?

If there's a stack overflow consider trying the (undocumented) function debugutility it could save you time by isolating a cell intersect where the stackoverflow is occurring, normally the result of a rule with an endless loop a = b and b = a scenario or perhaps the stack is too large aka rolling balances pushing forward over many years by month

I would also try temporarily removing any cellput / cellincrement in the process and seeing if still errors. If it does and there's no light at the end of the tunnel, perhaps hash out sections of the cube rules until it doesn't error, long winded and tedious but should get the job done
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: CellGetN crashing a procedure

Post by Wim Gielis »

[quote=burnstripe post_id=80247 time=1638402437 user_id=15311]
If you want to debug when it crashes consider using powershell to output the a text/csv file. If you run it this way the the file will represent the last moments before the crash and the file will always exist regardless of the crash. If you'd like some sample code let us know and I'll fish it out tomorrow. I find this great for debugging as by creating the file this way you can also append the file, rather than having it recreated every time the process is run with asciioutput/textoutput
[/quote]

Seems like an interesting approach and if you could show a sample of code, it would be greatly appreciated !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: CellGetN crashing a procedure

Post by burnstripe »

Attached is a copy of the process, also added the code below. The process has several parameters, include the pfile the file location and pMsg1/ pVal1 to pMsg9/pVal9 to add some outputs. In this instance you could either call this process on data line, or merge the content into the datatab, so that the psOutput is generated in the DataTab and the executecommand also runs there.

Personally I find this kind of debugging great where there's a data issue occurring but I don't know which process it is or what time the issue occurs so using this I can schedule a chore to output the cube value and a timestamp to help me identify when the problem occurs and then I can tally this with the server message log to home in on the issue.

If you want to see the end syntax required for the powershell, just output the variable psPowershellScript when running the process

CODE
-----------------------------

Code: Select all

####################################################
# Powershell Setup

psPowershellProgram='C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -command ';
psCommand = char(34) | 'Add-Content ' | pFile | ' ' ;

psOutput = '';
nParameters = 0;


####################################################
# Set Content

IF( pVal1 @<> '' );
	psOutput = psOutput | pVal1;
	nParameters = nParameters + 1;
ENDIF;


IF( pMsg1 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pMsg1;	
	Else;
		psOutput = psOutput | char(9) | pMsg1;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pVal2 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pVal2;	
	Else;
		psOutput = psOutput | char(9) | pVal2;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pMsg2 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pMsg2;	
	Else;
		psOutput = psOutput | char(9) | pMsg2;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pVal3 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pVal3;	
	Else;
		psOutput = psOutput | char(9) | pVal3;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pMsg3 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pMsg3;	
	Else;
		psOutput = psOutput | char(9) | pMsg3;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pVal4 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pVal4;	
	Else;
		psOutput = psOutput | char(9) | pVal4;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pMsg4 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pMsg4;	
	Else;
		psOutput = psOutput | char(9) | pMsg4;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pVal5 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pVal5;	
	Else;
		psOutput = psOutput | char(9) | pVal5;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;

IF( pMsg5 @<> '' );
	IF ( nParameters = 0 );
		psOutput = psOutput | pMsg5;	
	Else;
		psOutput = psOutput | char(9) | pMsg5;
	ENDIF;

	nParameters = nParameters + 1;
ENDIF;


####################################################
# Generate and execute Powershell Command

psPowershellScript = psPowerShellProgram | psCommand | char(39) | psOutput | char(39) | char(59) | 'Exit' | char(34);


ExecuteCommand(psPowershellScript,0);
Attachments
Powershell Message Generator.pro
(3.77 KiB) Downloaded 185 times
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: CellGetN crashing a procedure

Post by Wim Gielis »

Thanks, I will have a look.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: CellGetN crashing a procedure

Post by Wim Gielis »

To shorten the code with Expand, and also other changes were applied:

Code: Select all

# PowerShell setup
psPowershellProgram = 'C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -command ';
psOutput = '';

# Set content
k = 1;
While( k <= 5 );

   pVal = Expand( '%pVal' | NumberToString(k) | '%' );
   If( pVal @<> '' );
      psOutput = psOutput | Char(9) | pVal;
   EndIf;

   pMsg = Expand( '%pMsg' | NumberToString(k) | '%' );
   If( pMsg @<> '' );
      psOutput = psOutput | Char(9) | pMsg;
   EndIf;
   k = k + 1;

End;

# Generate and execute Powershell command
If( Long( psOutput ) > 0 );
   psOutput = Delet( psOutput, 1, 1 );
EndIf;
psPowershellScript = psPowerShellProgram | Char(34) | 'Add-Content ' | pFile | ' ' | Char(39) | psOutput | Char(39) | Char(59) | 'Exit' | Char(34);
ExecuteCommand( psPowershellScript, 0 );
Last edited by Wim Gielis on Thu Dec 02, 2021 1:45 pm, edited 1 time in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: CellGetN crashing a procedure

Post by MarenC »

Hi Wim,

I would add this line into your code:

k = k + 1;

Maren
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: CellGetN crashing a procedure

Post by Wim Gielis »

MarenC wrote: Thu Dec 02, 2021 1:32 pm Hi Wim,

I would add this line into your code:

k = k + 1;

Maren
Correct, thanks, very important !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: CellGetN crashing a procedure

Post by burnstripe »

Hi Wim,

does the output start with a tab using your example. Char(9) is used to add a horizontal tab to the file, which you wouldn't really want where k = 1 but you would where k > 1

scratch that i see you delete the tab at the end
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: CellGetN crashing a procedure

Post by Wim Gielis »

That's a good question but I like to take care of it with only 1 IF statement, instead of 9 or 10:

Code: Select all

If( Long( psOutput ) > 0 );
   psOutput = Delet( psOutput, 1, 1 );
EndIf;
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: CellGetN crashing a procedure

Post by 20 Ton Squirrel »

This forum always impresses me with the responses, I appreciate the advice! Our setup doesn't allow PowerShell but hopefully that code will help someone.

I neglected to mention I AM getting a stack overflow when the process barfs.

A buddy of mine recommended using AsciiOutput on the Data portion of the process to see where along the view/recordset it was failing. That works to isolate errors one-by-one but there's likely hundreds of these buggers in there. My luck will be that it boils down to one or two rules out of hundreds and it will be a typo :D

The lack of error handling in procedures is depressing. I'd sell my neighbor's hamster for a Try/Catch or On Error Resume Next. I vote they make an alternate procedural Java-like language. I already hear the bitter laughter of TM1 veterans echoing in the ether…
War teaches us geography, getting old teaches us biology.
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: CellGetN crashing a procedure

Post by burnstripe »

Since it's a stack overflow place this in the prolog of the process creating the stack overflow

DebugUtility( 107, 0, 0, 'Cubename', 'Debug.txt', '' );
DebugUtility( 118, 0, 0, ' ', ' ', ' ' );

Replace the cube name with the name of the cube that was mentioned in the stack overflow error message, and debug.txt with the location and name of the output file you'd like to create eg: c:\temp\debug.txt.

Then run the process. If the cause is a Loop then it will hopefully tell you where the loop is and that should help start your investigation into the rules.

Also you could try isolate it using the cube viewer. Where the error lies, they'll be either an n/a or #ref or null. start from the top of the consolidation and work your way down. If there is no top consolidation consider selecting all leaves and using the rollup function in the subset editor (legacy)

If the cause of the stack overflow is one number pushes to too many others for example rolling balances from one month to the next it'll be even harder to see the cause.... If you have anything spot any rules that looks like it would look to previous month or next months the consider this a candidate for the error. To confirm first hash out these suspect rules and see if the stack overflow still occurs. If it doesn't then you will need to put in a rule that will break the stack
Post Reply