VBA code to Run TI process

[ellissj3]

hello,

I know that there are some threads on this topic, but I have been having difficulty understanding something on the below code. I don't really understand parameter arrays. I am bit unclear on how to establish different parameter names / parameter choices. Can someone please take a moment to explain this to me? My intent is to execute a process with one parameter from the code below.

Thank you in advance,
Steve

Code: Select all

 ' v 1.0 23 August 2010
' Written by Martin Ryan based on code from Mike Cowie in the TM1 Forum
' http://forums.olapforums.com/viewtopic.php?f=3&p=4468#p4468

Declare Function TM1_API2HAN Lib "tm1.xll" () As Long

code]' #### SAMPLE CALL ####
Sub sampleTICall()
    Dim tm1ServerName As String, procName As String
    Dim p1, p2, p3, p4, p5, p6, p7 As String
   
    '--- Update this section as required
    tm1ServerName = "tm1_library" ' the name of the TM1 server
    procName = "DataExport_AnyDimension" ' the name of the process being called
    p1 = "ID" ' The first parameter
    p2 = "No" ' The second parameter
    '--- end updates section
   
    MsgBox RunTIProcess(tm1ServerName, procName, p1, p2)
End Sub
' #### END SAMPLE CALL ####

'HELPER FUNCTION TO GET TM1 API SESSION HANDLE
'  This allows you to leverage TM1 Excel Client's active connection to your TM1 Server
Public Function GetExcelSessionHandle() As Long
    GetExcelSessionHandle = Application.Run("TM1_API2HAN")
End Function

'EXECUTE PROCESS FUNCTION
Public Function RunTIProcess(ByVal ServerName As String, ByVal ProcessName As String, ParamArray ProcessParameters()) As String
  Dim SessionHandle As Long, ValPoolHandle As Long
  Dim ServerHandle As Long, ProcessHandle As Long
  Dim TotParams As Long, i As Long, RawParameterArray() As Variant, ArrOffset As Byte
  Dim InitParamArray() As Long, ParamArrayHandle As Long
  Dim ExecuteResult As Long, numResults As Integer, k As Integer, myMsg As String
  Dim iType As Integer, errStr As String * 75, tempVal As Long, returnStr As String * 75
 
  'Get handles
  SessionHandle = GetExcelSessionHandle()
  If SessionHandle = 0 Then
    RunTIProcess = "ERROR: Cannot communicate with TM1 API."
    Exit Function
  End If
 
  ValPoolHandle = TM1ValPoolCreate(SessionHandle)
  On Error GoTo Cleanup_API
   
  'Get handle to server; check if connected
  ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
  If ServerHandle = 0 Then
    RunTIProcess = "ERROR: Not connected to server " & ServerName & "."
    GoTo Cleanup_API
  End If
 
  'Get handle to process; check if valid
  ProcessHandle = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle, TM1ServerProcesses, TM1ValString(ValPoolHandle, ProcessName, 0))
  If TM1ValType(SessionHandle, ProcessHandle) <> TM1ValTypeObject Then
    RunTIProcess = "ERROR: Unable to access process " & ProcessName & "."
    GoTo Cleanup_API
  End If
 
  If TM1ValObjectCanRead(SessionHandle, ProcessHandle) = 0 Then
     RunTIProcess = "ERROR: No permissions to execute process " & ProcessName & "."
    GoTo Cleanup_API
  End If
 
  ' Get parameters, build array of param & type
  TotParams = UBound(ProcessParameters)
  If TotParams < 0 Then 'No parameters passed
    ReDim InitParamArray(1)
    InitParamArray(1) = TM1ValString(ValPoolHandle, "", 1)
    ParamArrayHandle = TM1ValArray(ValPoolHandle, InitParamArray, 0)
  Else
    ReDim RawParameterArray(TotParams, 1)
   
    'Collect the params, get their type
    For i = 0 To TotParams
      RawParameterArray(i, 0) = ProcessParameters(i)
      If VarType(ProcessParameters(i)) = vbString Then
        RawParameterArray(i, 1) = "S"
      Else
        RawParameterArray(i, 1) = "N"
      End If
    Next i
   
    'Now array of handles to params for process
    ReDim InitParamArray(TotParams)
    For i = 0 To TotParams
      If RawParameterArray(i, 1) = "S" Then
        InitParamArray(i) = TM1ValString(ValPoolHandle, CStr(RawParameterArray(i, 0)), 0)
      Else
        InitParamArray(i) = TM1ValReal(ValPoolHandle, CDbl(RawParameterArray(i, 0)))
      End If
    Next i
       
    'Set the parameters in TM1 array
    ParamArrayHandle = TM1ValArray(ValPoolHandle, InitParamArray, TotParams + 1)
    For i = 0 To TotParams
      TM1ValArraySet ParamArrayHandle, InitParamArray(i), i + 1
    Next i
  End If
 
  'Execute process
  ExecuteResult = TM1ProcessExecuteEx(ValPoolHandle, ProcessHandle, ParamArrayHandle)
  'ExecuteResult is an array containing information about the error type (minor, ProcessQuit, etc) and a reference to the error log location
  iType = TM1ValType(SessionHandle, ExecuteResult)

    If iType = CInt(TM1ValTypeIndex) Then
        RunTIProcess = "Process executed successfully"
    ElseIf iType = CInt(TM1ValTypeArray) Then
        'Retrieve the error
        TM1ValErrorString_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(1)), errStr, 75
        'Retrieve the error log file
        TM1ValStringGet_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(2)), returnStr, 75
        showProcessError errStr, returnStr
        RunTIProcess = "Errors occurred during process execution"
    ElseIf iType = CInt(TM1ValTypeError) Then
        RunTIProcess = "Returned an error value.  The process was not run for some reason"
    ElseIf iType = CInt(TM1ValTypeBool) Then
        RunTIProcess = "Returned a boolean value.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeObject) Then
        RunTIProcess = "Returned an object.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeReal) Then
        RunTIProcess = "Returned a real number.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeString) Then
        RunTIProcess = "Returned a string.  This should not happen."
    Else
        RunTIProcess = "Unknown return value: " & iType
    End If
 
