TM1 Rules

Creating Rules in TM1

Rules

Using TM1 Rules, you can:

Perform multiplication of cells to calculate totals based upon business rules
Override automatic TM1 consolidations when needed
Use data in one cube to perform calculations in another cube or share data between cubes
Assign the same values to multiple cells

The Cognos TM1 Rules Editor is a .NET component and therefore, to run it on your machine, you must first install the Microsoft .NET Framework 3.5 SP1. Errors will occur if you attempt to use the Rules Editor when .Net 3.5 SP1 is not properly installed.
To access the Rules Editor, you can use TM1 Server Explorer. Right-click on a cube (the cube you want to define a rule for) and from the menu select Create Rule… (if there are no rules already associated with this cube):

You can select Edit Rule… if one or more rules already exist for this cube.

TM1 rules are compiled one by one when the rules file is saved. TM1 stops compiling the rules when it encounters its first rule compile error. For example, if there are three compile errors, you would perform the following steps:

Click on Save.
TM1 prompts for the compile error (1st error encountered).
Fix the error.
Click on Save.
TM1 prompts for the compile error (2nd error encountered).
Fix the error.
Click on Save.
TM1 prompts for the compile error (3rd error encountered).
Fix the error.
The Save operation is successful.

Successfully compiled TM1 rules are stored in files called cube_name.rux. When you create a rule, TM1 also generates a file called cube_name.blb, which contains format information for the Rules Editor.

Rules – how do they really work?

Here is the sequence of events:

A value is requested from (a location in) a cube.
TM1 Server checks if the location corresponds to the area definition of any calculation statements associated with the cube.
If the location does correspond to a statement, TM1 evaluates the formula portion of the calculation statement.
TM1 returns the calculated value to the relevant area.

General rules of thumb:

Remember that, if most values in your cube are zeros, this is an indication that the cube is relatively sparse.
Multidimensional cubes are almost always sparse.
The more dimensions a cube has, the greater is the degree of sparsity.
In TM1, there is a distinction between a zero and a value that is missing (or non-applicable).
In TM1, values can only be real numbers, and the value zero is used to represent zero, no (or missing) value, and even the non-applicable values.
The impact of sparsity on calculations can be tremendous.

Sparsity

During consolidations, TM1 uses a sparse consolidation algorithm to skip over cells that contain zero or are empty. This algorithm speeds up consolidation calculations in cubes that are highly sparse. A sparse cube is a cube in which the number of populated cells as a percentage of total cells is low.

Combinatorial explosion

In mathematics, a combinatorial explosion describes the effect of functions that grow very rapidly as a result of combinatorial considerations.

Dissection of Cognos TM1 Rules

If you use feeder statements in a rule (and you should), the rule must also contain a SKIPCHECK declaration and a FEEDERS declaration. The SKIPCHECK declaration must immediately precede any calculation statements in the rule, while the FEEDERS declaration must precede the feeder statements.

Rules – calculation statements

A calculation statement consists of the following:

Area definition
Leaf, consolidation, or string qualifier
Formula
Terminator

Area definition

Using areas, you can specify which calculations apply to different parts of a cube.
Some valid area definitions include:

[]: This would apply the calculation to all cells in the cube.
[‘Jan 2012’]: This would apply the calculation to all cells identified by the Jan 2012 element.
[‘Jan 2012′,’New Jersey’]: This would apply the calculation to all cells identified by the Jan 2012 and New Jersey elements.
[‘Jan 2012’, {‘New Jersey’, ‘New York’, ‘Vermont’}]: This would identify a subset of data (by enclosing all subset members in curly braces) to apply the calculation to all cells identified by the Jan 2012, New Jersey, New York, and Vermont elements.

Area definition syntax

To create a valid area definition you must:

Enclose each element within single quotes
Use commas to separate each element
Enclose the entire area definition in square brackets

Special characters and non-unique element names

You can use the syntax ‘dimensionname’:’elementname’ to specify elements that are not unique to a single dimension or for dimension names that contain special characters. A good example is when you have to reference one of many control objects that are built into TM1. These objects contain the curly brace (}) special character. Using the previously mentioned syntax, you can write an area definition that defines the ADMIN group in the Groups dimension as follows:
[‘}Groups’:’ADMIN’]

The CONTINUE function and area definitions

Rules are applied sequentially. Therefore, if two rules reference the same cells defined by an area definition, the first rule encountered gets applied to the cells. You can utilize the CONTINUE function to apply additional rules to the cells.

Numeric, consolidation, or string (oh my!)

Cognos TM1 defines elements as either a numeric (N:), consolidation (C:), or string (S:).

If the qualifier is N:, the statement applies only to the leaf cells.
If the qualifier is C:, the statement applies only to the consolidated cells.
If the qualifier is S:, the statement applies only to the string cells.
If there is no qualifier, the calculation statement applies to all cells in the defined area.

You will need to be able to interpret the following rule syntaxes:

[‘Gross Margin%’]=[‘Gross Margin’][‘Sales’]*100: No area restrictions are applied.
[‘Sales’]=N:[‘Price’]*[‘Units’]1000: The calculation of ‘Sales’ only applies to N: or leaf level elements in the cube.
[‘Sales’]=C:[‘Sales’][‘Units’]*1000: The calculation of ‘Sales’ only applies to C: or consolidated cells.
[‘Sales’]=C:[‘Sales’][‘Units’]*1000 and N:[‘Price’]*[‘Units’]1000: There is a different calculation applied for leaf level elements and consolidated cells.

Formulas

Rule formulas can include numeric constants, arithmetic operators and parentheses, functions (those that are valid in rules), conditional logic, and references to data in other cubes.

Numeric constants

There are some basic points you should know about numeric constants:
Simplest components of a rule calculation

Numeric
Can include (optionally) a leading sign
Can include (also optionally) a decimal point
Alphabetic characters are not allowed
Maximum length is 20 (numeric) characters
Scientific notations are allowed

Arithmetic operators

There are some basic points you should know about arithmetic operators:

Valid operators include signs such as plus (+), minus (-), asterisk for multiplication (*), forward slash for division (/), backslash for division which returns zero when you divide by zero (), and caret for exponentiation (^).
Arithmetic operators are used in the following order:

Exponentiation
Multiplication
Division
Addition
Subtraction

Parentheses can be used to force a different order of evaluation.

Using conditional logic

Cognos TM1 Rules can be written to have conditional evaluation. You can use the IF function to force TM1 to evaluate a rule differently depending on a logic test. If you are familiar with Microsoft Excel, you will be able to recognize the format:
IF(Test, Value1, Value2)
Along with the important IF function, you need to be aware of the operators you can use in TM1 for comparing values. They are (the expected) greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), equal (=), and not equal to (<>).
To compare two string values, you must insert the @ symbol before the comparison operator.
You also need to be able to combine expressions within a TM1 rule by using the logical operators And (&), Or (%), and Not (~).
String values in Cognos TM1 can be joined (concatenated) together by using the pipe (|) character—just be sure to test the combined length of the resulting expression as TM1 only allows a single string to be up to 255 bytes.

Cube references

A very important part of understanding and using Cognos TM1 Rules is cube references which can be both internal references to data within the cube where the rule resides and external references to data within cubes other than the cube where the rule resides. To reference data in another cube, you must use the DB function. The DB function returns a value from a cube in a TM1 database.

External cube references

To reference data in a cube other than the cube where the rule resides, you need to code the DB function. The DB function must reference the cube (where the data you want to retrieve lives) and then a value for each of the dimensions in that cube:
DB(‘Cube’, dimension value, dimension value,… dimension value)
In the default TM1 Rules Editor, you can click on the button labeled DB(…) to insert a DB rule to reference another TM1 cube:

Remember, with nested DB functions, you must ensure that the inner DB function returns a valid argument to the outer DB function:

['Gross Sales','Budget']=N:
IF(!Period@='2012',
IF(!Customer@='00001001',
DB('BookSales',!Customer,'Gross Sales','Prior','Actuals'),
DB('BookSales',!Customer,'Gross Sales','Current','Actuals')
),
STET);

The Cognos exam specifically references the DB function and its use.

Drill assignment rule

Cognos TM1 allows you to create a link between two cubes that have related data. Keep in mind that this type of rule must be defined as a string.

More important Rules facts

There are some important facts about Rules:

