Skip to content
Casabase CubeCasabase Cube Documentation

Formula Overview

Formulas in Casabase Cube define calculations associated with members in a cube hierarchy.

Unlike stored data, the value of a member with a formula is calculated dynamically when the member is queried. This allows business calculations such as margins, variances, ratios, rates, and other derived measures to remain part of the multidimensional model without requiring calculated values to be stored in the underlying fact table.

Member Formulas

Section titled “Member Formulas”

Every formula in Casabase Cube is associated with a member.

For example, an ACCOUNT hierarchy might contain:

ACCOUNT
│
├── Revenue
├── Cost of Sales
├── Gross Profit
└── Gross Margin %

Revenue and Cost of Sales may ultimately resolve to data from the underlying fact table, while Gross Profit could have the formula:

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

and Gross Margin % could have:

[ACCOUNT].[Gross Profit]
/
NULLIF([ACCOUNT].[Revenue], 0)
* 100

The calculated members participate in the hierarchy like other members, but their values are determined by their formulas rather than stored as rows in the source data.

Query-Time Evaluation

Section titled “Query-Time Evaluation”

Member formulas are evaluated dynamically when the member carrying the formula is included in a query.

Conceptually:

Query POV
   │
   ▼
Resolve Selected Members
   │
   ▼
Hierarchy Aggregation
        +
Formula Evaluation
        +
Multidimensional Context
   │
   ▼
Return Query Results

There is no requirement to pre-calculate and store the formula result.

The same formula can produce different results depending on the Entity, Period, Scenario, Product, or other coordinates being evaluated because formula processing occurs within the multidimensional context of the query.

For example:

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

can calculate Gross Profit independently for every multidimensional intersection requested by the query.

Formulas and Hierarchy Aggregation

Section titled “Formulas and Hierarchy Aggregation”

Member formulas work with the multidimensional hierarchy and aggregation engine.

A formula can reference stored members, upper-level members, or other calculated members. Referenced values are resolved using the cube’s hierarchy structure and the current query context.

A calculated member can also explicitly access the value it would have received from normal hierarchy aggregation by using:

AGG_SELF()

AGG_SELF() returns the member’s natural operator-weighted roll-up without evaluating that member’s formula.

This makes it possible to create calculations that behave differently at different hierarchy levels while still using the hierarchy’s normal aggregation where appropriate.

For example:

CASE
    WHEN ISLEAF(PERIOD) THEN
        [VIEW].[Periodic]
    ELSE
        AGG_SELF()
END

The formula uses a specific calculation at leaf level and the member’s normal hierarchy roll-up at upper levels.

See Formula Syntax for additional information about AGG_SELF().

Multidimensional Calculations

Section titled “Multidimensional Calculations”

Formulas are evaluated within the current multidimensional context of the query.

A formula can reference another coordinate by specifying a member:

[SCENARIO].[Actual]

Dimensions that are not explicitly referenced remain at their current query coordinates.

Formulas can also reference several dimensions simultaneously using tuples or arrow coordinate shifts.

For example:

[ACCOUNT].[Revenue]
  ->[SCENARIO].[Actual]
  ->[VIEW].[Periodic]

This allows calculations to retrieve rates, drivers, comparison scenarios, and other values from different locations in the cube while preserving the surrounding query context.

The complete coordinate reference syntax is covered in Formula Syntax.

Formula Dependencies

Section titled “Formula Dependencies”

A calculated member can reference another calculated member.

For example:

Revenue ───────────────┐
                      │
Cost of Sales ─────────┤
                      ▼
                 Gross Profit
                      │
                      ▼
                 Gross Margin %

If Gross Margin % references Gross Profit, Casabase Cube recognizes that Gross Profit must be resolved before Gross Margin % can be calculated.

Formula dependencies are detected automatically.

Circular dependencies are not allowed. If formulas form a dependency cycle, Casabase Cube detects and reports the problem rather than attempting to evaluate an ambiguous calculation sequence.

See Solve Order & Dependencies for details.

Hierarchy-Aware Calculations

Section titled “Hierarchy-Aware Calculations”

Formulas can make decisions based on the current member’s position or metadata within a hierarchy.

For example, formulas can determine whether the current member:

  • Is a leaf member
  • Is at a particular level or generation
  • Is a child, descendant, ancestor, parent, or sibling of another member
  • Has a particular User-Defined Attribute (UDA)
  • Is associated with a particular attribute member

This allows formula behavior to change dynamically based on the cube’s metadata rather than requiring every member to be hard-coded into the expression.

See Hierarchy-Aware Functions for the supported hierarchy and metadata functions.

Time-Aware Calculations

Section titled “Time-Aware Calculations”

Casabase Cube supports relative time navigation through LAG and LEAD.

These functions allow formulas to reference earlier or later members in dimensions recognized for time navigation, such as PERIOD or YEARS.

For example:

[YEARS].[LAG(YEARS, 1)]

references the previous member in the YEARS time sequence while preserving the current coordinates of the other dimensions.

Period-to-date calculations such as YTD, QTD, and MTD are handled separately through Casabase Cube’s Dynamic Time Series capabilities rather than hand-written member formulas.

See Time Navigation for navigation behavior and Common Formula Patterns for practical time-based calculations.

Formula Evaluation

Section titled “Formula Evaluation”

Formula evaluation occurs as part of cube query processing.

Several mechanisms can influence how calculations are evaluated:

  • Formula dependencies are resolved automatically when one calculated member references another.
  • Solve order controls precedence when calculated members from different dimensions intersect.
  • Query grain determines the dimensional context in which a formula is evaluated.
  • Hierarchy aggregation supplies stored and aggregated values referenced by formulas.
  • Formula settings determine whether member formulas are applied for a query.

By default, member formulas are applied during query processing.

In Query Builder, Apply Member Formulas can be disabled when a query should execute without evaluating member formulas.

See Solve Order & Dependencies for calculation ordering.

Creating and Managing Formulas

Section titled “Creating and Managing Formulas”

Formulas can be associated with members through several mechanisms, including:

  • The Formula Editor in Edit Dimensions
  • Formula definitions supplied through hierarchy metadata
  • The SET_FORMULA stored procedure

Formulas can also be validated through the Formula Editor or programmatically.

See:

Formulas and Essbase Migration

Section titled “Formulas and Essbase Migration”

Casabase Cube’s member formula model is conceptually similar to dynamic member formulas in Oracle Essbase.

During an Oracle Essbase or Oracle Cloud EPM migration, supported formulas from the source outline can be translated into native Casabase Cube formula syntax.

Not every Essbase formula construct has a native equivalent. Review Compatibility Notes for migration-specific formula differences.

Native Casabase Cube formulas and Essbase formula syntax are not the same language.

Essbase constructs such as @ functions and Essbase calculation syntax are treated as import syntax and translated during migration rather than authored as native Casabase Cube formulas.

Conceptually:

Essbase Member Formula
        │
        ▼
Formula Translation
        │
        ▼
Native Cube Formula
        │
        ▼
Query-Time Evaluation

This allows migrated cubes to preserve applicable calculation logic while executing those calculations through the Casabase Cube query engine.

What Formulas Are Not

Section titled “What Formulas Are Not”

Member formulas define the calculated value of hierarchy members.

They are not intended to replace every type of cube processing.

For example:

  • Member-set selection such as children and descendants belongs in the query POV.
  • Period-to-date accumulation such as YTD, QTD, and MTD belongs in Dynamic Time Series.
  • Materializing query results is handled through query functionality rather than formulas.
  • Essbase calculation syntax is translated during migration rather than used as native Casabase Cube formula syntax.

Keeping these responsibilities separate allows formulas to remain focused on dynamic member calculations within the multidimensional model.

Section titled “Related Topics”