Excel VBA able to connect now via CAM Security to TM1Data

Post Reply
TM1Raider
Posts: 1
Joined: Fri Sep 28, 2012 12:23 pm
OLAP Product: TM1 / PowerPlay
Version: All versions
Excel Version: Minimum 2007

Excel VBA able to connect now via CAM Security to TM1Data

Post by TM1Raider »

Hi All,

Contrary to some members belief , IBM Cognos TM1 Development together with TM1 API Support is listening to your requests for Enhancements to be able to Automatically Connect to a TM1 Server under CAM Security in Cognos BA.
A new Technote was published in IBM BA Support that explains what to add and how to do it with a new Function for Excel VBA called :

N_CONNECT_CAM

See here the Technote :

TN 1959177 - TM1 API - Excel VBA Coding and Library to automatically Connect to a TM1 Server with IBM Cognos CAM Security Mode 4 and 5
http://www.ibm.com/support/docview.wss?uid=swg21959177

courtesy of IBM Cognos SDK Development Support. TJ
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Excel VBA able to connect now via CAM Security to TM1Dat

Post by lotsaram »

This is without a doubt the best and most surprising news from IBM over the last month.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Excel VBA able to connect now via CAM Security to TM1Dat

Post by Alan Kirk »

TM1Raider wrote:Contrary to some members belief , IBM Cognos TM1 Development together with TM1 API Support is listening to your requests for Enhancements
"Some" implies a minority. Given the number of widely requested things that have been hanging out there for literally years (SubIx, AsciiAppend, interactive TI debugging, a demarcation between user views and data sources, etc, etc) and which are, at best, somewhere "out there on the horizon, maybe, perhaps", I'm not sure that "some" is entirely accurate.

I did just try to get a view of where all of the formally submitted RFE requests are at, but every link in the How to submit an enhancement request for TM1 using RFE thread lands in the same place:
Error 500: java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Hex
So, thinking that this was down to (yet another) web site restructure I logged into DeveloperWorks instead. And was told that I was up for a FREEE! IBM Bluemix trial, whether I wanted it or not. So I bypassed that, and have now just received an e-mail asking me to validate my e-mail address for my Bluemix trial that I have neither the time nor inclination to do right now.

So after searching for "TM1 RFE" I found:
The RFE Community (Request for Enhancement) gives users the opportunity to collaborate directly with the IBM product development teams and other product users.

To add an enhancement for the TM1 SDK, please navigate to the Submit tab. There, please make the following selections:
Of course, there is no "Summit" tab on the page, and why I would need to "navigate to" places instead of being given a direct link to them is a question we can but ponder, and often do when trying to download software updates, for example.

There is however a link under the text "RFE Community" which takes me to, you guessed it...
Error 500: java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Hex
So I'll have to take your word for it about the extent to which the requests are being listened to.

All that having been said, however:
TM1Raider wrote:A new Technote was published in IBM BA Support that explains what to add and how to do it with a new Function for Excel VBA called :

N_CONNECT_CAM

See here the Technote :

TN 1959177 - TM1 API - Excel VBA Coding and Library to automatically Connect to a TM1 Server with IBM Cognos CAM Security Mode 4 and 5
http://www.ibm.com/support/docview.wss?uid=swg21959177

courtesy of IBM Cognos SDK Development Support. TJ
Very well done; it doesn't affect me personally but I know that it will be of benefit to quite a few people. It's definitely a step in the right direction. Keep that up and you may yet change some opinions.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Excel VBA able to connect now via CAM Security to TM1Dat

Post by Andy Key »

Good to see that the documentation is up to the usual IBM standard:
IBM Technote wrote:Included is Function N_CONNECT_CAM which allows this connection to be made seamlessly with IBM Cognos BA security software, by getting the credential of the User in place in the Excel VBA Application.

This function N_CONNECT_CAM is to be added to all Excel Workbook applications.
Within that function, API calls will be executed to establish the connection to the TM1 Server.
Parameters for this function would typically be the namespace, user name and password.
Function Signature :
Public Sub N_CONNECT_CAM( AdminHost As String, ServerName As String, NameSpace As String, UserName As String, Password As String)
(My emphasis)

The first bold section says that the function is going to use the credentials of the User in place, i.e. the user who is running Excel - and this is correct, this is what the function is going to do.

However, the second bold section says that the parameters would typically be Namespace, User Name and Password. But this contradicts the previous statement and the whole point of this function is to avoid having to specify a user name and password.

The third bold section shows an example Public Sub declaration with more parameters than those specified in the second section, but still including UserName and Password.

The line below shows the actual Private Sub declaration from the excel workbook in the provided zip file. Which does, at least, tie up with the first section.
IBM Example VBA Code wrote:Private Sub N_CONNECT_CAM(sAdminHost As String, sServerName As String)
There's nothing like attention to detail. And this is nothing like...etc. etc.
Andy Key
mberson
Posts: 30
Joined: Tue Jun 07, 2011 8:30 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Excel VBA able to connect now via CAM Security to TM1Data

Post by mberson »

I've got this working in 3 different environments with Excel 2010, 32-bit. The only rig I needed was to add a line in my VBA code to update the Excel TM1 Ribbon:

Code: Select all

Run("Main.Excel_TM1ServerConnect", sServerName)
It's working quite nicely now and don't really have any complaints.

I'm now trying to get this working with 64-bit Excel 2013; I'm assuming the published DLL is a 32-bit DLL as it seems that Excel is not recognizing the library. Anyone other have this working with 64-bit Excel?
Matthew Berson
ACG TM1 Consulting
Post Reply