Rules in IBM Planning Analytics allow you to reference components of a cube as part of your formula … either from the cube you are currently in or from another cube. This is done via the use of the DB function. The syntax of the DB function is:
DB(cube, elem1, elem2, …)
Where each elem value references an element from the dimensions of the cube, in the order of the cube definition
This is a great formula if you know all the dimensions of your cube in the proper order. But what if you don’t want to have to define every dimension in your cube?
Planning Analytics also allows you to use a formula called CellValueN. The syntax of the CellValueN function is the same as the other formula:
… but there are some differences when using this formula.
- You do not have to reference every dimension! Any undefined dimensions are the equivalent of using the exclamation point, often called a “bang.” This means that you only need to reference dimensions/elements that are different from the source value.
- Since you are not putting your dimensions in order, you must reference the dimension name in front of each element. The dimension name must be written within a set of single quotes, a colon is then used without quotes, and the element is then written within another set of single quotes.
For example, account 605100 would be written as ‘Account’:‘605100’
- There are different versions of the rule. One version is CellValueN which pulls numeric values and one version is CellValueS which pulls string values.
Here is an example of the rule. Note that the cube contains seven dimensions but the formula only references two of them.
This approach allows you to simplify your rules by referencing a data point without having to reference every dimension within the cube.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters! You can also sign up to get our Planning Analytics Tips & Tricks sent directly to your inbox!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula
IBM Planning Analytics Tips & Tricks: IFERROR
IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions
