CAM 5 Security - Connect to CXMD via API in Macro

[tomago]

We have changed from Security mode 1 - 5
we used to have a macro which would connect to cognos via N_Connect command
Now we are using Security mode 5, this no longer works.
Can someone please help with the API code that is needed to connect automatically via macro?

[Alan Kirk]

We have changed from Security mode 1 - 5
we used to have a macro which would connect to cognos via N_Connect command
Now we are using Security mode 5, this no longer works.
Can someone please help with the API code that is needed to connect automatically via macro?

Sorry, but there isn't any. Not that connects like N_Connect, anyway.

There are two ways of working with the API in VBA. The first is to obtain a user handle by passing the server, user and password information to the SystemServerConnect function. The problem with this is that the resulting user handle can only be used in the VBA API code; it does not connect your Excel client to the server, it only connects the VBA code to the server. So if you wanted to go down this path, you would have to essentially create your own TM1 client using VBA API code which would be a rather painful amount of work to do. You could literally put a couple of years' of work into it.

And then find out that IBM had left out some critical functions from the API.

Or you may come across some undocumented API functions that seem to do what you need but which seem to work erratically and raise a service request. And still be waiting for an answer coming up to 3 months later. I know of what I speak.

The other way is kind of the reverse of that. Instead of obtaining a user handle through the API you borrow one from the Excel client through the undocumented but essential (for Excel VBA API code anyway) TM1_API2HAN function. However obviously the client needs to already be logged in.

In short, the only way to connect the excel client by code is through N_Connect, but note the comment attached to the function in the 10.2 Reference Guide:

Note: The N_CONNECT function is not supported when a server is using Integrated Login or IBM Cognos 8 security for authentication. This function can only connect to a server that is configured to use standard TM1 authentication.

I believe that this is a euphemism for "tough luck, Charlie". (Though to be fair N_Connect wouldn't make sense with integrated login. I have no personal experience of CAM logins, so I can't speak to that.) What I can tell you, though, is that the API's not going to get you around this one.

[tomago]

oh man
I have been going through the documentation on IBM website about creating a VB application to connect to TM1, but that failing and the error isn't very explanatory.
I thought this method might have worked, but not looking so great.
it seems like such a simple task, so I don't know why they made it so difficult.

I have opened a Service Request with IBM, so will see what they say..

[rmackenzie]

It's not clear which API you want to use - there are two that you can use in VBA. One is the classic API and the other is what I call API-lite.

For the latter, you can usually replace this functionality with in-built Perspectives functionality and action buttons that call TI processes that do various jobs for you. It is more efficient to call TI processes on the server-side rather than re-build this functionality in VBA modules on the client-side. If you are heavy into N_CONNECTs, E_PICKs, QULOOPs and SUBDEFINEs then I suggest you need to have a rethink about your approach. I appreciate this is a pain if you are dealing with a lot of legacy code but IBM are showing clear signs of leaving this sort of stuff well on the sidelines per their road-map. They were a lot of fun though

In the case of the former, classic API, there are options to connect via VBA. See here and here which include consideration of Alan's suggestion re TM1_API2HAN. Carefully follow what Mike is saying in the linked posts. But, as everyone is saying, bear in mind that anything you do with the session after you've logged in is still going to require a ton of coding - is it really worth it? I'd suggest converting your application to work with native log-in functionality plus Action Buttons calling carefully coded TI processes is going to be your best long-term shot.