Cleanup_API:
  TM1ValPoolDestroy ValPoolHandle
End Function

Sub showProcessError(reason As String, fileName As String)
Dim myMsg As String, filePath As String, logPath As String, fso

On Error GoTo errorHandler

Set fso = CreateObject("Scripting.FileSystemObject")
' Retrieve the log path that the user set in the options dialog box.
logPath = GetSetting(gSC_APP_NAME, gSC_REG_SECT_OPTS, gSC_REGKEY_LOGDIR, "") & "\"
filePath = logPath & fileName

' Check they've specified a log path
If logPath = "\" Then
    myMsg = "There was an error when running your process.  Specify a logging directory " & _
    "in the TM1 Tools options dialog box to be able to view the error log from this workbook.  The error message was " & reason
    MsgBox myMsg, vbOKOnly, "An error occurred"
' Check they've specified a valid log path
ElseIf Not fso.FolderExists(logPath) Then
    myMsg = "There was an error when running your process, but I can't find the error directory.  Check you have specified it " & _
    "correctly in the TM1 Tools options dialog box and that you have access privileges.  The error message was " & reason
    MsgBox myMsg, vbOKOnly, "An error occurred"
' If we can find the error file then give them the option of opening it
ElseIf fso.FileExists(filePath) Then
    myMsg = "There was an error when running your process.  Do you wish to view the error log?  The error message was " & reason
    If MsgBox(myMsg, vbYesNo, "View error log?") = vbYes Then
        If Dir(filePath) = "" Then
            MsgBox "I was unable to open the file.  The path is " & filePath
        Else
            Workbooks.Open filePath
        End If
    End If
Else
    ' Incorrect parameters (either how many of them, or incorrect numeric/string type) sticks a '$' on the end of the error
    ' log and is locked by TM1 so we can't open it.  There may be other reasons too.
    myMsg = "An error occurred but I cannot find the error log.  This may something is wrong with " & _
     "the parameters, or that the error log folder you have specified is incorrect. " & _
     Chr(13) & Chr(13) & "The error message was: " & reason
     MsgBox myMsg, vbOKOnly, "An error occurred"
