Overview of cube rules

The most common calculation in OLAP applications involves aggregating data along a dimension. In IBM® Planning Analytics, you create these calculations by using consolidations. For example, in a Month dimension, you can define a quarterly consolidation that sums January, February, and March values.

In many applications, you also need to perform calculations that do not involve aggregating, such as generating cost allocations and calculating exchange rates. You can use rules to perform such calculations.

Using rules, you can also do the following:

  • Multiply prices by units to yield sales amounts.
  • Override consolidations when needed. For example, you can prevent a quarterly average price from displaying a tally of individual monthly prices.
  • Use data in one cube to perform calculations in another cube, or share data between cubes. For example, you can pull sales data into a cube that contains profit and loss data.
  • Assign the same values to multiple cells.

Each rule is associated with an individual cube. For example, if you create a rule to calculate values in the Purchase cube, the associated rule appears as a subordinate object of the Purchase cube in Server Explorer. The Purchase rule calculates values only in the Purchase cube.

Compiled rules are stored in files called cube_name.rux. When a cube for which you have defined rules is loaded into memory, TM1® searches for the cube's .rux file in the data directory that contains the cube. The .rux file must reside in the same directory as the associated cube (.cub) file or else rules do not load properly.

When you create a rule, TM1 also generates a file called cube_name.blb, which contains format information for the Rules Editor.

If you want to edit a .rux file in another text editor, you must delete the corresponding .blb file. If you do not delete the file, there will be a discrepancy between the contents of the .rux file and the display in the Rules Editor because the display of the Rules Editor is determined by the .blb file.

Note: When you use rules and spreading, if the rules are such that the resultant value does not match the spread desired value, an error is generated and the spread operation will not be done. For more information, see the SpreadingPrecision parameter in the Planning Analytics Local Installation and Configuration documentation for more information about spreading and rules.

Using object names with special characters in rules expressions

Special characters in object names might conflict when these objects are used in a rules expression. For example, the @ character and exclamation point ! character are both valid characters for object names but are also used in rules expressions.

The @ character is a string comparison operator in rules. If you reference any object that contains the @ character in rules, the object name must be enclosed in single quotation marks. For example, a dimension named products@location must be referenced as 'products@location' in rules.

The exclamation point character is used as part of the !dimension argument in the rules DB function and must not be used in object names that will be used in rules.

DB('MarketExchange',!market,!date)

For more information, TM1 object naming conventions, in TM1 for Developers.