Performance Issues

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

A slow query can therefore have several different causes:

Slow Query
    │
    ├── Query Shape
    │     ├── Broad member selections
    │     └── Excessive result grain
    │
    ├── Calculation Cost
    │     ├── Calculated members
    │     ├── Formula dependencies
    │     └── Cross-dimensional calculations
    │
    ├── Snowflake Compute
    │     ├── Warehouse size
    │     └── Concurrent workload
    │
    ├── Physical Data Layout
    │     ├── Clustering
    │     └── Search Optimization
    │
    └── Workload Pattern
          └── Repeated dynamic calculation

Do not assume that increasing the Snowflake warehouse size is always the correct first response. Query grain, calculated-member complexity, physical data organization, and repeated workload patterns can all affect performance.

Understand the Performance Model

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

Query processing follows the general pattern:

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

Performance can therefore depend on:

• 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
• Physical organization of the underlying Snowflake data
• Query concurrency

Because aggregation is dynamic, aggregate queries consume Snowflake compute when they execute rather than relying on previously stored aggregate blocks.

Start with the Exact Slow Query

Before changing configuration, capture:

• Cube name
• Complete query POV
• Query interface used
• Approximate execution time
• Snowflake warehouse used
• Whether calculated members are involved
• Whether the problem affects one query or many queries
• Approximate time period when the issue occurred
• Whether performance recently changed

For a programmatic query, preserve the complete procedure call when possible.

Do not replace the slow query with a simplified example unless the simplified query reproduces the same performance behavior.

Recommended Diagnostic Sequence

Use the following sequence:

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

Casabase Cube provides diagnostics for each stage of this workflow.

Run Performance Report

Start with:

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

PERFORMANCE_REPORT analyzes cube-query performance over the requested lookback period and returns performance metrics and recommendations.

Use it to determine whether the problem appears to be:

One Query
    │
    ▼
Query-Specific Investigation

Many Queries
    │
    ▼
Workload / Compute / Physical Design Investigation

A single outlier should generally be investigated differently from a broad slowdown across the cube.

Inspect Slow Queries

Use the Slow Query Inspector to identify queries requiring detailed review.

For example:

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

The procedure supports:

P_CUBE_NAME
P_LIMIT
P_DAYS
P_SLOW_MS

and returns slow-query details with optimization suggestions.

Use the returned workload information to identify recurring query shapes rather than optimizing only the first slow query reported.

Check the Query Grain

One of the most effective performance improvements is often to request only the grain that is actually needed.

For example, avoid:

Total Entity
    │
    ▼
All Descendants
    │
    ▼
Thousands of Members
    │
    ▼
Return Everything
    │
    ▼
Application Discards Most Rows

when the application actually needs:

Total Entity
    │
    ▼
Children
    │
    ▼
Required Members Only

Where appropriate:

• Narrow the POV
• Select only required members
• Avoid unnecessarily broad descendant expansions
• Avoid retrieving detail that the consuming application immediately discards
• Request only the dimensions and members required by the report

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

See Point of View (POV).

Broad Member Expansions

Large dimensions are supported, but broad selections increase the amount of work required by a query.

This becomes especially important when calculated members are also involved.

For example:

{"idescendants":"Total Entity"}

may expand to a very large member population.

If only the next level is required, prefer a narrower selection such as:

{"children":"Total Entity"}

The goal is not to avoid hierarchy navigation. It is to avoid requesting a much broader member population than the business question requires.

Check Calculated Members

Calculated members can be more expensive than stored members because they can require:

• Formula evaluation
• Dependency resolution
• Hierarchy-aware processing
• Cross-dimensional retrieval

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

A useful isolation test is:

Slow Query with Calculated Members
              │
              ▼
Run Comparable Query
Using Stored Members
              │
       ┌──────┴──────┐
       │             │
    Fast          Still Slow
       │             │
       ▼             ▼
Formula Cost     Query / Scan /
Likely           Compute Cost

Do not permanently remove required calculations simply to improve a benchmark. Use the comparison to identify where the cost originates.

Estimate Formula Cost

When calculated members appear to be contributing to the problem, run:

CALL CUBE.FORMULA_COST_ESTIMATOR('FINANCE');

This procedure is intended to investigate formula-related calculation cost when performance appears to be driven by calculated-member complexity rather than basic fact aggregation.

Review expensive formulas for:

• Long dependency chains
• Multiple calculated-member intersections
• Cross-dimensional references
• Hierarchy-aware processing
• Calculations applied over very broad selections

See Formula Errors and Best Practices.

Calculated Dimensions

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

For example:

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

involves calculated members on three dimensions and exceeds the supported limit.

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

This is more than a performance recommendation. A query exceeding the supported calculated-dimension limit is refused rather than executed approximately.

Calculation-Scale Refusals

A query can also be refused when broad member selections combine with calculated-member logic in a way that exceeds supported calculation scale.

For example:

Large Descendant Expansion
          +
Multiple Calculated Members
          │
          ▼
Large Calculation Space
          │
          ▼
Scale Safeguard
          │
          ▼
Query Refused

When this occurs:

• Narrow the member selection
• Prefer specific parents or children over unnecessarily broad descendant expansions
• Use stored members on additional dimensions where appropriate
• Surface the returned error to the user

Do not automatically retry the identical query. A deterministic scale refusal normally requires changing the query.

Slow Query vs. Refused Query

These are different conditions.

A slow query is executing but taking longer than expected.

A refused query is rejected because its calculation semantics or scale are outside the supported execution model.

Query Submitted
      │
      ▼
Supported?
   ┌──┴──┐
   │     │
  Yes    No
   │     │
   ▼     ▼
Execute  Return Explanatory Error
   │
   ▼
May Be Slow

Do not troubleshoot a deterministic refusal by increasing warehouse size.

Change the query according to the returned error.

Check Snowflake Warehouse Capacity

Casabase Cube pushes fact aggregation to Snowflake compute.

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

However, warehouse size should be evaluated after understanding the query shape.

Conceptually:

Slow Query
    │
    ▼
Is Query Broader Than Necessary?
    │
    ├── Yes ──► Fix Query Shape
    │
    └── No
           │
           ▼
Is Formula Cost Excessive?
           │
           ├── Yes ──► Optimize Calculation
           │
           └── No
                  │
                  ▼
Review Warehouse Capacity

Scaling compute is appropriate when the workload genuinely requires more aggregation capacity. It is less useful when the primary issue is an unnecessarily broad query or expensive calculation design.

Concurrent Workloads

Performance can also change when many users or applications execute queries concurrently.

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

If the same query is:

Fast During Low Activity
        │
        ▼
Slow During Peak Activity

investigate workload concurrency rather than assuming the cube metadata or formula changed.

Compare performance across representative periods and identify whether the slowdown affects:

• One user
• One query pattern
• One warehouse
• The entire cube workload

Fact-Table Volume

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

As fact volume grows, practical performance depends on:

• Workload
• Query shape
• Physical data organization
• Available Snowflake compute

Fact aggregation is pushed down to Snowflake, so warehouse capacity can be scaled as required.

Do not assume that a large fact table alone explains a slow query.

A narrowly filtered query against a large table and a broad query against the same table can have very different performance characteristics.

Review Physical Data Layout

If query shape and formula cost are reasonable, investigate the physical organization of the Snowflake data.

Casabase Cube can analyze workload history and recommend:

Clustering
Search Optimization

where appropriate.

Physical optimization should address demonstrated workload behavior rather than being enabled indiscriminately.

Clustering Recommendations

Run:

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

RECOMMEND_CLUSTERING analyzes query patterns and recommends clustering keys for cube tables. It can also target a specific data table and supports a minimum-query threshold and an explicit apply option.

Do not add clustering to every fact table automatically.

Clustering has operational and cost implications and should be based on actual workload behavior and Snowflake data characteristics.

Search Optimization Recommendations

Run:

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

The procedure analyzes query patterns and recommends Snowflake Search Optimization for cube tables where appropriate.

Search Optimization is not a universal requirement for every cube.

Use it when the workload and data characteristics justify it.

Review Recommendations Before Applying Them

Performance recommendation procedures are intended to identify optimization opportunities for administrator review.

They do not silently apply physical changes merely because a recommendation is generated.

Use this pattern:

Analyze Workload
      │
      ▼
Generate Recommendation
      │
      ▼
Review Recommendation
      │
      ▼
Apply if Appropriate
      │
      ▼
Retest Same Workload

Always compare the same representative workload before and after a physical optimization.

Repeated Reporting Workloads

Dynamic aggregation is appropriate for interactive multidimensional analysis, but repeatedly recalculating the same stable reporting result may not be the best workload pattern.

Casabase Cube’s architectural tradeoff is:

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

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

Consider Materialization

Consider materializing a query when:

• The same expensive result is requested repeatedly
• The result does not need to be recalculated for every interactive request
• A reporting table is appropriate for downstream consumption
• The security model for the materialized output is understood

A scheduled saved query can write its result to a Snowflake table for subsequent reads rather than recalculating the multidimensional query every time.

See Saved Queries.

Security Considerations for Materialized Results

A materialized result is stored as a normal Snowflake table.

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

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

Performance optimization must not accidentally change the intended security boundary.

Result Size

Query result size is bounded by the cube’s configurable row limit.

This prevents unintentionally broad requests from returning uncontrolled result sets, and the pivot query interface reports when a result has been truncated.

If a query is returning or attempting to return an extremely large result:

• Confirm the business requirement
• Narrow the POV where possible
• Select only required dimensions and members
• Consider whether the workload is an interactive query or an export/reporting workload

Do not increase result limits merely to hide an inefficient query design.

Deep Hierarchies Are Not Automatically a Problem

Hierarchy depth does not require Casabase Cube to store aggregate blocks for every hierarchy level.

Parent values are dynamically resolved from the applicable descendant data.

Therefore:

Deep Hierarchy
      ≠
Automatically Slow Query

What matters more is how much of that hierarchy the query actually expands and what calculations are applied to the resulting members.

New Fact Data Does Not Require a Dimension Rebuild

Do not rebuild dimensions as a performance response simply because new fact rows were loaded.

Fact-data changes do not require a dimension rebuild solely because the fact data changed. Dimension rebuilds are associated with dimension metadata or source-definition changes that affect the built dimension structure.

Unnecessary rebuilds add operational work without addressing a fact-query performance problem.

There Is No Traditional Aggregate Cache to Clear

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

Security configuration is read at query time, while dimension metadata changes can require rebuilding the affected dimension structure. That is different from clearing an aggregation cache.

Do not use “clear the cube cache” as a generic performance troubleshooting step.

Performance Changed Suddenly

If a query was previously fast and became slow, identify what changed around the same time.

Check for:

• Fact-data growth or refresh
• Query POV changes
• New or modified formulas
• Dimension changes
• Warehouse changes
• Workload concurrency changes
• Physical data-layout changes
• Application upgrades

The audit log can help determine whether administrative or application changes occurred around the time the behavior changed.

See Audit Log.

Run Health Check

For a configured cube, Health Check remains the primary general-purpose diagnostic:

CALL CUBE.HEALTH_CHECK('FINANCE');

Health Check can identify configuration and application-state problems that should be resolved before deeper performance tuning.

Review non-OK findings:

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

A configuration error should be corrected as a configuration error rather than treated as a warehouse-sizing problem.

See Using Health Check.

Retest Systematically

After making a change, rerun the same representative query.

Record:

Before
  │
  ├── Query POV
  ├── Warehouse
  ├── Execution Time
  └── Relevant Configuration

After
  │
  ├── Same Query POV
  ├── Same or Documented Warehouse Change
  ├── Execution Time
  └── Changed Variable

Change one major variable at a time.

For example, do not simultaneously:

• Resize the warehouse
• Rewrite the formula
• Add clustering
• Enable Search Optimization
• Change the POV

and then attempt to determine which change improved performance.

Practical Optimization Order

For most slow queries, use this order:

1. Confirm the exact slow query
          │
          ▼
2. Run PERFORMANCE_REPORT
          │
          ▼
3. Run SLOW_QUERY_INSPECTOR
          │
          ▼
4. Narrow unnecessary query breadth
          │
          ▼
5. Review calculated members
          │
          ▼
6. Run FORMULA_COST_ESTIMATOR
          │
          ▼
7. Review warehouse capacity
          │
          ▼
8. Review clustering / Search Optimization
          │
          ▼
9. Consider materialization
          │
          ▼
10. Retest

This prevents infrastructure changes from masking a query-design problem and prevents query rewrites from being used when the actual constraint is compute capacity.

If the Problem Remains

Generate support information:

CALL CUBE.GENERATE_SUPPORT_INFO('FINANCE');

Collect:

• Cube name
• Complete query POV
• Query interface used
• Approximate execution time
• Snowflake warehouse used
• Whether calculated members are involved
• Whether the problem affects one query or many queries
• Approximate time period when the issue occurred
• PERFORMANCE_REPORT output
• Relevant SLOW_QUERY_INSPECTOR output
• FORMULA_COST_ESTIMATOR output when formulas are involved
• Relevant Health Check findings
• Recent configuration or workload changes

GENERATE_SUPPORT_INFO produces diagnostic information within the customer’s Snowflake environment and does not automatically transmit it to Casabase Software.

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

See Getting Additional Help.

Related Topics

• Performance & Scalability
• Query Architecture
• Querying Overview
• Point of View (POV)
• Formula Overview
• Formula Errors
• Saved Queries
• Health and Diagnostics
• Using Health Check
• Getting Additional Help