Skip to content
Casabase CubeCasabase Cube Documentation

Performance & Scalability

What determines Casabase Cube query performance?

Section titled “What determines Casabase Cube query performance?”

Casabase Cube performs multidimensional aggregation and calculation at query time using Snowflake compute.

Performance therefore depends on factors such as:

  • Snowflake virtual warehouse size
  • Fact-table volume
  • Query grain
  • Number of members selected
  • Number and complexity of calculated members
  • Formula dependencies
  • Number of calculated dimensions involved
  • Physical organization of the underlying Snowflake data
  • Query concurrency

Casabase Cube does not rely on a separate OLAP server or prebuilt aggregate store.

Conceptually:

Query
  │
  ▼
Resolve POV
  │
  ▼
Apply Security
  │
  ▼
Snowflake Fact Data
  │
  ▼
Dynamic Aggregation
  │
  ▼
Formula Evaluation
  │
  ▼
Result

See Query Architecture.

Does Casabase Cube pre-aggregate cube data?

Section titled “Does Casabase Cube pre-aggregate cube data?”

No.

Casabase Cube uses dynamic aggregation rather than maintaining a separate collection of precomputed aggregate blocks.

This means:

Stored Fact Data
       │
       ▼
Query-Time Aggregation
       │
       ▼
Current Result

You do not need to run a traditional cube aggregation process after loading fact data.

The tradeoff is that aggregate queries consume Snowflake compute when they run.

For frequently reused reporting results, materialization can be appropriate.

Does data need to be duplicated into a separate OLAP database?

Section titled “Does data need to be duplicated into a separate OLAP database?”

No.

Casabase Cube runs inside Snowflake and operates on Snowflake data.

There is no separate OLAP server that requires the organization’s analytical data to be copied into another proprietary database solely for cube processing.

See Data Storage & Boundaries.

How does Casabase Cube scale with fact-table volume?

Section titled “How does Casabase Cube scale with fact-table volume?”

Fact aggregation is pushed down to Snowflake compute.

As fact volume increases, Snowflake warehouse capacity can be scaled to provide additional compute.

Conceptually:

Larger Fact Volume
       │
       ▼
More Aggregation Work
       │
       ▼
Snowflake Compute
       │
       ▼
Scale Warehouse as Needed

This allows Casabase Cube to use Snowflake’s elastic compute model rather than depending on a fixed-size cube server.

Is there a maximum fact-table size?

Section titled “Is there a maximum fact-table size?”

Casabase Cube does not impose a fixed application-level fact-row limit simply because a table reaches a particular number of rows.

Practical performance depends on the workload, query shape, physical data organization, and Snowflake compute available.

Very large fact tables should therefore be treated as a Snowflake workload-design problem rather than assuming that every query will perform identically regardless of warehouse size or query grain.

How does dimension size affect scalability?

Section titled “How does dimension size affect scalability?”

Dimensions containing many thousands of members are a normal use case.

Dimension depth also does not require stored aggregate blocks for every hierarchy level because aggregation is dynamic.

However, selecting very broad member sets can increase the amount of work required by a query, particularly when calculated members are also involved.

Does a deep hierarchy require more stored aggregate data?

Section titled “Does a deep hierarchy require more stored aggregate data?”

No.

Hierarchy depth does not multiply stored aggregate data because Casabase Cube does not precompute and store every hierarchy aggregation.

Parent values are resolved dynamically from the applicable descendant data.

This allows deep multidimensional hierarchies without the storage expansion associated with pre-aggregated cube structures.

How does Casabase Cube handle concurrent users?

Section titled “How does Casabase Cube handle concurrent users?”

Casabase Cube uses Snowflake’s concurrency and warehouse model.

There is no separate single-threaded OLAP server that serializes all cube requests through a dedicated cube process.

Concurrency planning therefore follows normal Snowflake workload principles, including appropriate warehouse sizing and workload isolation where required.

Can I increase the Snowflake warehouse size to improve performance?

Section titled “Can I increase the Snowflake warehouse size to improve performance?”

Yes.

For workloads constrained by aggregation compute, increasing the warehouse size can improve performance.

However, warehouse size is not the only factor.

A slow query can also be caused by:

  • Selecting far more members than necessary
  • Expensive calculated-member logic
  • Broad calculated-member expansion
  • Poor physical organization of a large fact table
  • A query pattern better suited to materialization

Use the performance diagnostic tools before assuming that warehouse size is always the correct solution.

What is the best first step when a query is slow?

Section titled “What is the best first step when a query is slow?”

Start by identifying whether the issue is:

Compute Capacity
      │
      ├── Warehouse sizing
      │
      ▼
Query Shape
      │
      ├── Broad member selection
      │
      ├── Excessive result grain
      │
      ▼
Calculation Cost
      │
      ├── Calculated members
      │
      ├── Formula dependencies
      │
      ▼
Physical Data Layout
      │
      ├── Clustering
      │
      └── Search optimization

Casabase Cube provides administrative diagnostics for examining these areas.

See Health and Diagnostics.

Does Casabase Cube provide a performance report?

Section titled “Does Casabase Cube provide a performance report?”

Yes.

Administrators can use:

CALL CUBE.PERFORMANCE_REPORT('FINANCE', '30');

to review performance information for a cube over the requested period.

This provides a starting point for identifying workload behavior that may require investigation.

See Health and Diagnostics.

Can I inspect slow queries?

Section titled “Can I inspect slow queries?”

Yes.

Casabase Cube provides a Slow Query Inspector.

For example:

CALL CUBE.SLOW_QUERY_INSPECTOR(
    'FINANCE',
    NULL,
    '7',
    NULL
);

This can be used to investigate query activity that requires more detailed performance review.

See Health and Diagnostics.

Can I estimate the cost of formulas?

Section titled “Can I estimate the cost of formulas?”

Yes.

Use:

CALL CUBE.FORMULA_COST_ESTIMATOR('FINANCE');

to review formula-related calculation cost.

This is useful when query performance appears to be driven by calculated-member complexity rather than basic fact aggregation.

See Health and Diagnostics.

Does Casabase Cube recommend Snowflake physical optimizations?

Section titled “Does Casabase Cube recommend Snowflake physical optimizations?”

Yes.

Casabase Cube provides procedures for analyzing actual query history and recommending physical optimizations.

For example:

CALL CUBE.RECOMMEND_CLUSTERING(
    'FINANCE',
    NULL,
    NULL,
    NULL
);

and:

CALL CUBE.RECOMMEND_SEARCH_OPTIMIZATION(
    'FINANCE',
    NULL,
    NULL,
    NULL
);

These procedures analyze workload history and report suggested optimizations.

They do not silently apply physical changes simply because a recommendation is generated. Changes are applied only when explicitly requested through the supported workflow.

See Health and Diagnostics.

Should I add clustering to every fact table?

Section titled “Should I add clustering to every fact table?”

No.

Clustering should be based on actual workload behavior and Snowflake data characteristics rather than enabled indiscriminately.

Use Casabase Cube’s recommendation capabilities and Snowflake workload information to determine whether clustering is appropriate for a particular cube.

Physical optimization has operational and cost implications, so it should address a demonstrated workload need.

Should I enable Snowflake Search Optimization for every cube?

Section titled “Should I enable Snowflake Search Optimization for every cube?”

No.

Search Optimization should be considered when the workload and data characteristics justify it.

Casabase Cube can analyze query history and recommend Search Optimization where appropriate.

Do not treat it as a universal requirement for every cube.

How can I reduce the cost of a query?

Section titled “How can I reduce the cost of a query?”

A useful first principle is to request the grain you actually need.

For example, avoid selecting an extremely broad member population only to discard most of the result afterward.

Where appropriate:

  • Narrow the POV
  • Select only required members
  • Avoid unnecessary calculated-member expansion
  • Review expensive formulas
  • Use stored members on additional dimensions when calculated-dimension limits are reached
  • Materialize frequently reused reporting results

The best optimization depends on whether the cost comes from scanning, aggregation, calculation, or repeated execution.

Are calculated members more expensive than stored members?

Section titled “Are calculated members more expensive than stored members?”

They can be.

Stored members can often be aggregated directly from fact data.

Calculated members may require additional formula evaluation, dependency resolution, hierarchy-aware processing, or cross-dimensional retrieval.

A query containing many calculated members can therefore be more expensive than an otherwise similar query using stored members.

Use FORMULA_COST_ESTIMATOR when formula cost requires investigation.

Is there a limit on calculated dimensions in a query?

Section titled “Is there a limit on calculated dimensions in a query?”

Yes.

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

A query that exceeds this limit is refused with guidance rather than allowed to run indefinitely or return an unreliable result.

For example:

Account   → Calculated Members
Scenario  → Calculated Members
View      → Calculated Members

would involve calculated members on three dimensions and exceed the supported limit.

Where appropriate, use stored members on additional dimensions or redesign the requested query grain.

Why does Casabase Cube limit calculated members to two dimensions?

Section titled “Why does Casabase Cube limit calculated members to two dimensions?”

Calculated-member expansion can grow rapidly when calculations from multiple dimensions intersect.

The limit exists to prevent requests that could otherwise become excessively expensive or produce calculation semantics that cannot be evaluated safely.

Casabase Cube follows a refuse rather than approximate approach.

If the engine cannot execute a request within supported calculation semantics, it returns an explanatory error instead of silently returning a potentially incorrect result.

Can a broad query with calculated members be refused even if it uses only one or two calculated dimensions?

Section titled “Can a broad query with calculated members be refused even if it uses only one or two calculated dimensions?”

Yes.

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

For example, a query that expands a very large population of members and then applies complex calculated-member logic can exceed the supported calculation scale even if the number of calculated dimensions is otherwise valid.

When this occurs, narrow the selection to the grain actually required.

What should an application do when Casabase Cube refuses a query?

Section titled “What should an application do when Casabase Cube refuses a query?”

Surface the returned error to the user.

Refusal messages are intended to identify the cause and provide guidance for correcting the request.

Do not automatically retry the identical query.

A scale or semantic refusal is deterministic, so repeating the same request without changing it will normally produce the same result.

Conceptually:

Query
  │
  ▼
Unsupported Scale or Semantics?
  │
  ├── No ──► Execute
  │
  └── Yes
         │
         ▼
Return Explanatory Error
         │
         ▼
Change Query

Is query result size limited?

Section titled “Is query result size limited?”

Yes.

Result size is bounded by a configurable row limit for the cube.

This prevents an unintentionally broad query from returning an uncontrolled result set.

The pivot query interface reports when a result has been truncated.

Administrators should set row limits appropriate for the expected workload and client applications.

Why would a query result be truncated?

Section titled “Why would a query result be truncated?”

A result can be truncated when it exceeds the configured result-row limit.

This usually indicates that the query is requesting a broader result grain than the environment is configured to return.

Rather than simply increasing the limit, first determine whether the query should be narrowed.

Large detailed extracts may also be better handled through a different SQL or reporting pattern depending on the use case.

Does using the bottom POV operator improve performance?

Section titled “Does using the bottom POV operator improve performance?”

bottom is useful when the query specifically requires bottom-level members.

It can avoid returning unnecessary parent-level members when only leaf-level detail is needed.

However, it should be used because it matches the desired query semantics, not as a universal performance switch.

See Point of View (POV).

When should I materialize a query result?

Section titled “When should I materialize a query result?”

Materialization is useful when the same calculated result will be read repeatedly between source-data changes.

Conceptually:

Repeated Dynamic Query
        │
        ▼
Same Calculation Repeatedly
        │
        ▼
Materialize Once
        │
        ▼
Reporting Table
        │
        ▼
Repeated SELECT Access

This trades dynamic recalculation for a persisted reporting result.

It is particularly useful for heavily read reports where the result does not need to be recalculated on every consumer request.

Does materialization make every workload faster?

Section titled “Does materialization make every workload faster?”

No.

Materialization is a workload pattern, not a universal optimization.

It is most useful when:

  • The same result is read many times
  • Query-time calculation is relatively expensive
  • The result can remain stable until the next scheduled refresh
  • Consumers can read a Snowflake table

