Xcelerator VBA API CAM Server Connect

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

Xcelerator VBA API CAM Server Connect

Post by jrizk »

Hi. For those who want to use TM1SystemServerConnect with Xcelerator below is an example of the code required which is used in place of TM1SystemServerConnect

- Declare Function -
Declare Function TM1SystemServerConnectWithCAMNamespace Lib "tm1api.dll" (ByVal hPool As Long, ByVal vServerName As Long, ByVal vCAMArr As Long) As Long

- Code -
Dim hCAMNameSpace As Long
Dim hArray As Long
Dim hArrayPool As Long
Dim lArray() As Long
Dim NoParameters As Integer
Dim nReturnCode As Long

hCAMNameSpace = TM1ValString(hPool, "CognosExpress", 0)

hArrayPool = TM1ValPoolCreate(hUser)
NoParameters = 3
ReDim lArray(NoParameters)

hArray = TM1ValArray(hArrayPool, lArray(), NoParameters)
TM1ValArraySet hArray, hCAMNameSpace, 1
TM1ValArraySet hArray, hClientName, 2
TM1ValArraySet hArray, hClientPassword, 3

hserver = TM1SystemServerConnectWithCAMNamespace(hPool, hServerName, hArray)
J.Rizk
Tm1 for everyone
AlastairFM
Posts: 10
Joined: Mon Feb 28, 2011 12:13 pm
OLAP Product: TM1 / Cognos Express
Version: PAL 2.0.9.1
Excel Version: 2016

Re: Xcelerator VBA API CAM Server Connect

Post by AlastairFM »

Thank you for posting this jrizk. I am now looking at this in order to convert VBA applications to work under Cognos Express.

Can you explain the function parameters for this example ?

[hPool] - Is this the value pool that is normally created for a user (such as the return from 'TM1SystemOpen') ?
[vServerName] - I am presuming this is the value capsule of the string for the server (no namespace prefix or anything). So for Cognos Express this would be 'cxmd'
[vCAMArr] - I am afraid I can't guess what this would be.

How do you call this function ? Once called are you fully logged into the server as far as Cognos Express is concerned ?

Do you still need to call 'TM1APIInitialize' ? I would have thought so.
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: Xcelerator VBA API CAM Server Connect

Post by Mike Cowie »

Hi AlastairFM:

Sorry, but a bit of a rushed response here. I think the sample code below pretty much covers most of what you need, though there are a few missing links...

hPool is a value pool created using TM1ValPoolCreate. It is not what you get from TM1SystemOpen (that gives the TM1 API User/Session handle, often referred to as hUser in the docs)...

However (and this could be one thing you're missing), if you're working in Excel VBA with the TM1 API then you'll want to steer clear of TM1APIInitialize, TM1SystemOpen, TM1SystemClose, TM1APIFinalize functions unless you want to interfere with the TM1 Xcelerator client and ultimately cause Excel to crash (best case) or your TM1/Xcelerator Server to crash (worst case). This thread offers another approach to getting the session handle that the TM1/Xcelerator client is using in Excel VBA, which is the preferred approach in this situation:
http://www.tm1forum.com/viewtopic.php?f=3&t=4788

Moving onto other questions...

You are correct about vServerName and what it means.

The third vCAMArr param as you may see below is a handle to a TM1 API array (created using TM1ValArray, and values in that array set with TM1ValArraySet) of the following parameters:
  • hCamNamespace in this example = Handle to a string representing the CAM Namespace (CognosExpress in this case - and it is unfortunately case-sensitive). This is created using TM1ValString.
  • hClientName in this example = Handle to a string representing the user name you're trying to login as. Create this using TM1ValString.
  • hClientPassword in this example = Handle to an encrypted string represeng the user's password. Create this using TM1ValStringEncrypt.
The above 3 handles to strings go into the array and it's the handle to the array that gets passed to the CAM login API function. Basically this CAM connect function bundles 3 parameters into an array rather than listing them as 3 separate parameters, for some reason.

Sorry again for the hurried response and any errors that may result ;) - I'm guessing from some of your questions that you may not be super-familiar with the TM1 API, so be sure to tread carefully and don't be afraid to search the forums for other general TM1 API advice and suggestions. Also, if you'd like to see N_CONNECT (or something similar) work with CAM/Cognos Express logins please tell IBM either via submitting service requests and/or contacting your IBM rep. I'm not saying they'll implement that sort of change immediately, but if multiple people aren't asking (customers usually hold more weight than partners) it probably won't be assigned much priority at all, even though it's a royal PITA if you want to use VBA to log someone in with Cognos Express (or when using CAM security with TM1) and have to instead resort to using the TM1 API.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
mberson
Posts: 30
Joined: Tue Jun 07, 2011 8:30 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Xcelerator VBA API CAM Server Connect

