Querying Overview

Casabase Cube provides multiple ways to query and consume multidimensional data stored in Snowflake.

For interactive analysis, users can build queries visually with Query Builder. For programmatic access, applications and SQL users can execute the Casabase Cube query engine directly with QUERY_CUBE or QUERY_CUBE_PIVOT_JSON. Secure Snowflake views provide direct SQL access to stored cube data and dimension metadata.

All of these interfaces operate on the same cube model and Snowflake data, but they are designed for different use cases.

Querying Casabase Cube

There are three primary ways to access cube data:

Method	Best For	Calculation Behavior
Query Builder	Interactive analysis and query development	Uses the Casabase Cube query engine
`QUERY_CUBE` / `QUERY_CUBE_PIVOT_JSON`	SQL, applications, reporting, and programmatic access	Applies hierarchy aggregation, member formulas, and row-level security
Secure SQL Views	BI tools, bulk extracts, and custom SQL	Provides security-filtered access to stored leaf-level data and dimension metadata

Saved Queries provide an additional layer on top of the query engine by allowing a Point of View (POV) to be named, reused, and optionally scheduled for recurring materialization.

The Point of View

A Casabase Cube query is defined by a Point of View (POV).

The POV identifies the dimensions and members that should participate in the query.

For example:

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

This query requests:

Units from the MEASURES dimension
The direct children of Qtr1 from TIME
Curr Year from YEARS

The query engine resolves those selections against the cube’s hierarchy metadata and returns the corresponding multidimensional results.

POVs can select individual members or dynamically expand hierarchy relationships such as children, descendants, ancestors, siblings, and bottom-level members.

See Point of View (POV) for the complete POV syntax and member-selection reference.

Dynamic Hierarchy Aggregation

Casabase Cube calculates hierarchy aggregations dynamically when a query is executed.

You can query a parent member directly:

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

or expand the hierarchy:

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

The first query returns the aggregated value for Qtr1.

The second returns the individual children of Qtr1.

This allows the query to request the level of detail required by the analysis rather than requiring pre-aggregated result tables for every possible hierarchy level.

Calculated Members

Calculated members are evaluated dynamically when they are included in a query.

From the perspective of the query, a calculated member can be selected like another member:

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

If the selected members contain formulas, the Casabase Cube calculation engine evaluates those formulas within the multidimensional context of the query.

No special query syntax is required simply because a member is calculated.

See Formulas for details about member formulas and calculation behavior.

Result Shape

QUERY_CUBE returns relational results containing the dimensions participating in the resolved query and an AMT column containing the calculated value.

For example:

MEASURES   TIME   YEARS       AMT
---------  -----  ----------  -----
Units      Jan    Curr Year   42228
Units      Feb    Curr Year   20841
Units      Mar    Curr Year   31434

The dimensions that appear in the result depend on the POV and the cube configuration.

A dimension explicitly included in the POV appears in the result.

When a dimension is omitted, its behavior depends on whether a default member has been configured for that dimension:

With no default member, the omitted dimension does not appear as an output column.
With a default member, the dimension resolves to that default and appears in the result.

A default member can also change the value returned because the query is sliced to the configured member rather than aggregated across the entire dimension.

Default members therefore affect both the shape and the values of query results.

See Point of View (POV) for the complete default-member behavior.

Query Builder

Query Builder provides a visual interface for constructing and executing cube queries.

Users can browse cube dimensions and members, select hierarchy expansions, configure aliases, review the generated POV, estimate query size, and execute the query without manually constructing SQL.

Conceptually:

Select Cube
    │
    ▼
Select Members
    │
    ▼
Build POV
    │
    ▼
Execute Query
    │
    ▼
Review Results

Because Query Builder uses the same cube query engine available through SQL, it is also useful for developing and validating POV definitions before incorporating them into applications or other programmatic workflows.

See Query Builder.

Querying with SQL

The primary SQL interface to the Casabase Cube calculation engine is:

CALL CUBE.QUERY_CUBE(
    cube_name,
    pov_string,
    output_table,
    alias_table
);

The cube configuration determines the underlying fact table and measure column. They are not supplied by the caller.

Results can be returned directly or written to a reporting table in SHARED_DATA.

Casabase Cube also provides:

QUERY_CUBE_PIVOT_JSON

for consumers that need cross-tabulated results, such as spreadsheet integrations or grid components.

See Querying with SQL for procedure syntax, materialization, discovery procedures, and programmatic query examples.

Saved Queries

A Saved Query is a named, reusable POV associated with a cube.

Instead of repeatedly rebuilding the same member selections, users can define the POV once and reuse it.

Saved Queries can also be scheduled to execute automatically and materialize their results into a reporting table in SHARED_DATA.

This is useful when the same calculated dataset is consumed repeatedly by dashboards, reports, applications, or other downstream workloads.

See Saved Queries.

Cube Variables

Cube Variables allow values inside a POV to be resolved dynamically at execution time.

For example, a Saved Query can reference a variable representing the current year rather than hardcoding a particular year into the POV.

This allows the same query definition to continue working as reporting periods, scenarios, versions, or other business selections change.

See Cube Variables.

Direct SQL Access

Casabase Cube also exposes secure Snowflake views for consumers that need direct relational access.

Each cube provides:

<CUBE>_FACT_SECURE

for security-filtered stored fact data, and:

<CUBE>_DIMENSIONS

for dimension and hierarchy metadata.

The secure fact view contains stored leaf-level data. It does not evaluate member formulas or dynamically calculate hierarchy roll-ups.

This distinction is important:

Need calculated members or hierarchy-aware totals?
        │
        └── Use QUERY_CUBE

Need stored leaf-level data for SQL or an extract?
        │
        └── Use <CUBE>_FACT_SECURE

Need hierarchy/member metadata?
        │
        └── Use <CUBE>_DIMENSIONS

See Querying with SQL for details.

Row-Level Security

Casabase Cube applies configured row-level security to query results for users who have active Casabase Cube security rules. Security is opt-in per user; enabling security on a dimension does not automatically restrict every application user.

Security filtering applies across the supported query paths, including:

Query Builder
QUERY_CUBE
QUERY_CUBE_PIVOT_JSON
Scheduled Saved Query results
Secure SQL views

When a user does not have access to a member, restricted rows are omitted from the result rather than producing an error.

This allows the same query definition or Saved Query to return the appropriate subset of data for different users based on their access.

Choosing a Query Method

Use Query Builder when exploring a cube interactively or developing a POV.

Use QUERY_CUBE when you need calculated multidimensional results through SQL, an application, a report, or another programmatic workflow.

Use QUERY_CUBE_PIVOT_JSON when the consumer needs a cross-tabulated result with dimensions represented as column headers.

Use a Saved Query when the same POV will be reused or scheduled.

Use the secure fact view when you need stored leaf-level data without formula evaluation or hierarchy aggregation.

Use the dimension view when you need member and hierarchy metadata.