IBM Support

TM1 API - Excel VBA Coding and Library to automatically Connect to a TM1 Server with IBM Cognos CAM Security Mode 4 and 5

Question & Answer


Question

How can VBA code in Excel automatically connect to a TM1 Server with CAM security ?

Cause

It is not possible to use N_Connect normally used with Security Mode 1 through 3 , to automatically connect with Excel VBA to the TM1 Data Server when this server is using IBM Cognos CAM security. Configured with Security Mode 4 and 5 as descibed in the TM1 manuals.

Answer

Until this Technote and Application , no solution exists to make this connection from Excel VBA Applications seamless , as in Single Sign On (SSO) for the user.

The solution for all previous Excel TM1 Perspectives / VBA installs :

TM1 Perspectives (the Ribbon) in Excel , needs to connect to the "Secured with CAM" TM1 DataServer.

When the user initiates this connection within Excel , the prompt page of the IBM Cognos BI Server would b received , as it should with Security Mode 4 and 5.

Then in addition , the VBA code in an Excel Application within the Spread Sheet , also needs to have the connection code in it for TM1 Data Server to make the VBA Code connect using the C/C++ Function Methods to the IBM Cognos BI server.

Following Functions exist for this connection.

- TM1SystemServerConnectWithCAMNamespace()

- TM1SystemServerConnectWithCAMPassport()

The implementation of these Functions as Wrapper methods to VBA is considered unsupported by TM1 Development , as these can have problems and issues for the VBA Application in Excel.

The Perspectives Application also a VBA application , and the TM1 VBA API, have not been modified to support Mode 4 and 5 directly.

The N_Connect function to get the User credential from Perspectives VBA Application to VBA under Security Mode 1 through 3 , does not have the functionality for getting a CAM credential.

This latest Technote solution is resolving the CAM Credential problem in VBA Excel :

Find attached an Excel Application sample, and the special DLL library n_connect_cam_bridge.dll

This Bridge technology is enabling Customers who rely on Excel Spread Sheet Applications with TM1 API VBA code , to automatically connect within their Excel VBA application to a TM1 CAM secured Server in security Mode 4 and 5.

This application is designed to operate in a IBM Cognos TM1 10.2.2 Client environment.

NOTE : this does not replace any needed connection of the TM1 Perspectives Ribbon, which may require to have been established separately.

For a Client who needs to work in Excel with Perspective Functions to the TM1 Data Server, it would be required to :

- Open Excel first , and connect to the TM1 Data Server with Perspectives. You be prompted for the logon to the IBM Cognos BA server.

- Then open the Spreadsheet with VBA application code.

Steps to implement this for your TM1 Client installation(s) for all the users who need this SSO function are :

- Add the DLL library to the <TM1_Client>/bin folder of the TM1 Data Server.

- Excel Workbooks with the new function added, will need to be able to locate the relevant TM1 API libraries.


This can be achieved by placing these workbooks in the same location as the API libraries - the <TM1_CLient>/bin folder , or by adding the location of the libraries to the System PATH= environment variable.

- Open Excel with TM1 Perspectives and connect as usual to the TM1 Data Server with the CAM credentials to IBM Cognos BI.

If a IBM Cognos BI Server has been configured to use Windows Integrated Authentication , or has some other Authentication Provider for a seamless SSO connection , then you shoud be automatically connected without a Prompt for Logon from Cognos BI to TM1 Perspectives.

- Use in this Technote included Excel Sheet with the VBA code application and Function Methods to test out your installation with the TM1 Client / TM1 Data Server. You should then not be prompted for any Credentials, as the VBA code will autoconnect to the TM1 Data Server.

- Update all your existing Excel Applications to include the Sample VBA application code for Security Mode 4 and 5.

The VBA Application relies on the Function N_CONNECT_CAM() which allows this connection to be made seamlessly with IBM Cognos BA security software , by getting the internal TM1 Data Server credential of the logged in User who runs this Application in Excel , transferred and in place in the Excel VBA code. Application.



This function N_CONNECT_CAM() is to be added to all the Excel Workbook applications needing this technology.
Within that function , the API calls will be executed to establish the connection to the TM1 Data Server.

Function Signature of N_CONNECT_ CAM :
Private Sub N_CONNECT_CAM(sAdminHost As String, sServerName As String)

This IBM Cognos BA TM1 Authentication technology will be included in a Future Release for the TM1 Product.

========



Additional Clarification on Automation considerations of Excel TM1 VBA Applications

Frequently the Business Applications for TM1 include Excel Applications with VBA that update the TM1 Data Server content , with or without the use of Perspectives functions.
With this Technote it is also possible to have Scheduled VBA applications perform this same connection automatically.

Here are the conditions that would be valid for such batch or schedule implementations :

1. The Scheduler need to run Excel Automation with Microsoft Domain Credentials.
2. Excel VBA tries to connect to TM1 Data Server
3. TM1 Data Server responds back with information on the CAM Security Environment for that Domain Credential.
4. Excel VBA gets a token from the OS, and directs authentication request to the TM1 Data Server, who sents an Authentication request with CAM Security to the Cognos BI Server
5. CAM Service responds with a Credential Passport to TM1 Data Sever / Excel VBA
6. Excel VBA uses N_CONNECT_CAM to get the Credential Passport to connect to TM1 in VBA code.

For Excel VBA applications which need to include Functions that are part of the TM1 Perspectives Ribbon , then it would be required to establish a connection from TM1 Perspectives to the CAM secured TM1 Data Server First !
This ensures that the credential for the user is in TM1 Data Server and can be read by Excel Perspectives / VBA code with N_CONNECT_CAM.

For VBA logon requests that need to include , NamespaceID, UserID, Password based on the Configuraton of Authentication in the Cognos BI Server , they would require additional VBA application code in the Sheet that is able to make that connection.

The above mentioned Wrapper Functions of TM1SystemServerConnectXxxxx() could be used to implement this , together with the standard N_CONNECT , or the given N_CONNECT_CAM Function within Excel VBA.

The downside using Excel and TM1 Perspectives is that this does require clear text credentials of NamespaceID, UserID and Password , or Domain Credentials in the VBA Excel application code. To prevent this for the Users, it is recommended to use a security protected TM1Connect.properties file, that is read by VBA code for these parameters.

Solutions to implement encryption of these strings would also be possible in Excel VBA, but is beyond the scope of support and use of TM1 Product.

A Microsoft Domain Credential and seamless SSO can only be used if the IBM Cognos BI Server is set up to handle entirely the Windows Integrated Authentication ( WIA ) with the ADS Authentication Provider.

For clarification see the references to DeveloperWorks documents for IBM Cognos BI Authentication

NOTE :

IBM Cognos BA SDK / TM1 Development Support is responsible for this extension in this Technote.

For questions or concerns, please do log a PMR / Case under the IBM Cognos BA 10.2.2 - TM1 API product.

This approach is NOT valid for Planning Analytics as it never was tested with Planning Analytics.

[{"Product":{"code":"SS9RXT","label":"Cognos TM1"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"TM1 API","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2;10.2.2","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SS9RXT","label":"Cognos TM1"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"TM1 Perspectives","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2;10.2.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
24 February 2020

UID

swg21959177