TM1 gotchas

  • Apr. 03, 2014

Introduction

Using TM1 on a day-to-day basis (from the developer/consultant side, not the end-user's), I regularly encounter a gotcha. You know, those little things that are worth sharing and which could make (TM1) life more pleasant and smooth to others. Hence, below you can read some of these. Feel free to email me other gotchas. The list might be extended later on, so visit my site regularly.

  • Browsing in the Server Explorer can be done with the mouse but also the arrow keys (obviously). If you want to contract a node (Applications, Cubes, Dimensions, …) you press the Arrow left key. The Arrow right key will expand again. What is not so well-known is the Backspace key: you will immediately jump to the node again. Admittedly, the left arrow key does the same :-)
  • A gotcha that is linked with the previous one but using the mouse. If you want to add a chore in the Server Explorer, but you want to see the dimensions of a particular cube. This is not always possible (the screen is not big enough and/or you have too many objects inbetween). What you could do is this trick:
    1. Show the relevant cube and its dimensions
    2. Click on the node of "Chores"
    3. Go up with the scrollbar but do not select anything
    4. Use the Server Explorer menu to choose 'Chores' > 'Create new chore'. In English: Alt+c+c.
  • Exporting a cube's data can be done using a right-click on the cubename (or viewname), provided you have Administrator rights in TM1. This will generate a CMA file. Opening the file through Excel is easy: just bring up an Excel instance and simply drag and drop the CMA file onto the Excel instance's window. It will open up automatically. You can also open up the file from within Excel but this will generally give you a wizard to import the file, which can be tedious (although the wizard could provide more possibilities).
  • Whenever you need to copy the DBRW formula in a cell to a different cell: cut the cell instead of copy the cell. That way, your cell references will be preserved.
  • In practice, we often need to change Excel reports (or websheets, for that matter) where rows or columns have been hidden. When doing the changes, we first need to unhide the rows, make the changes, then hide the SAME rows again. This can be tedious. Some solutions to spend less time on stupid actions like this, include:
    • Use a small generic VBA procedure to list the rows that are hidden. Then unhide all rows and make the changes. Have another generic VBA procedure to hide the same rows again.
    • An interesting way without VBA coding and using built-in Excel functionality:
      1. Write a little 'x' in all rows of the used range of the report, in a column that you do not use. To identify the visible cells of a range, select the cells in the column, press F5 or Ctrl+G, then Special…, then Visible cells. OK your way out of the dialog screen.
      2. Unhide all rows and make your changes to the sheet/formulas/formatting.
      3. When finished, select in the extra column the cells (as many rows as your report contains), press F5, then Special…, then Blanks. OK.
    To hide rows in Excel, you can use shortcut command Ctrl+9. An alternative approach to the above, is indicating in the first column(s) and/or the first row(s) of a spreadsheet, which rows/columns should be hidden. With appropriate formatting it's immediately clear what areas are to be hidden from sight. Data > Group and Data > Ungroup and possible too, but they did (do?) not work in TM1 Web.
  • Another one is this. A chore in TM1 can be executed by a user (manually) or on a scheduled basis. If it is run manually, you can call the TM1User() function to ask for the specific user that executes the chore. It will yield a valid element in the dimension }Clients. If the chore is run automatically, it is executed using the non-existing user "R* name of the chore". In that case, it makes no sense to ask for the TM1User() as the resulting string will not even occur in the }Clients dimension. Hence, retrieving the Default display name for that "user" will return an error.
  • To easily select all code in an Advanced tab of a Turbo Integrator process, hit the Tab twice. To easily copy all code in an Advanced tab of a Turbo Integrator process, press Ctrl-C al least 5 times after the previous action. That is just how copying goes in TM1.
  • In Windows Explorer, if you navigate towards install directories for TM1 (like "C:\Program Files\ibm\cognos\tm1_64\bin64"), you can show the version of the files. Right-click somewhere in the header portion, for instance "Date Modified", and add the column "File version". You might need to look it up from the list of possible columns.
  • More to come later… Stay tuned.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links