Creating a Cube from Snowflake Data

Casabase Cube can be used to create new multidimensional models directly from data and dimension definitions in Snowflake.

This example demonstrates the complete process for creating a financial planning cube named FIN_PLAN with five dimensions:

Account
Period
Entity
Scenario
Version

This is the standard process for creating a new cube directly in Casabase Cube.

If you are migrating an existing Oracle Essbase or Oracle Cloud EPM cube, do not recreate the cube manually using this process. Casabase Cube provides a dedicated migration workflow that uses the native Essbase outline (.otl) to create the cube’s multidimensional structure and supported metadata.

See Oracle Essbase & Cloud EPM Migration for the migration process.

Two Ways to Create a Cube

Casabase Cube supports two primary cube-creation workflows.

Create a New Cube from Snowflake Data

For a new multidimensional model, you create dimension definition tables in Snowflake and use CREATE_DIMENSION to create each dimension.

Dimension Definition Tables
           │
           ▼
     CREATE_DIMENSION
           │
           ▼
     Casabase Cube
           │
     ┌─────┼─────┐
     ▼     ▼     ▼
  ACCOUNT PERIOD ENTITY ...

There is no separate CREATE_CUBE operation.

The first CREATE_DIMENSION call establishes the cube. Each subsequent CREATE_DIMENSION call adds another dimension to that cube.

This is the workflow demonstrated on this page.

Migrate an Essbase or Cloud EPM Cube

Existing Oracle Essbase and Oracle Cloud EPM cubes use a different workflow.

Casabase Cube imports the native Essbase outline (.otl) to create the multidimensional model rather than requiring you to recreate each dimension manually.

Bottom-level cube data is then loaded from a native level-0 export.

Native Essbase Outline (.otl)
            │
            ▼
     IMPORT_ESSBASE_OTL
            │
            ▼
      Cube Structure
            │
            ▼
 Native Level-0 Export
            │
            ▼
         Cube Data

Both workflows result in a Casabase Cube, but the method used to create the multidimensional model is different.

New Cube	Essbase / Cloud EPM Migration
Create dimension definition tables	Use the existing native `.otl` file
Create dimensions with `CREATE_DIMENSION`	Import the outline with `IMPORT_ESSBASE_OTL`
Load source data	Load the native level-0 export
Intended for new Snowflake-based models	Intended for existing Essbase and Cloud EPM models

Example Model

This example creates the following cube:

FIN_PLAN
│
├── ACCOUNT
├── PERIOD
├── ENTITY
├── SCENARIO
└── VERSION

The example source environment contains:

FINANCE_DB.PLANNING
│
├── DIM_ACCOUNT
├── DIM_PERIOD
├── DIM_ENTITY
├── DIM_SCENARIO
├── DIM_VERSION
└── FACT_PLAN

The DIM_* tables are dimension definition tables containing the metadata Casabase Cube uses to construct each dimension.

FACT_PLAN contains the bottom-level financial data that will be loaded into the cube.

Step 1: Prepare the Dimension Definition Tables

Each dimension requires a dimension definition table containing the members, relationships, and properties Casabase Cube needs to construct the dimension.

The physical column names are configurable. CREATE_DIMENSION maps columns in the source table to the corresponding Casabase Cube dimension properties.

The following tables provide simplified examples for the five dimensions in this cube.

Account

PARENT          CHILD           ALIAS               OPERATOR  TB_TYPE  TB_SKIP
--------------  --------------  ------------------  --------  -------  -------
Total P&L       Revenue         Revenue             +         FLOW      NONE
Total P&L       Expenses        Expenses            +         FLOW      NONE
Revenue         Product Sales   Product Sales       +         FLOW      NONE
Expenses        Payroll         Payroll Expense     +         FLOW      NONE
Expenses        Facilities      Facilities Expense  +         FLOW      NONE

The Account dimension in this example contains Time Balance configuration.

Period

PARENT    CHILD  OPERATOR  SORTORDER
--------  -----  --------  ---------
Year      Q1     +         1
Year      Q2     +         2
Year      Q3     +         3
Year      Q4     +         4
Q1        Jan    +         1
Q1        Feb    +         2
Q1        Mar    +         3

PERIOD will be designated as the cube’s time dimension.

Entity

PARENT        CHILD       ALIAS          OPERATOR
------------  ----------  -------------  --------
Total Entity  Americas    Americas       +
Total Entity  Europe      Europe         +
Americas      US          United States  +
Americas      Canada      Canada         +

Scenario

PARENT         CHILD     OPERATOR
-------------  --------  --------
All Scenarios  Actual    +
All Scenarios  Budget    +
All Scenarios  Forecast  +

Version

PARENT        CHILD    OPERATOR
------------  -------  --------
All Versions  Working  +
All Versions  Final    +

These examples show only the properties needed for this model.

Dimension definition tables can contain additional supported properties such as:

Multiple aliases
Shared-member configuration
Member formulas
User Defined Attributes (UDAs)
Comments
Storage properties
Additional member properties
Security configuration
Sort order
Time Balance behavior

See Dimension Definition Tables for the complete dimension definition model.

Step 2: Validate the Dimension Definitions

Before creating a dimension, you can validate its definition using VALIDATE_DIMENSION.

For example:

CALL CASABASE_CUBE.CUBE.VALIDATE_DIMENSION(
    'FINANCE_DB.PLANNING.DIM_ACCOUNT',
    'PARENT',
    'CHILD',
    'OPERATOR',
    NULL,
    'true',
    'ALIAS'
);

An empty JSON array:

[]

indicates that no validation errors were found.

Validation can identify problems such as:

Multiple hierarchy tops
Duplicate base members
Invalid aggregation operators
Empty member names
Self-referencing members
Orphan members
Circular references
Invalid shared-member relationships

Repeat the validation for the other dimension definition tables as appropriate.

Running VALIDATE_DIMENSION independently is optional because dimension validation also occurs when a dimension is created.

See Dimension Validation for details.

Step 3: Create the Account Dimension

The first CREATE_DIMENSION call establishes the FIN_PLAN cube and creates its first dimension.

CALL CASABASE_CUBE.CUBE.CREATE_DIMENSION(
    'FIN_PLAN',                              -- Cube name
    'ACCOUNT',                               -- Dimension name
    'FINANCE_DB.PLANNING.DIM_ACCOUNT',       -- Dimension definition table
    'PARENT',                                -- Parent column
    'CHILD',                                 -- Child column
    'ALIAS',                                 -- Alias column
    'OPERATOR',                              -- Aggregation operator column
    'ACCOUNT',                               -- Cube data column
    FALSE,                                   -- Security
    NULL,                                    -- Shared member column
    'true',                                  -- Shared member value
    NULL,                                    -- Formula column
    'TB_TYPE',                               -- Time Balance type column
    'TB_SKIP',                               -- Time Balance skip column
    NULL,                                    -- Sort order column
    FALSE,                                   -- Time dimension
    NULL,                                    -- UDA column
    FALSE,                                   -- Skip snapshot
    NULL,                                    -- Comment column
    NULL,                                    -- Storage column
    NULL,                                    -- Property columns
    FALSE                                    -- Skip hierarchy view
);

This operation creates the ACCOUNT dimension using the members, relationships, and properties defined in DIM_ACCOUNT.

Because FIN_PLAN does not yet exist, the cube is established as part of creating its first dimension.

Step 4: Create the Period Dimension

Create the Period dimension and designate it as the cube’s time dimension:

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

Setting the time-dimension parameter to TRUE identifies PERIOD as the dimension representing time periods.

The Time Balance properties in this example are defined on the Account dimension and determine how applicable Account members aggregate across the time dimension.

Step 5: Create the Entity Dimension

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

Step 6: Create the Scenario Dimension

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

Step 7: Create the Version Dimension

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

At this point, the cube contains five dimensions:

FIN_PLAN
│
├── ACCOUNT
├── PERIOD
├── ENTITY
├── SCENARIO
└── VERSION

Each dimension is mapped to the corresponding column in the cube data:

Dimension	Data Column
`ACCOUNT`	`ACCOUNT`
`PERIOD`	`PERIOD`
`ENTITY`	`ENTITY`
`SCENARIO`	`SCENARIO`
`VERSION`	`VERSION`

The dimension names and data column names do not have to be identical. The mapping is defined when the dimension is created.

Step 8: Prepare the Cube Data

The financial data contains values at intersections of the cube’s dimensions.

For example:

ACCOUNT        PERIOD  ENTITY  SCENARIO  VERSION  AMOUNT
-------------  ------  ------  --------  -------  ------
Product Sales  Jan     US      Actual    Final    125000
Payroll        Jan     US      Actual    Final    -45000
Facilities     Jan     US      Actual    Final    -12000
Product Sales  Feb     US      Actual    Final    132000

Each dimension value corresponds to a member in the appropriate dimension.

For example:

Product Sales ──► ACCOUNT
Jan           ──► PERIOD
US            ──► ENTITY
Actual        ──► SCENARIO
Final         ──► VERSION
125000        ──► Value

The source data should contain the bottom-level intersections required by the cube. Higher-level values are calculated dynamically from the multidimensional structures and calculation rules defined in the cube.

Step 9: Load the Cube Data

Load the bottom-level source data using the appropriate Casabase Cube data-loading workflow.

After the data is loaded, Casabase Cube manages the cube data within the Casabase Cube Native Application database in the customer’s Snowflake account.

Conceptually:

Dimension Definition Tables
           │
           ▼
      Dimensions
           │
           │
Source Data│
     │     │
     ▼     ▼
      Data Load
           │
           ▼
        FIN_PLAN
           │
      ┌────┴────┐
      │         │
 Dimensions   Data
      │         │
      └────┬────┘
           ▼
      QUERY_CUBE

The original source data and the Casabase Cube-managed cube are distinct. Queries operate against the configured cube rather than requiring users to specify the original source table for each query.

See the data-loading documentation for the supported loading workflows.

Step 10: Run a Health Check

After creating the dimensions and loading the data, run a health check:

CALL CASABASE_CUBE.CUBE.HEALTH_CHECK('FIN_PLAN');

Review any WARNING or error-state results and correct applicable configuration issues before making the cube available for broader use.

See Health and Diagnostics for details.

Step 11: Query the Cube

The completed cube can now be queried through Casabase Cube.

For example, a user might request:

Account:   Children of Total P&L
Period:    Children of Q1
Entity:    Total Entity
Scenario:  Actual
Version:   Final

Casabase Cube resolves the requested members against the multidimensional model and dynamically calculates the requested results.

Conceptually:

                       FIN_PLAN
                          │
          ┌───────────────┼───────────────┐
          │               │               │
          ▼               ▼               ▼
       ACCOUNT          PERIOD          ENTITY
          │               │               │
     Total P&L            Q1         Total Entity
          │               │
       Children         Children
          │
          └───────────────┬───────────────┐
                          │               │
                          ▼               ▼
                       Actual           Final
                      SCENARIO         VERSION
                          │
                          ▼
                     QUERY_CUBE
                          │
                          ▼
                  Aggregated Results

Queries can be created interactively through Query Builder or programmatically through supported Snowflake SQL interfaces.

See Querying Overview for current query concepts and the related query pages for syntax and examples.

Resulting Cube

The completed example consists of:

FIN_PLAN
│
├── Dimensions
│   ├── ACCOUNT
│   ├── PERIOD
│   ├── ENTITY
│   ├── SCENARIO
│   └── VERSION
│
├── Hierarchy Structures
│   └── Parent-child relationships
│       within each dimension
│
├── Member Properties
│   ├── Aliases
│   ├── Aggregation Operators
│   ├── Time Balance
│   └── Sort Order
│
└── Cube Data
    └── Financial values by
        multidimensional intersection

The dimensions provide the analytical structure of the model, while the parent-child relationships within each dimension define how members are organized and aggregated.

Modifying the Cube

After the cube has been created, its dimension definition tables can continue to serve as the source definitions for its dimensions.

For example, you might:

Add new members
Remove members
Change parent-child relationships
Add shared members
Modify aliases
Add formulas
Change member properties

After changing a dimension definition, validate and rebuild the affected dimension as appropriate.

A typical maintenance workflow is:

Modify Dimension
Definition Table
       │
       ▼
VALIDATE_DIMENSION
       │
       ▼
REBUILD_DIMENSIONS
       │
       ▼
   HEALTH_CHECK
       │
       ▼
    Query Cube

See Rebuilding Dimensions for details.

Extending the Cube

After creating the basic model, you can extend it with additional Casabase Cube capabilities, including:

Additional dimensions
Member formulas and calculated members
Cube variables
Dimension security
Additional or alternate hierarchy structures
Automatic dimension rebuilds
Saved queries
Scheduled saved queries

The cube can evolve as the underlying analytical model changes.

Creating a New Cube vs. Migrating an Existing Cube

The process on this page is intended for new Casabase Cube models built from Snowflake data.

Do not manually reproduce an existing Essbase or Cloud EPM model by creating its dimensions one at a time.

For an existing Essbase or Cloud EPM cube:

Existing Essbase / Cloud EPM Cube
              │
              ▼
       Obtain Native .otl
              │
              ▼
      IMPORT_ESSBASE_OTL
              │
              ▼
     Casabase Cube Model
              │
              ▼
 Obtain Native Level-0 Export
              │
              ▼
         Load Data

Using the migration workflow allows Casabase Cube to use the source application’s native multidimensional metadata rather than requiring the model to be manually reconstructed.

See Oracle Essbase & Cloud EPM Migration for the complete process.

Key Takeaways

New Casabase Cubes can be created directly from dimension definitions and data in Snowflake.
There is no separate CREATE_CUBE operation; the first CREATE_DIMENSION call establishes the cube.
Each dimension is based on a dimension definition table.
CREATE_DIMENSION maps source columns to Casabase Cube dimension properties.
Parent-child relationships within each dimension define its hierarchy structure.
VALIDATE_DIMENSION can be used to check a dimension definition before creating or rebuilding the dimension.
One dimension can be designated as the time dimension.
Time Balance properties can define specialized aggregation behavior across time.
Bottom-level source data provides the multidimensional intersections used by the cube.
HEALTH_CHECK can be used to evaluate the resulting cube configuration.
This manual cube-creation workflow should not be used to migrate an existing Oracle Essbase or Oracle Cloud EPM cube.
Essbase and Cloud EPM migrations use the native Essbase outline (.otl) and native level-0 export through the dedicated migration workflow.

Next Steps

Continue with:

Dimension Definition Tables for the structure used to define dimensions.
Adding Dimensions for adding dimensions to an existing cube.
Dimension Validation for validating dimension definitions.
Rebuilding Dimensions for applying dimension definition changes.
Health and Diagnostics for evaluating cube configuration.
Querying Overview for querying the completed cube.
Oracle Essbase & Cloud EPM Migration if you are migrating an existing Essbase or Cloud EPM model.