Formula Errors

Formula problems can occur at several different stages:

Formula Definition
       │
       ▼
Syntax Validation
       │
       ├── Fails ──► Correct Formula Syntax
       │
       ▼
Dimension Build / Metadata Refresh
       │
       ├── Fails ──► Correct References or Dependencies
       │
       ▼
Query Execution
       │
       ├── Refused ──► Correct Unsupported Semantics / Scale
       │
       ▼
Calculated Result
       │
       ├── Unexpected ──► Check Context, Solve Order,
       │                  Security, Time Behavior
       ▼
Expected Result

The most important troubleshooting distinction is that a formula can parse successfully and still be unsuitable for execution.

VALIDATE_FORMULA is a syntax check. It confirms that the formula parses, but it does not prove that every construct in the formula can be executed by the query engine. Always test important formulas with a representative query before putting them into production.

Start with the Exact Formula

Before changing anything, capture:

• Cube name
• Dimension name
• Member name
• Formula source
• Solve order
• Exact validation, build, or query error
• Query POV that exposed the problem
• Executing Snowflake user
• Approximate time of the failure

When reviewing formulas from the built cube, use FORMULA_SOURCE.

For example:

SELECT DIM_NAME,
       CHILD,
       FORMULA_SOURCE,
       SRC_SOLVE_ORDER
FROM CASABASE_CUBE.SHARED_DATA.MYCUBE_DIMENSIONS
WHERE FORMULA_SOURCE IS NOT NULL
ORDER BY DIM_NAME, SORTORDER;

FORMULA_SOURCE contains the authored formula. The FORMULA column contains the engine’s internal compiled representation and is not intended for manual reading or editing.

Validate the Formula

For an individual formula, use VALIDATE_FORMULA.

For example:

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

Use validation to identify parsing and syntax problems before assigning or deploying the formula.

See Formula Validation.

Validation Success Does Not Guarantee Runtime Success

A successful VALIDATE_FORMULA result means the formula parses.

It does not mean:

• Every construct is supported by the calculation engine
• Every referenced member exists in the required context
• The formula is free of circular dependencies
• The formula will produce the intended business result
• The query using it is within calculation-scale limits

Conceptually:

VALIDATE_FORMULA
       │
       ▼
Formula Parses?
       │
       ├── No ──► Fix Syntax
       │
       └── Yes
              │
              ▼
       Test Real Query
              │
              ▼
       Supported at Runtime?
              │
              ├── No ──► Rewrite Formula
              │
              └── Yes ──► Validate Result

This distinction is particularly important for constructs that the parser recognizes but the member-formula execution model deliberately refuses.

Syntax Errors

If VALIDATE_FORMULA fails, inspect the formula for basic structural problems first.

Common examples include:

• Unbalanced brackets
• Unbalanced parentheses
• Malformed member references
• Incomplete expressions
• Invalid function syntax
• Invalid CASE structure
• Incorrect multidimensional-reference syntax

Start with the smallest expression that reproduces the error.

For example, simplify:

([Revenue] - [Cost of Sales]) / [Revenue]

to:

[Revenue] - [Cost of Sales]

and then add the remaining expression back incrementally.

This isolates the part of the formula that cannot be parsed.

Member References

Casabase Cube formulas can reference members directly.

For example:

[Revenue] - [Cost of Sales]

They can also reference a member in another dimension:

[PERIOD].[Jan]

or a specific multidimensional intersection:

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

These references are evaluated within the dimensional context of the query.

If a formula fails because of a member reference, verify:

• The member exists
• The dimension exists
• The member belongs to the intended dimension
• The dimension has been rebuilt after relevant metadata changes
• The reference uses current member names
• The multidimensional intersection is valid

Do not assume a display alias is interchangeable with the underlying member name.

Formula References a Newly Added Member

If a formula references a member that was recently added to a dimension definition, confirm that the affected dimension structures have been rebuilt.

Changing formula or dimension metadata does not require reloading fact data, but the relevant dimension structures must reflect the updated metadata before the new formula or member becomes part of the active model.

See Rebuilding Dimensions.

Circular Dependencies

Calculated members can reference other calculated members.

For example:

Gross Profit
     │
     ▼
Operating Income
     │
     ▼
Operating Margin

Dependencies are resolved automatically.

However, a cycle is invalid:

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

Circular formula dependencies are detected and rejected rather than being allowed to recurse indefinitely.

If a cycle is reported:

1. Identify the members participating in the cycle.
2. Determine which dependency is unintended.
3. Rewrite that formula to break the cycle.
4. Rebuild or refresh the applicable formula metadata.
5. Test the affected calculations again.

Do not try to fix a true dependency cycle by changing solve order. Solve order controls precedence; it does not make a circular dependency valid.

Self-References

A calculated member should not normally reference itself directly because that can create a dependency cycle.

When the formula needs the member’s natural aggregation before applying an adjustment, use the supported:

AGG_SELF()

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

For example:

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

See Formula Syntax and Common Formula Patterns.

Unsupported Formula Constructs

Some constructs may parse but are not supported in member formulas.

Examples include:

{TOTAL}
{TOTAL:DIM}

{PREV}
{NEXT}

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

.children
.descendants
.leaves
.members

STRTOMBR(...)

Set-based aggregate functions such as SUM, AVG, COUNT, MIN, MAX, MEDIAN, STDDEV, or RANK over member sets are also not part of the supported member-formula model.

If a query fails with one of these constructs, do not repeatedly retry it.

Rewrite the formula using the supported calculation model.

Replacing {TOTAL}

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

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

For example, a percent-of-total pattern can use an explicit tuple for the denominator rather than a generic {TOTAL} token.

See Common Formula Patterns.

Replacing {PREV} and {NEXT}

{PREV} and {NEXT} are not supported.

Use the supported time-navigation functions:

LAG
LEAD

instead.

These functions navigate according to the supported time-dimension model.

See Time Navigation.

Set Navigation Belongs in the POV

Functions such as:

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

represent member-set navigation.

Casabase Cube keeps set selection in the query POV rather than embedding those set operations inside member formulas.

For example, use a POV selection such as:

{"children":"Total Entity"}

when the query should return the children of a member.

Do not translate a query-selection requirement into a member formula.

Division by Zero

Protect denominators that can legitimately be zero.

The standard pattern is:

[Account].[NetIncome]
/
NULLIF([Account].[Revenue], 0)

NULLIF converts a zero denominator to NULL, allowing the result to represent no data rather than raising a divide-by-zero condition.

For example:

[Account].[Margin]
/
NULLIF([Account].[Revenue], 0)
* 100

Division already treats a missing denominator as NULL, but explicit NULLIF is recommended where the denominator can be zero. Formula validation can warn about division without this guard.

Formula Returns No Data

A formula can be syntactically and structurally valid but still return no data.

Check:

• Whether the referenced members have data at the current query intersection
• Whether a denominator resolves to zero or missing
• Whether the formula explicitly returns NULL
• Whether the POV resolves to the expected dimensional context
• Whether time-navigation references are valid at the requested period
• Whether row-level security removes the required data

Test the underlying stored members separately.

For example:

Calculated Member = No Data
        │
        ▼
Query Numerator Separately
        │
        ▼
Query Denominator Separately
        │
        ▼
Check Referenced Intersections

If the underlying stored values are also missing, continue with Query Returns No Data.

Formula Returns the Wrong Value

An incorrect calculated result does not necessarily mean the parser or calculation engine failed.

Check the formula in this order:

Formula Source
      │
      ▼
Referenced Members
      │
      ▼
Query POV
      │
      ▼
Security Scope
      │
      ▼
Dependency Chain
      │
      ▼
Solve Order
      │
      ▼
Time Behavior
      │
      ▼
Expected Result

Use a small, known data intersection when possible.

Avoid validating a complex business formula only at a high-level total. A leaf or narrowly scoped test case is usually easier to reconcile.

Check Solve Order

Solve order controls precedence when calculated members interact, especially when calculated members from different dimensions intersect.

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

For example:

Calculated Member A
        +
Calculated Member B
        │
        ▼
Same Cell Intersection
        │
        ▼
Solve Order
        │
        ▼
Final Result

If a formula is individually correct but returns an unexpected result when another calculated member is present, inspect the solve orders of both members.

Do not assign arbitrary solve-order values merely to make one test case pass. Determine which calculation should logically take precedence.

See Solve Order & Dependencies.

Solve Order Is Not Dependency Resolution

If:

Operating Income

references:

Gross Profit

you do not necessarily need to assign solve order just because one formula references another.

Casabase Cube resolves formula dependencies automatically. Solve order is primarily for calculation precedence when calculated members interact.

This distinction is important when troubleshooting:

Formula A references Formula B
        │
        ▼
Dependency Resolution

Formula A and Formula B
intersect from different dimensions
        │
        ▼
Solve Order

Check Query Grain

Formula behavior can depend on the grain at which it is evaluated.

Some constructs are meaningful only at particular hierarchy positions or grains. The engine can refuse a request rather than return a misleading value when the requested evaluation is not valid.

If a formula works for a leaf member but fails or behaves differently at an aggregate:

1. Determine whether the formula is intended to operate at both grains.
2. Review hierarchy-aware logic.
3. Consider ISLEAF(...) or another supported hierarchy test where appropriate.
4. Use AGG_SELF() where the aggregate case should fall back to natural aggregation.

See Hierarchy-Aware Functions.

Check Hierarchy-Aware Functions

Casabase Cube supports hierarchy-aware functions including:

ISLEAF
ISCHILD
ISDESC
ISGEN
ISLEV
HASUDA

These allow formulas to react to the current member’s hierarchy context.

If a hierarchy-aware formula returns an unexpected value, verify:

• Which member is current
• The member’s actual level or generation
• The intended strict vs. inclusive relationship
• UDA assignments
• Whether the query is at the grain expected by the formula

See Hierarchy-Aware Functions.

Time Navigation Errors

If a formula uses time navigation, verify that:

• The referenced dimension is configured as the appropriate time dimension
• The requested period exists
• Sort order reflects the intended chronological sequence
• The navigation offset is correct
• The query context contains the dimensions required by the calculation

Use supported LAG and LEAD constructs rather than Essbase-style {PREV} or {NEXT}.

See Time Navigation.

Time Balance Is Not Formula Logic

Do not recreate time-balance behavior inside a member formula unless the business requirement actually calls for a custom calculation.

Time-balance behavior is configured through the dimension’s time-balance metadata.

Supported behavior includes:

FLOW
LAST
FIRST
AVERAGE

with supported skip behavior such as:

NONE
MISSING
ZEROS
MISSING_AND_ZEROS

The effective settings are exposed through the dimension metadata.

If a balance-sheet account is aggregating incorrectly across time, verify its time-balance configuration before rewriting its formula.

Calculated Members on Too Many Dimensions

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

A query that exceeds that limit is refused rather than approximated. Broad member selections combined with calculated members can also encounter calculation-scale limits.

If this occurs:

• Use stored members on additional dimensions where appropriate
• Narrow broad member expansions
• Reduce the query to the dimensional grain actually required
• Surface the returned error rather than automatically retrying the same request

This is a query-design constraint, not a syntax error in the individual formula.

Formula Works for One User but Not Another

Check row-level security before assuming the formula is incorrect.

Formulas are evaluated against the data permitted to the executing user. Users with different security scopes can therefore receive different valid calculated results from the same formula.

For example:

User A
North America Access
        │
        ▼
Permitted Data
        │
        ▼
Formula
        │
        ▼
Result A


User B
Global Access
        │
        ▼
Permitted Data
        │
        ▼
Formula
        │
        ▼
Result B

This is especially important for:

• Ratios
• Variances
• Percent-of-total calculations
• Allocations
• Metrics whose denominator spans a broad population

When reconciling results between users, compare their security scope before changing the formula.

See Access Control.

Migrated Essbase or Cloud EPM Formula Fails

Supported member formulas contained in the native Essbase outline (.otl) can be imported into Casabase Cube.

However, Essbase has a broad calculation language, and not every construct maps directly to the Casabase Cube member-formula model. Migrated formulas should therefore be reviewed and tested, especially when the source uses complex Essbase-specific behavior.

Essbase-specific source syntax can include constructs such as:

@ISMBR
@ISCHILD
@ISDESC
@ISLEV
@ISUDA
@SUMRANGE
@CURRMBR
@ISATTRIBUTE

IF / ELSEIF / ENDIF

NONEMPTYTUPLE(...)

and Essbase member-range or quoted-arrow syntax.

These constructs are source/import syntax, not syntax that should normally be authored directly as Casabase Cube formulas. Supported constructs are translated during import. Some source functions may require manual translation.

Re-Importing Migrated Formulas

During Essbase or Cloud EPM migration, formula translation is normally performed as part of the rebuild/import workflow.

If formula translation problems are corrected in the imported source metadata, formulas can be re-imported for the affected dimension without necessarily repeating the entire migration.

For example:

CALL CUBE.IMPORT_FORMULAS(
    '<CubeName>',
    '<DimensionName>'
);

For cubes with formulas across many dimensions, IMPORT_ALL_FORMULAS can refresh formulas across the cube. The migration workflow uses the bulk process automatically during the applicable rebuild flow.

After re-importing, validate representative calculations against the source system.

Formula Changed but Query Still Uses Old Behavior

If formula source metadata was changed outside the normal formula-management path, compiled formula metadata may need to be refreshed.

The supported formula workflow includes:

CALL CUBE.REFRESH_FORMULA_METADATA('MYCUBE');

for refreshing compiled formula metadata after editing source formulas directly.

If the formula is part of dimension definition metadata, also follow the documented dimension rebuild workflow where applicable.

Avoid manually editing the engine’s compiled FORMULA representation.

Run Health Check

Health Check can identify formula-related problems, including dependency cycles and other formula-health conditions.

Run:

CALL CUBE.HEALTH_CHECK('MYCUBE');

Then review non-OK findings:

SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE STATUS <> 'OK';

Formula-related Health Check findings should be addressed before troubleshooting performance or downstream reporting behavior.

The current troubleshooting guidance identifies formula-health conditions including syntax, bracket, CASE, empty-formula, dimension-association, division-safety, and untranslated Essbase syntax problems.

See Using Health Check.

Estimate Formula Cost

A formula can be correct but expensive.

For a cube with complex formulas, use:

CALL CUBE.FORMULA_COST_ESTIMATOR('MYCUBE');

to investigate formula cost when performance is the symptom rather than correctness.

Do not rewrite a correct formula solely because a broad query is slow until you have separated:

Formula Complexity
       │
       +
Query Breadth
       │
       +
Calculated Dimensions
       │
       ▼
Observed Query Cost

Continue with the performance troubleshooting documentation when the formula is valid but query execution is slow.

Recommended Diagnostic Sequence

For a formula problem:

Capture Formula + Exact Error
          │
          ▼
VALIDATE_FORMULA
          │
          ├── Fails
          │     │
          │     ▼
          │ Fix Syntax
          │
          ▼
Check Member References
          │
          ▼
Check Dependencies / Cycles
          │
          ▼
Check Unsupported Constructs
          │
          ▼
Run Representative Query
          │
          ├── Refused
          │     │
          │     ▼
          │ Follow Runtime Error
          │
          ▼
Check Query Context
          │
          ▼
Check Security Scope
          │
          ▼
Check Solve Order
          │
          ▼
Check Time / Hierarchy Behavior
          │
          ▼
HEALTH_CHECK
          │
          ▼
Reconcile Expected Result

Change one variable at a time so the cause of the formula problem remains clear.

If the Problem Remains

Generate support information for the affected cube:

CALL CUBE.GENERATE_SUPPORT_INFO('MYCUBE');

Collect:

• Cube name
• Dimension name
• Member name
• FORMULA_SOURCE
• Solve order
• Exact validation result
• Exact query error
• Query POV
• Expected result
• Actual result
• Whether the formula was migrated from Essbase or Cloud EPM
• Relevant Health Check findings
• Approximate time of the failure

For migrated formulas, also include the original source formula when available.

Do not include passwords, private keys, authentication tokens, or other credentials.

See Getting Additional Help.

Related Topics

• Formula Overview
• Formula Syntax
• Formula Editor
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies
• Formula Validation
• Common Formula Patterns
• Best Practices
• Query Returns No Data
• Dimension Validation & Build Errors
• Using Health Check