TM1 Forum

Posted: **Tue May 17, 2016 9:16 pm**

Hello all,

While creating a TI process from inside Excel VBA (I'm almost there !), I run into an issue of I think a String limit of 1,024 characters.

For example, if we put code in the Prolog of a TI process, after 1,024 characters (vbCrLf characters added, which is 2 characters every time) we find an extra CRLF character.
In itself, this isn't too much of a problem, we can just delete the few extra CRLF characters and we're done.

But when the extra CRLF breaks a commentary line in 2 pieces, the process won't save since syntaxically there is an error.

For example, have a look at the line with "SubsetCreateByMDX" for the dimension }Processes where effectively TM1FilterByLevel has been chopped off:

Code: Select all

vCube = 'General_Process control' ;

vView_Tgt = 'ZZZ_tmp' | '_' | Timst( Now, '\Y\m\d\h\i\s' ) ;
vSubset_Tgt = 'ZZZ_tmp' | '_' | Timst( Now, '\Y\m\d\h\i\s' ) ;

# Turn off target cube log changes
CubeSetLogChanges( vCube, 0 );

# Custom settings to output information to a text file
DataSourceAsciiDelimiter = ';';
DataSourceAsciiQuoteCharacter = '';

##########
# Zero Out the Target View
##########

# Start from scratch: destroy the temporary objects
ViewDestroy( vCube, vView_Tgt );

SubsetDestroy( '}Processes', vSubset_Tgt );
SubsetDestroy( 'General_Process measures', vSubset_Tgt );

# Create a temporary view
ViewCreate( vCube, vView_Tgt );

# Limit the contents of the view
ViewExtractSkipRuleValuesSet( vCube, vView_Tgt, 1 );
ViewExtractSkipZeroesSet( vCube, vView_Tgt, 1 );
ViewExtractSkipCalcsSet( vCube, vView_Tgt, 1 );
ViewExtractSkipConsolidatedStringsSet( vCube, vView_Tgt, 1 );

# Create temporary subsets in dimensions

SubsetCreateByMDX( vSubset_Tgt, '{TM1Sort( {TM
1FilterByLevel( {TM1SubsetAll( [ }Processes ] )}, 0)}, Asc)}', '}Processes' );
SubsetCreateByMDX( vSubset_Tgt, '{TM1Sort( {TM1FilterByLevel( {TM1SubsetAll( [ General_Process measures ] )}, 0)}, Asc)}', 'General_Process measures' );

# Adding subsets to the view
ViewSubsetAssign( vCube, vView_Tgt, '}Processes', vSubset_Tgt );
ViewSubsetAssign( vCube, vView_Tgt, 'General_Process measures', vSubset_Tgt );

# Clear existing data
ViewZeroOut( vCube, vView_Tgt );

# Tidy up: destroy temporary objects
ViewDestroy( vCube, vView_Tgt );

SubsetDestroy( '}Processes', vSubset_Tgt );
SubsetDestroy( 'General_Process measures', vSubset_Tgt );

Here is the relevant portion of the code (without error handling, just the code):

Code: Select all

    '1 - PROLOG
    sqCode_Prolog = Split(sCode_Prolog, "§")
    CountOfLines = UBound(sqCode_Prolog) + 1

    ReDim PrologLines(CountOfLines - 1)

    For i = 0 To CountOfLines - 1
        PrologLines(i) = sqCode_Prolog(i)
    Next

    ReDim lArray(CountOfLines)
    hArray = TM1ValArray(hArrayPool, lArray(), CountOfLines)

    For m = 0 To UBound(PrologLines)
        TM1ValArraySet hArray, TM1ValString(hsPool, PrologLines(m), 0), m + 1
    Next

    lReturn6 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessPrologProcedure, hArray)

sCode_Prolog is a String variable with each time the character § to mark a new line.

Does this limit of 1,024 characters pertain to the function TM1ObjectPropertySet ? Or Tm1ValString ?
Any pointers towards a solution of avoiding the extra CrLf character ?

Thanks a lot !

Posted: **Wed May 18, 2016 2:48 pm**

The only limit I'm aware of is that TM1ValString can only create a string with length less than 65535.

You could always iterate and output PrologLines and hArray to verify they contain the expected data.

Posted: **Wed May 18, 2016 3:06 pm**

Thank you Brian, I will work on this.

As I have the correct output in the Prolog tab, and the extra vbCrLf is *exactly* each time after every 1,1024 characters, it is something strange to my mind.
If we have a lof of code in a TI tab, the extra vbCrLf is put more than once, each time after 1,024 characters.
In any case, while the String variable sCode_Prolog contains the full TI code for the Prolog tab, it is chopped up and passed line after line to the functions.

Thanks again.

Posted: **Wed May 18, 2016 3:19 pm**

Yeah, it's a strange issue. While I have no VBA experience, I don't see anything that looks wrong in your code. All the TM1 API calls match what I'd expect to see in the C API. On the other hand, I've seen lines longer than 1024 characters in a TI created in architect which indicates it's not some fundamental API limit...

Real curious to see how this pans out.

Posted: **Wed May 18, 2016 3:55 pm**

BrianL wrote:Real curious to see how this pans out.

Me too

Thanks for confirming that it will not be an obvious errer of my part

Posted: **Wed May 18, 2016 10:48 pm**

Duh... Error from my side.

The String variable sCode_Prolog already had vbCrLf characters to separate lines, rather than § (my separation character).
Therefore, splitting sCode_Prolog on § yielded an array with only 1 element, the very long String.
In that case, it seems that outputs with an extra vbCrLf each 1,024th character.
When I split the long string on vbCrLf, it gives me more than 100 lines in the Prolog, each line being much smaller. Then TM1 adds them fine.

Problem solved, thanks BrianL.

TM1 Forum

TM1 API string limit

TM1 API string limit

Re: TM1 API string limit

Re: TM1 API string limit

Re: TM1 API string limit

Re: TM1 API string limit

Re: TM1 API string limit