Using the API to generate a user list.

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 5833
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia

Using the API to generate a user list.

Post by Alan Kirk » Sat Mar 06, 2010 11:37 pm

In this thread a request was made for a file which generates a list of users. The file that was provided was driven from a slice of the }ClientProperties cube. Unfortunately that cube doesn't always tell the truth about who is connected and who isn't (particularly after a server crash), and in some 9.1 versions it doesn't work at all. In 9.1.4 and later (as Lotasram noted in the aforementioned thread) you need to set a server config parameter to have the user data written to the cube periodically.

The attached workbook uses the TM1 API to generate a user list in part because the API should give accurate results regardless of version (fingers crossed).

A second reason for using the API is that the workbook can be used as a primer for those who want to take their life into their hands and work with the API. It shows how to gather a list of servers and a list of clients, and iterate through them. The same principles can be applied to iterate through other lists of TM1 objects though, as has been observed elsewhere, the API should be your last resort for doing that. It's both easier and safer to loop through control dimensions like }Clients using the TM1 workbook and macro functions. However there are some values that you can only get via the API like an object's memory usage; it just depends on how important those things are to you.

The attached workbook uses the undocumented TM1_API2HAN function to "piggy-back" off your existing login to TM1, though it can be modified to use the "stand-alone" connection methods illustrated in the TM1 API manuals should you wish to do that.

The file comes with the usual disclaimers; you use it at your own risk, and the code's author will have no liability if it crashes your client session, crashes your server, creates a universe-ending black hole, or creates or assists in creating any event between or outside those specified. All rights are reserved though, meaning that you can modify and use it in your own applications but you can't sell it, even if you find someone who is insane enough to want to buy it.

Important Warning

Anyone considering implementing TM1 API code, particularly in a production environment, should read Mike Cowie's post in this thread first.

Edit: Be careful what you name the file when you save it; a name like UserList(1).xls (which it may have if you run it straight from here) causes a problem with one of the Excel range names and the main form errors out. Stick to something like UserList.xls.
(356.5 KiB) Downloaded 358 times
"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.

Post Reply