7/29/15

Cognos TM1 TurboIntegrator (TI) is a great ETL tool that allows you to bring in data to TM1. It also allows you to drill down to an ODBC connection. To get a more seamless “communication” between TM1 and your database, you want to keep a couple things in mind when building your database tables. Here are some database table manners tips:

Do not use spaces or TM1 restricted characters in your headers. If you do, when you get to the Variables tab in Cognos TM1 TurboIntegrator, you will then get an ambiguous variable name. So if your first column is called “Department Name”, you will get “v1” as your Variable name. Not much of a problem if you have only a few columns and the sample value for that column is easily recognizable as to what it is. This is because you do have the option to rename the variable in TM1. However, that is adding an additional step and if you are using the same table in several TI Processes, then the additional step multiplies into many additional steps. There are also some reserved words in TM1. Year and Month are examples, so it would be preferable to call those Years and Months, as the plural is not a reserved word.

Click here for an overview of TM1 Object Naming Conventions.

Elements in TM1 dimensions are always considered text/string data. Therefore, format elements such as Year (ie 2015) as text. That way you do not have to convert numeric values to string with a user created variable. Likewise, if you are passing a TM1 element as a parameter in a drill to an ODBC database, TM1 will pass back a string value. This is unless you code it to convert to numeric. If drilling back to an Oracle database, you may need to add additional scripting in your TI Process to convert your parameters passed to be in Upper Case.

During a build phase, you may need to add additional information to your table. If you are the one that is updating the SQL code in the TI process, please add the addition columns in your query at the end of the existing list of columns. If you randomly insert the new columns, it requires the TM1 admin to rework the Variables tab due to the order change. Also, changing the format of an existing column can disrupt coding for the TM1 admin.

If you have any other questions about the Cognos TM1 TurboIntegrator or any other general concerns, do not hesitate to contact us at Sales@LodestarSolutions.com.