VB API to create a process

Wim Gielis · Post by **Wim Gielis** » Fri Dec 11, 2015 10:03 am

Hello all,

Quick question, but not an easy answer... If it's possible at all.

Does anyone know a place where I can find the API code to create (generate) a process in TI ? I have used the VB API code for TM1 in the past, no experience with Java or C though.
I have text strings for Prolog, Metadata, Data, Epilog tab, but I am also looking at generating a new TI process containing those scripts. Added bonus: assigning a parameter or setting variables in the process.

Thanks in advance !

Wim

Wim Gielis · Post by **Wim Gielis** » Sat Dec 12, 2015 5:02 pm

Okay, so I (only now) saw this post:
http://www.tm1forum.com/viewtopic.php?t=3355

I will investigate and see if I can get this running.

Wim

Wim Gielis · Post by **Wim Gielis** » Sun Dec 13, 2015 7:44 pm

Update: the below code works fine to put code in Prolog and Epilog.
It can be extended to do the Metadata and Data tabs as well.

But I don't seem to be able to set the datasource of the process to a text file or to a cube view.
In addition, setting parameters (numeric or string) is not achieved either.

Does anyone have some clues ? How do I translate CHARACTERDELIMITED as the data source to a value that the API likes ?

Code: Select all

Option Explicit

'Source: 'http://www.tm1forum.com/viewtopic.php?t=3355

Declare Sub TM1APIInitialize Lib "tm1api.dll" ()
Declare Sub TM1APIFinalize Lib "tm1api.dll" ()

Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Sub TM1SystemClose Lib "tm1api.dll" (ByVal hUser As Long)

Declare Sub TM1SystemAdminHostSet Lib "tm1api.dll" (ByVal hUser As Long, ByVal AdminHosts As String)
Declare Function TM1SystemServerConnect Lib "tm1api.dll" (ByVal hPool As Long, ByVal sServer As Long, ByVal sClient As Long, ByVal sPassword As Long) As Long

Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Sub TM1ValPoolDestroy Lib "tm1api.dll" (ByVal hPool As Long)

Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long

Declare Function TM1ValArray Lib "tm1api.dll" (ByVal hPool As Long, ByRef sArray() As Long, ByVal MaxSize As Long) As Long
Declare Function TM1ValArrayGet Lib "tm1api.dll" (ByVal hUser As Long, ByVal vArray As Long, ByVal index As Long) As Long
Declare Function TM1ValArrayMaxSize Lib "tm1api.dll" (ByVal hUser As Long, ByVal vArray As Long) As Long
Declare Sub TM1ValArraySet Lib "tm1api.dll" (ByVal vArray As Long, ByVal val As Long, ByVal index As Long)
Declare Sub TM1ValArraySetSize Lib "tm1api.dll" (ByVal vArray As Long, ByVal Size As Long)

Declare Function TM1ServerProcesses Lib "tm1api.dll" () As Long
Declare Function TM1ProcessCreateEmpty Lib "tm1api.dll" (ByVal hPool As Long, ByVal hServer As Long) As Long
Declare Function TM1ProcessPrologProcedure Lib "tm1api.dll" () As Long
Declare Function TM1ProcessEpilogProcedure Lib "tm1api.dll" () As Long
Declare Function TM1ProcessCheck Lib "tm1api.dll" (ByVal hPool As Long, ByVal hProcess As Long) As Long
Declare Function TM1ProcessUpdate Lib "tm1api.dll" (ByVal hPool As Long, ByVal hOldProcess As Long, ByVal hNewProcess As Long) As Long

Declare Function TM1ObjectListHandleByIndexGet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal iPropertyList As Long, ByVal iIndex As Long) As Long
Declare Function TM1ObjectListHandleByNameGet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal iPropertyList As Long, ByVal sName As Long) As Long

Declare Function TM1ObjectPropertySet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal Property_P As Long, ByVal ValRec_V As Long) As Long

Declare Function TM1ObjectDuplicate Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long) As Long
Declare Function TM1ObjectDestroy Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long) As Long
Declare Function TM1ObjectRegister Lib "tm1api.dll" (ByVal hPool As Long, ByVal hParent As Long, ByVal hObject As Long, ByVal sName As Long) As Long

Sub createEmptyProcess()
    Call addProcess("adminhost", "tm1servername", "username", "password", "testprocedure")
End Sub

Public Function addProcess(ByVal sAdminHost As String, ByVal sServerName As String, _
                           ByVal sClientName As String, ByVal sClientPassword As String, ByVal sProcessName As String) As Boolean

    Dim hUser As Long
    Dim hServerName As Long, hClientName As Long, hClientPassword As Long, hProcessName As Long
    Dim hServer As Long
    Dim hPool As Long, hsPool As Long, hpPool As Long, hppPool As Long, hArrayPool As Long
    Dim hArray As Long
    Dim lArray() As Long
    Dim NoParameters As Integer
    Dim lReturn1, lReturn2, lReturn3, lReturn4, lReturn5, lReturn6, lReturn7, lReturn8, lReturn9 As Long

    hUser = TM1SystemOpen()

    hPool = TM1ValPoolCreate(hUser)
    hsPool = TM1ValPoolCreate(hUser)
    hpPool = TM1ValPoolCreate(hUser)
    hppPool = TM1ValPoolCreate(hUser)
    hArrayPool = TM1ValPoolCreate(hUser)


    hServerName = TM1ValString(hsPool, Trim(sServerName), 0)
    hClientName = TM1ValString(hsPool, Trim(sClientName), 0)
    hClientPassword = TM1ValString(hsPool, Trim(sClientPassword), 0)
    hProcessName = TM1ValString(hsPool, Trim(sProcessName), 0)


    TM1SystemAdminHostSet hUser, sAdminHost

    hServer = TM1SystemServerConnect(hPool, hServerName, hClientName, hClientPassword)

    lReturn1 = TM1ProcessCreateEmpty(hpPool, hServer)
    lReturn2 = TM1ObjectRegister(hpPool, hServer, lReturn1, hProcessName)

    lReturn3 = TM1ObjectListHandleByNameGet(hpPool, hServer, TM1ServerProcesses, hProcessName)
    lReturn4 = TM1ObjectDuplicate(hpPool, lReturn3)

    
    Dim sCode_Prolog As String
    Dim sCode_Epilog As String
    
    sCode_Prolog = "§§§vDim = 'Account_PL';§DimensionCreate( vDim );"
    
    NoParameters = 1
    ReDim lArray(NoParameters)
    hArray = TM1ValArray(hArrayPool, lArray(), NoParameters)

    TM1ValArraySet hArray, TM1ValString(hsPool, Replace(sCode_Prolog, "§", vbCrLf), 0), 1

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


    sCode_Epilog = "§§§vCube = 'PL';§CubeSetLogChanges( vCube, 0 );"
    
    NoParameters = 1
    ReDim lArray(NoParameters)
    hArray = TM1ValArray(hArrayPool, lArray(), NoParameters)

    TM1ValArraySet hArray, TM1ValString(hsPool, Replace(sCode_Epilog, "§", vbCrLf), 0), 1

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


    lReturn7 = TM1ProcessCheck(hpPool, lReturn4)
    lReturn8 = TM1ProcessUpdate(hpPool, lReturn3, lReturn4)

    lReturn9 = TM1ObjectDestroy(hpPool, lReturn4)

    TM1ValPoolDestroy (hPool)
    TM1ValPoolDestroy (hsPool)
    TM1ValPoolDestroy (hpPool)
    TM1ValPoolDestroy (hppPool)

    TM1SystemClose (hUser)
    TM1APIFinalize

End Function

BrianL · Post by **BrianL** » Mon Dec 14, 2015 4:30 pm

I've never played around with the VB api at all, but since it looks like it's just calling into the C api I'll try to help.

You should be able to set the datasource type with the TM1ProcessDataSourceType() property. It expects a string value and I'd expect CHARACTERDELIMITED to work.

You probably need to also specify the TM1ProcessDataSourceNameForServer() property with the file name. I'd have to spin up some tests to see if there are other required properties for the CHARACTERDELIMITED datasource type.

Parameters again have their own properties. TM1ProcessParametersDefaultValues(), TM1ProcessParametersNames(), TM1ProcessParametersPromptStrings(), and TM1ProcessParametersTypes().

The parameters names property takes an array of strings and each is given a default type of string, default value of "", and a default empty prompt string.
TM1ProcessParametersTypes() takes an array of index values. Value 32 is string and 33 is numeric.
TM1ProcessParametersDefaultValues() takes an array of either string or index values (depending on the type of the parameter).
TM1ProcessParametersPromptStrings() takes an array of strings.

note: These arrays need to all be in the same order. You should specify names first. Types need to be specified before default values. I don't think it matters when prompt strings are sent, as long as you've already done the names.

Wim Gielis · Post by **Wim Gielis** » Mon Dec 14, 2015 7:05 pm

Thanks BrianL,

Code: Select all

Dim lReturn As Long
lReturn = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceType, TM1ValString(hppPool, "VIEW", 0))

works ! Just as CHARACTERDELIMITED does in the string.
Continuing the journey but thank you very much for your help.

Wim Gielis · Post by **Wim Gielis** » Mon Dec 14, 2015 8:38 pm

Update:

For a text file, it now works, but not completely.
Setting 1 header record is not covered yet:

Code: Select all

    'lReturnDataSource = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceType, TM1ValString(hppPool, "VIEW", 0))
    lReturnDataSource = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceType, TM1ValString(hppPool, "CHARACTERDELIMITED", 0))
    'lReturnDataSource = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceType, TM1ValString(hppPool, "POSITIONDELIMITED", 0))

    lReturnDataSourceNameForClient = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceNameForClient, TM1ValString(hppPool, "D:\data.txt", 0))
    lReturnDataSourceNameForServer = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceNameForServer, TM1ValString(hppPool, "D:\data.txt", 0))
    
    lReturnDataSource1 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceASCIIDelimiter, TM1ValString(hppPool, vbTab, 0))
    lReturnDataSource2 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceASCIIDecimalSeparator, TM1ValString(hppPool, ",", 0))
    lReturnDataSource3 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceASCIIThousandSeparator, TM1ValString(hppPool, ".", 0))
[b][u]    lReturnDataSource4 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceASCIIHeaderRecords, TM1ValReal(hppPool, 1))[/u][/b]
    lReturnDataSource5 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessDataSourceASCIIQuoteCharacter, TM1ValString(hppPool, """", 0))

The other properties work fine, the process is created, but the box for the number of header records remains empty. No errors though.

BrianL · Post by **BrianL** » Mon Dec 14, 2015 8:57 pm

Try the TM1ProcessDataSourceASCIIHeaderRecords() property. It takes an index value.

Wim Gielis · Post by **Wim Gielis** » Mon Dec 14, 2015 9:00 pm

BrianL wrote:Try the TM1ProcessDataSourceASCIIHeaderRecords() property. It takes an index value.

I indeed use that property but I did not know about the index value...
I tested with 1, TM1ValReal( ... , 1 ) and so on.
Thanks, I'll experiment tomorrow.

BrianL · Post by **BrianL** » Mon Dec 14, 2015 9:22 pm

For a cube view, the datasource type should be "VIEW". You'll also need to set the cube name as a string value for the TM1ProcessDataSourceNameForServer() property and the view name as a string for the TM1ProcessDataSourceCubeView() property.

Wim Gielis · Post by **Wim Gielis** » Tue Dec 15, 2015 9:43 pm

Hi there,

I have got the number of header records working based on your advice that it's an index.
Also, the process can now have as a data source: text file, cube view, dimension subset, ODBC. Pretty much everything I need for now

Getting good variables and the variable contents set to "Other" is more difficult. For the moment they are all on 'Ignore'.
Furthermore, my text file with 6 columns / variables is shown in TI as 12 variables in the Variables tab (6 good names and once more 6 (the same) names). Weird !

Last part will be the parameters, first getting the variables correct.

For any code snippets, even in C programming language, I would be very happy

pdxTm1Guy · Post by **pdxTm1Guy** » Wed Dec 16, 2015 9:30 pm

Wim,

In the Java API you can set the properties of the variables. I am sure you can do it in the VP API.
The first thing you do is create TM1Val's for the Names, Types, UIData, and Positions of the Variables.
If the Variable is a String you want to add the TM1ObjectType.VariableString property to the array
If the Variable is Numeric you want to add the TM1ObjectType.VariableNumeric.

Code: Select all

varTypes.addToArray(i, new TM1Val(TM1ObjectType.VariableString));

Code: Select all

TM1Val varNames = TM1Val.makeArrayVal(varCount); // Variable Names
TM1Val varTypes = TM1Val.makeArrayVal(varCount); // Variable Types
TM1Val varPos = TM1Val.makeArrayVal(varCount); // Variable Positions
TM1Val varCode = TM1Val.makeArrayVal(varCount); // Variable UIData

The biggest trick here is the UIData. If your variable is a String and you want to set it to OTHER, this is how you would add it to the varCode like so:

Code: Select all

varCode.addToArray(i, new TM1Val("VarType=32\u000cColType=827\u000c"));

The VarType=32 is the code for String and the ColType=827 is the code for OTHER. The BIG Trick is that you have to separate it with an FormFeed Character \u000c or \f in Java.

Go to one of your processes on your server and open it with notepad. You will see if you have other variables set as an Element the ColType will be different (ColType=825).
Again the trick is when setting the UIData TM1Val, it is VarType=code + FormFeed + ColType=code + FormFeed. You can probably set formulas dynamically but I haven't needed to do that.

Once everything is set, set the Process Properties and do a check to make sure it works.

Code: Select all

TM1Val chk = aProcess.setProperty(TM1Properties.ProcessVariablesNames, varNames);
		aProcess.setProperty(TM1Properties.ProcessVariablesTypes, varTypes);
		aProcess.setProperty(TM1Properties.ProcessVariablesUIData, varCode);
		aProcess.setProperty(TM1Properties.ProcessVariablesPositions, varPos);

If you really want to get crazy... You can do what I do and set the variables of a cubeView by adding a v in front of the dimension name and changing the case. I also replace the spaces in dimension names with an underscore. This also takes out some of the funky characters using a Regular Expression.

Code: Select all

private String setDimVarNames(String dimName) {
		/** Takes a string like this: account and makes it: vAccount */
		StringBuilder nb = new StringBuilder("");
		// replace any spaces with underscores.
		String ele = dimName.toUpperCase().replaceAll("\\s", "_").replaceAll(MEAS_REGEX, "_").replaceAll("\\-", "_").replaceAll("\\+","_");
		nb.append(ele);
		String firstLetter = ele.substring(0, 1);
		nb.insert(0, 'v');
		nb.replace(1, 2, firstLetter);
		ele = null;
		firstLetter = null;
		return nb.toString();

Since Alan Kirk posted his series on the TM1 Java API (nice job by the way!), I can't let him make me look like a Java rookie so I am going to be posting a blog on how to create a process that creates a Cube View and exports it to a file with the Java API soon on our site.

Cheers
Kevin Beckley

pdxTm1Guy · Post by **pdxTm1Guy** » Thu Dec 17, 2015 12:42 am

I found one of my methods in Java that creates a Parameter...

Code: Select all

private void setProcessParameters(String parameterName) {
		/** If the Parameter is set to ALL, then the process will export the entire cube**/
		if(!parameterName.equals(measureName)) {
			parameterName = "ALL";
		}
		TM1Val propArr1 = TM1Val.makeArrayVal(1);
		propArr1.addToArray(0, new TM1Val("pMeasure"));
		aProcess.setProperty(TM1Properties.ProcessParametersNames, propArr1);

		TM1Val propArr3 = TM1Val.makeArrayVal(1);
		propArr3.addToArray(0, new TM1Val(TM1ObjectType.VariableString));
		aProcess.setProperty(TM1Properties.ProcessParametersTypes, propArr3);

		TM1Val propArr2 = TM1Val.makeArrayVal(3);
		propArr2.addToArray(0, new TM1Val("Enter the Measure to Export"));
		aProcess.setProperty(TM1Properties.ProcessParametersPromptStrings,
				propArr2);

		TM1Val propArr4 = TM1Val.makeArrayVal(3);
		propArr4.addToArray(0, new TM1Val(measureName));
		TM1Val chk = aProcess.setProperty(TM1Properties.ProcessParametersDefaultValues,
				propArr4);
		if(chk.getBoolean()) {
			logger.info("Process: " + processName + " Parameters set.");
		} else {
			logger.warn("Process: " + processName + " Parameters had errors: " + chk.getErrorCode());
		}
	}

Hope this helps.

Wim Gielis · Post by **Wim Gielis** » Thu Dec 17, 2015 4:14 am

Superb Kevin, many thanks.
.
I will only need colType = 827 for Other, no TI wizard for me

pdxTm1Guy · Post by **pdxTm1Guy** » Thu Dec 17, 2015 5:49 am

Wim,

Glad to be of service. And yes... I know that YOU don't need a TI wizard.

One more thing to mention... If you are going to only use the prolog and epilog, It is worthy to mention to set your datasource like so (at least in the Java API):

Code: Select all

aProcess.setProperty(TM1Properties.ProcessDataSourceType, new TM1Val("NULL"));

I spent a few hours trying to crack that one.

Cheers!

Kevin

Wim Gielis · Post by **Wim Gielis** » Thu Dec 17, 2015 7:19 am

pdxTm1Guy wrote:Wim,

Glad to be of service. And yes... I know that YOU don't need a TI wizard.

One more thing to mention... If you are going to only use the prolog and epilog, It is worthy to mention to set your datasource like so (at least in the Java API):
Code: Select all
aProcess.setProperty(TM1Properties.ProcessDataSourceType, new TM1Val("NULL"));
I spent a few hours trying to crack that one.

Cheers!

Kevin

Thanks ! Though without setting it to NULL, (at least what I saw until now) TI does this out of his own and nothing bad happens. But better to set it to NULL, you are right.

Thanks for becoming active on the forum (after 2 years) just when I needed it

Wim Gielis · Post by **Wim Gielis** » Thu Dec 17, 2015 10:08 pm

I succeeded in setting the variable names and types when the Data source is a cube view

Thanks both.

Wim Gielis · Post by **Wim Gielis** » Thu Dec 17, 2015 10:51 pm

And setting the parameters is also fine. I've got everything I need by now !

pdxTm1Guy · Post by **pdxTm1Guy** » Fri Dec 18, 2015 1:07 am

Excellent!

Phil66 · Post by **Phil66** » Mon Apr 04, 2016 3:51 pm

Wim Gielis wrote:And setting the parameters is also fine. I've got everything I need by now !

Dear Wim,

with great interest I have followed your posts here. What I really need, since a long time can not be realized, is the same you write here but I am not able to get it to run with things got written here.

I need to

create a process by VB / VBA,
change the data source for this process (e.g. to text or view),
declare variables with different types and for severals uses (e.g. text or numeric and ignore or data),
apply parameters,
create codes in the different sections of the ti procedure.

I startet to follow your code but already the change of the data source type I can not understand. What does it mean to set a parameter as an index (I tried everything I can imagine)?! Would you be so nice to post some of your running code as an example here? This would be a really great help for me, thanks a lot in advance!

Phil

Wim Gielis · Post by **Wim Gielis** » Mon Apr 04, 2016 4:18 pm

Hello Dirk,

My code is still very fragmented. It works but is not steamlined yet and contains a lot of hardcoded values.
It is very much work in progress. Once it's ready I will let you know. It's from VBA for now, not VB.
I don't think I already had everything you list, maybe no parameters if I remember correctly.

Best regards,

Wim

TM1 Forum

VB API to create a process

VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process

Re: VB API to create a process