Applying Formulas

An administrator can create a formula on a Dimension Member or in a Business Rule.

Member Formulas

The preferred and more common approach is to write formulas on Dimension Members. Member formulas are written using the Formula property on individual Scenario, Account, Flow, or User Defined Members in the Dimension Library. The primary reasons for writing formulas on Dimension Members are they provide intuitive formula organization and promote reusability of Dimension and Members with their associated calculation across multiple Cubes.  They also enable parallel processing for performance optimizations using advanced multi-threading that executes multiple formulas at the same time.  Finally, writing formulas on Dimension Members support drill down from a calculated amount to the amount used as the source for calculation and they support the ability to vary formulas by Scenario and Time.  This is useful because changes can be made without affecting the calculation for older data or data in other Scenarios.   

Finance Business Rules

Formulas can also be written using one or more Business Rule files. While this typically is not the preferred approach, it provides the ability to put all formulas in one location, and it is similar to the approach used by some older competitive products. The primary reasons for writing formulas in a Finance Business Rule are the formula requires extensive cross-dimensional dependencies and it is not clear on which Member an equivalent Member Formula should be written, or the formula requires complex sequential logic, variable, or conditional statements that affect multiple dependent calculations.  Another reason for writing formulas in a Finance Business Rule is when the application requires custom algorithms for Currency Translation, Share, or Intercompany Eliminations.

TIP: Due to the sophisticated built-in translation and consolidation algorithms, most applications only require Member Formulas for the Data Unit Calculation Sequence (DUCS) (i.e., Chart Logic). Custom Business Rules for Translation, Share, and Intercompany Eliminations are not typically needed.

NOTE: Due to the way OneStream stores its data, Decimal should always be used instead of Double or Integer when declaring variables that return a number within a Business Rule or Member Formula.

Dim myNumber as Decimal = Api.Data.GetDataCell(“A#Sales – A#CostOfSales”).CellAmount 

See Business Rules in Application Tools for more details.