Cube Configuration

4.1 Overview

A cube in Casabase Cube is a named configuration that brings together one or more dimension hierarchies, a fact table, and an amount column. Once configured, a cube enables dynamic aggregation, formula evaluation, and security, all driven by the metadata you define.

This section walks through the process of creating and managing cubes, hierarchies, and cube-level settings.

4.2 Creating a Cube

A cube is created implicitly when you add your first hierarchy. There is no separate “create cube” step; the cube comes into existence when its first dimension is defined.

Via the Streamlit UI

1. Open the Casabase Cube application in Snowsight
2. Navigate to Cube Config in the sidebar (requires CUBE_ADMIN role)
3. Select — New Cube — from the cube dropdown
4. The Create New Hierarchy wizard will launch automatically

The wizard walks you through four steps:

Step 1: Basics

• Cube Name: A logical name for the cube (e.g., FINANCE, SALES, PLAN1). This groups all related hierarchies together.
• Hierarchy Name: The name of the dimension you are defining (e.g., ACCOUNT, PERIOD, ENTITY).
• Template: Optionally select a quick-start template (Custom, Time, Geography, Accounts, Products) that pre-fills common column mappings.

Step 2: Source Table

• Enter the fully qualified name of your dimension table or view (e.g., MY_DB.MY_SCHEMA.DIM_ACCOUNT).
• Click Analyze Table to detect available columns and preview the data.
• The wizard displays the table’s row count, column list, and a data preview.

Step 3: Column Mapping

Map your dimension table’s columns to Casabase Cube’s hierarchy structure:

Required mappings:

Mapping	Description
Parent Column	The column containing the parent member name (NULL or empty for the root).
Child Column	The column containing the member name.
Operator Column	The column containing the aggregation operator (+, -, ~).
Fact Table Column Name	The name of the column in your fact table that corresponds to this dimension.

Optional mappings:

Mapping	Description
Alias Column	Display-friendly member names. Accepts a single column name, or a JSON object mapping alias table names to columns for multi-language or multi-purpose aliases (e.g., {"Default": "ALIAS", "French": "ALIAS_FR"}).
Shared Member Column	Identifies members that roll up under multiple parents.
Sort Order Column	Custom member display ordering.
Time Balance Column	Time balance type: SUM, FIRST, LAST, AVG.
Time Balance Skip Column	What to skip during time balance aggregation: NONE, MISSING, ZEROS, or MISSING_AND_ZEROS. Required when Time Balance Column is specified.
UDA Column	User Defined Attributes for formula logic.
Formula Column	Formula expressions for calculated members (used when importing from Essbase/Cloud EPM).

The wizard validates your column mappings before proceeding.

Step 4: Review & Create

• Time Hierarchy: Check this if the dimension represents time periods (enables time balance aggregation).
• Row-Level Security: Check this to enable per-user member filtering for this dimension.
• Auto Rebuild: Check this to include the hierarchy in scheduled automatic rebuilds when the source data changes.
• Review the full configuration summary, then click Create Hierarchy.

Repeat the wizard for each additional dimension in the cube.

Via SQL

You can also create hierarchies directly via SQL using the CREATE_HIERARCHY procedure:

CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'MY_CUBE',                              -- Cube name
    'ACCOUNT',                              -- Hierarchy name
    'MY_DB.MY_SCHEMA.DIM_ACCOUNT',          -- Source dimension table
    'PARENT',                               -- Parent column
    'CHILD',                                -- Child column
    'ALIAS',                                -- Alias column (NULL if none; or JSON for multiple alias tables)
    'OPERATOR',                             -- Operator column
    'ACCOUNT',                              -- Fact table column name
    FALSE,                                  -- Use row-level security
    NULL,                                   -- Shared member column (NULL if none)
    'true',                                 -- Shared member value
    NULL,                                   -- Formula column (NULL if none)
    NULL,                                   -- Time balance column (NULL if none)
    NULL,                                   -- Time balance skip column (NULL if none)
    NULL,                                   -- Sort order column (NULL if none)
    FALSE,                                  -- Is time hierarchy
    NULL                                    -- UDA column (NULL if none)
);

Parameter	Type	Default	Description
CUBE_NAME	VARCHAR		The name of the cube to add the hierarchy to.
HIER_NAME	VARCHAR		The name for this hierarchy (dimension).
SOURCE_TABLE	VARCHAR		Fully qualified name of the dimension table or view (DATABASE.SCHEMA.TABLE).
PARENT_COL	VARCHAR		Column containing the parent member name. NULL or empty for root members.
CHILD_COL	VARCHAR		Column containing the member name.
ALIAS_COL	VARCHAR	NULL	Column for display-friendly member names. Accepts a column name, or a JSON object for multiple alias tables (e.g., '{"Default": "ALIAS", "French": "ALIAS_FR"}'). NULL if not used.
OPERATOR_COL	VARCHAR		Column containing the aggregation operator (+, -, or ~).
FACT_COL_NAME	VARCHAR		The name of the column in your fact/data table that corresponds to this dimension.
USE_RLS	BOOLEAN	FALSE	Enable row-level security (per-user member filtering) for this dimension.
SHARED_COL	VARCHAR	NULL	Column that identifies shared members (members that roll up under multiple parents). NULL if not used.
SHARED_VALUE	VARCHAR	'true'	The value in the shared column that marks a member as shared.
FORMULA_COL	VARCHAR	NULL	Column containing formula expressions for calculated members (typically used when importing from Essbase/Cloud EPM). NULL if not used.
TIME_BAL_COL	VARCHAR	NULL	Column containing time balance type: SUM, FIRST, LAST, or AVG. NULL if not used.
TIME_BAL_SKIP_COL	VARCHAR	NULL	Column for time balance skip logic: NONE, MISSING, ZEROS, or MISSING_AND_ZEROS. Required when TIME_BAL_COL is specified.
SORT_COL	VARCHAR	NULL	Column for custom member display ordering. NULL if not used.
IS_TIME	BOOLEAN	FALSE	Set to TRUE if this dimension represents time periods (enables time balance aggregation).
UDA_COL	VARCHAR	NULL	Column containing User Defined Attributes for formula logic. NULL if not used.

Each CREATE_HIERARCHY call adds one dimension to the cube. A cube can be defined with as many dimensions as you need.

4.3 Cube Settings

Once a cube exists, you can configure cube-level settings that apply to all queries and operations for that cube. These are managed from the Cube Settings panel on the Cube Config page.

Setting	Description
Data Table	The default fact table for this cube (fully qualified: DB.SCHEMA.TABLE). When set, users do not need to specify the table in every query.
Amount Column	The default numeric column in the fact table to aggregate (e.g., AMT, VALUE).
Data Classification	A label for the sensitivity level of the cube’s data: INTERNAL, CONFIDENTIAL, RESTRICTED, or PUBLIC.
Row Limit	Optional. Maximum number of rows returned by a query. Usually unnecessary. Only set this if you want to guard against accidentally large result sets.

4.4 Adding Hierarchies to an Existing Cube

To add additional dimensions to an existing cube:

Via the UI

1. Select the cube from the dropdown on the Cube Config page
2. Click Add Hierarchy
3. The wizard launches with the cube name pre-filled
4. Follow Steps 2–4 as described above

Via SQL

Call CREATE_HIERARCHY with the same cube name and a new hierarchy name.

4.5 Rebuilding Hierarchies

When the source data in your dimension tables changes (e.g., new members added, parent-child relationships updated, formulas modified), hierarchies need to be rebuilt to reflect those changes. Rebuilding re-reads the source dimension table, validates it, regenerates the hierarchy, and re-imports any formulas defined in the source table’s formula column.

Manual Rebuild

Via the UI

From the Cube Config page:

1. Select the cube
2. Click Rebuild Hierarchies
3. Choose whether to rebuild all hierarchies or only those marked for auto-rebuild
4. Click Rebuild

Via SQL

CALL CASABASE_CUBE.CUBE.REBUILD_AUTO_HIERARCHIES('MY_CUBE', TRUE);

Parameter	Type	Default	Description
P_CUBE_NAME	VARCHAR	NULL	The cube to rebuild. If NULL, rebuilds hierarchies across all cubes.
P_REBUILD_ALL	BOOLEAN	FALSE	When TRUE, rebuilds all hierarchies for the cube regardless of the Auto Rebuild setting. When FALSE, only rebuilds hierarchies that have Auto Rebuild enabled or are currently inactive.

The procedure performs the following steps for each hierarchy:

1. Re-reads and validates the source dimension table
2. Rebuilds the hierarchy
3. Re-imports formulas from the source table’s formula column, if one is configured

The procedure returns a summary of results including the number of hierarchies rebuilt, any errors encountered, and formula import results. This procedure requires the CUBE_ADMIN application role.

Automatic Rebuild

When Auto Rebuild is enabled for a hierarchy, Casabase Cube uses a Snowflake managed task to periodically check for changes in the source dimension table and automatically rebuild affected hierarchies. This requires the EXECUTE MANAGED TASK privilege granted during installation.

The auto-rebuild task can be configured via SQL:

CALL CASABASE_CUBE.CUBE.CONFIGURE_AUTO_REBUILD_TASK('15 MINUTE', 'SMALL');

Parameter	Type	Default	Description
P_SCHEDULE	VARCHAR	'15 MINUTE'	How often the task checks for changes (e.g., '15 MINUTE', '1 HOUR', 'USING CRON 0 */6 * * * UTC').
P_WAREHOUSE_SIZE	VARCHAR	'SMALL'	The serverless warehouse size used by the managed task.

When the task runs, it calls REBUILD_AUTO_HIERARCHIES with P_REBUILD_ALL = FALSE, so only hierarchies with Auto Rebuild enabled are rebuilt.

4.6 Hierarchy Validation

Casabase Cube validates your dimension table when creating or rebuilding a hierarchy. In the Streamlit UI, validation runs automatically as part of the Create Hierarchy and Rebuild Hierarchies operations — if errors are found, the hierarchy is not created and the specific errors are displayed inline.

To validate a dimension table independently — for example, to pre-check your data before committing to a create or rebuild — use the VALIDATE_HIERARCHY SQL procedure described below.

If validation fails, the hierarchy will not be created and the specific errors are returned so you can correct your dimension data.

Via SQL: VALIDATE_HIERARCHY

You can validate a dimension table directly via SQL before creating or rebuilding a hierarchy. This procedure requires the CUBE_ADMIN application role.

CALL CASABASE_CUBE.CUBE.VALIDATE_HIERARCHY(
    'DATABASE.SCHEMA.DIM_TABLE',   -- INPUT_TABLE (fully qualified)
    'Parent_Col',                   -- PARENT_COL
    'Child_Col',                    -- CHILD_COL
    'Operator_Col',                 -- OPERATOR_COL
    'Is_Shared_Col',                -- IS_SHARED_COL (optional)
    'true',                         -- SHARED_VALUE (default: 'true')
    'Alias_Col'                     -- ALIAS_COL (optional)
);

Parameter	Type	Default	Description
INPUT_TABLE	VARCHAR		Fully qualified dimension table name (DATABASE.SCHEMA.TABLE).
PARENT_COL	VARCHAR		Column containing the parent member name.
CHILD_COL	VARCHAR		Column containing the child member name.
OPERATOR_COL	VARCHAR		Column containing the aggregation operator (+, -, or ~).
IS_SHARED_COL	VARCHAR	NULL	Column that indicates whether a member is shared. Omit if the dimension has no shared members.
SHARED_VALUE	VARCHAR	'true'	The value in IS_SHARED_COL that marks a member as shared.
ALIAS_COL	VARCHAR	NULL	Column containing member aliases. Omit if not applicable.

The procedure returns a JSON array of validation errors. An empty array ([]) means the hierarchy is valid. Each error object contains:

Field	Description
ERROR	The type of validation error detected.
MEMBER	The member that triggered the error.
VALUE	Additional context (e.g., the parent name, duplicate count).

Validation checks performed:

• Multiple hierarchy tops — More than one root member detected
• Duplicate base members — Non-shared members appearing more than once
• Invalid aggregation operator — Operator is not +, -, or ~
• Empty or NULL child member — Blank or missing member names
• Self-referencing member — A member whose parent equals itself
• Orphan member — Parent does not exist in the table
• Member name exceeds 2000 characters — Name length limit violation
• Leading or trailing spaces — Whitespace in member names
• Circular reference — Cycle detected in the parent-child chain
• Shared member has no corresponding base member — Shared member without a primary entry
• Shared member under shared member — Nested shared members (not allowed)

4.7 Health Check

The Health Check feature provides a comprehensive diagnostic of your cube configuration. Access it from the Cube Config page by clicking Health Check.

The health check examines:

• Whether all hierarchy source tables are accessible
• Whether hierarchies are in a valid state
• Whether referenced objects still exist
• Missing or misconfigured settings

Results are categorized as OK, WARNING, MISSING, or ERROR, helping you quickly identify and resolve configuration issues.

Via SQL

You can also run the health check directly via SQL using the HEALTH_CHECK procedure:

-- Run health check for all cubes
CALL CASABASE_CUBE.CUBE.HEALTH_CHECK(NULL);

-- Run health check for a specific cube
CALL CASABASE_CUBE.CUBE.HEALTH_CHECK('MY_CUBE');

The procedure returns a table with the following columns:

Column	Description
CUBE_NAME	The cube being checked
HIER_NAME	The hierarchy being checked (blank for cube-level checks)
CHECK_TYPE	The type of check performed (see below)
STATUS	OK, WARNING, MISSING, or ERROR
MESSAGE	Details about the check result

Check types include:

• HIERARCHY_TABLE — Whether the hierarchy table exists
• HIERARCHY_ROW_COUNT — Number of rows in the hierarchy
• INPUT_TABLE_CONFIG — Whether the input table and columns are configured correctly
• QUERY_FUNCTION — Whether the query function exists for the hierarchy
• DATA_FRESHNESS — How recently the hierarchy was updated
• CONFIG_INTEGRITY — Whether data column mappings are unique across hierarchies
• FORMULA_COUNT — Number of active formulas in the cube
• FORMULA_SYNTAX — Whether formula expressions are syntactically valid
• FORMULA_BRACKETS — Whether brackets are balanced in formulas
• FORMULA_CASE_END — Whether CASE/END statements are balanced
• FORMULA_DIVISION_SAFETY — Whether divisions use NULLIFZERO to prevent divide-by-zero
• FORMULA_EMPTY — Whether formula expressions are non-empty
• FORMULA_ESSBASE_SYNTAX — Whether any unconverted Essbase syntax remains
• FORMULA_NO_HIERARCHY — Whether formulas have a hierarchy assigned

4.8 Cloning a Cube

You can create a copy of an existing cube, including all of its hierarchy definitions and configuration. This is useful for creating a sandbox to test changes without affecting a production cube, setting up variations of a cube with different formulas or security rules, or quickly spinning up a new cube that shares the same dimension structure as an existing one.

Via the UI

1. Select the cube on the Cube Config page
2. Click Clone Cube
3. Enter a name for the new cube
4. Optionally check Rebuild hierarchies after clone
5. Click Clone

Via SQL

CALL CASABASE_CUBE.CUBE.CLONE_CUBE('SOURCE_CUBE', 'NEW_CUBE', TRUE);

Parameter	Type	Default	Description
P_SOURCE_CUBE	VARCHAR		The name of the existing cube to clone.
P_TARGET_CUBE	VARCHAR		The name for the new cube. Must not already exist.
P_REBUILD	BOOLEAN	FALSE	When TRUE, rebuilds all hierarchies immediately after cloning so the new cube is ready to query. When FALSE, only the configuration is copied and hierarchies remain inactive until manually rebuilt.

The clone copies the following from the source cube:

• All hierarchy definitions (dimensions, column mappings, settings)
• Formulas
• Cube variables
• Saved queries
• Row-level security rules
• Cube settings (data table, amount column, data classification, row limit)

The procedure returns a summary of what was copied and, if P_REBUILD = TRUE, the rebuild results. This procedure requires the CUBE_ADMIN application role.

4.9 Exporting & Importing Configuration

Cube configurations can be exported as JSON and imported into another cube or environment. This is useful for promoting configurations between environments (e.g., development to production), sharing cube templates across teams, backing up a cube’s configuration before making changes, or replicating a cube setup in a different Snowflake account. The export captures hierarchy definitions, formulas, saved queries, and cube variables — everything needed to recreate the cube’s configuration without the underlying data.

Export

Via the UI:

1. Select the cube on the Cube Config page
2. Click Export
3. Download the JSON file

Via SQL:

CALL CASABASE_CUBE.CUBE.EXPORT_CUBE_CONFIG('MY_CUBE');

Parameter	Type	Description
P_CUBE_NAME	VARCHAR	The cube to export.

Returns a JSON object containing:

• cube_name — The name of the cube
• hierarchies — All hierarchy definitions including source tables, column mappings, shared member settings, time balance settings, and sort order
• formulas — All formula names, expressions, and target hierarchies
• saved_queries — All saved query names, POV strings, and formula references
• cube_vars — All variable names and values

You can save this output to a file for version control or transfer to another environment.

Import

Via the UI:

1. Click Import on the Cube Config page
2. Upload a previously exported JSON configuration file
3. Optionally check Rebuild hierarchies after import
4. Click Import

Via SQL:

CALL CASABASE_CUBE.CUBE.IMPORT_CUBE_CONFIG('{ ... }', FALSE);

Parameter	Type	Default	Description
P_CONFIG_JSON	VARCHAR		The JSON configuration string (as returned by EXPORT_CUBE_CONFIG).
P_OVERWRITE	BOOLEAN	FALSE	When TRUE, replaces existing formulas, saved queries, and cube variables if the cube already exists. When FALSE, returns an error if the cube already exists.

The import procedure:

• Imports formulas, saved queries, and cube variables from the JSON
• If P_OVERWRITE = TRUE and the cube already exists, existing formulas, saved queries, and cube variables are replaced with the imported values
• If the cube already has hierarchies built, they are automatically rebuilt after import to pick up any formula changes

Returns a summary of what was imported, including counts of formulas, saved queries, and cube variables. Both procedures require the CUBE_ADMIN application role.

4.10 Dropping a Cube

When a cube is no longer needed, you can permanently remove it along with all of its associated configuration. This removes the cube’s hierarchies, formulas, cube variables, saved queries, and row-level security rules. Consider using Export to back up the configuration before dropping, in case you need to recreate the cube later.

Via the UI

1. Select the cube on the Cube Config page
2. Click Drop Cube
3. Type the cube name to confirm
4. Click Drop Cube

Via SQL

The DROP_CUBE procedure supports a two-step confirmation pattern to prevent accidental deletion:

-- Step 1: Preview what will be dropped (dry run)
CALL CASABASE_CUBE.CUBE.DROP_CUBE('MY_CUBE', FALSE);

-- Step 2: Confirm and drop
CALL CASABASE_CUBE.CUBE.DROP_CUBE('MY_CUBE', TRUE);

Parameter	Type	Default	Description
P_CUBE_NAME	VARCHAR		The name of the cube to drop.
P_CONFIRM	BOOLEAN	FALSE	When FALSE, returns a summary of what will be deleted (hierarchy count, formula count, variable count, saved query count, security rule count) without making any changes. When TRUE, permanently deletes the cube and all associated objects.

This action cannot be undone. This procedure requires the CUBE_ADMIN application role.

4.11 Complete Example: Creating a Financial Cube

Here is a complete example of setting up a financial planning cube with five dimensions:

-- Step 1: Grant access to your data
GRANT USAGE ON DATABASE FINANCE_DB TO APPLICATION CASABASE_CUBE;
GRANT USAGE ON SCHEMA FINANCE_DB.PLANNING TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.FACT_PLAN TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.DIM_ACCOUNT TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.DIM_PERIOD TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.DIM_ENTITY TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.DIM_SCENARIO TO APPLICATION CASABASE_CUBE;
GRANT SELECT ON TABLE FINANCE_DB.PLANNING.DIM_VERSION TO APPLICATION CASABASE_CUBE;

-- Step 2: Create hierarchies (one per dimension)
CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'FIN_PLAN', 'ACCOUNT',
    'FINANCE_DB.PLANNING.DIM_ACCOUNT',
    'PARENT', 'CHILD', 'ALIAS', 'OPERATOR', 'ACCOUNT', FALSE,
    NULL, 'true', NULL, 'TB_TYPE', 'TB_SKIP', NULL, FALSE, NULL
);

CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'FIN_PLAN', 'PERIOD',
    'FINANCE_DB.PLANNING.DIM_PERIOD',
    'PARENT', 'CHILD', NULL, 'OPERATOR', 'PERIOD', FALSE,
    NULL, 'true', NULL, NULL, NULL, 'SORTORDER', TRUE, NULL
);

CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'FIN_PLAN', 'ENTITY',
    'FINANCE_DB.PLANNING.DIM_ENTITY',
    'PARENT', 'CHILD', 'ALIAS', 'OPERATOR', 'ENTITY', FALSE,
    NULL, 'true', NULL, NULL, NULL, NULL, FALSE, NULL
);

CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'FIN_PLAN', 'SCENARIO',
    'FINANCE_DB.PLANNING.DIM_SCENARIO',
    'PARENT', 'CHILD', NULL, 'OPERATOR', 'SCENARIO', FALSE,
    NULL, 'true', NULL, NULL, NULL, NULL, FALSE, NULL
);

CALL CASABASE_CUBE.CUBE.CREATE_HIERARCHY(
    'FIN_PLAN', 'VERSION',
    'FINANCE_DB.PLANNING.DIM_VERSION',
    'PARENT', 'CHILD', NULL, 'OPERATOR', 'VERSION', FALSE,
    NULL, 'true', NULL, NULL, NULL, NULL, FALSE, NULL
);

-- Step 3: Run a test query
CALL CASABASE_CUBE.CUBE.QUERY_CUBE(
    'FIN_PLAN',
    'FINANCE_DB.PLANNING.FACT_PLAN',
    'AMT',
    '{
        "ACCOUNT": [{"children": "Total P&L"}],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["Total Entity"],
        "SCENARIO": ["Actual"],
        "VERSION": ["Final"]
    }',
    NULL,
    NULL
);