TM1 API: Connecting and disconnecting a user


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 '
''''' ' 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 '
''''' ' 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


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 '
''''' ' 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) !


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links