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, write-back, 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)
);

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), hierarchies need to be rebuilt to reflect those changes.

Manual Rebuild

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:

-- Rebuild all hierarchies for a cube
CALL CASABASE_CUBE.CUBE.REBUILD_AUTO_HIERARCHIES('MY_CUBE', TRUE);

-- Rebuild only auto-rebuild hierarchies
CALL CASABASE_CUBE.CUBE.REBUILD_AUTO_HIERARCHIES('MY_CUBE', FALSE);

Automatic Rebuild

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

4.6 Hierarchy Validation

Casabase Cube validates your dimension table when creating or rebuilding a hierarchy. Validation checks include:

Check	Description
Orphaned members	Members whose parent does not exist in the table.
Circular references	Parent-child loops that would cause infinite recursion.
Missing operators	Members without a valid aggregation operator.
Duplicate members	Non-shared members that appear more than once.
Invalid shared members	Shared members without a corresponding primary member.

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

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.

4.8 Cloning a Cube

You can create a copy of an existing cube, including all of its hierarchy definitions and configuration:

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);

The third parameter controls whether hierarchies are rebuilt immediately after cloning.

4.9 Exporting & Importing Configuration

Cube configurations can be exported as JSON and imported into another cube or environment.

Export

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

Import

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

This is useful for migrating configurations between environments (development to production) or sharing cube templates across teams.

4.10 Dropping a Cube

To permanently remove a cube and all its associated configuration:

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

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

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

Dropping a cube removes all hierarchies, formulas, variables, saved queries, and security rules associated with that cube. This action cannot be undone.

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
);