TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post Reply
User avatar
Epttinger
Posts: 3
Joined: Tue Jan 29, 2019 4:40 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2016

TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post by Epttinger » Wed Jan 30, 2019 9:53 am

Hello everybody, after years of passive use of the TM1 forum I have now a concrete request. Thanks in advance for feedback.
I've been trying to get the TM1 API up and running for days without success.

There is a virtual server with TM1 10.2.2 running three TM1 databases (all with IntegratedSecurityMode 1 or 2). One of the databases is the SData sample database.
On the client side I use the Perspectives 10.2.2 (FP7) under Excel 2016. Login e.g. to the database "SData" with user "Admin" and password "apple" is possible without problems in server explorer.
Now I try to execute this VBA macro in this Excel instance. The code is mostly taken from the supplied TM1 samples.
Private Sub DoStart()

Dim sHostName As String
Dim sUserName As String
Dim sPassword As String
Dim sTM1DBName As String

Dim hUserHandle As Long
Dim pPoolHandle As Long
Dim vPassword As Long
Dim vServerName As Long
Dim vUserName As Long
Dim vStringLength As Long
Dim RetVal As String * 75
Dim i As Long
Dim lngCount As Long

Dim vServerHandle As Variant

'
' Each Microsoft Visual Basic application begins by calling TM1APIInitialize,
' TM1SystemAdminServerHostSet, and TM1SystemServerConnect.
'
'sHostName = "192.168.195.153"
sHostName = "tm1-develop"
sTM1DBName = "SData"
sUserName = "Admin"
sPassword = "apple"

TM1APIInitialize
hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, sHostName
pPoolHandle = TM1ValPoolCreate(hUser)

vStringLength = TM1ValIndex(pPoolHandle, 10)

Call TM1SystemServerReload(hUser)
lngCount = TM1SystemServerNof(hUser)
Debug.Print "Number of TM1-Databases: " & lngCount

vUserName = TM1ValString(pPoolHandle, sUserName, vStringLength)
vPassword = TM1ValString(pPoolHandle, sPassword, vStringLength)
vServerName = TM1ValString(pPoolHandle, sTM1DBName, vStringLength)
vServerHandle = TM1SystemServerConnect(pPoolHandle, vServerName, vUserName, vPassword)

If TM1ValType(hUser, vServerHandle) = TM1ValTypeError() Then
Debug.Print "Fehlercode: " & TM1ValErrorCode(hUser, vServerHandle)
End If

'...

End Sub
The function "TM1SystemServerNof" returns the value 0.
Using "TM1SystemServerConnect" I get error code 87.

I tried the short name, long name and IP address of the host.
As far as SSL certification is concerned, I access this file: "C:\Program Files\ibm\cognos\tm1_64\bin\ssl\tm1ca_v2.pem" (due to configuration of TM1 Admin server).

Anybody got an idea?
Thank you in advance & kind regards,
Jörn

jrizk
Posts: 48
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post by jrizk » Thu Jan 31, 2019 12:57 am

Assuming the code is correct then this could be an issue with the change in certificates and using tm1ca_v2.pem vs applixca.pem

This link might help: https://www-01.ibm.com/support/docview. ... wg21993992

The link specifies only changing bin64\ssl- however it may be necessary to change bin\ssl as well.

After the changes to the ssl files point the certificate authority to:

C:\Program Files\ibm\cognos\tm1_64\bin\ssl\applixca.pem
J.Rizk
Tm1 for everyone

User avatar
Epttinger
Posts: 3
Joined: Tue Jan 29, 2019 4:40 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2016

Re: TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post by Epttinger » Mon Feb 04, 2019 4:49 pm

Dear jrizk,

thank you for the link to the IBM Technote. There you will find a lot of talk about the "CDM application server". We don't use CDM, but pure TM1.
Do I understand the article correctly that the changes have to be made on the TM1 server? And on the clients then only the certification authority has to be changed?
Many thanks and best regards!

jrizk
Posts: 48
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post by jrizk » Mon Feb 04, 2019 9:08 pm

While the technote relates to the CDM application server the workaround for connectivity using the API (TM1SystemOpen()) for your Tm1 install is "Switch to V2 certificate".

I would test this out on the server side (in a development environment preferably) - pointing the certificate to:

C:\Program Files\ibm\cognos\tm1_64\bin\ssl\applixca.pem

I'm not sure you need to change the certificate on the client side - which can still point to tm1ca_v2.pem - unless there is a requirement to use your code to connect from the client - you may want to test this out.

You could consider using TM1_API2HAN() instead of TM1SystemOpen() - there is a post on this in the link below:

https://www.tm1forum.com/viewtopic.php?t=2126
J.Rizk
Tm1 for everyone

User avatar
Epttinger
Posts: 3
Joined: Tue Jan 29, 2019 4:40 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2016

Re: TM1 10.2.2. TM1API: TM1SystemServerNof doesn't work

Post by Epttinger » Fri Feb 08, 2019 12:03 pm

Thank you very much for your hints.
I have now first dealt with the function "TM1_API2HAN()", before I take care of the certificates. If I use this function instead of "TM1SystemOpen()", now the query "TM1SystemServerNof " works correctly.
Subsequently I also get a ServerHandle (both for SSO logon and with User & Password), with which I can e.g. read the N elements of a dimension under consideration of the user rights.
If no user is logged on to the data model yet, I can perform a SSO login or a login with user and password via VBA.
To say: everything seems to work as desired.

Many thanks and best regards

Post Reply