TM1 (Classic) API: The TM1_API2HAN function

[Alan Kirk]

I've kept this one to myself to date but it's just passed the point of absurdity. I therefore do encourage anyone who may potentially be affected by it to contact Big Blue and make their own displeasure known.

As many of you who have programmed with the "classic" API know (and others are about to), there is a massively useful but undocumented function in the TM1.xll library called TM1_API2HAN. You have to add the function declaration yourself to the tm1api.bas module to use it.

Code: Select all

Declare Function TM1_API2HAN Lib "tm1.xll" () as Long

This function can only be used when you're using Client or Perspectives within Excel. It returns a user handle. If you don't use that function, you must instead obtain the user handle via the TM1SystemOpen() function, and then obtain a handle to the server by supplying the user name and password (in value capsules) via the TM1SystemServerConnect() function.

The big advantage of using the handle returned by TM1_API2HAN is that it inherits the permissions that the Client/Perspectives session has. You can therefore have VBA code in an Excel workbook or add-in call the API using TM1_API2HAN without needing the user to log in again.

A most sensible and practical solution, one would think. Assuming that one is not IBM engineering, at least.

There is one weakness with TM1_API2HAN. Well, one and a bit. The "one" is that it's not documented so there are no cautions about its pitfalls. The "and a bit" is that there is one huge pitfall in that you must not, ever, call the TM1SystemClose function at the end of your VBA code or your Excel session is only mouse clicks away from a crash since, I assume, that destroys the user handle that Client / Perspectives is using.

Amongst other things, I asked for TM1_API2HAN to be documented.

The response that I got was {clenches teeth, sucks in air...}:

I do not believe it is recommended to use TM1_API2HAN method. It is indicated that a TM1 9.4/9.5 Program should have the following code Template Construct , this is also shown in the examples in the manual :

Which then goes on to document the TM1SystemOpen() function.

I could not help but express a certain amount of displeasure:

Was this written by anyone who has created VBA code for use in the real world?

I *know* how to log in by passing credentials directly in the code, that's not the point. The reason that code is written using TM1_API2HAN is that if someone is already logged in through client/perspectives it's ridiculous to either require them to re-login every time an API function needs to be called, or to violate security by having their login and password stored somewhere on their system so that the code can look it up as needed (assuming that the client hasn't changed their password in the meantime.)

Accordingly it's time to recommend AND support the function with documentation.

And this is the reply:

I am reviewing the comment of the client, but I can mention that Progammatic Access to anything with the SDK or TM1 API is always done via a single , or multiple authenticated session. This may be a difference in how things were done in the past.

So using the SDK or TM1 API you write code that connects and authenticates, then does it thing what needs to be done, and disconnects. You dont leave sessions active , unless you stay within that code application , and you only need to get that handle once then.

{Thump}, {Thump}, {Thump}, my head goes on the well worn spot on my desk.

"You dont leave sessions active". This would be... the client/perspectives session, perhaps? The one from which TM1_API2HAN borrows its handle? We should terminate that every time an API function is called, should we? Or is it possible that the client may get a tad narky about that?

TM1_API2HAN's purpose is to allow auxiliary code for a logged in client to use. To plug the gaps in functionality. The ones that are needed to make the application work the way you want, not the ones that look pretty in a sales presentation. I'm really not entirely sure how much more clearly that can be explained.

If it's a stand alone application, then yes, you have to get the client to log in.

If it's VBA code which is intended to complement the user's existing session, why in the name of Glub would you want them to have to log in again???

OK, breathe, breathe, calm, centred...

I therefore reiterate; if you have code which uses TM1_API2HAN, and you have cause for concern that Iboglix may pull support for it completely, I do encourage you to make your feelings known on this subject.

Oh, and as a sidebar... if anyone is looking for the legendary bordering on mythical .NET API documentaion? You won't find it on a download page. You won't find it (in raw form) in the installation package. You certainly won't find it in a .pdf that you can read through to get a feel for how the .Net API works.

You'll find it as a .chm (a Windows compiled HTML help file) in the \Cognos\TM1\API\DOTNETAPIDOC folder after you install 9.5.

But hey, at least it doesn't have a "Beware of the Leopard" sign on it.