Querying

How do I query a Casabase Cube?

Casabase Cube supports several query paths depending on the use case:

Query Builder for interactive multidimensional querying
QUERY_CUBE for programmatic calculation-engine queries
QUERY_CUBE_PIVOT_JSON for pivot-oriented output
Saved Queries for reusable query definitions
Scheduled Saved Queries for materialized recurring results
Secure views for direct SQL access to security-filtered stored facts and dimension metadata

For most analytical use cases that require hierarchy aggregation, formulas, Time Balance, or other multidimensional calculations, use the Casabase Cube query engine.

See Querying Overview.

What is QUERY_CUBE?

QUERY_CUBE is the primary programmatic query interface for Casabase Cube.

Its documented interface is:

QUERY_CUBE(
    cube_name,
    pov_string,
    output_table,
    alias_table
)

For example:

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

The query engine handles:

Hierarchy-aware aggregation
Member formulas
Time-aware calculations
Solve order
Row-level security
Point of View resolution

See Querying with SQL.

Do I specify the fact table when querying a cube?

No.

The fact table is resolved from the cube’s own configuration.

You do not provide a physical fact table or measure column to QUERY_CUBE.

Conceptually:

QUERY_CUBE
    │
    ├── Cube Name
    └── POV
         │
         ▼
Cube Configuration
         │
         ├── Managed Fact Data
         ├── Dimensions
         ├── Hierarchies
         ├── Formulas
         └── Security
         │
         ▼
       Result

This allows clients to query the logical multidimensional model rather than coupling every query to an underlying physical storage object.

What is a Point of View?

A Point of View, or POV, defines the multidimensional selections for a query.

Programmatically, a POV is represented as JSON.

For example:

{
  "MEASURES": ["Units"],
  "TIME": [{"children": "Qtr1"}],
  "YEARS": ["Curr Year"]
}

Each key identifies a dimension, and each value contains the member selections for that dimension.

See Point of View (POV).

Do POV values have to be JSON arrays?

Yes.

Each dimension value in a programmatic POV is represented as an array.

Correct:

{
  "TIME": ["Qtr1"]
}

Incorrect:

{
  "TIME": "Qtr1"
}

Even when selecting only one member, use an array.

Can I select more than one member from a dimension?

Yes.

Each selected member or set expression is a separate element in the dimension’s array.

For example:

{
  "SCENARIO": ["Actual", "Budget", "Forecast"]
}

Do not place several member names into one comma-separated string.

For example, this is not equivalent:

{
  "SCENARIO": ["Actual,Budget,Forecast"]
}

How do I select a level-0 member?

A level-0 member can be referenced directly by name.

For example:

{
  "ENTITY": ["United States"]
}

The direct member reference identifies that member in the query context.

How do I select a parent member?

For non-level-0 members selected individually, use the supported member-selection syntax.

For example:

{
  "ENTITY": [
    {
      "member": "North America"
    }
  ]
}

This explicitly selects the parent member itself rather than expanding one of its hierarchy sets.

See Point of View (POV) for the supported selection operators.

How do I select the children of a member?

Use:

children

For example:

{
  "TIME": [
    {
      "children": "Qtr1"
    }
  ]
}

This selects the immediate children of Qtr1.

Conceptually:

Qtr1
├── Jan
├── Feb
└── Mar

returns:

Jan
Feb
Mar

How do I select all descendants of a member?

Use:

idescendants

for the member and all applicable descendants.

For example:

{
  "ENTITY": [
    {
      "idescendants": "Total Entity"
    }
  ]
}

Hierarchy expansion occurs against the cube’s configured multidimensional structures.

See Point of View (POV).

Does Casabase Cube support hierarchy-aware member selection?

Yes.

POV selection can use hierarchy-aware operators for navigating members based on their relationships in the dimension.

Examples include operations such as:

member
children
idescendants
bottom
members

The appropriate operator depends on whether you want:

One specific member
Immediate children
A broader descendant set
Bottom-level members
A broader member set

See Point of View (POV) for the authoritative syntax and behavior.

What happens if I omit a dimension from the POV?

The behavior depends on whether the dimension has a configured default member.

If a dimension is omitted and has no default member, it is not included in the result set.

If an omitted dimension does have a default member, Casabase Cube automatically resolves that dimension to the configured default and includes it in the effective query context.

Conceptually:

Dimension Omitted
       │
       ▼
Default Member?
       │
   ┌───┴───┐
   │       │
  Yes      No
   │       │
   ▼       ▼
Resolve   Dimension
Default   Not Included
Member    in Result

