Referencing Values Using Hierarchies in IBM Planning Analytics

IBM Planning Analytics enables developers to create sophisticated models by utilizing cell references in TurboIntegrator (TI) processes and rules. With the introduction of hierarchies in dimensions, developers can now leverage these references to perform targeted updates, calculations, and validations at various levels within a hierarchy. This article explains how to use cell references with hierarchies effectively and provides examples of their practical applications.

Understanding Hierarchy-Aware Cell References

Hierarchies add a layer of flexibility to dimensions, allowing for alternate groupings or roll-ups of elements within the same dimension. In Planning Analytics, cell references can explicitly reference a specific hierarchy in a dimension using the following syntax, comparing to the standard element reference:

Process Syntax:

Standard:    CellGetN( CubeName, Element1 , Element2 ) ;

Hierarchy:   CellGetN( CubeName, Hierarchy1:Element1 , Element2 ) ;

Example:     Value = CellGetN( ‘Sales’ , ‘Division’:’East’ , ‘Total Products’ ) ;

Rule Syntax:

Standard:   DB( CubeName, !Dimension1 , !Diimension2 ) ;

Hierarchy:  DB( CubeName, Hierarchy1:!Dimension1 , !Diimension2 ) ;

Example:     [‘East Sales’] = DB( ‘Sales’ , ‘Division’:’East’ , ‘Total Products’ ) ;

The syntax examples above use the CellGetN and DB function for Processes and rules respectively, but other functions support the hierarchy reference syntax as well. In addition, there are hierarchy specific functions that take a hierarchy as one of the arguments. An example being the ElementIsAncestor function has the same purpose as the ELISANC function, but includes an argument for the Hierarchy. For more functions that can be used with hierarchies, check out IBM’s official Planning Analytics documentation.

Practical Applications of Hierarchy-Aware Cell References

  • Alternate Reporting Structures
    • Use hierarchies to generate reports that cater to different organizational views, such as geographic regions or product lines.
  • Custom Roll-Ups
    • Implement calculations that target alternate roll-ups, such as sales by strategic regions instead of standard geographic divisions.
  • Data Validation
    • Validate data consistency across hierarchies and ensure that alternate hierarchies align with the base data.
  • Scenario Analysis
    • Use hierarchy references to simulate various business scenarios, such as comparing performance across different organizational structures.

Using hierarchy-aware cell references in IBM Planning Analytics empowers developers to create tailored models and processes that align with specific business requirements. By understanding and applying these references effectively in TI processes and rules, you can unlock greater flexibility and precision in your Planning Analytics applications. Practice with real-world scenarios to fully leverage the power of hierarchies in your models.