Xcelerator VBA API CAM Server Connect
-
- 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
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)
- 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
Tm1 for everyone
-
- 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
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.
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.
- 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
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:
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
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.
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!
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!
-
- 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
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?
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
ACG TM1 Consulting
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Xcelerator VBA API CAM Server Connect
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: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.
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.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.
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
-
- 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
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.
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.
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
Matthew Berson
ACG TM1 Consulting
ACG TM1 Consulting
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Xcelerator VBA API CAM Server Connect
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.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.
Robin Mackenzie
-
- 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
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!!
-
- 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
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
http://www-01.ibm.com/support/docview.w ... wg21959177
Matthew Berson
ACG TM1 Consulting
ACG TM1 Consulting
-
- 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
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
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
-
- 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
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.
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.