String size limit of a parameter in a TI

Post Reply
Emixam
Posts: 45
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016

String size limit of a parameter in a TI

Post by Emixam » Sun Feb 02, 2020 4:47 pm

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.

User avatar
paulsimon
MVP
Posts: 739
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: String size limit of a parameter in a TI

Post by paulsimon » Sun Feb 02, 2020 5:38 pm

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
MVP
Posts: 1642
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: String size limit of a parameter in a TI

Post by declanr » Sun Feb 02, 2020 7:30 pm

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.
Declan Rodger

User avatar
paulsimon
MVP
Posts: 739
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: String size limit of a parameter in a TI

Post by paulsimon » Sun Feb 02, 2020 10:09 pm

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
Posts: 45
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016

Re: String size limit of a parameter in a TI

Post by Emixam » Mon Feb 03, 2020 12:57 am

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
Community Contributor
Posts: 318
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: String size limit of a parameter in a TI

Post by EvgenyT » Mon Feb 03, 2020 5:20 am

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
MVP
Posts: 2364
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: String size limit of a parameter in a TI

Post by Wim Gielis » Mon Feb 03, 2020 8:45 am

Emixam wrote:
Mon Feb 03, 2020 12:57 am
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).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Emixam
Posts: 45
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016

Re: String size limit of a parameter in a TI

Post by Emixam » Mon Feb 03, 2020 3:56 pm

Thanks Guys,

I appreciate it !

User avatar
paulsimon
MVP
Posts: 739
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: String size limit of a parameter in a TI

Post by paulsimon » Mon Feb 03, 2020 10:42 pm

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
MVP
Posts: 2364
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: String size limit of a parameter in a TI

Post by Wim Gielis » Tue Feb 04, 2020 11:35 am

Emixam wrote:
Mon Feb 03, 2020 3:56 pm
Thanks Guys,

I appreciate it !
Is it solved ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Emixam
Posts: 45
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016

Re: String size limit of a parameter in a TI

Post by Emixam » Tue Feb 04, 2020 2:29 pm

Wim Gielis wrote:
Tue Feb 04, 2020 11:35 am
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
MVP
Posts: 2364
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: String size limit of a parameter in a TI

Post by Wim Gielis » Tue Feb 04, 2020 8:31 pm

Emixam wrote:
Tue Feb 04, 2020 2:29 pm
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 !
:shock:
Even with that tiny font size 8 or so in TM1 Architect you could have spotted that one faster :lol:
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Post Reply