Skip to content
Casabase CubeCasabase Cube Documentation

Query Returns No Data

A query that returns no rows is different from a query that fails with an error.

Start by identifying which condition occurred:

Query Executed
     │
     ├── Returned No Rows
     │        │
     │        ▼
     │   Check POV, Data,
     │   Dimensions, Security
     │
     └── Returned an Error
              │
              ▼
         Query Was Refused
         or Could Not Execute

Casabase Cube follows a refuse rather than approximate approach. If a query cannot be evaluated correctly within supported semantics or scale, it returns an explanatory error rather than silently substituting an approximate result.

If the query returned an error, preserve that error and troubleshoot the stated cause. If it executed successfully but returned no rows, use the steps below.

Start with the Exact Query

Section titled “Start with the Exact Query”

Capture the complete query before changing anything.

For QUERY_CUBE, preserve:

  • Cube name
  • Complete POV JSON
  • Optional output-table argument
  • Alias-set argument
  • Executing Snowflake user
  • Approximate execution time

For example:

CALL CUBE.QUERY_CUBE(
    'ASOSAMP',
    '{
      "MEASURES": ["Units"],
      "PRODUCTS": ["Digital Cameras"],
      "YEARS": ["Curr Year"]
    }',
    NULL,
    NULL
);

A small difference in cube name, member name, selection operator, or omitted dimension can materially change the query context.

Verify the Cube Name

Section titled “Verify the Cube Name”

Confirm that the query references the intended cube.

For example:

ASOSAMP

and:

ASOSAMP_TEST

may contain different:

  • Dimension structures
  • Default members
  • Fact data
  • Formulas
  • Security configuration

Do not assume that a query that works against one cube should return the same result from another cube with a similar configuration.

Verify Member Names

Section titled “Verify Member Names”

POV selections use underlying member names, not aliases.

Aliases affect returned display names. They are not substitutes for member keys in the POV.

For example, if the member name is:

100-10

and its display alias is:

Cash

the POV must use the actual member name expected by the model.

When a query returns no rows, verify each explicitly named member against the built dimension.

Check the POV One Dimension at a Time

Section titled “Check the POV One Dimension at a Time”

A POV is a multidimensional intersection.

Conceptually:

MEASURES = Units
       ×
PRODUCTS = Digital Cameras
       ×
YEARS = Curr Year
       ×
Other Resolved Dimensions
       │
       ▼
Effective Query Context

A valid member in each dimension does not guarantee that fact data exists at their combined intersection.

When troubleshooting, simplify the POV and add selections back incrementally.

For example:

Known Working Query
        │
        ▼
Add One Dimension / Selection
        │
        ▼
Still Returns Data?
        │
        ├── Yes ──► Add Next Selection
        │
        └── No  ──► Investigate Last Change

This is usually more useful than changing several POV elements at once.

Verify Member Selection Operators

Section titled “Verify Member Selection Operators”

Selection operators determine which members are expanded from the named member.

For example:

{"descendants":"Personal Electronics"}

returns members beneath the selected member, including intermediate rollup levels, while:

{"bottom":"Personal Electronics"}

returns only leaf descendants.

If the query returns an unexpected shape or no matching rows, confirm that the operator matches the intended selection.

Also verify that the starting member itself exists.

Do Not Use Aliases in the POV

Section titled “Do Not Use Aliases in the POV”

If a member is displayed in Query Builder or another interface using an alias, do not assume that alias is the value required by the POV.

For example:

Member Name: 100-10
Alias:       Cash

Use the underlying member name when constructing the POV unless the documented interface explicitly states otherwise.

The alias-set argument controls output display behavior, not POV member resolution.

Check Default Members

Section titled “Check Default Members”

Dimensions omitted from the POV can still participate in query resolution through configured default members.

Conceptually:

POV
 │
 ├── ACCOUNT = Sales
 ├── ENTITY = Total Entity
 └── PERIOD omitted
          │
          ▼
Configured PERIOD Default
          │
          ▼
Effective Query Context

An unexpected default member can therefore cause a query to evaluate at a different intersection than expected.

When troubleshooting an empty result:

  1. Identify dimensions omitted from the POV.
  2. Review their configured default members.
  3. Determine whether those defaults intersect with the expected fact data.

Default-member resolution also interacts with row-level security. Security is applied to the resolved query context.

See Point of View (POV).

Verify That Matching Fact Data Exists

Section titled “Verify That Matching Fact Data Exists”

A structurally valid query can correctly return no rows if no fact data exists for the requested intersection.

Separate the question:

Does the Member Exist?
        │
        └── Dimension Metadata


Does Data Exist at the Intersection?
        │
        └── Fact Data

If necessary, use the supported secure fact view to inspect stored fact rows available to the current user.

A cube exposes a security-aware fact view such as:

<CUBE>_FACT_SECURE

For example:

SELECT *
FROM CASABASE_CUBE.SHARED_DATA.ASOSAMP_FACT_SECURE;

The secure fact view exposes stored fact rows available to the executing user under the applicable security configuration.

Use appropriate predicates to test whether data exists for the expected bottom-level intersection.

Remember that the secure fact view contains stored fact data. It does not reproduce hierarchy-aware aggregation, formulas, or time-balance calculations performed by the query engine.

New Fact Data Does Not Require a Dimension Rebuild

Section titled “New Fact Data Does Not Require a Dimension Rebuild”

If new fact rows were loaded but existing dimension metadata did not change, a dimension rebuild is not required merely to make the new fact values queryable.

Casabase Cube performs dynamic aggregation at query time. Dimension metadata changes require rebuilds; fact-data changes alone do not.

If newly loaded data is missing, investigate:

  • Whether the rows were actually loaded
  • Whether their dimensional member keys match the cube
  • Whether the queried POV intersects those rows
  • Whether security filters the rows for the executing user

Do not rebuild every dimension as the first response to missing fact data.

Check Whether Dimension Metadata Changed

Section titled “Check Whether Dimension Metadata Changed”

If the dimension definition changed, the built dimension must be rebuilt before the new metadata is reflected in queries.

Examples include:

  • New members
  • Changed parent relationships
  • Changed member properties
  • Changed formulas stored in dimension metadata
  • Shared-member changes

If a POV references a member that was added to the source definition but the dimension has not been rebuilt, the active model may not yet contain that member.

See Dimension Validation & Build Errors and Rebuilding Dimensions.

Check Row-Level Security

Section titled “Check Row-Level Security”

Casabase Cube row-level security can silently remove rows or hierarchy members from a query result.

Configured security applies across supported query paths, including:

  • Query Builder
  • QUERY_CUBE
  • QUERY_CUBE_PIVOT_JSON
  • Scheduled query output
  • Secure fact views
  • Secure dimension views

Security filtering occurs before aggregation and calculation.

For a restricted user, a broad hierarchy expansion can therefore return only the permitted subset.

Security Filtering Is Silent

Section titled “Security Filtering Is Silent”

When a user is restricted from a member, Casabase Cube does not need to return an error identifying the restricted member.

Restricted rows can simply be absent from the result.

This means:

Query Returns Fewer Rows
        │
        ▼
Could Be Missing Data
        │
        ├── Yes
        │
        └── Could Be Security Filtering

If the query works for one user but returns fewer or no rows for another user, compare their security scope before assuming that the underlying cube data differs.

The Access Control documentation specifically recommends reviewing the user’s active rules, secured dimensions, selected hierarchy branches, POV selections, and default-member configuration when a user sees fewer rows than expected.

No Active Security Rules Does Not Restrict the User

Section titled “No Active Security Rules Does Not Restrict the User”

This is an important Casabase Cube security rule:

No active security rules means no Casabase Cube row-level restriction.

Security is opt-in per user. Filtering applies when security is enabled on the cube and the user has at least one active rule. A user with application access but no active rules is unrestricted by Casabase Cube row-level security.

Therefore:

No Active Security Rules
          │
          ▼
Not Restricted by
Casabase Cube Row-Level Security

Do not diagnose an empty result by saying the user has no security grant.

If a user has no active security rules and receives no data, investigate the POV, fact data, dimension state, query semantics, or access/configuration instead.

User Has Active Security Rules

Section titled “User Has Active Security Rules”

If the user does have active rules, review all secured dimensions involved in the query.

Check:

  • Which dimensions have security enabled
  • Which active rules apply to the user
  • Which hierarchy branches those rules permit
  • Whether multiple secured dimensions intersect
  • Whether the requested member is inside the permitted branch
  • Whether an omitted dimension resolves to a default member outside the effective permitted context

Security is applied before aggregation and formula evaluation, so totals and calculations reflect the permitted data scope.

See Access Control and Access & Permission Issues.

Compare Users Carefully

Section titled “Compare Users Carefully”

If:

User A → Query Returns Data
User B → Query Returns No Data

the difference can help isolate the cause.

Compare:

  • Active Snowflake user
  • Application-role access
  • Active Casabase Cube security rules
  • Default-member interaction
  • Exact POV
  • Query interface

Run the same query text for both users when possible.

Do not compare results from two slightly different POVs and conclude that security caused the difference.

Query Builder Works but SQL Does Not