For example:

SCENARIO
Default Member = Actual

means that a query omitting SCENARIO can automatically resolve:

SCENARIO = Actual

See Point of View (POV).

Which dimensions appear in the result set?

The result shape follows the effective Point of View.

Output includes:

Dimensions explicitly named in the POV
Dimensions automatically resolved through configured default members
The calculated value column

An omitted dimension without a default member does not appear simply because it exists in the cube.

Conceptually:

Submitted POV
ACCOUNT
ENTITY

SCENARIO Default = Actual
PERIOD No Default
        │
        ▼
Effective Result Shape
ACCOUNT
ENTITY
SCENARIO
AMT

PERIOD is absent because it was neither specified nor resolved through a default member.

Does omitting a dimension change the total?

Omitting a dimension changes the shape of the result, not the underlying multidimensional meaning of the calculation.

Casabase Cube resolves the requested multidimensional context and calculates the result across the applicable data and hierarchy scope.

A query with fewer displayed dimensions can therefore return a narrower result shape without requiring a different physical aggregation process.

What is the AMT column?

QUERY_CUBE returns the calculated cube value in:

AMT

The other output columns represent the dimensions in the effective query context.

For example:

MEASURES  TIME  YEARS      AMT
--------  ----  ---------  -----
Units     Qtr1  Curr Year  94503

The query caller does not identify an external amount column. Casabase Cube resolves the underlying cube value from the model configuration.

Are parent totals stored in the fact table?

Casabase Cube does not require traditional pre-aggregated parent totals.

Hierarchy aggregation is performed dynamically during query processing.

Conceptually:

Stored Bottom-Level Data
          │
          ▼
Hierarchy Expansion
          │
          ▼
Security Scope
          │
          ▼
Aggregation
          │
          ▼
Formulas
          │
          ▼
Result

This means data changes become available to the query engine without requiring a separate cube aggregation process.

Dimension metadata changes, however, require the applicable dimension to be rebuilt.

See Query Architecture.

Are formulas calculated at query time?

Yes.

When member formulas are enabled for the cube, applicable formulas are evaluated dynamically during query processing.

Formula evaluation can depend on:

Query context
Hierarchy relationships
Security scope
Formula dependencies
Solve order
Time behavior

See Formula Overview and Formula Capabilities.

Is security applied before or after aggregation?

Security is applied before aggregation and formula evaluation.

Conceptually:

Requested Members
       │
       ▼
Resolve Query Scope
       │
       ▼
Apply User Security
       │
       ▼
Permitted Data
       │
       ▼
Aggregation
       │
       ▼
Formula Evaluation
       │
       ▼
Result

This allows restricted users to receive valid calculations based only on their permitted data.

See Access Control and Security Model.

Can two users run the same query and get different totals?

Yes.

If the users have different row-level security scopes, the same query can produce different valid results.

For example:

Total Entity
├── North America
├── Europe
└── Asia Pacific

A user restricted to North America can query:

Total Entity

and receive a total calculated from the permitted North America descendants.

A user with broader access can receive a larger Total Entity value from the same query definition.

This behavior is called a partial rollup.

See Access Control.

Can a restricted user see a parent above their permitted branch?

Yes, where required to represent the requested aggregate.

The parent can appear in the result while its value is calculated only from the user’s permitted descendants.

For example:

Total Entity
        │
        ▼
User permitted only:
North America
        │
        ▼
Total Entity value =
North America permitted scope

Visibility of an aggregate member does not imply visibility of all underlying data.

What happens if a user has no security rules?

Casabase Cube row-level security is opt-in per user.

A user with application access but no active security rules for the cube is unrestricted by Casabase Cube row-level security.

Security filtering applies when:

Security Enabled
       +
Active Rule for User
       │
       ▼
Security Filtering

This is an important distinction when troubleshooting or reviewing access.

See Access Control.

Does security apply in Query Builder and SQL?

Yes.

Configured Casabase Cube security is enforced when supported Casabase Cube query interfaces execute, including:

Query Builder
QUERY_CUBE
QUERY_CUBE_PIVOT_JSON
Secure fact views
Secure dimension views

A scheduled Saved Query applies the security context of the identity that executes the materialization. The resulting materialized table is then a Snowflake table and is not dynamically re-filtered for each later reader.

Switching between interactive Casabase Cube query interfaces does not provide an alternate path around configured Casabase Cube security.

What is Query Builder?

Query Builder is the interactive Casabase Cube interface for constructing multidimensional queries without manually writing the JSON POV.

Users can select:

Cube
Dimensions
Members
Hierarchy sets
Other supported query options

and execute the resulting multidimensional query interactively.

See Query Builder.

Does Query Builder use a different calculation engine from QUERY_CUBE?

No.

Query Builder is an interactive way to construct and execute Casabase Cube multidimensional queries.

The same underlying multidimensional concepts apply:

Cube
  +
POV
  +
Security
  +
Hierarchy Aggregation
  +
Formulas
  =
Result

Programmatic SQL provides another interface to the same modeled business logic.

Do I need MDX?

No.

Casabase Cube does not require MDX or Oracle Essbase report scripts for querying.

Programmatic queries use Snowflake SQL and JSON POV definitions.

Interactive users can use Query Builder without writing SQL or POV JSON manually.

What is QUERY_CUBE_PIVOT_JSON?

QUERY_CUBE_PIVOT_JSON is a supported query-engine interface for producing pivot-oriented query output.

Like QUERY_CUBE, it operates against the Casabase Cube calculation engine and can apply multidimensional behavior such as:

Hierarchy aggregation
Member formulas
Time logic
Row-level security

See Querying with SQL for the supported interface and output format.

Can QUERY_CUBE write results to a table?

Yes.

QUERY_CUBE includes an optional output-table argument.

When an output destination is supplied, the query result can be written rather than returned directly to the caller.

The operation follows full-replacement behavior rather than exposing a partially written result.

Conceptually:

Execute Query
      │
      ▼
Calculate Complete Result
      │
   ┌──┴────┐
   │       │
Success   Failure
   │       │
   ▼       ▼
Replace   Existing
Output    Output Not
Table     Partially Replaced

This helps prevent consumers from reading an incomplete result.

What is an alias table?

QUERY_CUBE includes an optional alias-table argument.

Aliases allow display-oriented member names to be used where supported without changing the underlying member identities in the multidimensional model.

See Querying with SQL for the exact alias-table behavior.

What is a Saved Query?

A Saved Query is a named, reusable Point of View.

Instead of reconstructing the same multidimensional selections each time, the query definition can be stored and reused.

For example:

Q1 Units by Month

MEASURES → Units
TIME     → Children of Qtr1
YEARS    → Curr Year

Saved Queries can also participate in scheduled execution.

See Saved Queries.

How do I create a Saved Query?

Saved Queries can be managed through the supported Casabase Cube interfaces.

Programmatically, the public procedure supports actions such as:

ADD
UPDATE
DELETE

through:

MANAGE_SAVED_QUERY

For example:

CALL CUBE.MANAGE_SAVED_QUERY(
    'ADD',
    '{
      "cube_name": "ASOSAMP",
      "query_name": "Q1 Units by Month",
      "pov": {
        "MEASURES": ["Units"],
        "TIME": [{"children":"Qtr1"}],
        "YEARS": ["Curr Year"]
      }
    }'
);

See Saved Queries for complete management procedures.

Can I pass a Saved Query name directly to QUERY_CUBE?

No.

QUERY_CUBE accepts a Point of View, not a Saved Query name.

To execute a Saved Query programmatically, retrieve its stored POV and use that POV with the query engine.

For recurring unattended execution, schedule the Saved Query instead.

Can Saved Queries be scheduled?

Yes.

A Saved Query can be scheduled to run on a recurring basis and materialize its result into a reporting table.

The scheduled execution:

Saved Query
     │
     ▼
Snowflake Managed Task
     │
     ▼
Execute Query
     │
     ▼
Replace Reporting Table

The materialized table is replaced on each successful run so it represents the latest scheduled result.

See Saved Queries.

Does scheduling require an additional Snowflake privilege?

Yes.

Supported scheduled functionality uses Snowflake managed tasks and requires:

EXECUTE MANAGED TASK

to be granted to the Native App.

See Roles and Privileges.

Is the security of a scheduled result dynamic when someone later reads the table?

No.

This is an important distinction.

Security is evaluated when the scheduled query executes.

The resulting materialized table contains the output generated in that execution context.

It does not rerun Casabase Cube row-level security independently for every future reader of the materialized table.

Conceptually:

Scheduled Execution
       │
       ▼
Apply Execution Security
       │
       ▼
Calculate Result
       │
       ▼
Materialized Table
       │
       ▼
Stored Result

Access to the resulting Snowflake table must therefore be governed appropriately.

What are the secure fact and dimension views?

Each cube can expose security-aware views for direct SQL access.

Examples include:

<CUBE>_FACT_SECURE
<CUBE>_DIMENSIONS

The secure fact view provides access to stored leaf-level fact data within the current user’s applicable security scope.

The dimensions view provides security-aware multidimensional metadata.

These views are useful when a tool needs standard SQL rather than a stored-procedure query interface.

See Querying with SQL.

Are secure views equivalent to QUERY_CUBE?

No.

They solve different problems.

	Query Engine	Secure Views
Interface	`QUERY_CUBE` / `QUERY_CUBE_PIVOT_JSON`	Standard `SELECT`
Hierarchy aggregation	Yes	No
Member formulas	Yes	No
Time-aware calculations	Yes	No
POV selection	Yes	SQL predicates
Stored leaf data	Used to calculate results	Exposed directly
Security	Enforced	Enforced

Use the query engine when the answer depends on the multidimensional model.

Use secure views when you need security-filtered stored facts or dimension metadata and intend to perform your own SQL processing.

Can Power BI, Tableau, or other SQL tools use Casabase Cube?

Yes, depending on the integration pattern.

Tools that can connect to Snowflake can consume supported Casabase Cube interfaces such as:

Query results
Materialized Saved Query output
Secure SQL views
Other documented integration interfaces

The appropriate option depends on whether the tool needs calculated cube results or direct security-filtered leaf-level data.

See Integration Points and Integrations.

What happens if a query cannot produce a correct result?

Casabase Cube favors refusal over approximation.

If the engine cannot correctly evaluate a requested construct, it returns an error rather than silently producing an approximate or incomplete answer.

Examples can include:

Unsupported calculated-member combinations
A selection exceeding a supported calculated-member scale limit
Missing query context required for Time Balance
Other unsupported query constructs

Applications consuming Casabase Cube should surface these errors rather than suppressing them.

Why might a broad hierarchy selection fail with calculated members?

Some combinations of large hierarchy expansions and calculated members can exceed supported query scale limits.

For example, a very broad:

{
  "ENTITY": [
    {
      "idescendants": "Total Entity"
    }
  ]
}

combined with complex calculated-member selections can require more calculation expansion than the query supports.

When this occurs, narrow the selection where appropriate, for example by selecting:

A more specific parent
Immediate children
A smaller descendant branch
Stored members where possible

Casabase Cube returns an error rather than approximating the result.

Are there limits on calculated members across dimensions?

Yes. Some query shapes involving calculated members across multiple dimensions have supported limits.

Because these limits are calculation-engine specific and can evolve, use the current Querying with SQL and Formulas documentation as the authoritative reference rather than relying on a hard-coded FAQ limit.

Why might a Time Balance query require another dimension in the POV?

Time Balance behavior can depend on metadata defined in another dimension, commonly an Account-like dimension.

If the engine requires that dimension to resolve the applicable Time Balance behavior, the dimension must participate in the effective query context.

A query lacking required context is refused rather than calculated approximately.

See Time Navigation and Point of View (POV).

Is query output always current after loading new fact data?

Yes, subject to completion of the applicable data-load workflow.

Casabase Cube does not require a separate scheduled aggregation process to refresh stored parent totals.

Once the new fact data is available to the cube, subsequent query calculations use the current data.

Dimension metadata changes are different and require the affected dimension structures to be rebuilt.

Conceptually:

Fact Data Change
      │
      ▼
Load Data
      │
      ▼
Query Current Data


Dimension Metadata Change
      │
      ▼
REBUILD_DIMENSIONS
      │
      ▼
Query Updated Structure

Why does my query return fewer rows than expected?

Common causes include:

The POV selected fewer members than expected.
A hierarchy operator expanded differently than expected.
A dimension has a default member and was automatically resolved.
Row-level security restricted the effective scope.
The query used a secure view rather than the multidimensional calculation engine.
A saved or materialized query reflects a different execution context.

When troubleshooting, review:

POV
Default Members
Security Rules
Hierarchy Selection
Query Interface

See Point of View (POV) and Access Control.

Why is a dimension appearing in my result even though I didn’t include it?

The dimension probably has a configured default member.

An omitted dimension with a default member is automatically resolved into the effective query context.

For example:

Submitted POV
ACCOUNT
ENTITY

SCENARIO Default = Actual

can return:

ACCOUNT
ENTITY
SCENARIO
AMT

with:

SCENARIO = Actual

See Point of View (POV).

Why is a dimension missing from my result?

If a dimension was not included in the POV and does not have a configured default member, it is not included in the result set.

This is expected behavior.

The presence of a dimension in the cube does not mean every query must display that dimension.

Where should I start if I’m new to querying?

Start with:

For the underlying processing model, see Query Architecture.