Querying with SQL

Casabase Cube can be queried programmatically through Snowflake SQL.

The primary multidimensional query interface is:

CUBE.QUERY_CUBE

Casabase Cube also provides:

• QUERY_CUBE_PIVOT_JSON for cross-tabulated JSON output
• Discovery procedures for inspecting cubes, dimensions, and members
• Secure Snowflake views for direct access to stored leaf-level data and dimension metadata

Use QUERY_CUBE when the requested result requires Casabase Cube’s multidimensional processing, including hierarchy aggregation, member formulas, and row-level security.

QUERY_CUBE

QUERY_CUBE is the primary SQL interface for retrieving multidimensional cube results.

The procedure signature is:

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

Parameters

Parameter	Required	Description
cube_name	Yes	Name of the cube to query.
pov_string	Yes	JSON Point of View defining the requested multidimensional selection.
output_table	No	If supplied, writes the result to a table in SHARED_DATA instead of returning it inline.
alias_table	No	Alias set used for returned display names.

The fact table and measure column are resolved from the cube’s configuration. They are not specified by the caller.

Basic Query

For example:

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

A result could be:

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

AMT contains the calculated value for the returned multidimensional intersection.

Querying Detail Members

Hierarchy operators can be used directly in the POV.

For example:

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

This returns the direct children of Qtr1:

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

The complete selection language is documented in Point of View (POV).

Calculated Members

Calculated members can be selected in the same way as other members.

For example:

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

If the selected members contain formulas, Casabase Cube evaluates them dynamically as part of query processing.

No alternate query procedure is required simply because a member is calculated.

Aliases

Pass an alias set name as the fourth argument when the result should display aliases instead of member names.

For example:

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

POV member references always use underlying member names.

Aliases affect output only.

To inspect the alias sets available for a cube:

CALL CUBE.GET_CUBE_DIMENSIONS_META('ASOSAMP');

The returned ALIAS_COLS value identifies the configured alias columns for each dimension.

Returning Results Inline

When output_table is NULL, the query result is returned directly by the procedure.

For example:

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

Inline results are useful for:

• Snowflake worksheets
• Interactive SQL development
• Applications that consume stored-procedure results
• Validation and troubleshooting
• Ad hoc analytical queries

For repeated downstream consumption, materializing the result to a table is often more appropriate.

Materializing Query Results

Supplying an output table name causes QUERY_CUBE to write the result to a Snowflake table instead of returning the data inline.

For example:

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

The result can report:

ROWS_WRITTEN   TABLE_NAME   STATUS
-------------  -----------  -------
3              Q1_UNITS     SUCCESS

The target is created in SHARED_DATA.

Pass an unqualified table name such as:

Q1_UNITS

rather than a fully qualified target.

A target outside SHARED_DATA is rejected.

The resulting table can then be queried normally:

SELECT *
FROM CASABASE_CUBE.SHARED_DATA.Q1_UNITS;

Materialization Replaces the Table

Materialized output is a current snapshot.

Each execution fully replaces the target table rather than appending additional rows.

Conceptually:

QUERY_CUBE
    │
    ▼
Calculate Current Result
    │
    ▼
Replace Target Table
    │
    ▼
Current Snapshot

This is not an accumulating historical table.

If historical snapshots are required, use a separate downstream process to preserve each materialized result.

Why Materialize Query Results

Materialization is useful when the calculated result needs to become a stable relational dataset that can be consumed independently of the original cube query.

For example:

Casabase Cube
     │
     ▼
 QUERY_CUBE
     │
     ▼
Materialized Table
     │
     ├──► BI Dashboard
     ├──► Finance Team
     ├──► Analytics Team
     ├──► Data Pipeline
     ├──► Snowflake SQL
     └──► Application

The cube performs the multidimensional calculation once, and downstream consumers read the resulting table using ordinary Snowflake SQL.

This can be valuable when:

• A result is reused many times between data loads
• Other teams need access to a prepared business dataset
• BI tools work more naturally with tables than stored procedures
• A calculated cube result needs to participate in additional relational modeling
• Applications need a stable result shape
• The same business calculation would otherwise be executed repeatedly by many consumers

The materialized table contains the output of the cube query, including the hierarchy aggregation and applicable calculated-member behavior used to produce that result.

Materialization for BI and Analytics

A common pattern is to materialize a multidimensional result and expose the resulting table to downstream analytics tools.

For example:

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

Consumers can then use:

SELECT *
FROM CASABASE_CUBE.SHARED_DATA.PRODUCT_DETAIL_Q1;

This allows tools and teams that do not need to understand Casabase Cube POV syntax to consume a standard Snowflake table.

Materialization for Organizational Data Sharing

Materialized results are also useful when the cube calculation is owned by one team but the resulting data is needed by others.

For example:

Finance
  │
  │ Defines governed cube logic
  ▼
Casabase Cube
  │
  │ Calculates approved result
  ▼
SHARED_DATA.REPORTING_TABLE
  │
  ├──► FP&A
  ├──► Corporate Reporting
  ├──► Data Engineering
  ├──► Business Intelligence
  └──► Data Science

This allows the multidimensional business logic to remain centralized in Casabase Cube while the resulting dataset can participate in the organization’s broader Snowflake analytics environment.

Scheduled Materialization

When a materialized result needs to be refreshed automatically, use a scheduled Saved Query.

A Saved Query stores the POV, and the schedule executes that query on a recurring basis and replaces the target table in SHARED_DATA.

Conceptually:

Saved Query
   │
   ▼
Schedule
   │
   ▼
QUERY_CUBE
   │
   ▼
Target Table

The underlying query behavior remains the same. Scheduling automates when the Saved Query is executed and materialized.

See Saved Queries for schedule configuration and management.

QUERY_CUBE_PIVOT_JSON

QUERY_CUBE_PIVOT_JSON executes the same type of multidimensional query but returns the result as cross-tabulated JSON.

The procedure signature is:

CALL CUBE.QUERY_CUBE_PIVOT_JSON(
    cube_name,
    pov_string,
    column_dims,
    alias_table
);

Parameters

Parameter	Required	Description
cube_name	Yes	Name of the cube.
pov_string	Yes	POV using the same syntax as QUERY_CUBE.
column_dims	Yes	Array of dimensions that should become pivoted columns.
alias_table	No	Alias set used for display names.

At least one column dimension is required, and at most two column dimensions can be specified.

Pivoted JSON Example

For example:

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

The returned JSON includes structures such as:

{
  "row_dimensions": ["MEASURES", "YEARS"],
  "column_dimensions": ["TIME"],
  "column_names": [
    "MEASURES",
    "YEARS",
    "Jan",
    "Feb",
    "Mar"
  ],
  "headers": [
    {
      "dimension": "TIME",
      "level": 0,
      "values": ["Jan", "Feb", "Mar"]
    }
  ],
  "data": [
    ["Transactions", "Curr Year", 44500, 22038, 33505],
    ["Units", "Curr Year", 42228, 20841, 31434]
  ],
  "row_count": 2,
  "column_count": 3,
  "max_rows": 100000,
  "truncated": false
}

The dimensions listed in column_dims become columns, while the remaining dimensions stay on the rows.

Pivoted JSON Result Fields

Important fields include:

Field	Description
row_dimensions	Dimensions represented on the row axis.
column_dimensions	Dimensions pivoted to columns.
column_names	Complete output column order.
headers	Header metadata used to render column groups.
data	Result rows aligned with column_names.
row_count	Number of returned rows.
column_count	Number of pivoted data columns.
max_rows	Maximum row threshold for the result.
truncated	Indicates whether the result was cut off by the row limit.

When two dimensions are pivoted, the headers structure provides the information needed to render nested column groupings.