You must use a semicolon to end every rule statement.
To make longer rules more readable, you can distribute statements over multiple lines in the cube rules file, as long as each of the statements ends with a semicolon.
If a calculation statement references a string element, you must use a DB function to retrieve the string, even if the string resides in the same cube for which you are writing a rule.
By using the STET function, you can bypass the effect of a calculation statement for specific areas of a cube.
Cognos TM1 Rules syntax is not case-sensitive.
You can use spaces within rules to improve clarity.
A rules statement can occupy one or more lines in the Rules Editor. It can also contain one or more formulas. End each statement with a semicolon.
To add comments and to exclude statements from processing, insert the number (#) sign at the beginning of a line or statement
The length of a comment line is limited to 255 bytes. For comments longer than 255 bytes, you must break up the comment into multiple lines, with each one including the number sign (#) at the beginning.
When more than one statement in a set of rules is applied to the same area, the first statement takes precedence.
The exclamation point (or bang) can be used in a rule to evaluate to the current element of a referenced dimension.
If you reference any object containing a special character in TM1 Rules, the object name must be enclosed in single quotation marks.
If a calculation must be updated continuously (in real time), you should create a rule for it, conversely, if a calculation is slow moving—say updated only one time per month, it would be a better idea to utilize a TurboIntegrator (TI) process to maintain the data.
You can load a rule file using a TI process by using the RuleLoadFromFile function.

Rules performance

Cognos TM1 Rules is very powerful but not always the best way to calculate results.

Key notes on Rules behaviors

Cognos TM1 Rules takes precedence over dimension consolidations but keep in mind that if a rule refers to cells in the cube that are the result of a consolidation calculation, the calculation is performed first, then the rule is calculated based upon that result.
You have no control over the order in which Cognos TM1 performs dimension consolidations. So, do not write rules that override consolidated elements.
Cognos TM1 allows you to apply more than one rule to a cube cell. This is called rule stacking and is only limited to the machine memory. TM1 calculates the cell value based upon how the rule statements are coded.
It is important to understand the precedence of Cognos TM1 Rules. For example, if you create a rule to apply to all elements in a dimension but do not see the results you expected, you will need to determine if a rule exists above or before the rule that affects this area.
Again, as in Microsoft Excel, circular references are not allowed within Cognos TM1 Rules.
You also need to be aware of how TM1 deals with percentages and fractions. Depending upon your requirements for precision you may have to rethink your rule logic.
It is not uncommon for a rule to be written to move data from one level to another (leaf or N: level to a consolidation).
Typical functions used in Cognos TM1 Rules include DIMIX, DIMNM, DNEXT, TIMVL, and STET.
If a calculation occurs naturally in a dimension (hierarchy) consolidation, use it! Do not write a rule to circumvent the fast consolidation engine!

Rule feeders

The more rules you implement the more overall cube performance will be impacted. Specifically, the native consolidation ability of TM1 will slow down. To address this performance issue, you must make use of the SKIPCHECK and FEEDERS declarations.
Adding rules to a cube will impact TM1’s ability to utilize its sparse consolidation algorithm. Fortunately, you can tell TM1 where to look for rules-derived values.
The SKIPCHECK declaration should be placed in the cube’s rule file. This turns on the TM1 sparse consolidation algorithm. The FEEDERS declaration indicates that you have provided feeder statements which will ensure that the correct cells are not skipped during consolidations.
Another declaration to be aware of is the FEEDSTRINGS declaration. This must be used if any of your rules define string values. This will ensure that string values are fed properly (in the same manner as described above for the FEEDERS declaration).

Persistent feeders

To improve the reload time of cubes with feeders, set the PersistingOfFeeders configuration parameter to true (T) to store the calculated feeders to a feeders file. Any installation with a server load time of over five minutes can probably improve its performance using this parameter.

The special feeders

You can edit the Cognos TM1 configuration file to add the parameter PersistingOfFeeders=T. When this parameter is set and a cube with rules is saved, the feeders are stored alongside the cube data in a .feeders file. The cube files are called cube-name.cub and cube-name.feeders. The best way is to use a TI process and the DeleteAllPersistentFeeders() function to delete all persistent feeders.

Modifying rules and feeders

You will have to re-calculate all of your feeders periodically by using the DeleteAllPersistentFeeders() function (within a TI process) and then restarting the TM1 Server.

Guidelines for simple feeders

There are some important guidelines for simple feeders:

Every calculation statement in a rule should have a corresponding feeder statement.
The simplest feeders are those that apply to calculations within one dimension of one cube, where the presence of a non-zero value in one element implies the presence of a non-zero value in another element.
To write accurate feeders, you need to focus on which component(s) of a formula determines when a non-zero value is returned to the area.
Formatting errors within a rules file will cause a rules file to become corrupted.
Do not attempt to edit the rules file of a cube using a non-TM1 editor.
When working with inter-cube rules, calculation statements reside in the target cube, while feeder statements always reside in the source cube.
TM1 feeders are used in parallel with TM1 rules to reduce the number of calculations done by the rules.
Feeders are not required to make rules work. However, they can yield dramatic performance improvements in sparse cubes.

Troubleshooting

To diagnose rule issues, you can utilize the Cognos TM1 Rules Tracer. The Rules Tracer will allow you to:
Trace feeders, to ensure that selected leaf cells are feeding rules-calculated cells properly
Check feeders, to ensure that the children of a selected consolidated cell are fed properly as well as verifying that the calculation paths of the selected cells are correct

Rules Tracer

There are some key facts about Rules Tracer:

The Rules Tracer functionality is available only in the Cube Viewer.
Rules Tracer lets you trace the way a selected cell feeds other rules-calculated cells.
Because you can only feed from a leaf element, this option is not available for consolidated cells. The option is, however, available for leaf cells defined by the rules.
The Rules Tracer window contains two panes.
The Rules Tracer window’s top pane displays the definition of the current cell location, as well as the feeder statements associated with the current cell.
The Rules Tracer window’s bottom pane displays the locations fed by the current cell.
If a cube has a rule attached that uses SKIPCHECK and FEEDERS declarations, you can use Rules Tracer to check that the components of consolidations which are subject to rules are properly fed.
You can check feeders only from a consolidated cell.
You can trace the calculation path of all cube cells that are derived from either rules or consolidation. These cells appear shaded in the Cube Viewer.
Tracing a calculation path can help you determine if the value for a cell location is being calculated properly.
Rules Tracer lets you trace the way a selected cell feeds other cells.
Since you can only feed from a leaf element, this option is not available for consolidated cells. The option is, however, available for cells defined by the rules.