TM1 API: Connecting and disconnecting a user

  • Mar. 22, 2011

Introduction

As you might (or will) probably know, TM1 exposes a number of functions and procedure calls using its API. In plain English, this means that a standalone program can interact with TM1 objects like cubes, dimensions, processes, and so on. Also in Excel, which is for obvious reasons widely used with the TM1 software.

IBM Cognos provides a document on how to work with the API, it is the API Guide. It lists everything that a developer can use regarding TM1. I apply the VB version of the code in VBA for Excel.

First step

I see this exercise as the first step to do different data spreading operations in an automated way. As it appears to me, Turbo integrator is very limited in this respect, and only allows proportional spread operations. To be continued in the next article if ever I succeed. But we first need to be able to log in in a good way.

In this article, I will share my code to connect a user to a certain TM1 server (that is up and running) using the VB variant of the TM1 API code. Also, the code to disconnect in a safe way will be shared. Well, in a safe way, … Excel will crash in case of an unexpected error but AFAIK you cannot avoid this behaviour.

Before writing code, you should take a number of steps to get you started. Please refer to the beginning of the TM1 API Guide and the Web. For instance, TM1 forum or this article by Ben Hill.

VBA code

Here is the code to connect, based on the material in the API Guide and other code excerpts from Iboglix. Apologies for the bad layout, I guess that anyone trying out this code will copy it to the VBE. 

Public Sub Connect( _
       sTM1Server As String, _
       sUser As String, _
       sPassword As String, _
       Optional sMachine As String = "localhost")


' Wim Gielis
' https://www.wimgielis.com


'''''
' VBA code to connect to a TM1 server using the TM1 API
' 22/03/11
'''''

    Dim blnServerFound As Boolean
    Dim iServer As Integer
    Dim sDbName As String * FIXED_STR_LENGTH
    Dim sFriendlyMessage As String

    sErrorMsg = ""

    If Len(Trim$(sTM1Server)) = 0 Or Len(Trim$(sUser)) = 0 Then Exit Sub
    If Trim(sMachine) = 0 Then sMachine = "localhost"

    ' initialize the API
    TM1APIInitialize
    hUser = TM1SystemOpen()

    'Set the Admin Host Server Name
    TM1SystemAdminHostSet hUser, Trim$(sMachine)

    blnServerFound = False

    ' Loop through the available servers
    For iServer = 1 To TM1SystemServerNof(hUser)

        TM1SystemServerName_VB hUser, iServer, sDbName, FIXED_STR_LENGTH
        If UCase(Replace(sDbName, Chr(0), "")) = UCase(Trim$(sTM1Server)) Then

            blnServerFound = True

            If Not blnConnected Then

                ' Create a Pool Handle
                hPool = TM1ValPoolCreate(hUser)

                ' Establish Login information
                vServerName = TM1ValString(hPool, Trim$(sTM1Server), 0)
                vUser = TM1ValString(hPool, Trim$(sUser), 0)
                vPassword = TM1ValString(hPool, Trim$(sPassword), 0)

                'Log in to the TM1 Server
                hServer = TM1SystemServerConnect(hPool, vServerName, vUser, vPassword)

                If TM1ValType(hUser, hServer) = TM1ValTypeObject() Then

                    blnConnected = True

                ElseIf TM1ValType(hUser, hServer) = TM1ValTypeError() Then

                    Select Case TM1ValErrorCode(hUser, hServer)

                    Case 80    '= TM1ErrorSystemServerClientNotFound()
                        sFriendlyMessage = "The specified client (" & Trim$(sUser) & ") was not found."

                    Case 81    '= TM1ErrorSystemServerClientPasswordInvalid()
                        'NOT: TM1ErrorSystemServerClientPasswordIncorrect() as mentioned in the API guide TM1 9.5.0
                        sFriendlyMessage = "The specified password (" & Trim$(sPassword) & ") was not found for this client (" & Trim$(sUser) & ")."

                    Case 87    '= TM1ErrorSystemServerNotFound()
                        sFriendlyMessage = "The specified server (" & Trim$(sTM1Server) & ") was not found. The TM1 server is not running or it does not exist."

                    Case 120    '= TM1ErrorClientMaximumPortsExceeded() in TM1 9.5.2 ?
                        sFriendlyMessage = "The specified client (" & Trim$(sUser) & ") might already be logged in to the specified server (" & _
                                           Trim$(sTM1Server) & ") elsewhere, e.g. TM1 Perspectives."

                    Case 138    '= TM1ErrorSystemServerClientExceedMaxLogonNumber() in TM1 9.5.2 ?
                        sFriendlyMessage = "You exceeded the maximum number of logons."

                    Case Else
                        sFriendlyMessage = "There appears to be an unknown error."

                    End Select

                    blnConnected = False

                    Call TM1ValErrorString_VB(hUser, hServer, sErrorMsg, FIXED_STR_LENGTH)
                    HandleError TM1ValErrorCode(hUser, hServer), sErrorMsg, sFriendlyMessage

                End If

            End If

            Exit For

        End If

    Next

    If Not blnServerFound Then

        HandleError 0, vbNullString, "The specified server (" & Trim$(sTM1Server) & ") seems to be not running."

    End If


End Sub

And more code…

Here is my code to disconnect: 

Public Sub Disconnect()


' Wim Gielis
' https://www.wimgielis.com


'''''
' VBA code to disconnect from a TM1 server using the TM1 API
' 22/03/11
'''''

    Dim vDisconn As Long
    Dim sFriendlyMessage As String

    If hPool And vServerName Then

        vDisconn = TM1SystemServerDisconnect(hPool, vServerName)

        If TM1ValBoolGet(hUser, vDisconn) Then

            TM1ValPoolDestroy hPool
            TM1SystemClose hUser
            TM1APIFinalize

            blnConnected = False

        ElseIf TM1ValType(hUser, vDisconn) = TM1ValTypeError() Then

            Select Case TM1ValErrorCode(hUser, vDisconn)

            Case 91    '=TM1ErrorSystemParameterTypeInvalid()
                sFriendlyMessage = "SystemParameterTypeInvalid."

                blnConnected = False

            Case 79    '=TM1ErrorSystemServerClientNotConnected()
                sFriendlyMessage = "It seems that the client was not connected."

                blnConnected = False

            Case Else
                sFriendlyMessage = "There appears to be an unknown error."

                blnConnected = True

            End Select

            Call TM1ValErrorString_VB(hUser, vDisconn, sErrorMsg, FIXED_STR_LENGTH)
            HandleError TM1ValErrorCode(hUser, vDisconn), sErrorMsg, sFriendlyMessage

        End If

    Else

        blnConnected = False

    End If


End Sub

Remarks

The attentive reader will have noticed 2 things.

  1. I use a number of global variables (the Boolean variable blnConnected will track whether the user is logged in or not)
  2. auxiliary functions like HandleError need to be given to you

Below you can find the variables: 

'Variables with global scope
Global hServer As Long
Global vServerName As Long

Global hUser As Long
Global vUser As Long

Global vPassword As Long

Global hPool As Long

Global blnConnected As Boolean

Global sErrorMsg As String * FIXED_STR_LENGTH
Global Const FIXED_STR_LENGTH As String = 100

Global Const siErrorOutput As Single = 2

Now, I provide the custom helper functions: 

' Wim Gielis
' https://www.wimgielis.com


'''''
' Auxiliary custom functions
' 22/03/11
'''''


Sub ShowOutput(sMessage As String)


    Select Case siErrorOutput
    Case 1: MsgBox sMessage, vbInformation, "Message" 'a simple Msgbox
    Case 2: Debug.Print sMessage 'the Immediate Window
    End Select


End Sub



Sub HandleError(lErrorCode As Long, _
                sErrorDescription As String, _
                sErrorFriendlyMessage As String)


    Dim sOutput As String

    sOutput = Join(Array("Error number: " & IIf(lErrorCode, lErrorCode, ""), _
                         "Error description: " & Replace(sErrorDescription, Chr(0), ""), "Friendly error message: " & sErrorFriendlyMessage), vbCr)

    Select Case siErrorOutput
    Case 1: MsgBox sOutput, vbCritical, "Error message" 'a simple Msgbox
    Case 2: Debug.Print sOutput 'the Immediate Window
    End Select


End Sub

Have fun experimenting (and do read that API Guide will you) !




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links