Creating a Cube

Casabase Cube supports two primary ways to create a multidimensional model:

Import an existing Oracle Essbase or Oracle Cloud EPM application using its native Essbase outline (.otl).
Create a cube directly in Snowflake using dimension definition tables that describe the dimensions, hierarchies, members, and properties of the cube.

Both approaches ultimately create the dimensions and multidimensional structures used by the Casabase Cube query engine.

Cube Creation Paths

Conceptually, the two creation paths converge on the same Casabase Cube dimension model:

Oracle Essbase / Cloud EPM              Native Snowflake Model
            │                                   │
            ▼                                   ▼
         .otl file                    Dimension Definition Tables
            │                                   │
            ▼                                   │
  IMPORT_ESSBASE_OTL                            │
            │                                   │
            ▼                                   │
  Dimension Definition Tables                   │
            │                                   │
            └────────────────┬──────────────────┘
                             ▼
                     CREATE_DIMENSION
                             │
                             ▼
                       Dimensions
                             │
                             ▼
                       Casabase Cube

The difference is primarily how the dimension definition tables are created.

Creating a Cube from Essbase or Cloud EPM

For Oracle Essbase and Oracle Cloud EPM migrations, Casabase Cube can read the application’s native Essbase outline (.otl).

The outline contains the multidimensional structure of the source application, including information such as:

Dimensions
Hierarchies
Members
Parent-child relationships
Aliases
Aggregation operators
Shared members
Formulas
Time Balance properties
UDAs
Other supported member properties

Casabase Cube imports the outline using:

CUBE.IMPORT_ESSBASE_OTL

During the import, Casabase Cube creates a dimension definition table for each dimension in:

CASABASE_CUBE.SHARED_DATA

Tables generated from an Essbase outline use the naming convention:

<CUBE_NAME>_<DIMENSION_NAME>_OTL

For example:

ASOSAMP_MEASURES_OTL
ASOSAMP_PRODUCTS_OTL
ASOSAMP_TIME_OTL
ASOSAMP_YEARS_OTL

The _OTL suffix identifies dimension definition tables generated by the Essbase outline import process.

For the complete migration workflows, see:

Creating a Cube Without Essbase or Cloud EPM

Casabase Cube can also be used as a standalone multidimensional OLAP engine for applications that do not originate from Oracle Essbase or Oracle Cloud EPM.

In this workflow, you create a dimension definition table for each dimension required by the cube.

Customer-created dimension definition tables can reside in any Snowflake database and schema that the Casabase Cube Native App is authorized to read. They do not need to be stored in the application database.

Each table defines one dimension, including the members, hierarchy relationships, and other supported member properties used by Casabase Cube.

For example:

CASABASE_CUBE.SHARED_DATA.FINANCE_ACCOUNT_INPUT
CASABASE_CUBE.SHARED_DATA.FINANCE_ENTITY_INPUT
CASABASE_CUBE.SHARED_DATA.FINANCE_PERIOD_INPUT
CASABASE_CUBE.SHARED_DATA.FINANCE_PRODUCT_INPUT

The table names are customer-defined. An _OTL suffix is not required.

Once the required dimension definition tables exist, Casabase Cube can use them to create the dimensions that make up the cube.

Dimension Definition Tables

A dimension definition table describes the members, hierarchy relationships, and properties that make up a dimension.

A typical table can contain columns such as:

Column	Purpose
`PARENT`	Parent member name
`CHILD`	Member name
`ALIAS`	Primary member alias
`OPERATOR`	Aggregation operator
`MEMBER_IS_SHARED`	Identifies shared members
`FORMULA`	Member formula
`TB_TYPE`	Time Balance behavior
`TB_TYPE_SKIP_VALUE`	Time Balance Skip behavior
`UDA`	User Defined Attributes
`MEMBER_COMMENT`	Member comments
`DATA_STORAGE`	Member storage behavior
`SORTORDER`	Member display order
`SOLVE_ORDER`	Formula solve order
`IS_TWO_PASS`	Imported member property where present
`ALIASES`	Additional alias information

Not every property must contain a value for every member.

For example, a simple dimension definition table might conceptually contain:

PARENT          CHILD             OPERATOR
--------------  ----------------  --------
NULL            Total Entity      +
Total Entity    North America     +
Total Entity    EMEA              +
North America   United States     +
North America   Canada            +
EMEA            Germany           +
EMEA            France            +

From these relationships, Casabase Cube can construct a hierarchy such as:

Dimension: ENTITY

Total Entity
│
├── North America
│   ├── United States
│   └── Canada
│
└── EMEA
    ├── Germany
    └── France

The dimension definition table becomes the source from which Casabase Cube builds the dimension and its multidimensional hierarchy structures.