Section titled “Query Builder Works but SQL Does Not”

If Query Builder returns data but a manually constructed QUERY_CUBE call does not, compare the effective POVs.

Common differences include:

  • Member aliases used instead of member names
  • Different member-selection operators
  • Omitted dimensions
  • Different default-member resolution
  • JSON construction errors
  • Different cube names
  • Different alias-set arguments

Query Builder and QUERY_CUBE use the same security-aware model, so switching interfaces is not a security bypass.

SQL Works but Query Builder Does Not

Section titled “SQL Works but Query Builder Does Not”

If the user can execute an equivalent supported SQL query but Query Builder does not operate correctly, verify the application’s session-context privilege.

Casabase Cube uses:

READ SESSION

for functionality involving session context such as CURRENT_USER(), row-level security, and Query Builder user context.

See Access & Permission Issues.

Empty Result vs. Query Refusal

Section titled “Empty Result vs. Query Refusal”

Do not confuse these conditions.

Empty Result

Section titled “Empty Result”

The query executes successfully but no rows match the effective query context.

Investigate:

  • POV
  • Member names
  • Selection operators
  • Default members
  • Fact data
  • Dimension state
  • Security

Query Refusal

Section titled “Query Refusal”

The query returns an explanatory error because the request cannot be evaluated within supported calculation semantics or scale.

Examples can include broad member expansions combined with calculated-member logic or other unsupported calculation conditions.

Casabase Cube refuses these queries rather than returning an approximate answer.

Do not repeatedly retry an identical refused query. Change the query according to the returned guidance.

A Broad Query Was Refused

Section titled “A Broad Query Was Refused”

Broad selections involving calculated members can exceed supported calculation scale.

For example:

{"idescendants":"Total Entity"}

can expand a large population of members.

If the returned error indicates a calculation-scale limit, narrow the query to the grain actually required.

Possible approaches include:

  • Select a more specific branch
  • Use a smaller explicit member list
  • Use children when only one level is required
  • Use bottom when only leaf detail is required

The goal is not merely to make the query smaller. The revised POV should still represent the business question being asked.

Result Truncation Is Not an Empty Result

Section titled “Result Truncation Is Not an Empty Result”

Casabase Cube can bound result size using a configured result-row limit.

A result that exceeds that limit may be truncated. The pivot query interface reports when truncation occurs.

Truncation means:

Rows Were Produced
       │
       ▼
Result Exceeded Limit
       │
       ▼
Only Supported Result Size Returned

It does not mean that no data exists.

If truncation occurs, first determine whether the POV should be narrowed rather than automatically increasing the configured row limit.

Check Formula Behavior Separately

Section titled “Check Formula Behavior Separately”

If stored members return data but a calculated member does not, the problem may be formula-related rather than a general data-availability issue.

Test a nearby stored member using the same dimensional context.

For example:

Stored Member Returns Value
        │
        ▼
Calculated Member Empty / Wrong
        │
        ▼
Investigate Formula

Continue with Formula Errors and Formula Validation.

Run Health Check

Section titled “Run Health Check”

For a configured cube, Health Check is the primary general-purpose diagnostic.

Run:

CALL CUBE.HEALTH_CHECK('FINANCE');

Then review non-OK findings:

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

For query problems, pay particular attention to findings involving:

  • Cube configuration
  • Dimension tables
  • Dimension row counts
  • Input-table configuration
  • Configuration integrity
  • Formula health

See Using Health Check.

Section titled “Recommended Diagnostic Sequence”

For a query that returns no data:

Capture Exact Query
        │
        ▼
Did It Error?
        │
        ├── Yes ──► Follow Refusal / Error
        │
        └── No
              │
              ▼
        Verify Cube Name
              │
              ▼
        Verify Member Names
              │
              ▼
        Verify POV Operators
              │
              ▼
        Review Default Members
              │
              ▼
        Check Matching Fact Data
              │
              ▼
        Check Dimension State
              │
              ▼
        Check Active Security Rules
              │
              ▼
          HEALTH_CHECK
              │
              ▼
        Simplify and Retest

Work from the smallest known-good query outward.

If the Problem Remains

Section titled “If the Problem Remains”

Generate support information:

CALL CUBE.GENERATE_SUPPORT_INFO('FINANCE');

Collect:

  • Cube name
  • Exact query call
  • Complete POV JSON
  • Exact error, if any
  • Executing Snowflake user
  • Whether the issue affects one or multiple users
  • Expected result
  • Actual result
  • Relevant security rules
  • Recent dimension or data changes
  • Health Check findings
  • Approximate time of execution

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

See Getting Additional Help.

Section titled “Related Topics”