Formula Capabilities

Casabase Cube supports member formulas for defining calculated members within the multidimensional model.

A formula is always associated with a member. The calculated member stores no fact data of its own. Instead, its formula is evaluated dynamically when that member participates in a query.

This allows business calculations such as ratios, variances, allocations, cross-dimensional calculations, and other derived measures to remain part of the cube model rather than being duplicated across reports and consuming applications.

Calculated Members

A member with a formula is a calculated member.

Conceptually:

Member
  │
  ├── Name
  ├── Hierarchy position
  └── Formula
        │
        ▼
   Evaluated at Query Time

Unlike a stored member:

Stored Member
     │
     ▼
Stored Fact Data

a calculated member behaves as:

Calculated Member
       │
       ▼
Formula Expression
       │
       ▼
Runtime Calculation

The authoritative architecture establishes that calculated members store no data and compute when read.

Dynamic Evaluation

Member formulas are evaluated dynamically as part of query processing.

The high-level sequence is:

Resolve POV
     │
     ▼
Determine Permitted Data Scope
     │
     ▼
Aggregate Required Values
     │
     ▼
Evaluate Member Formulas
     │
     ▼
Return Result

There is no separate formula-calculation process that must be run after data changes.

When underlying fact data changes, the next query evaluates the formula against the current data.

This is consistent with the broader Casabase Cube architecture, where both aggregate values and formula members are computed dynamically rather than stored as precomputed cube values.

Formulas Follow the Cube Model

Formulas are evaluated within the multidimensional context of the query.

A formula can reference:

• Members in its own dimension
• Members in other dimensions
• Other calculated members
• Specific multidimensional intersections
• Hierarchy relationships
• Time-relative members
• Supported Snowflake SQL expressions and functions

The Formula section documents the supported expression language in detail.

From an architectural perspective, the important point is that formula evaluation is integrated into the cube engine rather than executed as a separate reporting-layer calculation.

Cross-Dimensional Calculation

A formula can reference members from other dimensions.

Conceptually:

ACCOUNT
└── Margin %
      │
      ▼
Revenue
  ÷
Cost / Revenue Context
      │
      ▼
Query Intersection

or a formula can explicitly change one or more coordinates:

Current Query Context
        │
        ▼
Formula Reference
        │
        ├── Different ACCOUNT
        ├── Different SCENARIO
        └── Different DATATYPE

The calculation engine resolves these references against the multidimensional model and the current query context.

This is what allows calculations to behave as cube calculations rather than simple row-level SQL expressions.

Formula Dependencies

A calculated member can reference another calculated member.

For example:

Formula A
   │
   ▼
Formula B
   │
   ▼
Stored Members

The engine detects these dependencies and determines the required calculation sequence.

Conceptually:

Formula Definitions
       │
       ▼
Dependency Analysis
       │
       ▼
Evaluation Order
       │
       ▼
Runtime Calculation

Dependencies are resolved automatically, and circular references are rejected rather than being allowed to recurse indefinitely at query time.

Formula Solve Order

There are two related ordering concepts in the calculation model.

Formula Solve Order

Formula solve order operates within a dimension.

Its purpose is dependency sequencing:

Formula B
   │
   ▼
Formula A depends on B
   │
   ▼
Formula A

If one calculated member requires the result of another calculated member in the same dimension, the dependency must be resolved first.

Casabase Cube derives this ordering from formula dependencies.

Conceptually:

Independent Formula       solve order 0

Dependent Formula         solve order 1

Depends on Previous       solve order 2

This provides deterministic evaluation without requiring administrators to manually sequence ordinary formula dependencies.

Dimension Solve Order

Dimension solve order addresses a different problem.

It matters when calculated members from different dimensions intersect in the same cell.

For example:

Calculated SCENARIO Member
          ×
Calculated ACCOUNT Member

Both formulas potentially define how that intersection should be calculated.

Dimension solve order determines which dimension’s calculation has precedence and is applied last.

Conceptually:

Calculated Member
Dimension A
       │
       ▼
Calculated Member
Dimension B
       │
       ▼
Final Cell

Changing the cross-dimensional order can change the result when the calculations do not commute.

Set solve order deliberately where calculated members from different dimensions can intersect.

For the complete distinction between formula dependency ordering and dimension solve order, see Solve Order & Dependencies.

Hierarchy-Aware Formulas

Formulas can also respond to the current member’s hierarchy position.

A calculation can determine structural properties such as whether the current member is:

Leaf
Child
Descendant
Ancestor
Specific Generation
Specific Level

This allows calculation behavior to adapt as the query navigates the hierarchy.

Conceptually:

Current Member
      │
      ▼
Inspect Hierarchy Position
      │
      ▼
Choose Formula Behavior

This capability is useful when the correct calculation depends on where the current member sits in the multidimensional structure.

See Hierarchy-Aware Functions.

Time Navigation

Formulas can navigate relative positions in the cube’s time dimension.

This supports calculations such as:

Current Period
      │
      ▼
Prior Period

or:

Current Period
      │
      ▼
Next Period

The detailed time-navigation syntax is documented in Time Navigation.

Formula-based time navigation should be distinguished from configured cube time intelligence.

Formula Logic vs. Configured Time Intelligence

Not all time calculations are implemented as formulas.

The architecture deliberately treats certain time behavior as model configuration.

For example, period-to-date accumulation and time-balance behavior are represented through cube metadata so that the engine can apply those rules consistently across queries.

Conceptually:

Formula
   │
   └── Custom business calculation

versus:

Time Intelligence Configuration
   │
   └── Standard time aggregation behavior

This distinction keeps standard multidimensional time behavior centralized in the model instead of requiring every report or formula author to reproduce it.

Formulas and Aggregation

Formula evaluation and hierarchy aggregation work together.

A calculated member is evaluated dynamically, and its result participates in the multidimensional query according to the hierarchy and calculation context.

Conceptually:

Stored Leaf Data
       │
       ▼
Required Aggregations
       │
       ▼
Formula Operands
       │
       ▼
Member Formula
       │
       ▼
Calculated Result

This allows formulas to operate on dynamically aggregated cube values rather than requiring every calculation to reference only stored leaf rows.

Formulas and Security

Security is applied before formula calculation.

A formula evaluates against exactly the data scope available to the executing user.

Conceptually:

Requested Data
      │
      ▼
Apply User Security
      │
      ▼
Permitted Scope
      │
      ▼
Formula Evaluation

A formula therefore cannot be used as a path around row-level security.

Formula evaluation respects the permitted user scope. A formula cannot use a direct reference to a restricted member to retrieve a value outside that scope, while references to permitted members continue to calculate normally.

Three Security Cases

The interaction can be summarized as:

Formula references	Restricted-user behavior
A member the user is permitted to access	Formula receives the permitted value
A member the user cannot access	Restricted value is unavailable
An aggregate above the user’s granted branch	Formula receives the partial aggregate over permitted leaves

The third case is particularly important. A formula referencing an ancestor does not receive the unrestricted ancestor value. It receives the same security-scoped partial rollup the user would receive by querying that ancestor directly.

Security-Safe Does Not Mean User-Independent

Formula security prevents a user from extracting restricted data.

It does not mean that a calculated member must return the same value to every user.

Consider a calculation such as:

Share of Total
=
Current Value / Total

An unrestricted user’s denominator can represent the complete population.

A restricted user’s denominator can represent only the leaves that user is permitted to access.

The two results can therefore differ even though both are correct for the viewer’s scope.

Conceptually:

Same Formula
    │
    ├── Full-Scope User
    │       ▼
    │   Full Operands
    │
    └── Restricted User
            ▼
        Permitted Operands

This behavior should be considered when designing ratios, percentages, variances, allocations, or other calculations whose meaning depends on a complete population.

Formulas Are Not a Privilege-Escalation Path

A user does not gain additional data access simply because an administrator created a formula referencing a broader set of members.

The formula’s author does not determine the runtime security scope.

Instead:

Formula Definition
       │
       ▼
Executing User
       │
       ▼
User's Permitted Scope
       │
       ▼
Formula Result

This means calculated members can safely be shared with users who have narrower scopes, provided the business meaning of a scope-dependent result is understood.

Formula Application at Query Time

Member formulas are a query-time capability.

When formula application is enabled, calculated members included in the query can be evaluated.

When member-formula application is disabled for the query, member formulas are not calculated.

The default query behavior is to apply member formulas.

This gives callers the ability to control whether formula evaluation participates in a particular query while preserving formulas as part of the cube model.

See Formula Overview and Querying Overview for detailed behavior.

Calculation Engine vs. Secure Views

Formula evaluation belongs to the Casabase Cube calculation engine.

It is available through query interfaces such as:

QUERY_CUBE
QUERY_CUBE_PIVOT_JSON

It is not reproduced by directly selecting from:

<CUBE>_FACT_SECURE

The architectural distinction is:

Calculation Engine	Secure Fact View
Member formulas evaluated	No member formula evaluation
Calculated members available	Calculated members absent
Hierarchy-aware aggregation	Stored leaf data
Time intelligence	No cube time-balance calculation
Security enforced	Security enforced

The authoritative guide explicitly distinguishes these two read paths.

Use the calculation engine when you need the cube’s calculated business result.

Use secure views when you want stored relational data and intend to provide the calculation logic yourself.

Materialized Formula Results

A Saved Query can calculate formulas and write the completed result to a Snowflake table.

Conceptually:

Saved Query
      │
      ▼
Cube Calculation Engine
      │
      ▼
Member Formulas
      │
      ▼
Calculated Result
      │
      ▼
Materialized Table

This is useful when the same calculated result is consumed repeatedly or by a tool that can query tables but cannot directly invoke the calculation engine.

Once written, however, the table contains the result of the calculation. The formulas are not dynamically reevaluated when a consumer later selects from that table.

The Saved Query must run again to refresh the calculated output.

Formula Scale

Dynamic calculated members are more computationally demanding than stored members.

Calculated members are the primary query-scale constraint.

A query can contain calculated members on at most two dimensions. A query requiring calculated members across more than two dimensions is refused rather than allowed to execute with unsound or excessive behavior.

Conceptually:

Calculated Dimension A
          ×
Calculated Dimension B
          │
          ▼
Supported

while:

Calculated Dimension A
          ×
Calculated Dimension B
          ×
Calculated Dimension C
          │
          ▼
Query Refused

Broad Selections with Calculated Members

Broad member selections combined with calculated members are also subject to a scale guard.

This is because a request such as:

Many Members
      ×
Many Members
      ×
Calculated Member Expansion

can create a very large calculation space.

Rather than allow a request to run indefinitely or risk an unsound result, the engine can refuse it with guidance.

The recommended design pattern is to request the grain actually required by the consuming report rather than selecting large portions of the cube and aggregating them afterward.

Refusal Is Part of the Calculation Contract

When a formula-related request cannot be calculated correctly within supported limits, Casabase Cube returns an explicit error rather than silently approximating the result.

Client applications should:

• Surface the returned error.
• Avoid automatically retrying a deterministic refusal.
• Narrow overly broad selections where appropriate.
• Use stored members where a calculation design exceeds supported calculated-member dimensionality.

This behavior is intentional. The engine favors a clearly refused request over a plausible but incorrect number.

Formula Architecture

Putting the pieces together:

Member Formula Definition
           │
           ▼
     Dependency Analysis
           │
           ▼
        Query POV
           │
           ▼
    User Security Scope
           │
           ▼
  Dynamic Base Aggregation
           │
           ▼
 Formula Dependency Order
           │
           ▼
 Cross-Dimension Precedence
           │
           ▼
   Calculated Member Value
           │
           ▼
      Query Result

This architecture makes formulas part of the governed multidimensional model rather than calculations that must be reconstructed separately in every consuming application.

Why Model Calculations as Member Formulas

Defining business calculations as member formulas provides a shared calculation layer.

Instead of implementing the same metric independently in:

Power BI
Tableau
Python
Excel
Custom SQL
AI

the calculation can be modeled once in Casabase Cube and evaluated by the query engine.

Conceptually:

Business Formula
      │
      ▼
Casabase Cube
      │
      ├── Query Builder
      ├── SQL Procedures
      ├── Saved Queries
      ├── BI Materialization
      └── AI Consumption

This provides a common multidimensional definition wherever consumers use the Casabase Cube calculation engine.

Key Principles

• Formulas are always attached to members.
• A calculated member stores no fact data of its own.
• Formulas evaluate dynamically at query time.
• Formulas operate within the multidimensional query context.
• Calculated members can reference other calculated members.
• Formula dependencies are resolved automatically.
• Circular dependencies are rejected.
• Formula solve order handles dependencies within a dimension.
• Dimension solve order controls precedence when calculated members from different dimensions intersect.
• Security scope is established before formula evaluation.
• A formula cannot expose a member the executing user cannot access.
• Security-scoped aggregates can cause calculated results to differ between users.
• Standard time intelligence can be model configuration rather than formula logic.
• Secure fact views do not evaluate calculated members.
• Calculated-member queries are subject to deliberate scale limits.
• Unsupported calculations are refused rather than approximated.

Related Topics

• High-Level Architecture
• Query Architecture
• Hierarchy Capabilities
• Security Model
• Formula Overview
• Formula Syntax
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies