Formula Validation

Formula validation is an important part of developing calculated members, but validation and execution testing answer different questions.

VALIDATE_FORMULA is a syntax check. It confirms that Casabase Cube can parse the expression. It does not prove that every parsed construct is supported by the query engine at runtime, and it does not prove that the formula produces the intended business result.

A reliable workflow is:

Author Formula
     │
     ▼
VALIDATE_FORMULA
  Syntax Check
     │
     ▼
SET_FORMULA
     │
     ▼
Representative Query
     │
     ▼
Verify Result

Validation in the Formula Editor

The Formula Editor provides validation feedback while a formula is being created or modified.

Use the editor to:

1. Select the cube and member.
2. Create or modify the Cube Formula.
3. Review syntax or expression feedback.
4. Correct identified problems.
5. Save the formula.
6. Test the calculated member in Query Builder or with QUERY_CUBE.

Formula changes made through the supported formula-management interface take effect without requiring a dimension rebuild.

See Formula Editor for the editing workflow.

Programmatic Validation

Use:

CALL CUBE.VALIDATE_FORMULA(
    'MYCUBE',
    'Margin_Pct',
    '[Account].[Margin] / NULLIF([Account].[Revenue], 0) * 100',
    'VIEW'
);

The arguments identify:

Cube
Member
Formula Expression
Dimension

Use the procedure signature provided by your installed Casabase Cube version when integrating validation into automated workflows.

Validation Is a Syntax Check

A successful VALIDATE_FORMULA result means that the expression parses.

It does not mean that:

• Every parsed construct is executable by the query engine.
• Every referenced calculation is meaningful at the query grain.
• The formula is free of runtime scale limits.
• The formula implements the intended business definition.
• Every combination with calculated members from other dimensions is valid.

This distinction is especially important for constructs that are recognized by the parser but intentionally refused during query execution.

Constructs That Can Validate but Fail at Runtime

The following native-formula constructs are not supported for execution even though they can parse:

Construct	Recommended Alternative
{TOTAL} / {TOTAL:DIM}	Explicit tuple references
{PREV} / {NEXT}	LAG / LEAD
Member-set navigation such as CHILDREN, DESCENDANTS, LEAVES, MEMBERS, SIBLINGS, CROSSJOIN	POV set selection, AGG_SELF(), or Dynamic Time Series
STRTOMBR(...)	Dynamic member syntax [DIM].[{expression}]
Set aggregates such as SUM, AVG, COUNT, MIN, MAX, MEDIAN, STDDEV, or RANK over member sets	AGG_SELF(), Dynamic Time Series, or explicit coordinate arithmetic

A formula using one of these constructs can validate successfully and still be refused when queried.

See Formula Syntax for the supported native language.

Member and Dimension References

Use qualified member references where practical:

[ACCOUNT].[Revenue]

Cross-dimensional coordinate references can also be used:

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

A formula can parse correctly even when a business assumption about a member or hierarchy is wrong. Use the Formula Editor member picker and representative queries to verify the intended references.

Hierarchy-Aware Functions

Supported hierarchy-aware functions include:

ISLEAF
GENERATION
LEVEL
PARENT
ISGEN
ISLEV
ISCHILD / ISICHILD
ISDESC / ISIDESC
ISANCEST / ISIANCEST
ISPARENT / ISIPARENT
ISSIBLING / ISISIBLING
HASUDA
ISATTR

ISATTR has an additional execution constraint: attribute associations exist at the leaf level of the associated base dimension. If the base dimension is evaluated at aggregate grain, Casabase Cube refuses the unsupported usage rather than silently returning a misleading value.

See Hierarchy-Aware Functions.

Time Navigation

LAG and LEAD are supported only for dimensions recognized as time dimensions.

For example:

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

A time dimension can be recognized because it is the declared time dimension, participates in Dynamic Time Series configuration, uses a recognized time-oriented name, or is explicitly admitted through the NAV_TIME_DIMS cube variable.

Using LAG or LEAD against an ineligible dimension is refused at query time.

See Time Navigation.

Formula Dependencies

A calculated member can reference another calculated member.

For example:

Gross Profit
     │
     ▼
Gross Margin %

Casabase Cube resolves formula dependencies automatically.

Circular references are not valid. They are detected and rejected during build processing, and HEALTH_CHECK reports formula dependency problems.

For example:

Formula A
    │
    ▼
Formula B
    │
    ▼
Formula C
    │
    └────────► Formula A

has no valid dependency order.

See Solve Order & Dependencies.

Division and Missing Values

Division automatically protects against a missing denominator by returning NULL.

A legitimate zero denominator should still be guarded explicitly.

Preferred pattern:

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

VALIDATE_FORMULA can warn when division does not include a NULLIF guard.

In addition and subtraction, missing values behave as zero.

Validate Query Grain

Some formula constructs depend on the query grain.

For example, ISATTR is meaningful only at leaf grain on the associated base dimension.

A formula should therefore be tested using the same kinds of POVs that production workloads will use.

Include tests for:

• Leaf-level coordinates
• Parent or aggregate coordinates
• Calculated members
• Cross-dimensional calculated-member intersections
• Security-restricted users where applicable
• Missing and zero values
• Time boundaries for LAG and LEAD

Query Limits Still Apply

Formula validation does not remove query-engine limits.

A query supports calculated members on at most two dimensions.

Broad member selections combined with calculated members are also subject to calculation-scale safeguards.

When a request exceeds a supported limit, Casabase Cube refuses the query with an explanatory error rather than returning an approximate result.

Health Check

Use:

CALL CUBE.HEALTH_CHECK('MYCUBE');

for cube-wide diagnostics.

Health Check can identify formula conditions such as syntax problems, bracket or CASE structure problems, and dependency issues across the cube.

Use VALIDATE_FORMULA for an individual expression and Health Check for broader cube diagnostics.

Functional Testing

Syntax validation cannot determine whether a formula implements the correct business rule.

For example:

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

can be valid syntax while still being the wrong definition of Gross Profit for a particular organization.

For important calculations, compare Casabase Cube results against known expected values.

A useful pattern is:

Known Business Result
        │
        ▼
Representative POV
        │
        ▼
Casabase Cube Query
        │
        ▼
Compare

Migration Validation

After importing formulas from Oracle Essbase or Oracle Cloud EPM:

• Review migration warnings.
• Review formulas that were translated.
• Review formulas removed from stored-data members.
• Check for unsupported source functions.
• Run Health Check.
• Test important calculations against the source application.

See Compatibility Notes for known migration differences.

Key Takeaways

• VALIDATE_FORMULA is a syntax check.
• A formula can validate successfully and still be refused at query time.
• Always run a representative query after validation.
• Formula dependencies are resolved automatically.
• Circular dependencies are rejected during build processing and reported by Health Check.
• ISATTR requires leaf grain on the associated base dimension.
• LAG and LEAD are limited to recognized time dimensions.
• A query supports calculated members on at most two dimensions.
• Validation cannot determine whether the business logic itself is correct.
• Migrated formulas should be tested against known source-system results.

Related Topics

• Formula Syntax
• Formula Editor
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies
• Common Formula Patterns
• Best Practices
• Compatibility Notes