Sunday 23 June 2013

Basic TM1 Tips for Green Hand

1.    When presenting views to business users, make sure you create the view in all detailed levels as default and tick “Automatic Recalculation”.
2.    You can create a “Picklist” in dimension’s attributes which allows users to select values from drop-down list.
3.    Use “Driver” cubes to centralize all rates, values etc and use DB function in rules to refer to it.
4.    Don’t forget to consider maintenance when designing cubes.
5.    Prefer to use weight to do addition or subtraction instead of rules for consolidations.
6.    Dimension order should be from smallest/sparsest to largest/densest and measure dimension would be the last one when creating cubes.
7.    Always add roll-up level for dimensions.
8.    If dimensions are nested, you can’t use filter in cube viewer.
9.    Think about the following when building a cube
       a.    Purpose of the cube
       b.    What the measures?
       c.    What else dimensions do I need?
       d.    Build
       e.    Create default view and format
       f.    Write rules/feeders if necessary and test
       g.    Load
       h.    Publish if necessary
10.    When an element belongs to a group in dimension, if you delete it, TM1 just takes it out from the group and put it in the last. Delete it again if you really don’t need it.
11.    Alias is useful for displaying alternative names but also can be used for loading. E.g. you have staff name in the dimension and also use alias for staff number. TM1 would firstly search for staff name and then look for staff number in alias.
12.    Set order each time when creating dimension before saving it.
13.    When you see “#N/A” in cubes, it would be either circular reference or dividing by zero.
14.    Dimension order is very important.
15.    As long as you write a rule for a cube, performance engine would be turned off automatically. So you need remember rules and feeders are combined together.
16.    Under feeding or only writing “SKIPCHECK” would lead to zero consolidations.
17.    Over feeding or only writing “FEEDERS” could work but still have slow performance.
18.    When you have DB functions in rules, you need write feeders in the referenced cubes and use DB functions to feed it back to the source cubes.
19.    Whenever you open a view in a cube, TM1 would execute rules first and then aggregate cells for consolidations.
20.    Addition and subtraction need feeders for all elements in the formula.
21.    Multiplication and division only need feeders for the sparsest element depends on business rules.
22.     When using wizard for DB function, remove anything in the field before clicking on subset button on the right.
23.    Use “Trace Calculation” or “Check Feeders” in views when you see zero for consolidations and values for detail levels.
24.    Use curly braces to feed multiple elements to same target cell.
25.    Rule sequence should be from specific to generic.
26.    Use DB function in rules to test a string.
27.    You can use custom format sometimes such as 0.00”%”.
28.    Use UPPERCASE for all rollup elements or consolidations.
29.    Active form can be drilled down, formatted quickly and have dynamic subset on rows.
30.    Whenever you don’t save data when exiting the server, TM1 would generate *.REJ file to backup and you can use TI process to load it for recovery.
31.    You can create TM1 server by shortcuts or windows service by using different parameters.
32.    Attribute and lookup cube can be useful but only admin can modify attributes.
33.    When specifying values for leaf cell and consolidation cell, you need to write them down in one formula.  E.g. [‘Price’] =:N .... ; :C 0;
34.    When creating dimension, usually we need to use TI process to create them from files dynamically and further create dynamic subset from them as well. These processes would be scheduled automatically.
35.    When using TI to create dimension, you should just put one variable as “Element”. For loading cubes, they have many.
36.    Make sure you use your own view such as “TIXXXXX” to copy data within cube or between cubes.
37.    The security applies according to this order. Cube->Dimension->Element->Cell
38.    No need to add feeders for string elements and they are only for numeric elements.
39.    You can use “Re-order dimensions” or “Load on Demand” to improve performance.
40.    You can use relative data spreading functions to reference data from other cubes but make sure referenced cube has same dimensions on column.
41.     When replicating, you need to careful about modification on same cells on different servers as only the source server takes precedence and change from other servers would be overwritten. The replication is merging changes from all different servers and bi-directional.
42.    When copying data in one cube for different views, you can just use a new variable in TI to specify different element such as “Final Version” and the original one should be ignored.
43.    When Cognos Framework Manager is connecting to TM1, you need to specify which dimension is measure in cube properties first.
44.    For lock permission, only admin can unlock it.
45.    Six permissions have precedence.

Useful Excel Tips for TM1 Operations

(F8) - Toggles whether to extend a selection with the arrow keys.

(Ctrl + Spacebar) - Selects the current column.

(Shift + Spacebar) - Selects the current row.
   
(Ctrl + Shift + 8) - Selects the current region (block of contiguous cells).

(Ctrl + Shift + Spacebar) - Selects the whole worksheet.

(Shift + F4) - Repeat last find.

(Ctrl + Backspace) - return to the active cell.

(Ctrl + Arrow Key) - Move to the edge of next data region (cells that contains data).

(Ctrl + Shift + Arrow Key) - Selects the rest whole worksheet.

(Ctrl + D) - Fill complete cell down (Copy above cell).

(Ctrl + R) - Fill complete cell to the right (Copy cell from the left).

It is possible to change the active cell in a selected range without changing the whole selection by using the Enter key to manoeuvre within the selection.

F9 = recalculate all worksheets in all open workbooks

SHIFT-F9 = recalculate the active spreadsheet only ( but not rebuild the active forms).

CTRL-F9 =  minimizes a workbook window to an icon.

Ctrl-Alt-F9 = recalculate all worksheets in all active workbooks

CTRL-ALT-SHIFT-F9 = "relink" the workbook to Tm1 when the connection was "confused" between Excel and "Perspectives".

Alt-F9 = rebuild of the active form

F2-Enter = recalculate a single cell