Post by mberson »

I realize this is an old thread, but I'm using some of this code to connect to TM1 via CAM.

Our BI environment is setup to use the Active Directory and Single Sign-on, so in a normal logon, the user is never challenged for credentials. However, thus far, I have not found a way to use the TM1SystemServerConnectWithCAMNamespace function with a null or blank password. Additionally, I haven't found a way for VBA to pull the password for the current user (which I suppose is a good thing from a security perspective).

Has anyone had success with this when using BI configured with SSO and AD?
Matthew Berson
ACG TM1 Consulting
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Xcelerator VBA API CAM Server Connect

Post by rmackenzie »

mberson wrote:Our BI environment is setup to use the Active Directory and Single Sign-on, so in a normal logon, the user is never challenged for credentials. However, thus far, I have not found a way to use the TM1SystemServerConnectWithCAMNamespace function with a null or blank password.
You may have a bit of a struggle on with this one - there is another method TM1SystemServerConnectWithCAMPassport that doesn't require credentials, but does require a CAM passport. You get the passport by making a request to the BI runtime - if you've not installed this before, I guess you need to go through the installation and configuration of the component before you try and code against it otherwise you may be somewhat in the dark. The theory is explained here anyhow. Pertinently, it says:
IBM wrote:As of IBM Cognos BI version 10, authentication functionality is centrally provided by the internal CAM_AsyncAAService which is only reachable indirectly through the Content Manager Service (CMS). Consequently all IBM Cognos BI services will employ the CMS to verify session authentication or to trigger authentication for unauthenticated sessions. The CMS is implemented as a Java servlet and is the service interface to the IBM Cognos Content Manager (CM), the central component with exclusive access to IBM Cognos BI's application repository known as the Content Store (CS).
As soon as a request is handled by a Dispatcher, it will first check the authentication status of the current session the received request belongs to. If a session doesn't exist yet, a new session ID will be implicitly created and the request automatically becomes the first request of that new session. If the session is not yet authenticated, the Dispatcher will employ the CAM_AsyncAAService to run the authentication process and pass the request off to it. The CAM_AsyncAAService service is a service interface to the Cognos Access Manager (CAM) sub-component which handles authentication and authorization (AA). Other CAM components that are not exposed through the CAM_AsyncAAService service will handle tasks such as encryption (CAM-CRP). The AA sub-component of CAM will then employ authentication providers (AP), hosted in separate processes, to attach to external authentication sources to drive the process of session authentication. The details of this process will be covered later in the Authentication Process section but there are still some additional concepts that need to be introduced.
So, basically the call to the BI runtime should look after all the talking to AD or whatever you're authentication provider is. However, the TM1 API function doesn't do this for you, you need to faff about getting it first and then simply end up with the Passport as the argument to the connection method. GIYF but I tend to suspect that you need to shell out to some Java based exe to do the work for you and the methods aren't in com.applix.tm1 just as they aren't in the VB header either.

After that, I can only see this C example; conversion to VBA is left as an exercise for interested readers - my only conjecture is that it mighn't matter in VBA-land if the Passport string was passed ByRef or ByVal:

Code: Select all

TM1V vArray[1];
vArray[0] = TM1ValStringW(hPool, (TM1_UTF16_T*)pszPassport, 0);      
TM1V vCAMArgArr = TM1ValArray(hPool, vArray, 1);
TM1V vTmpServer = TM1SystemServerConnectWithCAMPassport(hPool, voServerName, vCAMArgArr);
Robin Mackenzie
mberson
Posts: 30
Joined: Tue Jun 07, 2011 8:30 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Xcelerator VBA API CAM Server Connect

Post by mberson »

Thanks for the reply. I've been working hand-in-hand with some developers at IBM, but to no avail. The code required in VBA is not exported, so it's not available to declare. They're still working with me on it, so more to come...hopefully.

However, I did discover a somewhat clugey way to do it using Net_Conn and SendKeys. So far it's working smoothly.

Code: Select all

Sub CamLogin()
' Connect using Net Conn
' Use SendKeys to find the instance and press enter
vInstanceName = "MyTM1Instance"
SendKeys "+" & vInstanceName & "~", False
Application.Run "Net_Conn"

End Sub
You could take it the next level and disable screen painting while this is running to prevent the dispaly of the Net_Conn dialog that pops up for a split second. But that adds more complexity to it. If anyone is interested, let me know and I'll post it.
Matthew Berson
ACG TM1 Consulting
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Xcelerator VBA API CAM Server Connect

Post by rmackenzie »

mberson wrote:Thanks for the reply. I've been working hand-in-hand with some developers at IBM, but to no avail. The code required in VBA is not exported, so it's not available to declare. They're still working with me on it, so more to come...hopefully.
I'd be interested to hear what they come up with. I assume that the bit of (Java) code to get the passport is sitting around in the client install _somewhere_ otherwise Perspectives wouldn't be able to call on it.
Robin Mackenzie
diegopro
Posts: 2
Joined: Thu May 23, 2013 12:00 pm
OLAP Product: TM1
Version: 10.1
Excel Version: Excel 2010

Re: Xcelerator VBA API CAM Server Connect

Post by diegopro »

mberson wrote: You could take it the next level and disable screen painting while this is running to prevent the dispaly of the Net_Conn dialog that pops up for a split second. But that adds more complexity to it. If anyone is interested, let me know and I'll post it.

Thanks for the explanation of the login screen it Works for me too , but the issue now is what you are explaining about the second screen that pops up when it is a sucessfull login. did you find a way to avoid the screen of
"Connected to Server: XXXXXXXXX as Client: xxxxxxxxxxxxxx" and a OK button that stops the execution of the macros code

could you share the code if you have something to avoid that window modal pop up?

thanks!! :D
mberson
Posts: 30
Joined: Tue Jun 07, 2011 8:30 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Xcelerator VBA API CAM Server Connect

Post by mberson »

FYI - IBM published a new DLL and sample VBA code to achieve to simulate CAM "Single Sign-on" through VBA. Just need to pass the admin host and instance name and it takes care of the rest:
http://www-01.ibm.com/support/docview.w ... wg21959177
Matthew Berson
ACG TM1 Consulting
slu
Posts: 22
Joined: Sun Jan 22, 2012 8:53 pm
OLAP Product: TM1
Version: Planning Analytics 2.0
Excel Version: 2016

Re: Xcelerator VBA API CAM Server Connect

Post by slu »

Hi Matthew,

I saw your posting, I had a hard to time to get the N_CONNECT_CAM () working for me in planning analytics 2.0. I am interested in knowing how you stop the popup box and stop the confirmation after sending the key. Hope this post reaches you. Thank you
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Xcelerator VBA API CAM Server Connect

Post by kavitha2002 »

Hi Slu,

Currently I am trying to implement N_CONNECT_CAM() in Tm1 cam Security SSO enabled Environment. I am using Vb.net application and how can I implement in my application. Do i need to convert the excel VBA code into vb.net code??

Any suggestion kindly share.
Post Reply