See Dimension Definition Tables for the complete table format and column reference.

Creating a Dimension

Once the dimension definition table exists, use:

CUBE.CREATE_DIMENSION

to create the dimension within the target cube.

CREATE_DIMENSION maps columns from the dimension definition table to their Casabase Cube meanings and creates the dimension and its hierarchy structures.

The procedure signature is:

CREATE_DIMENSION(
    CUBE_NAME,
    DIMENSION_NAME,
    INPUT_TABLE_NAME,
    PARENT_COL,
    CHILD_COL,
    ALIAS_COL,
    OPERATOR_COL,
    DATA_TABLE_COL_NAME,
    USE_SECURITY,
    IS_SHARED_COL,
    SHARED_VALUE,
    FORMULA_COL,
    TB_TYPE_COL,
    TB_TYPE_SKIP_COL,
    SORTORDER_COL,
    IS_TIME_DIMENSION,
    UDA_COL,
    SKIP_SNAPSHOT,
    COMMENT_COL,
    STORAGE_COL,
    PROPERTY_COLS,
    SKIP_HIERARCHY_VIEW
)

Example

Assume the following dimension definition table exists:

CASABASE_CUBE.SHARED_DATA.FINANCE_ACCOUNT_INPUT

and contains columns corresponding to the Casabase Cube dimension and member properties.

The ACCOUNT dimension can be created using:

CALL CASABASE_CUBE.CUBE.CREATE_DIMENSION(
    'FINANCE',                                  -- Cube name
    'ACCOUNT',                                  -- Dimension name
    'CASABASE_CUBE.SHARED_DATA.FINANCE_ACCOUNT_INPUT',
    'PARENT',                                   -- Parent column
    'CHILD',                                    -- Child column
    'ALIAS',                                    -- Alias column
    'OPERATOR',                                 -- Aggregation operator
    'ACCOUNT',                                  -- Cube data column
    FALSE,                                      -- Use security
    'MEMBER_IS_SHARED',                         -- Shared member column
    'true',                                     -- Shared member value
    'FORMULA',                                  -- Formula column
    'TB_TYPE',                                  -- Time Balance column
    'TB_TYPE_SKIP_VALUE',                       -- Time Balance Skip column
    'SORTORDER',                                -- Sort order column
    FALSE,                                      -- Is time dimension
    'UDA',                                      -- UDA column
    FALSE,                                      -- Skip snapshot
    'MEMBER_COMMENT',                           -- Comment column
    'DATA_STORAGE',                             -- Storage column
    NULL,                                       -- Additional property columns
    FALSE                                       -- Skip hierarchy view
);

The exact mappings depend on the columns present in the dimension definition table.

CREATE_DIMENSION Parameters

Parameter	Description
`CUBE_NAME`	Target cube name.
`DIMENSION_NAME`	Name of the dimension being created.
`INPUT_TABLE_NAME`	Table containing the dimension definition data.
`PARENT_COL`	Column containing parent member names.
`CHILD_COL`	Column containing member names.
`ALIAS_COL`	Column containing primary alias or display names.
`OPERATOR_COL`	Column containing member aggregation operators.
`DATA_TABLE_COL_NAME`	Dimension column name used in the cube’s data.
`USE_SECURITY`	Enables user-based security for this dimension.
`IS_SHARED_COL`	Column identifying shared members.
`SHARED_VALUE`	Value indicating that a member is shared.
`FORMULA_COL`	Column containing member formulas.
`TB_TYPE_COL`	Column containing Time Balance behavior.
`TB_TYPE_SKIP_COL`	Column containing Time Balance Skip behavior.
`SORTORDER_COL`	Column controlling member ordering.
`IS_TIME_DIMENSION`	Indicates whether this dimension represents time within the cube.
`UDA_COL`	Column containing User Defined Attributes.
`SKIP_SNAPSHOT`	Controls creation of the initial dimension snapshot.
`COMMENT_COL`	Column containing member comments.
`STORAGE_COL`	Column containing member storage properties.
`PROPERTY_COLS`	Additional property columns, when applicable.
`SKIP_HIERARCHY_VIEW`	Controls creation of the hierarchy view.

Detailed parameter behavior and advanced configuration are covered in the dimension configuration documentation.

Adding Additional Dimensions

Create a dimension definition table and call CREATE_DIMENSION for each dimension required by the cube.

For example, a Finance cube could contain:

FINANCE
│
├── ACCOUNT
├── ENTITY
├── PERIOD
├── YEARS
├── SCENARIO
└── VERSION

Each dimension has its own definition table and its own CREATE_DIMENSION configuration.

Conceptually:

ACCOUNT Definition Table ──► CREATE_DIMENSION ──► ACCOUNT
ENTITY Definition Table  ──► CREATE_DIMENSION ──► ENTITY
PERIOD Definition Table  ──► CREATE_DIMENSION ──► PERIOD
YEARS Definition Table   ──► CREATE_DIMENSION ──► YEARS
                                             │
                                             ▼
                                         FINANCE Cube

The first dimension created for a new cube establishes the cube. Additional CREATE_DIMENSION calls add dimensions to that cube.

Mapping Dimensions to Cube Data

DATA_TABLE_COL_NAME identifies the data column associated with the dimension in the cube’s bottom-level data.

For example:

Dimension        Cube Data Column
---------------  ----------------
ACCOUNT          ACCOUNT
ENTITY           ENTITY
PERIOD           PERIOD
YEARS            YEARS
SCENARIO         SCENARIO

This mapping allows Casabase Cube to connect members in the dimension with the corresponding dimension values in the cube’s stored bottom-level data.

Users querying the cube do not need to provide the physical cube data table. Casabase Cube manages the underlying cube data within the Native Application database.

Security

Security can be enabled independently for each dimension using:

USE_SECURITY

When enabled, the dimension uses Casabase Cube’s user-based security model.

Security rules can then restrict individual Snowflake users to specific members and their descendants according to the applicable hierarchy.

USE_SECURITY makes the dimension eligible for Casabase Cube row-level security, but it does not automatically restrict every user. Security filtering applies when the cube has a security-enabled dimension and the executing user has at least one active security rule for the cube.

See Security Model for the complete security architecture.

Time Dimensions and Time Balance

IS_TIME_DIMENSION identifies the dimension that represents time within the cube.

Member properties can also define Time Balance behavior using the mapped:

TB_TYPE
TB_TYPE_SKIP_VALUE

properties.

Supported Time Balance values are:

FLOW
FIRST
LAST
AVERAGE

FLOW sums values across periods. FIRST, LAST, and AVERAGE provide the corresponding non-additive time behavior.

Supported Time Balance Skip values include:

NONE
MISSING
ZEROS
MISSING_AND_ZEROS

Time Balance properties define how applicable members aggregate across the time dimension.

See Hierarchy Capabilities for additional information about Time Balance processing.

Formulas and Solve Order

Dimension definition tables can contain formulas and calculation metadata for calculated members.

For example:

FORMULA
SOLVE_ORDER

Casabase Cube evaluates formulas dynamically as part of multidimensional query processing.

Solve order controls calculation precedence when multiple calculations interact.

See Formula Capabilities for the formula architecture and Formulas for detailed formula configuration.

Rebuilding Dimensions

After dimension definitions have been created or changed, use REBUILD_DIMENSIONS to rebuild the applicable dimensions and their hierarchy structures.

For example:

CALL CUBE.REBUILD_DIMENSIONS(
    'FINANCE',
    TRUE,
    TRUE,
    TRUE
);

The exact parameter behavior for REBUILD_DIMENSIONS is covered in the dimension rebuild documentation.

See Rebuilding Dimensions for details.

Validate the Cube

After creating or rebuilding the cube’s dimensions, validate the configuration before using the cube in production.

Casabase Cube provides hierarchy validation and cube health checks for identifying configuration or structural issues.

Hierarchy validation evaluates the structural relationships within a dimension, while a health check evaluates the broader state of the cube.

See:

Dimension Validation for validating the source definition.
Health and Diagnostics for validating the resulting cube.

Migration vs. Native Cube Creation

The important difference between the two cube creation paths is how the dimension definition tables are produced.

	Essbase / Cloud EPM	Native Cube Creation
Starting point	Native Essbase `.otl`	Customer-defined dimension data
Dimension tables created by	`IMPORT_ESSBASE_OTL`	Customer
Table location	Generated in `CASABASE_CUBE.SHARED_DATA`	Any Snowflake table the Native App is authorized to read
Typical naming	`<CUBE>_<DIMENSION>_OTL`	Customer-defined
`_OTL` required	Generated automatically	No
Dimension creation	`CREATE_DIMENSION`	`CREATE_DIMENSION`
Multidimensional engine	Casabase Cube	Casabase Cube
Query engine	Casabase Cube	Casabase Cube

Once the dimensions and their hierarchy structures have been created, both models use the same Casabase Cube multidimensional architecture.

Next Steps

Continue with:

Dimension Definition Tables for the dimension table schema and property mappings.
Adding Dimensions for adding dimensions to an existing cube.
Rebuilding Dimensions for rebuilding dimensions after their definitions change.
Dimension Validation for validating dimension definitions.
Health and Diagnostics for validating the overall cube configuration.
Oracle Essbase & Cloud EPM Migration if you are migrating an existing application.