TM1 TOP API results in excel

[TM1Bug]

I am using the below code to fetch the current state of an instance, but want the results to be populated in excel sheet, then can put the code in a loop to get the real time state. As soon as lTemp = TM1TopConnect(lPool, hServer) is executed, TM1 crashes....can you please advise of what I am doing worng here or if there is any other way to get the current status of an instance in Excel through API ?

Public Declare Function TM1TopConnect Lib "tm1api.dll" Alias "_TM1TopConnect@8" (ByVal hPool As Long, ByVal sServerName As Long) As Long
Public Declare Function TM1TopGetCurrentState Lib "tm1api.dll" Alias "_TM1TopGetCurrentState@8" (ByVal hPool As Long, ByVal hServer As Long) As Long



Sub TEST()
Dim hUser As Long
Dim hServer As Long
Dim lPool As Long
Dim lResult As Long
Dim lTemp As Long
Dim lParams As Long
Dim sServer As String
sServer = "GLU-1T"
hUser = getUserHandle()
hServer = getServerHandle(hUser, sServer)
lPool = TM1ValPoolCreate(hUser)

lTemp = TM1TopConnect(lPool, hServer)

End Sub

[Mike Cowie]

Hi TM1Bug:

First, I'm going to warn you that retrieving TM1TOP statistics repeatedly in a loop without pause is probably not a good idea, especially using Excel and Excel VBA to do so. TM1TOP information isn't ever really "real-time", as was discussed in this thread; at best you'd just be getting very frequent snapshots of what is going on according to TM1TOP functions. Also, hammering the TM1 Server with TM1TOP requests in that manner could have unintended consequences - do I think it's a high risk, probably not, but I'm pretty sure it wasn't designed with that usage in mind. I can understand how nice it might be to drop TM1TOP statistics into Excel and that's very reasonable, but if you're hoping to turn an Excel spreadsheet into a realtime TM1 Server monitor I think you may be disappointed on a number of fronts.

On to your issue - you've left out some details in your code (like what getUserHandle and getServerHandle do), so I'm making assumptions that those methods aren't the culprit. That being said, I believe your issue could be one of the following:

• Your function declaration for TM1TOPConnect is incorrect. I'm not sure where you got these declarations, but since these functions aren't yet officially published/documented by IBM AFAIK it's easy to misunderstand a function argument or possibly get the signature wrong.
• You're trying to pass a handle to the TM1 Server object to TM1TOPConnect; I believe TM1TopConnect requires a handle to the server name, if I'm reading the signature correctly (the "s" prefix on that argument).

My guess is that it's the second item, but without knowing what "getServerHandle" does I can't say for sure. Remember that the TM1TOP doesn't require user ID and password unless you need to Verify as an admin in order to do things like cancel operations. Therefore, it would make sense that it's looking for just the name of a server.

As has been discussed in other forum threads on the TM1 API, TM1 API functions are notoriously unforgiving of passing in wrong TM1 function parameter "types". Unforgiving meaning host application crashes, and sometimes TM1 Server crashes, so it always pays to tread carefully if you must use the TM1 API for something.

Hope this helps!

Regards,
Mike