TM1 API - Simple Question - No Answer

[tomdekuyffer]

Hi all,

I'm trying to write some code in VB to simply check if my TM1 Server is still 'up'.
The only thing i'm trying to do is connect to the server, and simply get a value out of a simple cube with two dimensions.

For some reason, after reading trough the API guide, and related topics on this forum, I can't seem to get my code to work.
My returned value always keeps on saying '6' which is an error code I suppose

The connection seems to run alright, but when I start 'querying' the cube with the 'TM1CubeCellValueGet' function,
I can't seem to read the return value correctly.
I think it has something to do with my array-container which doesn't seem to fill up as it should,
but i really don't know how to debug it....

Can anyone take a look at my code, or provide me with some working code to answer a simple query of a cube value via the API ?


info cube = Vessel List
dim 1 = 'vessels'
dim 2 = 'measures vessels'


This is my code :



Private Sub cmdgetcubevalue_Click()

Dim sServerName As String
Dim sUsername As String
Dim sPassword As String
Dim hUserHandle As Long
Dim pPoolHandle As Long
Dim vPassword, vServerName, vUserName As Long
Dim vStringLength As Long
Dim RetVal As String * 75

TM1APIInitialize
hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, "myadminserver"
pPoolHandle = TM1ValPoolCreate(hUser)
vStringLength = TM1ValIndex(pPoolHandle, 10)
vUserName = TM1ValString(pPoolHandle, "Myuser", vStringLength)
vPassword = TM1ValString(pPoolHandle, "", vStringLength)
vServerName = TM1ValString(pPoolHandle, "myserver", vStringLength)
vServerHandle = TM1SystemServerConnect(pPoolHandle, vServerName, vUserName, vPassword)

'Start the cube querying

Dim vessel As String
Dim measure As String
vessel = "BB"
measure = "Volume"
Dim hvessel As Long
Dim vvessel As Long
Dim hmeasure As Long
Dim vmeasure As Long
ReDim elementArray(2) As Long

hvessel = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, "Vessels", 0))
vvessel = TM1ObjectListHandleByNameGet(pPoolHandle, hvessel, TM1DimensionElements(), TM1ValString(pPoolHandle, vesel, 0))
hmeasure = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, "Measures Vessels", 0))
vmeasure = TM1ObjectListHandleByNameGet(pPoolHandle, hmeasure, TM1DimensionElements(), TM1ValString(pPoolHandle, measure, 0))
elementArray(1) = vvessel
elementArray(2) = vmeasure

Dim elementArrayCapsule As Long
elementArrayCapsule = TM1ValArray(pPoolHandle, elementArray, 2)

'set the array values
Call TM1ValArraySet(elementArrayCapsule, elementArray(1), 1)
Call TM1ValArraySet(elementArrayCapsule, elementArray(2), 2)

'create cube handle.
hCubeObject = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerCubes(), TM1ValString(pPoolHandle, "Vessel List", 0))

Dim Value As Variant
Value = TM1CubeCellValueGet(pPoolHandle, hCubeObject, elementArrayCapsule)

' This doesn't work
MsgBox TM1ValType(hUser, Value)

' This doesn't work either

Value2 = TM1ValIndexGet(hUser, Value)

MsgBox (Value2)

'Log Out

vResult = TM1SystemServerDisconnect(pPoolHandle, vServerName)
TM1ValPoolDestroy (pPoolHandle)
TM1SystemClose hUser
TM1APIFinalize
End Sub


I would much appreciate an answer , cause after a day of searching I still end up with a diabolic '6' in my message box AAAAAARCH

Regards,

Tom

[lotsaram]

Hi Tom

I think you might be overcooking things. I use simple Excel VBA to achieve pretty much the same thing.

Code: Select all

Public Function TM1ConnectMsg(TM1_server As String) As String
' Attempt to connect to the server using a ridiculous user name and password
'If already logged on as a valid user returns "N3) Already connected to server"
'If not logged in returns "N6) Client not registered"
'If server not registered with admin host returns "Server not found"
  
  TM1ConnectMsg = Application.Run("N_CONNECT", TM1_server, "nouser", "nopassword")
  
End Function

As long as the function doesn't return "Server not found" then the server is up. Sure it relies on tm1p.xla being loaded in the Excel session but if you only want to check the connection then it does the trick. Just a matter of calling the function from the workbook open event and using windows scheduler to open the workbook periodically.

[tomdekuyffer]

Thx for the info,

But I need to run this code in a stand-alone VB executable, so that a Network administrator can run it without having a client on his system, only the API's dll.
Therefore i cannot use the Application.Run functionality

Thanx for the reply anyway

Any one ?

[Martin Ryan]

Can't help with the api, but what about getting the network guy to fire up TM1 Top?

Martin

[Tom De Kuyffer]

Because the network guy wants his own program, with his own code,
so he can add or delete functionality when end-users need it ....

TM1Top is only my last resort.

Isn't there anyone out there who programmed in the API ?

I'm still looking for extra info on it, response : ZERO

Luckily the TM1 support is now in hands of the IBM guys, and their side is 'Top of the Bill'.

A search of their TM1 support site over the term API gave me 0.00000000000 hits in 1.2 seconds

Any one out there ?

Thanx for your time !!!

[mykill]

I did something in the past.

I checked the number of cubes on the server.
If I got an answer the server is still up.

I had to do the check asynchronous (ActiveX.exe is your friend), because if the server is hanging you are not able to react.

This is my check:

Code: Select all

    vhCubeCount = TM1ObjectListCountGet(hPool, hServer, TM1ServerCubes())
    If TM1ValIndexGet(hUser, vhCubeCount) > 0 Then
    'all fine
    else
    'something is wrong
    endif

Michael

[Jeroen Eynikel]

Michael,

is it possible to provide more details on what exactly you did? I would be interested in this solution as we are looking for a way to monitor the TM1 server as well, primarily to distinguish between a server that hangs and a server that is 'locked' because of for instance dataloads. Your solution sounds like exactly what I would be looking for.

I am not very literate in the API, so please be 'extensive' in the description of what you did exactly.

Thanks a lot,

Jeroen

[Steve Rowe]

Again I would have thought TM1 Top is what you need.

Even if you have been coding VBA for a long time the API is a quite different animal and you are going to need to take some time out to play with it. IMO I don't think it's a very good idea to just grab code from somewhere like this if you don't know enough to maintain it yourself.

The samples of API that are provided with the install go someway to provide working code to log into the server and so on, they can be found here

C:\Program Files\Cognos\TM1\API\TM1API\SampleCode\vb

This should provide a good start for figuring it out.
HTH somewhat

[Jeroen Eynikel]

Hi Steve,

TM1 Top is a great tool but it requires someone to actually monitor the output. What we are looking for is an automated mechanism that can be interpreted automatically. The goal is to be more proactive; ie instead of using TM1 Top to check whether a server is hanging when users complain that they can't do this or that, we want to have automatic monitoring.

So to say: if we detect that the server is hanging at say 2 AM we can at least restart it (possibly automatically) instead of having to wait for users to come in and complain.

It would be pretty good if there was some sort of explanation box for end users as well. It is very difficult for them to know exactly why the server is (appears to be) hanging. They do not have any indication about whether this is due to a dataload or because something is really wrong. I suggested this earlier but instead of just putting users in a wait state when trying to retrieve locked values it should be possible to at least give them a pop up explaining that process X currently has the cells locked and give them the option to:

- either wait for it to finish and release the lock (possibly with an automatic warning for them once this has happened)
- go do something else.

Codewise these seem like very minor extensions to the code and it would help a lot. TM1 Top is great but it is not an end-user tool.

[Gregor Koch]

Hi

Is anyone willing to share the API calls (and how they work) for TM1Top?

Cheers

[lotsaram]

Why not run tm1top and use a log file monitoring utility to scan the top log for "Wait." If a thread has wait status for more than x log intervals then the server is hanging (that is define a definition of a "hang" as per your requirements). Then monitoring utility emails/texts administrator to remote on and see what's happening.

This might be a bit round about but I see no reason why it shouldn't work.