Formula Types

Two types of formulas can be applied to dimensional members or members in a business rule file:  

  • Dynamic Cell Calculation (Dynamic Calc): An in-memory calculation that runs on demand when a cell containing a dynamic member formula is requested.  A Dynamic Calc formula computes a value for a single cell and runs whenever the cell needs to be displayed without storing the result.

  • Stored Calculation: A persisted calculation that runs as part of the Data Unit Calculation Sequence (DUCS). With these formulas you can calculate many cells simultaneously, such as data buffer.

Performance Considerations

Dynamic Cell Calculation (Dynamic Calc): Dynamic Cell Calculations enhance the consolidation process because the amount is calculated when requested for display and is not written to the database. For reporting however, performance may be impacted because data is calculated on demand.  Dynamic calculations are usually used for ratio or percentage calculations.  

Stored Calculation: Consolidation performance is directly impacted by the volume and complexity of stored calculations. Carefully consider each stored calculation since one poorly written rule can cause large amounts of data to be written to the cube, negatively impacting consolidation performance. If you use many member formulas or if data volumes are not considered in member formulas, over 100,000 stored numbers may be generated from just 1,000 initially loaded numbers. The quantity of stored numbers is a critical factor to consider to optimize consolidation performance.

Reference and Aggregation Considerations

Dynamic Cell Calculations: Dynamic cell calculations can reference other dynamic cell calculations and stored calculations but should not be used by stored calculations because they do not naturally aggregate parent members. For example, if a parent member in an Account, Flow, or User Defined dimension has a child member calculated by a dynamic calculation formula, the parent excludes the child member from the aggregated amount. Achieve aggregation by writing another dynamic calculation on the parent member.   

Stored Calculations: Stored calculations can reference other stored calculations and parent members aggregate naturally.

TIP: You do not need to write a stored calculation to add or subtract individual members.  Instead, create an alternative member hierarchy and use the Aggregation Weight property, set to -1.0 to negate. This aggregated value is dynamic and supports drill-down.

Stored Calculation Evaluation Tools: You can access detailed process logging information. Run Force Consolidate With Logging and click Task Activity to analyze steps in the consolidation process to identify bottlenecks or errors for performance optimizations.  Examples:

  • Calculate with Logging

  • Translate with Logging

  • Consolidate with Logging

Formula Calculation Threshold Monitoring: Calculation time threshold values can be set in the Application Server Configuration File using the NumSecondsBeforeLoggingSlowFormulas setting to log formulas exceeding the specified threshold time.