RunProcess in PAFE

Within IBM Planning Analytics for Excel (PAFE), you can use the ExecuteFunction PAX API method to execute a Turbo Integrator process (TI script) using Excel VBA. While this command is good, there are several limitations:

  • ExecuteFunction takes a comma-separated string of TI parameter values but not parameter name
  • Therefore, the parameter values must be in the correct order
  • Therefore, you can’t skip optional TI parameters

If your TI script has the following parameters:

p1 (required)
p2 (required)
p3 (optional)

You can’t simply NOT specify the p3:

ExecuteFunction(host_name, server_name, process_name, "val1,val2")

This creates issues when the TI evolves on the server with new capabilities through additional optional parameters.

I took the challenge and created my own RunProcess subroutine to solve for these limitations.. This assumes you have the standard PAX API Reporting properties already in your Excel App.

RunProcess

Option Explicit

Const Host_Name As String = "https://production.MyPAWServer.com"
Const Server_Name As String = "tm1db"

Sub Run_Process(Process_Name As String, Parameter_Name_Value As Scripting.Dictionary)
    
    Dim Parameter_List() As String: Parameter_List() = Get_Parameter_List(Process_Name)
    Dim Current_Parameter_Name As Variant
    Dim Parameter_Value_String As String: Parameter_Value_String = ""
    Dim Run_Once As Boolean: Run_Once = False
    
    For Each Current_Parameter_Name In Parameter_List()
        
        If Run_Once = True Then
            Parameter_Value_String = Parameter_Value_String & ","
        End If
        
        If Parameter_Name_Value(Current_Parameter_Name) <> "" Then
            Parameter_Value_String = Parameter_Value_String & Parameter_Name_Value(Current_Parameter_Name)
        End If
        
        Run_Once = True
    
    Next

    CognosOfficeAutomationObject.ExecuteFunction _
        Host_Name, _
        Server_Name, _
        Process_Name, _
        Parameter_Value_String

End Sub

Function Get_Parameter_List(Process_Name As String) As String()

    If Process_Name = "" Then Exit Function
    If Reporting.ActiveConnection Is Nothing Then Exit Function

    Dim REST_API_Header As String: REST_API_Header = "/tm1/" & Server_Name & "/api/v1/Processes('" & Process_Name & "')"
    Dim REST_API_Response As Object: Set REST_API_Response = Reporting.ActiveConnection.Get(REST_API_Header)
    If REST_API_Response Is Nothing Then Exit Function
    
    Dim Current_Parameter_ID As Long
    Dim Parameter_Count As Long: Parameter_Count = REST_API_Response.Properties.item("Parameters").Members.Count - 1
    Dim Parameter_List() As String: ReDim Parameter_List(Parameter_Count)
    
    For Current_Parameter_ID = 0 To Parameter_Count
        Parameter_List(Current_Parameter_ID) = REST_API_Response.Properties.item("Parameters").Members.item(Current_Parameter_ID).Properties.item("Name").Value
    Next Current_Parameter_ID
    
    Get_Parameter_List = Parameter_List()

End Function
ParameterDescription
Process_NameThe name of the Turbo Integrator Process on the tm1 server.
Parameter_Name_ValueA dictionary object where the key is the name of the parameter and the value is the value for the parameter.
Parameters can be in any order.
Optional parameters can be skipped.

Examples

Let’s assume a Turbo Integrator Process being called is named My Process and has seven parameters:

  • pPar1 – Required
  • pPar2 – Required
  • pPar3 – Required
  • pPar4 – Required
  • pPar5 – Required
  • pPar6 – Required
  • pPar7 – Optional

Add the following to your Excel App, alongside RunProcess and Get_Parameter_List from above:

Sub Test_Run_Process()

    Dim Parameter_Name_Value As Scripting.Dictionary
    Set Parameter_Name_Value = New Scripting.Dictionary
 
    Parameter_Name_Value.Add "pPar3", "C3"
    Parameter_Name_Value.Add "pPar2", "B2"
    Parameter_Name_Value.Add "pPar1", "A1"
    ' Parameter_Name_Value.Add "pPar7", "G7" ' Optional parameters are not necessary to include
    Parameter_Name_Value.Add "pPar6", "F6"
    Parameter_Name_Value.Add "pPar4", "D4"
    Parameter_Name_Value.Add "pPar5", "E5"
 
    Run_Process "My Process", Parameter_Name_Value

End Sub

Despite the parameter order being shuffled in the Parameter_Name_Value dictionary and the optional parameter not being included, this process will still be executed as expected.

Scroll to Top