Querying

5.1 Overview

Querying is how you retrieve aggregated data from Casabase Cube. Every query is defined by a POV (Point of View), a JSON structure that specifies which members to include from each dimension. Casabase Cube traverses your hierarchies, aggregates data from your source tables, applies formulas, and returns results, all at query time with no pre-computation.

You can query through two interfaces:

• Query Builder — The visual Streamlit UI with interactive dimension trees
• SQL — Stored procedures callable from any Snowflake-connected tool

5.2 Query Builder (Streamlit UI)

The Query Builder is the primary interface for interactive querying. It is available to all application roles (CUBE_ADMIN, CUBE_WRITER, CUBE_PUBLIC).

Building a Query

1. Navigate to Query Builder in the sidebar
2. Select a cube from the dropdown
3. For each dimension, select members using one of these methods:
   • Dimension trees — Expand the interactive tree and check the members you want
   • Member operators — Select operators like children, descendants, or bottom of a specific member
4. Optionally select formulas to include in the results
5. Click Run Query
6. Results appear in a data grid below

Query Builder Features

Feature	Description
Interactive dimension trees	Visual tree display of each hierarchy. Expand nodes, check members, and see aliases.
Member operators	Select members using operators like children, descendants, bottom, ancestors, and siblings.
Formula selection	Choose which calculated members to include in results.
Saved queries	Save your current POV as a named query for reuse. Load previously saved queries with one click.
Cube variables	Reference substitution variables in your selections (e.g., [CUBE_VAR=current_year]).
Export	Download query results as CSV.

5.3 Querying via SQL

All query functionality is available through stored procedures, making Casabase Cube accessible from any tool that can execute Snowflake SQL.

QUERY_CUBE

The primary query procedure. Requires you to specify the data table and amount column explicitly.

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE',                    -- Cube name
    'DB.SCHEMA.FACT_TABLE',       -- Fact table (fully qualified)
    'AMT',                        -- Amount column
    '{                            -- POV JSON
        "ACCOUNT": ["Revenue"],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["Total Entity"]
    }',
    NULL,                         -- Formula list (optional)
    NULL,                         -- Output table (optional)
    NULL                          -- Alias table (optional)
);

Parameters:

Parameter	Required	Description
CUBE_NAME	Yes	The name of the cube to query.
DATA_TABLE	Yes	Fully qualified fact table name (e.g., DB.SCHEMA.TABLE).
AMT_COLUMN	Yes	The numeric column to aggregate.
POV_STRING	Yes	JSON string defining the member selections for each dimension.
FORMULA_LIST	No	Formulas to include. Accepts a JSON array of formula names, a JSON array of inline formula objects, or NULL.
OUTPUT_TABLE	No	If specified, results are written to this table instead of returned. Useful for materializing results for downstream consumption.
ALIAS_TABLE	No	Fully qualified alias table name. When provided, dimension columns return alias values instead of member names.

QUERY_CUBE_AUTO

A simplified version that reads the data table and amount column from the cube’s settings (configured on the Cube Config page). Use this when your cube has default settings configured.

CALL CASABASE_CUBE.CUBE.QUERY_CUBE_AUTO(
    'MY_CUBE',                    -- Cube name
    '{                            -- POV JSON
        "ACCOUNT": ["Revenue"],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["Total Entity"]
    }',
    NULL,                         -- Formula list (optional)
    NULL,                         -- Output table (optional)
    NULL                          -- Alias table (optional)
);

QUERY_CUBE_PIVOT_JSON

Returns results in a pivoted JSON format with nested header structures. Useful for tools that need to render multi-level column headers (e.g., spreadsheet integrations).

CALL CASABASE_CUBE.CUBE.QUERY_CUBE_PIVOT_JSON(
    'MY_CUBE',
    'DB.SCHEMA.FACT_TABLE',
    'AMT',
    '{
        "ACCOUNT": [{"children": "Total P&L"}],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["East", "West"]
    }',
    NULL,                         -- Formula list
    ARRAY_CONSTRUCT('PERIOD'),    -- Dimensions to pivot to columns
    NULL                          -- Alias table (optional)
);

The COLUMN_DIMS parameter specifies which dimensions should become columns in the pivoted output, while all other dimensions remain as rows.

5.4 POV Syntax Reference

The POV (Point of View) is a JSON object where each key is a dimension name and each value defines the member selection for that dimension.

Member Selection Operators

Syntax	Example	Description
["Member"]	["Jan", "Feb", "Mar"]	Explicit list of members.
{"member": "X"}	{"member": "Total"}	Single member (returns only that member’s aggregate value).
{"children": "X"}	{"children": "Q1"}	Direct children of X.
{"ichildren": "X"}	{"ichildren": "Q1"}	X and its direct children.
{"descendants": "X"}	{"descendants": "Year"}	All descendants of X (every level below).
{"idescendants": "X"}	{"idescendants": "Year"}	X and all its descendants.
{"bottom": "X"}	{"bottom": "Year"}	Only bottom-level members under X.
{"ancestors": "X"}	{"ancestors": "Jan"}	All ancestors of X up to the root.
{"iancestors": "X"}	{"iancestors": "Jan"}	X and all its ancestors.
{"siblings": "X"}	{"siblings": "Jan"}	Members that share the same parent as X.
{"isiblings": "X"}	{"isiblings": "Jan"}	X and its siblings.
{"parent": "X"}	{"parent": "Jan"}	The parent of X.
[CUBE_VAR=name]	[CUBE_VAR=current_year]	Substitution variable — replaced with the variable’s value at query time.

Combining Operators

You can combine multiple selections for a single dimension using an array:

{
    "PERIOD": [{"children": "Q1"}, {"children": "Q2"}],
    "ACCOUNT": ["Revenue", "COGS", {"children": "Operating Expenses"}]
}

Using Saved Queries

Reference a saved query by name instead of building a POV from scratch:

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE',
    'DB.SCHEMA.FACT_TABLE',
    'AMT',
    '{"SAVED_QUERY": "Monthly P&L"}',
    NULL
);

The saved query’s POV and formula selections are loaded and executed automatically.

5.5 Including Formulas

Formulas (calculated members) can be included in queries in two ways:

Named Formulas (Stored)

Reference formulas by name that were previously defined on the Formulas page:

-- Single formula
CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE', 'DB.SCHEMA.TABLE', 'AMT',
    '{"ACCOUNT": [{"children": "Total P&L"}], "PERIOD": ["Q1"]}',
    '["Variance"]'
);

-- Multiple formulas
CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE', 'DB.SCHEMA.TABLE', 'AMT',
    '{"ACCOUNT": [{"children": "Total P&L"}], "PERIOD": ["Q1"]}',
    '["Variance", "Variance %", "YoY Growth"]'
);

Inline Formulas (Ad-Hoc)

Define formulas directly in the query without saving them. Inline formulas are passed as objects in the FORMULA_LIST parameter (5th argument), not in the POV string:

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE', 'DB.SCHEMA.TABLE', 'AMT',
    '{
        "ACCOUNT": ["Revenue", "COGS"],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["Total Entity"]
    }',
    '[{"name": "Gross Margin", "expression": "[ACCOUNT].[Revenue] - [ACCOUNT].[COGS]", "hierarchy": "ACCOUNT"}]'
);

Inline Formula Fields

Field	Required	Description
name	Yes	The display name for the calculated member in results.
expression	Yes	The formula expression. Use [DIMENSION].[Member] syntax for member references.
hierarchy	Yes	The dimension this formula belongs to. The formula appears as a member of this dimension in results.

5.6 Output Options

Direct Results (Default)

By default, QUERY_CUBE returns results as a result set — a table with one column per dimension plus an AMT column containing the aggregated value.

Example result:

ACCOUNT	PERIOD	ENTITY	AMT
Revenue	Jan	East	150000
Revenue	Feb	East	162000
Revenue	Mar	East	148000
COGS	Jan	East	85000

Write to Table

Specify an output table to materialize results for downstream consumption:

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'MY_CUBE', 'DB.SCHEMA.TABLE', 'AMT',
    '{"ACCOUNT": [{"children": "Total P&L"}], "PERIOD": [{"children": "Year"}]}',
    NULL,
    'MY_DB.MY_SCHEMA.CUBE_RESULTS'   -- Output table
);

This creates (or replaces) the specified table with the query results. Useful for:

• Feeding BI dashboards
• Creating scheduled refreshes via Snowflake Tasks
• Sharing results with users who do not have access to Casabase Cube

Pivoted JSON

Use QUERY_CUBE_PIVOT_JSON when you need a cross-tab layout with dimensions pivoted to columns. See Section 5.3 for details.

5.7 Query Examples

Basic: Single Member per Dimension

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": ["Revenue"],
        "PERIOD": ["Jan"],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    NULL
);

Drill-Down: Children of a Parent

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"children": "Total P&L"}],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    NULL
);

Multi-Level: Descendants

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"idescendants": "Total P&L"}],
        "PERIOD": ["YearTotal"],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    NULL
);

Bottom-Level Detail

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"bottom": "Operating Expenses"}],
        "PERIOD": [{"bottom": "Year"}],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    NULL
);

With Cube Variables

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"children": "Total P&L"}],
        "PERIOD": ["[CUBE_VAR=current_period]"],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["[CUBE_VAR=current_scenario]"],
        "VERSION": ["Final"]
    }',
    NULL
);

With Formulas and Inline Formulas Combined

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": ["Revenue", "COGS"],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": [{"children": "All Entities"}],
        "SCENARIO": ["Actual", "Budget"],
        "VERSION": ["Final"]
    }',
    '["Variance", {"name": "Gross Margin %", "expression": "([ACCOUNT].[Revenue] - [ACCOUNT].[COGS]) / NULLIF([ACCOUNT].[Revenue], 0) * 100", "hierarchy": "ACCOUNT"}]'
);

Materializing Results to a Table

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"idescendants": "Total P&L"}],
        "PERIOD": [{"children": "Year"}],
        "ENTITY": ["Corporate"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    '["Variance", "Variance %"]',
    'FINANCE_DB.REPORTING.MONTHLY_PL_REPORT'
);

5.8 Saved Queries

Saved queries allow you to name and reuse POV definitions so you do not have to rebuild them each time.

Saving a Query (UI)

1. Build your query in the Query Builder
2. Click Save Query
3. Enter a name and optional description
4. The query is saved and appears in the saved queries dropdown

Saving a Query (SQL)

Saved queries are stored in the application’s configuration and can be referenced by name in any QUERY_CUBE call using the {"SAVED_QUERY": "name"} syntax.

Loading a Saved Query

In the Query Builder, select a saved query from the dropdown to pre-populate all dimension selections and formula choices. Via SQL, reference it by name in the POV string:

CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{"SAVED_QUERY": "Monthly P&L Report"}',
    NULL
);

Saved queries can include both POV selections and formula choices. Any additional formulas passed in the FORMULA_LIST parameter are merged with the saved query’s formulas.

5.9 Tips & Best Practices

Tip	Details
Start narrow, then expand	Begin with a small member selection to verify your query returns expected results, then expand to more members.
Use QUERY_CUBE_AUTO	If you have configured default data table and amount column in cube settings, use QUERY_CUBE_AUTO for simpler syntax.
Use cube variables	Instead of hardcoding member names like "FY24", define cube variables (current_year = FY24) and reference them with [CUBE_VAR=current_year]. This makes queries reusable across periods.
Save common queries	Save frequently used POV definitions as named queries to avoid rebuilding them.
Materialize for BI tools	Use the OUTPUT_TABLE parameter to write results to a table, then point your BI tool at that table for faster dashboard performance.
Combine operators	Mix explicit members and operators in the same dimension: ["Revenue", {"children": "Operating Expenses"}].
Inline formulas for ad-hoc analysis	Pass inline formula objects in the FORMULA_LIST parameter for one-off calculations without saving them permanently.