String size limit of a parameter in a TI

[Emixam]

Hello everybody,

Based on this link, TurboIntegrator is capable of handling string data in sizes of up to 8000 single-byte characters at a time.

I have to execute a process with a 200+ characters in one of my parameter. and it keeps crashing. I wonder what is the string size limit of a parameter value in a TI ?

Thanks and have a good day.

[paulsimon]

Hi

It can depend on how you are passing the parameter. If you are passing it from an Action Button via Excel then I believe the limit is only 255 chars, but this is due to a limitation in Excel rather than TI.

I have never really needed to pass a parameter that big, but in TI I have certainly built up strings of SQL to be executed that have been over 30,000 characters (in PA 2.0.5 although I would not expect 10.2.2 to be that different).

Regards

Paul Simon

[declanr]

For the strings you are passing I wonder if there are any special characters? The sizes don’t seem likely to be a problem but maybe the content is doing something spurious.

[paulsimon]

Good point Declan. I recently had an issue where someone had used a hard-return in the column heading of an Excel worksheet and then saved it as a CSV file. When read in, it made the TI miss out something like the first 500 lines of the file. It wasn't something you could detect and fix in the TI because those lines never got read in by the TI. However, that was with reading in a CSV rather than passing parameters. However, if the parameter is being passed in from Excel by an Action Button then it could be an issue.

Regards

Paul Simon

[Emixam]

Thanks guy,

I pass the parameter through an ExecuteProcess in a TI.

I have the following code I want to execute in one of my master TI :

Code: Select all

sMDX = '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)}, 0)}, "*User*")}';
ExecuteProcess( 'SYS - Create Subset',
	'pCubeName', 'Process Control',
	'pViewName', sViewName,
	'PDimName', 'Process Control Element',
	'pMDX', sMDX );

And I did some test and here's the result

Process completed successfully

Code: Select all

sMDX = '{TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)}';

Process completed successfully

Code: Select all

sMDX = '{TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)}, 0)}';

Process completed with minor errors. ...

Code: Select all

sMDX = '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)}, 0)}, "*User*")}';

and here's the error message:

Code: Select all

Error: Prolog procedure line (246): Could not create dynamic subset: Syntax error at or near: '', character position 99 expression:
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)

And line 246 is:

Code: Select all

SubsetCreatebyMDX( sSubsetName, sMDX);

Do you think there is a maximum of 100 characters ?
Any thoughts ?

[EvgenyT]

No, issue is with your MDX statement at position 99, try replacing ' " ' around *User* with sQuote = CHAR (34)

sMDX = '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Process Control Element] )}, ASC)}, 0)}, ' | sQuote |'*User*'| sQuote| ')}';

[Wim Gielis]

Any thoughts ?

Your code works fine provided you fill out the dimension name and pattern.
Here's my test code:

Code: Select all

sMDX = '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Consultant] )}, ASC)}, 0)}, "*Wim*")}';
Subsetcreatebymdx( 'test', sMDX );

If you use extra variables for user and / or dimension, make sure the syntax is correct. If you can't find the issue, post the full code.

Also, make sure at least 1 element qualifies for the MDX selection. If not the MDX will give an error (unless you suppress the error with an optional additional argument).

[Emixam]

Thanks Guys,

I appreciate it !

[paulsimon]

Hi

Also when you run a TI process always check the return code eg

vRet = ExecuteProcess(...)

IF( vRet <> ProcessExitNormal() ) ;
.... Some error handling
ENDIF ;

[Wim Gielis]

Thanks Guys,

I appreciate it !

Is it solved ?

[Emixam]

Is it solved ?

Yes.. I made a rookie mistake..

I had a something like this in my code:

Code: Select all

SUBST( sMDX, 1, 100)

That's why I only get the first 100 characters..

At least I know for sure there is no string size limit !!

Thanks !

[Wim Gielis]

I had a something like this in my code:

Code: Select all

SUBST( sMDX, 1, 100)

That's why I only get the first 100 characters..

Thanks !

Even with that tiny font size 8 or so in TM1 Architect you could have spotted that one faster

[ichermak]

Hi guys,

I know this discussion is old but in case someone is looking for the limit size of a string TI parameter, I did the test with PAL 2.0.9.10. It is equal to 65535 characters.

Cheers