When to Use QUERY_CUBE_PIVOT_JSON

Use QUERY_CUBE_PIVOT_JSON when the consumer needs a grid-oriented result.

Typical uses include:

• Spreadsheet integrations
• Cross-tab reports
• Analytical grid components
• UI components with multi-level headers
• Application APIs that need structured row and column metadata

Use QUERY_CUBE when a relational row-based result is more appropriate.

Discovering Available Cubes

Before constructing a query programmatically, you can inspect the cubes available to the current user.

Use:

CALL CUBE.LIST_CUBES();

The result includes information such as:

CUBE_NAME
HIERARCHY_COUNT
ACTIVE_HIERARCHIES
FORMULA_COUNT
HAS_SECURITY
LAST_UPDATED

This procedure requires only CUBE_PUBLIC.

Discovering Cube Dimensions

Use:

CALL CUBE.LIST_DIMENSIONS('ASOSAMP');

to list the dimensions associated with a cube.

Returned information includes fields such as:

DIM_NAME
INPUT_TABLE_NAME
DATA_TABLE_COL_NAME
ALIAS_COL
ACTIVE
AUTO_REBUILD

The DIM_NAME values are the dimension keys used in a POV.

Inspecting Top and Default Members

Use:

CALL CUBE.GET_CUBE_INFO('ASOSAMP');

to inspect information such as:

CUBE_NAME
DIM_NAME
DESCRIPTION
DEFAULT_MEMBER
IS_TIME_DIMENSION
TOP_OF_DIMENSION

TOP_OF_DIMENSION identifies the top member that can be used as an anchor for hierarchy selections such as:

{"ichildren": "Top Member"}

or:

{"idescendants": "Top Member"}

DEFAULT_MEMBER is also important because it affects how omitted dimensions are resolved.

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

Inspecting Dimension Size and Aliases

Use:

CALL CUBE.GET_CUBE_DIMENSIONS_META('ASOSAMP');

to inspect metadata such as:

DIM_NAME
TABLE_NAME
DATA_TABLE_COL_NAME
MEMBER_COUNT
LEAF_COUNT
ALIAS_COLS

MEMBER_COUNT and LEAF_COUNT are useful when deciding whether a broad hierarchy expansion is appropriate.

ALIAS_COLS identifies available aliases.

Searching for Members

Use SEARCH_MEMBERS to find members by name or alias before building a POV.

For example:

CALL CUBE.SEARCH_MEMBERS(
    '{
        "cube_name": "ASOSAMP",
        "dimension": "PRODUCTS",
        "search": "Digital",
        "limit": 25
    }'
);

Supported parameters include:

Parameter	Required	Description
cube_name	Yes	Cube to search.
dimension	Yes	Dimension to search.
search	No	Member or alias search text.
alias_table	No	Alias set used for searching.
limit	No	Maximum results. Default is 100.

The result contains member names that can then be used directly in POV definitions.

A Programmatic Query Workflow

A useful programmatic workflow is:

LIST_CUBES
    │
    ▼
LIST_DIMENSIONS
    │
    ▼
GET_CUBE_INFO
    │
    ▼
GET_CUBE_DIMENSIONS_META
    │
    ▼
SEARCH_MEMBERS
    │
    ▼
Construct POV
    │
    ▼
QUERY_CUBE

Not every application needs every discovery call.

However, these procedures make it possible to build clients that discover the cube model rather than hardcoding every dimension and member.

Direct SQL Access

Casabase Cube also exposes secure Snowflake views for consumers that need standard relational access rather than multidimensional query-engine results.

Each cube provides two secure views:

<CUBE>_FACT_SECURE
<CUBE>_DIMENSIONS

Both are security-filtered and available to CUBE_PUBLIC.

<CUBE>_FACT_SECURE

The secure fact view provides stored leaf-level fact data.

For example:

SELECT
    PRODUCTS,
    TIME,
    SUM(AMT) AS UNITS
FROM CASABASE_CUBE.SHARED_DATA.ASOSAMP_FACT_SECURE
WHERE MEASURES = 'Units'
  AND YEARS = 'Curr Year'
GROUP BY PRODUCTS, TIME
ORDER BY PRODUCTS, TIME;

The view contains one column per dimension plus AMT.

It represents stored intersections.

It does not:

• Evaluate member formulas
• Dynamically aggregate hierarchy parent values
• Return calculated members

A SQL SUM over this view aggregates the stored rows selected by the SQL statement. It is not equivalent to asking the Casabase Cube calculation engine for a parent member value.

When to Use the Secure Fact View

Use <CUBE>_FACT_SECURE for:

• Bulk extracts
• Custom relational SQL
• Stored leaf-level data analysis
• BI tools that need a table
• Data pipelines that do not require member formulas or hierarchy-aware totals

Use QUERY_CUBE when the result requires multidimensional behavior.

<CUBE>_DIMENSIONS

The dimension view exposes hierarchy and member metadata.

For example:

SELECT
    DIM_NAME,
    CHILD,
    PARENT,
    ALIAS,
    GENERATION,
    LEVEL,
    ISLEAF,
    SORTORDER
FROM CASABASE_CUBE.SHARED_DATA.ASOSAMP_DIMENSIONS
WHERE DIM_NAME = 'TIME'
ORDER BY SORTORDER;

Useful metadata includes:

Column	Description
DIM_NAME	Dimension name used as a POV key.
CHILD	Member name.
PARENT	Parent member.
ALIAS	Display alias.
GENERATION	Depth from the top.
LEVEL	Depth from the leaves.
ISLEAF	Indicates whether the member has children.
AGG	Consolidation operator.
SORTORDER	Natural hierarchy order.
LEAF_COUNT	Number of leaf descendants.
UDA	User-Defined Attributes.
FORMULA_SOURCE	Formula as authored.
TB_TYPE	Time-balance behavior.

This view is useful for building:

• Member pickers
• Drill-down trees
• Validation tools
• Metadata reports
• Hierarchy-aware applications

Choosing Between QUERY_CUBE and Secure Views

Use the following rule:

Requirement	Interface
Parent-member aggregation	QUERY_CUBE
Calculated members	QUERY_CUBE
Formula evaluation	QUERY_CUBE
POV hierarchy operators	QUERY_CUBE
Pivoted multidimensional result	QUERY_CUBE_PIVOT_JSON
Stored leaf-level fact data	<CUBE>_FACT_SECURE
Custom relational SQL	<CUBE>_FACT_SECURE
Hierarchy/member metadata	<CUBE>_DIMENSIONS

Conceptually:

Need Cube Calculation Logic?
       │
       ├── Yes ──► QUERY_CUBE
       │
       └── No
            │
            ├── Fact Data ──► <CUBE>_FACT_SECURE
            │
            └── Metadata ──► <CUBE>_DIMENSIONS

Security

Row-level security is applied across all supported query paths.

This includes:

• QUERY_CUBE
• QUERY_CUBE_PIVOT_JSON
• Scheduled query output
• <CUBE>_FACT_SECURE
• <CUBE>_DIMENSIONS

Row-level filtering is opt-in per user. Enabling security on a dimension does not by itself restrict every user. Filtering applies when the cube has a security-enabled dimension and the executing user has at least one active Casabase Cube security rule.

For a restricted user, the query returns only the permitted member scope. Security filtering is silent: if a query explicitly or implicitly requests members outside that scope, those rows are omitted rather than causing an error.

A user with application access but no active Casabase Cube security rules is not restricted by Casabase Cube row-level security.

This allows the same POV or SQL interface to return the appropriate permitted subset for restricted users while remaining unrestricted for users without active rules.

Query Limits and Errors

Casabase Cube refuses unsupported or overly broad requests rather than returning an approximate result.

Common conditions include:

Situation	Resolution
Calculated members on more than two dimensions	Limit calculated members to at most two dimensions.
Expansion exceeds the supported scale for a calculated query	Narrow the hierarchy selection.
Time balance requires a related account dimension that is missing from the POV	Include the required dimension or query a base time period.
A dimension has not been built	Build the dimension before querying it.
A formula contains an unsupported construct	Correct the formula or avoid that calculated member.
POV dimension value is not an array	Correct the POV JSON structure.

The query engine reports the problem instead of attempting to approximate an answer.

Pivot Result Limits

QUERY_CUBE_PIVOT_JSON reports:

max_rows
truncated

If:

"truncated": true

the requested result exceeded the supported row threshold.

Narrow the POV rather than repeatedly executing the same oversized query.

Writing Efficient Queries

Select Only the Dimensions You Need

Omitted dimensions do not appear in the result unless a configured default causes them to resolve into the query.

A narrower query typically produces fewer result columns and less work.

Be aware that default members can change this behavior.

Request the Grain You Need

Use the hierarchy operator that matches the intended result.

For example:

{"children": "Qtr1"}

is much narrower than expanding an entire Time dimension to bottom level.

Check Dimension Size Before Broad Expansions

Use:

CALL CUBE.GET_CUBE_DIMENSIONS_META('ASOSAMP');

before performing broad expansions on unfamiliar dimensions.

A dimension containing thousands of members can multiply dramatically when crossed with other dimensions.

Prefer Specific Anchors

If only one hierarchy branch is needed:

{"descendants": "Personal Electronics"}

is more targeted than:

{"idescendants": "Products"}

Materialize Repeated Results

If the same calculated result is read repeatedly between data refreshes, materialize it once rather than recalculating it for every consumer.

For unattended recurring refreshes, use a scheduled Saved Query.

Use the Secure Fact View for Bulk Stored-Data Extracts

If you need raw stored data and do not need formulas or hierarchy aggregation, <CUBE>_FACT_SECURE is the more direct interface.

Use Cube Variables for Moving Targets

A POV containing:

&CurrentPeriod

or:

&CurrentYear

can follow a changing business period without requiring the query definition itself to be rewritten.

Example: Single Aggregated Value

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

Example: Monthly Trend

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

Example: Product Branch with Subtotals

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

Because idescendants is inclusive, the selected branch total appears together with its descendants.

Example: Two Dimensions with a Pivot

CALL CUBE.QUERY_CUBE_PIVOT_JSON(
    'ASOSAMP',
    '{
        "MEASURES": ["Units"],
        "PRODUCTS": [
            {"children": "Personal Electronics"}
        ],
        "TIME": [
            {"children": "Qtr1"}
        ],
        "YEARS": ["Curr Year"]
    }',
    ARRAY_CONSTRUCT('TIME'),
    NULL
);

Products remain on the rows while months are returned across columns.

Example: Materialized Dashboard Dataset

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

Then:

SELECT *
FROM CASABASE_CUBE.SHARED_DATA.PRODUCT_DETAIL_Q1;

This separates the multidimensional calculation from downstream consumption.

Example: Search Before Querying

CALL CUBE.SEARCH_MEMBERS(
    '{
        "cube_name": "ASOSAMP",
        "dimension": "PRODUCTS",
        "search": "Camera"
    }'
);

Use the returned member names as anchors in the POV.

Example: Direct SQL Against Stored Data

SELECT
    TIME,
    SUM(AMT) AS UNITS
FROM CASABASE_CUBE.SHARED_DATA.ASOSAMP_FACT_SECURE
WHERE MEASURES = 'Units'
  AND YEARS = 'Curr Year'
GROUP BY TIME
ORDER BY TIME;

This query works against stored data only. It does not invoke calculated-member or hierarchy-rollup logic.

Related Topics

• Querying Overview
• Query Builder
• Point of View (POV)
• Saved Queries
• Cube Variables
• Formula Overview