End If
Exit Sub 'avoid error handler
errorHandler:
    MsgBox "An unknown error occured in the showProcessError sub of the bas_TurboIntegrator module in the TM1_Tools addin"
End Sub 

[blackhawk]

The way to think of this is that each parameter is an array of two values. The first value is what type the parameter is and the second element is the actual value.

The ParameterArray then is a collection (another array) of each of these value pairs. So for a two parameter process you would have the following

The 0th element in the param array is the first parameter of the TI script
. The 0th element of this is an "S" or "N" to indicate a string or numeric.
. The 1st element of this is the value.

The 1st element in the param array is the second parameter of the TI script:
. The 0th element of this is an "S" or "N" to indicate a string or numeric.
. The 1st element of this is the value.

Does that help?

[ellissj3]

Blackhawk,

Thank you for the help. I do understand what you are referring to. For example, if I had a TI called "TI-1" and it had one string parameter (LoadVersion) which i named "Target", then I would input.

ExecuteProcess("TI-1","LoadVersion","s","Target")

Where i'm failing to understand is if i were to only be running this code for 1 process all the time would there be a way to hardcode the parameter name, Numeric/String Indicator, and parameter selection information within the VBA?

Thank you,
Steve

[blackhawk]

Unfortunately TM1's API doesn't work that way. It is pretty ugly, but it does so for network efficiency.

When you really get deep into it you start to learn tricks about what to do and what to avoid, but you really need to think about performance and pool management constantly.

If you want to avoid all this api stuff, let me know, I can show you something that will actually allow you to use functions like:

Code: Select all

TM1ProcessDefnGet(String processName)
TM1ProcessExecute(String ProcessName, String Param1,...)
TM1ProcessExecuteBackground(String SuccessNotify, String FailureNotify, Boolean IncludeLog, String ProcessName, String Param1,...)
TM1ProcessExecuteByXML(String ProcessDefn, String Param1,...)
TM1ProcessParameters(String ProcessName)
TM1SandboxCommit(String sSandbox)
TM1SandboxCreate(String sSandbox, String sBasedOn)
TM1SandboxDelete(String sSandbox)
TM1SandboxMerge(String sFromSandbox, String sIntoSandbox)
TM1SandboxReset(String sSandbox)
TM1SandboxView(String CubeName, String ViewName, Boolean UseLabels, String SandboxName)
TM1SandboxViewByXML(String ViewDefnXML, String SandboxName)
TM1SandboxViewSubmit(XmlDocument xmlDoc, String SandboxName)
TM1SandboxViewWrite(Object ViewData, String SandboxName)

[blackhawk]

Wait a sec...after re-reading your comment:

Where i'm failing to understand is if i were to only be running this code for 1 process all the time would there be a way to hardcode the parameter name, Numeric/String Indicator, and parameter selection information within the VBA?

This code takes care of everything for you. All you have to do is call the function:

Code: Select all

RunTIProcess( "MyServer", "TI-1","Target")

What is wrong with that?

[ellissj3]

Nothing, this worked like a charm. On a separate topic, i would like to begin learning API. Do you have any reccomendations on resources (besides the TM1 API) that would be helpful?

Steve

[Alan Kirk]

hello,

I know that there are some threads on this topic, but I have been having difficulty understanding something on the below code.

The other thing to be aware of is that you don't even need to bother about understanding the code if you don't want to. If you use the TM1 Tools add-in you can just call the function from your own code as if it were a standard VBA function.

[blackhawk]

That is another matter altogether. I suppose with IBM now in the mix we might start to see something, but as of yet, I have not. Just keep looking at sample code and play with it...sooner or later you will get the hang of it.

This board is a great tool for taping into the knowledge base of a lot of experienced TM1 folks. When you get stuck, and genuinely stuck, ask the question. Sometimes it is frowned upon if people post messages like "fix this for me" or "can someone code this up for me", but beyond that everyone here is pretty good about answering questions.

[Alan Kirk]

On a separate topic, i would like to begin learning API. Do you have any reccomendations on resources (besides the TM1 API) that would be helpful?

I have no idea what you mean by that. There is no one thing called "API". An Application Programming Interface is something which is unique to each application that exposes one. Windows has an API. TM1 has an API; actually, three of them. The methods of handling of data in the former is nothing like the latter. In fact the handling of data in each of the three TM1 APIs is quite different from the other two. Office applications have APIs. Some APIs will be object oriented, some will not. Some will be available for specific programming languages. You can't learn "API" as a single subject.

[ellissj3]

Alan,

I am trying to better understand the TM1 API. I was not aware that each application has its own API. I apologize for any confusion. I am just trying to get closer to understand the TM1 application.

Thank you,
Steve

[blackhawk]

Ouch Alan.

I think given the fact that he is posting based on the C/VB API and the fact that IBM calls their own "IBM Cognos API Guide" as the C/VB API, we can get some idea of what he is requesting.

[Alan Kirk]

I am trying to better understand the TM1 API. I was not aware that each application has its own API. I apologize for any confusion.

The confusion only stemmed from when you said "besides the TM1 API"

I am just trying to get closer to understand the TM1 application.

There's a TM1 API manual if you're looking at the VB/C version. (Download locations will be found in the FAQ thread.) If you're looking at the .Net API, there's only a compiled help file which will be in the install directory. If it's the Java API there's a html based help file in the install directory.

But to be honest if you want to understand the application then I don't think that the API (any of them) is the place to go. The VB/C APIs, for instance, use value pools and value capsules, which is a concept that you'll never come across in day to day TM1 use (or in other APIs come to that).

[Alan Kirk]

Ouch Allen.

I think given the fact that he is posting based on the C/VB API and the fact that IBM calls their own "IBM Cognos API Guide" as the C/VB API, we can get some idea of what he is requesting.

The reason that it was unclear is specified in my previous post. And at least I can be bothered to spell his name correctly.

{Edit: Oh, I see that you edited your post. Well, that's something....}

And read the bits of the forum policies about not boosting commercial products that posters are associated with.

[blackhawk]

Alan,

Sorry about that...typo. I have a number of friends with a different spelling and I am used to typing their name. I did edit it though. My apologies.

And I am aware of the rules that is why I asked to take it off line (which is the board rule) if he wanted to stay away from the API. Nothing more, no links, no names etc.

[Alan Kirk]

And I am aware of the rules that is why I asked to take it off line (which is the board rule) if he wanted to stay away from the API. Nothing more, no links, no names etc.

The policies are actually:

- If a user posts about a problem that they're having and you think that your product or service can fix it, please link them to your post in the Commercial forums. "Boosting" your product in the main forums isn't acceptable.

- If you're providing a good or service you need to make that known in your post. Under no circumstances should suppliers or contractors recommend products or services while masquerading as end users only. Any posts found (or strongly suspected) of being in that category will be deleted.

The appropriate link would have been to your commercial forum post, if any. But you definitely should have specified that it was in relation to a commercial product that you're involved with. Steve, as a relatively new user, may have been unaware of that, and that it was something that he was being offered for sale. The objective is just complete transparency.

[blackhawk]

Alan,

I understand and will comply.

Sorry, I didn't mean to ruffle feathers; I was trying to be polite and not call attention, but it didn't come out that way, I guess.

[Alan Kirk]

I am just trying to get closer to understand the TM1 application.

...

But to be honest if you want to understand the application then I don't think that the API (any of them) is the place to go. The VB/C APIs, for instance, use value pools and value capsules, which is a concept that you'll never come across in day to day TM1 use (or in other APIs come to that).

Actually it occured to me that there's not much point saying what not to look at without saying what to look at.

IMHO if you really want to get under the hood of TM1 the two best places are Rules and the TI. The Rules manual is reasonably comprehensive, and working through the examples will give you a good working understanding of how calculations happen and therefore how to optimise for performance.

The TI manual leaves a great deal more to be desired as it concentrates on using the Map tab to generate code automatically. However if you come to grips with the manual creation of code, you'll develop an understanding of everything from dimension and other metadata maintenance to data uploads to importing and exporting data.

Both of these will be of far greater value in developing a deep understanding of the TM1 system than the API, which is really aimed at niche situations where you just can't get the results you want any other way.