Best Practices

Well-designed member formulas are easier to understand, maintain, validate, and troubleshoot as cubes, hierarchies, and business requirements evolve.

The following practices help keep calculation logic clear, reusable, and predictable.

Keep Formulas Focused

A member formula should represent a clear business calculation.

For example:

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

clearly expresses Gross Profit.

Avoid combining unrelated business rules into a single formula simply because they can be expressed together. Focused formulas are easier to understand, test, and maintain.

When part of a calculation represents an established business metric, consider defining that metric as its own calculated member and reusing it.

Reuse Established Calculated Members

Calculated members can reference other calculated members.

For example, if Gross Profit is already defined as:

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

a Gross Margin formula can reference it directly:

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

This keeps the definition of Gross Profit in one place and makes the relationship between calculations clear.

Reuse calculated members when there is a genuine business dependency. Avoid creating unnecessary dependency chains simply to break an expression into smaller pieces.

Casabase Cube detects formula dependencies and determines the required evaluation sequence automatically.

See Solve Order & Dependencies for details.

Use Explicit Multidimensional References

Use qualified member references to make the intended dimensional coordinate clear:

[ACCOUNT].[Revenue]

When a calculation needs to change more than one coordinate, use a cross-dimensional reference:

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

Dimensions that are not explicitly referenced retain their current query context.

For example:

[SCENARIO].[Actual] - [SCENARIO].[Budget]

changes only the Scenario coordinate. Account, Entity, Period, Year, and other dimensions remain at their current intersections.

Specify only the coordinates the calculation intentionally needs to change.

See Formula Syntax for member and cross-dimensional reference syntax.

Prefer Model-Driven Logic

When a business rule follows the structure or metadata of the cube, use that model information rather than maintaining hard-coded member lists.

For hierarchy-based rules, use hierarchy-aware functions such as:

ISLEAF(ACCOUNT)

or:

ISDESC(ACCOUNT, 'Operating Expenses')

For metadata-driven rules, use UDAs or attributes where appropriate:

HASUDA(ACCOUNT, 'EXPENSE')

This allows formulas to adapt as members are added, removed, or reorganized.

Hard-coded member references remain appropriate when the business rule genuinely refers to specific members. For example:

[SCENARIO].[Actual] - [SCENARIO].[Budget]

is appropriately explicit because the calculation specifically compares Actual and Budget.

The goal is not to eliminate hard-coded references. It is to avoid duplicating hierarchy or metadata relationships inside formulas when those relationships already exist in the cube model.

See Hierarchy-Aware Functions for hierarchy and metadata-driven calculations.

Preserve Natural Aggregation When Appropriate

Consider how a formula should behave at upper levels of the hierarchy.

Some formulas should be evaluated independently at every requested member. Others should be calculated at detail level and then follow the hierarchy’s natural aggregation.

When upper-level values should use natural hierarchy aggregation, use AGG_SELF().

For example:

CASE
    WHEN ISLEAF(ACCOUNT) THEN
        [ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]
    ELSE
        AGG_SELF()
END

In this example, the formula is evaluated at leaf members while upper-level members use the hierarchy’s normal operator-weighted roll-up.

Do not add AGG_SELF() automatically to every formula. Use it when natural hierarchy aggregation reflects the intended business calculation.

See Formula Syntax for AGG_SELF() behavior.

Use the Right Time Calculation Mechanism

Use relative time navigation when a calculation refers to another position in time.

For example, when the business rule means “previous year”:

{ROW} - [YEARS].[LAG(YEARS, 1)]

is preferable to hard-coding a particular year.

Use:

LAG for previous time members
LEAD for subsequent time members

For cumulative time calculations such as:

Year-to-date
Quarter-to-date
Month-to-date

use Dynamic Time Series rather than manually accumulating periods in a member formula.

A useful distinction is:

Relative point in time
        │
        ▼
    LAG / LEAD


Accumulation through time
        │
        ▼
Dynamic Time Series

See Time Navigation for time-navigation behavior and Dynamic Time Series guidance.

Handle Zero and NULL Values Intentionally

Ratio and percentage formulas should account for zero denominators.

Instead of:

[ACCOUNT].[Gross Profit]
/
[ACCOUNT].[Revenue]

prefer:

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

This prevents division-by-zero conditions and makes the intended behavior explicit.

Similarly, do not assume that NULL and zero mean the same thing.

For example:

COALESCE([ACCOUNT].[Adjustment], 0)

explicitly treats a NULL Adjustment as zero.

Use this behavior only when it matches the business meaning of the data.

The Formula Editor can identify potentially unsafe calculation patterns during validation.

Keep Dependencies Simple

Formula dependencies are a normal part of multidimensional calculation design.

A dependency chain such as:

Gross Profit
     │
     ▼
Operating Profit
     │
     ▼
Operating Margin %

can clearly represent the relationship between business calculations.

As dependency chains grow, however, formulas become more difficult to understand and troubleshoot.

Keep dependencies aligned with recognizable business calculations and avoid unnecessary layers of calculated members.

Casabase Cube automatically detects formula dependencies. Circular dependencies are not valid and are rejected during build processing.

See Solve Order & Dependencies for dependency and circular-reference behavior.

Use Solve Order Only When Required

Casabase Cube resolves normal formula dependencies automatically.

Configure explicit solve order when calculated members from different dimensions intersect and the order of those calculations affects the intended result. Solve order is member metadata, and the higher value is applied later at the calculated-member intersection.

Do not use solve order as a workaround for:

An incorrect formula
A circular dependency
An incorrect member reference
An unexpected hierarchy structure
An incorrect POV
An aggregation problem

Solve order should represent an actual cross-dimensional calculation-precedence requirement in the business model.

See Solve Order & Dependencies before changing solve-order behavior.

Keep Formulas Readable

Readable formulas are easier to review, validate, and maintain.

Instead of:

CASE WHEN ISLEAF(ACCOUNT) THEN [ACCOUNT].[Gross Profit]/NULLIF([ACCOUNT].[Revenue],0)*100 ELSE AGG_SELF() END

prefer:

CASE
    WHEN ISLEAF(ACCOUNT) THEN
        [ACCOUNT].[Gross Profit]
        /
        NULLIF([ACCOUNT].[Revenue], 0)
        * 100
    ELSE
        AGG_SELF()
END

Use formatting and indentation to make the business logic visible.

The Formula Editor’s Format function can help format supported expressions.

For migrated formulas, Casabase Cube also preserves the original Source Formula alongside the executable Cube Formula. Use the Source Formula as a reference when reviewing translated calculations, while making changes to the Cube Formula that Casabase Cube executes.

Validate and Test Formulas

Validation and testing serve different purposes.

Validation confirms that Casabase Cube can interpret and evaluate the formula.

Testing confirms that the formula produces the intended business result.

Always validate a new or modified formula before relying on it in production queries.

After saving the formula, test it in Query Builder using representative POVs.

Depending on the calculation, testing may include:

Leaf and upper-level members
Multiple entities or organizational units
Multiple periods and years
Different scenarios
Zero and NULL values
Time-sequence boundaries
Dependent calculated members
Calculated members from multiple dimensions

For dependent formulas, test the dependency chain progressively. Verify the underlying calculated member before testing formulas that depend on it.

When formulas exist on multiple dimensions, also test calculated-member intersections explicitly.

See Formula Validation for validation and testing guidance.

Recommended Workflow

For new or modified member formulas:

Identify the business calculation the member should represent.
Determine whether an existing calculated member can be reused.
Use explicit member and dimensional references.
Use hierarchy or metadata-driven logic when the rule follows the cube model.
Choose the appropriate mechanism for time-based calculations.
Determine how the formula should behave at upper hierarchy levels.
Handle zero and NULL values intentionally.
Keep dependencies clear and limited to genuine business relationships.
Format the formula for readability.
Validate the formula.
Save the Cube Formula.
Test representative POVs in Query Builder.
Test hierarchy, time, dependency, and calculated-member boundaries where applicable.

Best Practices

Keep Formulas Focused

Reuse Established Calculated Members

Use Explicit Multidimensional References

Prefer Model-Driven Logic

Preserve Natural Aggregation When Appropriate

Use the Right Time Calculation Mechanism

Handle Zero and NULL Values Intentionally

Keep Dependencies Simple

Use Solve Order Only When Required

Keep Formulas Readable

Validate and Test Formulas

Recommended Workflow

Related Topics