Let's step out of the comfort zone of the IBM Cognos TM1 environments for a second.
TM1, data warehouses and managing the ETL
Imagine that you have a data warehouse that is the dedicated data source for both your IBM Cognos TM1 and BI environments. You load and transform some data from the DW to TM1, but you also need to write data back to the DW, e.g. for reporting with Cognos BI.
You could, perhaps, succeed in scheduling the DW ETL and TM1 data export processes separately in each system to run nicely together. Sometimes you might still need to manually update the DW, trigger the TM1 import or export processes at a specific time as one of the ETL steps. Besides, wouldn't it be great to have control of the whole ETL process and have all the steps executed in the right sequence?
There's of course at least the two options to control of the process: You could execute some packages from TM1 (just google "tm1-run-sql-server-job-from-ti-process"), or run the TM1 processes from the data warehouse side, that usually sports some sort of ETL tools. You may already have some end user interfaces in both systems, like SQL Server Agent to run the ETL, and TM1 Architect or other clients to run the TI processes, and a few end users who are getting gray hairs for having to use lots of different tools to accomplish one simple thing.
People running a data warehouse might not be thrilled about the idea of handing over the control of the ETL to a TM1 process, and neither would I, so let's take a look at the other alternative.
Running TI scripts from the command line with authentication
Let's finally get to the point, and focus on actually executing the TI processes from outside the TM1 environment, as promised in the title of the article.
Since version 9.5 Hotfix 1, Cognos TM1 has offered a command line interface tool called TM1RunTI. The tool is documented
here and
there, but the command syntax is quite strict and you're likely to see a couple of
SystemServerNotFound errors before you get the command line right.
You can execute the command line tool from almost anywhere, meaning ETL tools, scheduling agents, PHP scripts, you name it. Here's just a couple of ideas what to do with it. My examples below are unfortunately Windows server specific.
Safety first - encrypting the passwords
First of all, you have to choose the user account used to run the processes with. To be able to securely run the processes without saving any plaintext passwords in your ETL scripts, you can encrypt the passwords with another TM1 tool, TM1Crypt.
Run the TM1Crypt utility (located in your <TM1 root>\bin64) from the command prompt using the following syntax,
password being the plaintext password for the chosen user account:
tm1crypt.exe -pwd password [-keyfile filename] [-outfile filename]
The output should be two files:
tm1key.dat - contains the key used to encrypt/decrypt the password
tm1cipher.dat - contains the encrypted password
You can then run TM1RunTI from the command prompt with the newly created password files.
Running the TM1RunTI tool
Basically, to run a process
Process_name on the TM1 server, you would run:
tm1runti -process "Process_name" -adminhost machine_name -server "server name" -user username -CAMNamespace namespaceid -pwd password
or, with the encrypted password files that we created above:
tm1runti -process "Process_name" -adminhost machine_name -server "server name" -user username -CAMNamespace namespaceid -passwordfile tm1cipher.dat -passwordkeyfile tm1key.dat
.. where
adminhost,
server and
user are just what they say, and the optional
CAMNamespace is the CAM authentication realm, if you are logging in as a CAM user (Windows AD etc).
Instead of specifying all the parameters on the command line, you can also specify a default configuration file, and use the command line parameters only to override the default values. More of the configuration files can be found in the
official documentation.
Running it remotely, away from the TM1 server
The above example of tm1runti.exe was still executing on the TM1 server. No worries - you can grab the TM1RunTI executable and run it from virtually any server, without the need to install a complete TM1 environment.
The required components, as tested in Cognos Express 10.2.1, seem to be all found in <TM1 root>\bin64 :
libibmcogeay32.dll
log4cxx.dll
sslibmcogeay32.dll
tm1api.dll
tm1runti.exe
TM1ULibDll.dll
ssl (the whole folder)
(you might need more or get by with less components, your mileage may vary)
Drop them all in a single directory on the server where your ETL tools or other scripts can access them, put the password files somewhere safe, and you're ready to roll.
Running TM1RunTI using MS SQL Server Agent with CmdExec
Here's one real life example what you can do with the command line tool. We are executing TM1RunTI from MS SQL Server Agent, using a CmdExec job to run the command line tool and export data from TM1 to our data warehouse's staging area. After this, the next steps that follow will then refresh the normalized data warehouse tables and aggregate the data from TM1 to fit the right grain in the DW.
The process exit codes are also documented well in the
official documentation. Unfortunately the SQL Server Agent doesn't allow much exit code processing out of the box, but if you monitor the output of the script (tick 'Include step output in history' in the Advanced tab) you can probably get going without doing much extra scripting.
If you have bright ideas how to use TM1RunTI, or other less documented TM1 or Cognos tools to do something cool, let me know!