For highly interactive analysis where users continuously change dimensional selections, dynamic cube queries are generally the more natural pattern.

Is a materialized query result still dynamically secured for each reader?

Section titled “Is a materialized query result still dynamically secured for each reader?”

No.

Security is evaluated for the identity that executes the materialization.

The output is then stored as a normal Snowflake table.

Subsequent readers are governed by Snowflake privileges on that table rather than a new Casabase Cube row-level security evaluation.

Do not use one materialized table for audiences requiring different dynamic security scopes unless its Snowflake access design explicitly supports that requirement.

See Security & Governance and Saved Queries.

Do scheduled materializations require a customer warehouse?

Section titled “Do scheduled materializations require a customer warehouse?”

Scheduled Casabase Cube operations use Snowflake serverless managed tasks for the supported scheduling workflow.

They therefore do not require a customer-owned virtual warehouse simply to run the schedule.

The application requires the applicable Snowflake privilege:

EXECUTE MANAGED TASK

See Roles and Privileges and Saved Queries.

Do I need to rebuild dimensions after loading new fact data?

Section titled “Do I need to rebuild dimensions after loading new fact data?”

No.

Fact-data changes do not require a dimension rebuild solely because new fact rows were loaded.

Dimension rebuilds are associated with dimension metadata or source-definition changes that affect the built dimension structure.

See Rebuilding Dimensions.

Do dimension rebuilds affect query concurrency?

Section titled “Do dimension rebuilds affect query concurrency?”

A dimension build uses concurrency control to prevent conflicting rebuild operations.

A build acquires a lock while it is running.

If a build is interrupted, an administrator may need to release a stale build lock before another rebuild can proceed.

See Rebuilding Dimensions and Health and Diagnostics.

Does Casabase Cube use a cache that must be cleared after configuration changes?

Section titled “Does Casabase Cube use a cache that must be cleared after configuration changes?”

Casabase Cube does not depend on a traditional OLAP aggregate cache that administrators must clear after security-rule changes.

Security configuration is read at query time, so a security-rule change applies to the next query.

Dimension metadata changes can require rebuilding the affected dimension structure, but that is different from clearing an aggregation cache.

How do I troubleshoot a performance problem systematically?

Section titled “How do I troubleshoot a performance problem systematically?”

A useful workflow is:

Identify Slow Workload
        │
        ▼
Review PERFORMANCE_REPORT
        │
        ▼
Inspect Slow Queries
        │
        ▼
Review Query Grain
        │
        ▼
Estimate Formula Cost
        │
        ▼
Review Warehouse Capacity
        │
        ▼
Review Physical Optimization
        │
        ▼
Consider Materialization
        │
        ▼
Retest

Useful administrative procedures include:

CALL CUBE.PERFORMANCE_REPORT('FINANCE', '30');


CALL CUBE.SLOW_QUERY_INSPECTOR(
    'FINANCE',
    NULL,
    '7',
    NULL
);


CALL CUBE.FORMULA_COST_ESTIMATOR('FINANCE');


CALL CUBE.RECOMMEND_CLUSTERING(
    'FINANCE',
    NULL,
    NULL,
    NULL
);


CALL CUBE.RECOMMEND_SEARCH_OPTIMIZATION(
    'FINANCE',
    NULL,
    NULL,
    NULL
);

See Health and Diagnostics.

What is the main performance tradeoff in Casabase Cube’s architecture?

Section titled “What is the main performance tradeoff in Casabase Cube’s architecture?”

Casabase Cube favors dynamic, current calculation over stored aggregate structures.

The tradeoff is:

Dynamic Aggregation
        │
        ├── No aggregate rebuild cycle
        ├── No stored aggregate duplication
        ├── Current results
        │
        └── Aggregation consumes compute at query time

For interactive multidimensional analysis, dynamic execution provides flexibility and current results.

For heavily repeated, stable reporting outputs, materialization can move that work out of the interactive read path.

Where should I start with performance and scalability?

Section titled “Where should I start with performance and scalability?”

Start with: