1. Administrator's Guide
  2. Managing Formulas
  3. Considerations for Using Formulas

Considerations for Using Formulas

Data Type Conversion

Some functions require that data values be of a specific data type to be properly evaluated. For example, functions that perform mathematical calculations require that input arguments are integer or floating point values, whereas string manipulation functions require that string values be provided as input. In some cases, data values must be converted from one data type to another to be successfully derived. Oracle Data Relationship Management provides a set of functions to handle data type conversions within formulas.

Property Level Restrictions

Generally, property definitions created to manage data at a lower level of granularity can reference other properties that manage data at a higher level of granularity.

  • Local Node––May refer to other local node, global node, hierarchy, or version properties

  • Global Node––May refer to other global node or version properties

  • Hierarchy––May refer to other hierarchy or version properties (Lookup only)

  • Version––May refer to other version properties (Lookup only)

Referencing Properties from Other Nodes

It is common for a derived property or validation to evaluate or retrieve a property value from a different node than the current node for which the formula is being calculated. Data Relationship Management provides several functions that enable you to access property values from nodes within the same version.

  • NodePropValue

  • ParentPropValue

  • HierNodePropValue

  • AncestorProp

  • DualAncestorProp

  • AscNodeProp

  • ReplacePropValue

  • ListPropValues

  • ListNodePropValues

Referencing Local Node Properties from Global Node Properties

Global node properties do not require a hierarchy context to return a value, whereas local node properties do require a hierarchy to be specified. Derived properties or validations that are calculated for a global node cannot reference local node property values using the standard PropValue or NodePropValue functions. Global node properties may reference local node property values using the HierNodePropValue function whereby a particular hierarchy must be specified to retrieve the value of the property for a specific local node in the hierarchy.

Nesting Functions

Combining functions into the same formula is referred to as nesting functions. The output of one function is used as an input argument for another function in the formula. When evaluating nested functions, Data Relationship Management executes the innermost function first and then works its way outward. Functions may be nested explicitly within the same formula or nested implicitly by using one formula that refers to a property that uses a different formula.

Using Properties as Variables for Other Properties

Data Relationship Management enables you to use a combination of nested functions, references to other properties or nodes, and literal values, which may result in lengthy or complex formulas. You can use separate property definitions to modularize formula logic and simplify the formula syntax required to achieve the same results. This approach may significantly improve the ease of maintenance for these formulas.

In addition, formulas may evaluate the same data or perform the same calculation multiple times within the same property definition or across multiple property definitions for a given node. When this logic is embedded in a much larger formula or implemented within property definitions, these checks and calculations are performed multiple times, which may affect the performance for operations that require the properties to be calculated. You can minimize redundant processing by isolating the duplicate formula logic within a separate property definition.

Using Recursion to Traverse Hierarchy Relationships

Business rules for nodes at lower levels of a hierarchy may require the evaluation of property values from ancestor nodes above them. One way to allow these property values to be referenced by lower-level nodes is to enable inheritance on the property definition that manages the values that must be referenced. However, in many cases, using an inheritance for a property definition is not appropriate.

You can use specific hierarchical formula functions with a self-reference to the current property definition to recurse up a branch of a hierarchy to retrieve or evaluate property values for ancestor nodes.

ParentPropValue––Use this function to recurse up a branch of ancestors in the current hierarchy. For example: If(Equals(Integer,PropValue(Core.Level),1),Label Only,ParentPropValue(Essbase.DataStorage))

HierNodePropValue––Use this function to recurse up a branch of ancestors in another hierarchy. For example: If(Equals(Boolean,PropValue(Custom.PlanPoint),True),Abbrev(),HierNodePropValue(Geography,HierNodePropValue(Geography,Abbrev(),Core.Parent),Custom.PlanMember))