Formulas

What are formulas in Casabase Cube?

Formulas define calculated members and other multidimensional calculation logic.

They are stored as part of the cube’s multidimensional metadata and evaluated dynamically during query processing.

Conceptually:

Cube Data
    +
Hierarchies
    +
Aggregation Rules
    +
Formulas
    +
Solve Order
    │
    ▼
Multidimensional Calculation
    │
    ▼
Requested Result

Calculated results do not need to be physically stored for every possible multidimensional intersection.

See Formula Overview and Formula Capabilities.

Are formulas calculated when data is loaded?

No.

Member formulas are evaluated dynamically when the cube is queried.

The same formula can therefore return different values depending on:

The members selected by the query
Hierarchy relationships
Aggregation operators
Other calculated members
Cross-dimensional references
Solve order
User security scope
The dimensional context of the query

This is part of Casabase Cube’s query-time multidimensional calculation model.

Do calculated members store data?

No.

A calculated member carrying a formula does not need to store a calculated value for every possible intersection.

For example:

Gross Profit
=
Revenue - Cost of Sales

can be evaluated dynamically for:

January
February
March

Actual
Budget
Forecast

North America
Europe
Asia Pacific

without storing a separate Gross Profit value for every combination.

What types of formula references are supported?

Casabase Cube supports multidimensional formula references such as:

Member references
Cross-dimensional references
Multidimensional intersections
Dynamic member expressions
Formula dependencies
Hierarchy-aware functions
Cube variables
Supported time-navigation functions

For example, a simple member formula can reference:

[Revenue] - [Cost of Sales]

A formula can also reference another dimension:

[PERIOD].[Jan]

or a specific multidimensional intersection:

[YEARS].[FY24]->[PERIOD].[Jan]

See Formula Syntax.

Can a formula reference a member in another dimension?

Yes.

For example:

[PERIOD].[Jan]

references the Jan member of the PERIOD dimension.

Cross-dimensional references allow a calculation to retrieve values from a dimensional context other than the current coordinate.

Can a formula reference multiple dimensions at once?

Yes.

A formula can identify a multidimensional intersection.

For example:

[YEARS].[FY24]->[PERIOD].[Jan]

can explicitly reference:

YEARS  = FY24
PERIOD = Jan

rather than relying entirely on the current query context.

Can a formula reference another calculated member?

Yes.

Formula dependencies are resolved automatically by Casabase Cube.

For example:

Revenue
   │
   ├── Cost of Sales
   │
   ▼
Gross Profit
   │
   ├── Operating Expenses
   │
   ▼
Operating Income

Operating Income can reference Gross Profit, even though Gross Profit is itself calculated.

This allows larger business calculations to be constructed from reusable calculated members.

Do I need to assign solve order whenever one formula references another?

Not necessarily.

Casabase Cube resolves formula dependencies automatically.

Solve order is primarily used to control calculation precedence when calculated members interact, especially when calculated members from different dimensions intersect.

For example:

Calculated Member A
        │
        ├──── intersects ────┐
        │                    │
Calculated Member B          │
        │                    │
        └────────────┬───────┘
                     ▼
                 Solve Order
                     │
                     ▼
                Final Result

A higher solve order is evaluated later and takes precedence at the intersection.

See Solve Order & Dependencies.

What is solve order?

Solve order controls precedence when calculations intersect.

When calculated members from different dimensions apply to the same cell, solve order determines which calculation is applied later.

Conceptually:

Calculation A
     +
Calculation B
     │
     ▼
Solve Order
     │
     ▼
Final Cell Value

Higher solve order means:

Evaluated Later
      =
Higher Precedence

Solve order works together with dependency resolution and query context.

Is solve order the same as Essbase two-pass calculation?

No.

Casabase Cube uses solve order to control calculation precedence.

Imported source metadata can retain two-pass-related properties where applicable, but Casabase Cube does not expose two-pass calculation as a separate calculation mechanism that users must execute.

For calculation design, use solve order and the current Casabase Cube formula model.

See Solve Order & Dependencies.

What are hierarchy-aware functions?

Hierarchy-aware functions allow a formula to respond to the current member’s position or relationships within a dimension.

Supported capabilities include functions such as:

Function	Purpose
`ISLEAF`	Tests whether the current member is a bottom-level member.
`ISCHILD`	Tests a child relationship.
`ISDESC`	Tests a descendant relationship.
`ISGEN`	Tests member generation.
`ISLEV`	Tests member level.
`HASUDA`	Tests whether a member has a specified UDA.

For example:

ISLEAF(PERIOD)

can be used when a calculation should behave differently at bottom-level periods than at aggregated periods.

See Hierarchy-Aware Functions.

What is the difference between IS… and ISI… hierarchy functions?

Where both forms are supported, functions beginning with:

IS...

test a strict hierarchy relationship.

Functions beginning with:

ISI...

are inclusive and also return true when the current member is the referenced member itself.

Use the exact function behavior documented in Hierarchy-Aware Functions.

Can formulas use UDAs?

Yes.

User Defined Attributes can be used as member metadata that influences formula behavior.

For example, members might have UDAs such as:

Revenue
Expense
Statistical
NoInput

A formula can use:

HASUDA(...)

to apply different business logic based on those classifications.

See Hierarchy-Aware Functions.

What does {ROW} mean?

{ROW} represents the current cell’s own stored or naturally rolled-up value at the current coordinate before the formula’s own adjustment is applied.

For example:

{ROW} + [Account].[Adjustment]

can add an adjustment to the current value.

Another example:

{ROW} * 1.05

can apply a percentage adjustment.

See Formula Syntax for the exact semantics.

What is AGG_SELF()?

AGG_SELF() returns the natural aggregation for the member carrying the formula, without re-entering that member’s formula.

It is useful when a calculated member should override its normal aggregation only under certain conditions.

For example:

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

Conceptually:

Formula Member
      │
      ├── Condition Met
      │       └── Custom Calculation
      │
      └── Condition Not Met
              └── Natural Rollup

A normal self-reference could create a dependency cycle; AGG_SELF() provides the supported way to retrieve the member’s natural rollup.

Is {TOTAL} supported in member formulas?

No.

{TOTAL} and {TOTAL:DIM} are not supported member-formula constructs.

If a formula needs a total, use an explicit multidimensional reference that pins the applicable dimension or dimensions to their required top members.

See Common Formula Patterns for supported percent-of-total patterns.

Are {PREV} and {NEXT} supported?

No.

Use the supported time-navigation functions such as LAG and LEAD rather than {PREV} or {NEXT}.

See Time Navigation.

Can I use CHILDREN(), DESCENDANTS(), or other member-set functions inside a formula?

No.

Set navigation belongs to the query POV, not to member formulas.

Constructs such as:

CHILDREN(...)
DESCENDANTS(...)
LEAVES(...)
MEMBERS(...)
SIBLINGS(...)
CROSSJOIN(...)

should not be used as member-formula set operations.

For natural descendant aggregation, use the hierarchy model or AGG_SELF() as appropriate.

For member selection in a query, use the supported POV operators.

See:

Can formulas construct a member name dynamically?

Yes.

Casabase Cube supports dynamic member expressions where the target member depends on the current coordinate.

For example:

[ICP].[{'ICP_' || ENTITY}]

can construct a member reference from the current ENTITY value.

If:

ENTITY = 6378

the expression can resolve:

[ICP].[ICP_6378]

See Formula Syntax for dynamic-member syntax.

Can formulas use cube variables?

Yes.

Formula syntax can reference configured cube variables.

This allows business logic to use centrally managed values rather than hard-coding a member everywhere.

For example, a variable can represent a current year, scenario, or another configurable business context.

See:

Does Casabase Cube support CASE expressions?

Yes.

CASE expressions can be used to apply conditional formula logic.

For example:

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

This is useful when calculation behavior depends on hierarchy position, metadata, or multidimensional context.

See Formula Syntax.

How do I avoid divide-by-zero problems?

Use the supported defensive formula patterns documented for division.

Rather than relying on unchecked division, guard the denominator so a zero value does not produce an invalid calculation.

See Best Practices and Common Formula Patterns for the current recommended patterns.

Are formulas evaluated before or after aggregation?

Formula evaluation and hierarchy aggregation work together as part of the multidimensional calculation engine.

They should not be treated as two completely independent processing phases.

A calculated result can depend on:

Hierarchy relationships
Aggregation operators
Other formulas
Cross-dimensional references
Solve order
Query context

Casabase Cube resolves these elements together to produce the requested result.

How does row-level security affect formulas?

Formulas are evaluated within the executing user’s permitted security scope.

Conceptually:

Requested Query
      │
      ▼
Apply Security
      │
      ▼
Permitted Data
      │
      ▼
Aggregation
      +
Formula Evaluation
      │
      ▼
User Result

A formula does not bypass row-level security.

This also means the same formula can return different valid values for users with different security scopes.

See Access Control and Security Model.

Can formulas expose data a user is not allowed to see?

No.

Calculated members are evaluated within the user’s permitted data scope.

Publishing a calculated member does not create a privilege-escalation path around Casabase Cube security.

However, the meaning of a calculated result can depend on the viewer’s security scope.

For metrics that only make sense against a complete population, administrators should consider whether restricted users should use that metric.

Can a formula produce different values for different users?

Yes.

If users have different security scopes, a formula can operate on different permitted data.

For example:

User A → North America only
User B → Global access

A ratio, variance, or share-of-total calculation can therefore produce different valid values for the two users even when they run the same query.

When reconciling a calculation discrepancy on a secured cube, compare security scope before assuming the formula is incorrect.

How are formulas imported from Essbase or Cloud EPM?

When migrating Oracle Essbase or Oracle Cloud EPM, supported formula metadata in the native Essbase outline (.otl) can be imported into Casabase Cube as part of the multidimensional model.

You do not need to recreate every supported source formula manually after migration.

See:

Can I create formulas without migrating from Essbase?

Yes.

Formulas can be defined directly in Casabase Cube for Snowflake-native models as well as migrated models.

They become part of the cube’s multidimensional metadata and are evaluated by the same calculation engine.

Do formula changes require reloading fact data?

No.

Changing formula metadata does not require reloading the cube’s fact data.

Where formula metadata is part of a dimension definition, the affected dimension structures must be rebuilt so the updated formula becomes part of the active model.

Conceptually:

Formula Metadata Change
        │
        ▼
Rebuild Dimension
        │
        ▼
Updated Formula Available


Fact Data
        │
        └── No Reload Required
           solely because formula changed

See Rebuilding Dimensions.

How are formulas validated?

VALIDATE_FORMULA is a syntax check. It confirms that Casabase Cube can parse the expression, but successful validation does not guarantee that every parsed construct is supported by the query engine at runtime.

After validation, test the calculated member with a representative cube query. Circular formula dependencies are rejected during build processing rather than allowed to loop indefinitely during query execution.

Health checks can also identify formula syntax, structure, and dependency problems across the configured cube.

See:

What happens if formulas contain a circular dependency?

Circular formula dependencies are not allowed.

For example:

Formula A → Formula B
Formula B → Formula C
Formula C → Formula A

creates a cycle.

Casabase Cube detects circular dependencies and rejects the invalid configuration rather than allowing the query engine to recurse indefinitely.

Are all syntactically valid constructs supported at query time?

No.

Some constructs can be recognized by the parser but are deliberately refused by the query engine because they cannot be evaluated correctly within the supported member-formula model.

Casabase Cube follows a refusal-over-approximation principle: if a correct result cannot be produced, the engine returns an explanatory error rather than silently returning a misleading result.

See Formula Validation and Formula Syntax.

Is there a limit on calculated dimensions in one query?

Yes.

The current calculation engine supports calculated members on at most two dimensions in a single query.

A query exceeding that limit is refused rather than approximated.

Broad member selections combined with calculated members can also be subject to calculation-scale limits.

Use stored members on additional dimensions or narrow the requested selection when appropriate.

Why might a formula work at one query grain but not another?

Formulas are evaluated against the query’s multidimensional grain.

Some formula constructs only have meaningful semantics at particular grains.

If an expression cannot be evaluated correctly at the requested grain, Casabase Cube can refuse the query rather than return an artificial zero or otherwise misleading result.

Does Time Balance belong in member formulas?

Time Balance is primarily dimension metadata rather than formula logic.

Time Balance properties define how applicable members behave across the configured time dimension.

This is separate from formulas such as custom period navigation or calculated time measures.

See:

Where should I start with formulas?

Start with: