Migrating from Oracle Essbase

Casabase Cube can migrate an existing Oracle Essbase application to Snowflake using native Essbase artifacts.

A typical migration requires:

• The application’s native Essbase outline (.otl)
• A native level-0 export containing the source application data

The outline is used to create the multidimensional structure of the Casabase Cube, while the native level-0 export provides the detailed data used for querying and aggregation.

Migration Workflow

The migration process consists of the following steps:

1. Obtain the native Essbase outline (.otl).
2. Create a native level-0 export.
3. Upload the migration artifacts to Snowflake.
4. Import the outline.
5. Rebuild the cube dimensions.
6. Import the native level-0 data.
7. Review outline-import messages and Compatibility Notes.
8. Validate the migrated cube.
9. Query the cube and verify the results.

Casabase Cube supports both an interactive workflow through the application interface and a SQL-based workflow for automation and production use.

Before You Begin

You will need:

• Casabase Cube installed in your Snowflake account.
• Administrative access to Casabase Cube.
• The source application’s native .otl file.
• A native level-0 export of the data to migrate in .txt or .gz format.
• A Snowflake stage accessible to Casabase Cube for the native level-0 data export.
• If using the SQL-based outline import workflow, the .otl file must also be uploaded to a Snowflake stage accessible to Casabase Cube.

Caution

Casabase Cube uses the native Essbase outline (.otl) for migration. XML outline exports are not used by the current migration process.

1. Obtain the Essbase Outline

Obtain the native Essbase outline file for the application you want to migrate.

The file has the .otl extension.

For example:

Sample.otl

Casabase Cube uses the outline to create the multidimensional structure of the cube, including:

• Dimensions
• Hierarchies
• Members
• Parent-child relationships
• Aliases
• Aggregation operators
• Shared members
• Formulas
• Member properties

2. Create a Native Level-0 Export

Create a native level-0 export from the source Essbase database.

The native level-0 export contains the detailed data values from the Essbase application and is used to populate the Casabase Cube data table in Snowflake.

The export must be provided in one of the following formats:

Format	Description
.txt	Uncompressed native Essbase level-0 export
.gz	Gzip-compressed native Essbase level-0 export

Use a native level-0 export. This ensures only the bottom-level data is loaded to Casabase Cube.

The export may consist of one or more .txt or .gz files.

The exact procedure for generating the export depends on your Oracle Essbase environment and administrative processes. Please contact your Oracle Essbase administrator for more information.

3. Upload the Migration Artifacts to Snowflake

For production and automated migrations, upload the migration artifacts to a Snowflake stage that Casabase Cube can access.

Casabase Software recommends organizing migration artifacts using a consistent directory structure based on the Essbase application and Essbase cube.

Recommended Stage Structure

Use the following structure:

<Snowflake Stage>/<Essbase Application>/<Essbase Cube>/
├── otl/
└── data/

For example, for an Essbase application named ASOsamp containing a cube named Sample:

@CASABASE_CUBE_DEMO.SAMPLE_DATA.EPM_STAGE/
└── ASOsamp/
    └── Sample/
        ├── otl/
        │   └── Sample.otl
        └── data/
            ├── data1.gz
            ├── data2.gz
            └── data3.gz

The directories contain:

Directory	Contents
otl/	The native Essbase outline (.otl) file for the cube.
data/	One or more native level-0 data export files in .txt or .gz format.

This structure keeps the outline and data for each Essbase cube together and provides predictable stage paths for automated migration and refresh processes.

Note

This directory structure is the recommended convention. The stage paths supplied to the Casabase Cube stored procedures determine the actual locations from which the migration artifacts are read.

Grant Casabase Cube Access to the Stage

If the stage is located outside the Casabase Cube application database, grant the application access to the database, schema, and stage.

For example:

GRANT USAGE ON DATABASE CASABASE_CUBE_DEMO
  TO APPLICATION CASABASE_CUBE;

GRANT USAGE ON SCHEMA CASABASE_CUBE_DEMO.SAMPLE_DATA
  TO APPLICATION CASABASE_CUBE;

GRANT READ ON STAGE CASABASE_CUBE_DEMO.SAMPLE_DATA.EPM_STAGE
  TO APPLICATION CASABASE_CUBE;

Interactive Outline Upload

For an initial migration, testing, or ad hoc administration, the .otl file does not need to be staged manually.

From the Casabase Cube Home page:

1. Select Import Outline.
2. Enter the new Cube Name.
3. Drag and drop the .otl file or select Browse files.
4. Optionally select Upload to a different stage.
5. Optionally select Rebuild dimensions after import.
6. Select Upload & Import.

If no alternate stage is selected, Casabase Cube uploads the outline to a stage within the application database.

For recurring production processes, Casabase Software recommends staging the .otl file and using the SQL-based workflow.

4. Import the Outline

The stored procedure for importing a native Essbase outline is:

CUBE.IMPORT_ESSBASE_OTL

IMPORT_ESSBASE_OTL imports an Oracle Essbase .otl file and creates the hierarchy structures for the target cube.

Example

Using the recommended stage structure:

CALL CUBE.IMPORT_ESSBASE_OTL(
    'ASOSAMP',
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/ASOsamp/Sample/otl/Sample.otl'
);

The procedure accepts:

Parameter	Description
P_CUBE_NAME	Name of the Casabase Cube to create or update.
P_STAGE_FILE	Snowflake stage path to the native .otl file.
P_BUILD_ATTRIBUTES	Optional parameter controlling whether attribute dimensions are built as part of the import.

Note

P_STAGE_FILE identifies the specific .otl file to import, not the otl/ directory.

The interactive Import Outline workflow uses the same underlying migration capability while providing a guided user interface.

5. Rebuild dimensions

After importing the outline, rebuild the cube dimensions.

The stored procedure is:

CUBE.REBUILD_DIMENSIONS

The procedure can rebuild all dimensions for a cube and provides options for snapshots and asynchronous execution.

Example

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

The parameters are:

Parameter	Description
P_CUBE_NAME	Cube to rebuild.
P_REBUILD_ALL	When TRUE, rebuild all dimensions in the cube.
P_SKIP_SNAPSHOTS	Controls whether hierarchy snapshots are skipped during the rebuild.
P_ASYNC	Controls whether the rebuild runs asynchronously.

For an initial migration, the production example rebuilds all dimensions.

If Rebuild dimensions after import was selected during the interactive outline import, this step is performed automatically.

6. Import the Native Level-0 Data

After the cube structure has been created and the dimensions rebuilt, import the native level-0 export.

The native level-0 export must first be uploaded to a Snowflake stage that Casabase Cube can access.

Supported data-file formats are:

Format	Description
.txt	Uncompressed native Essbase level-0 export
.gz	Gzip-compressed native Essbase level-0 export

The stored procedure used to import the data is:

CUBE.IMPORT_ESSBASE_EXPORT

IMPORT_ESSBASE_EXPORT imports data from a native Essbase export into the cube’s fact table.

Stage the Data Files

Using the recommended directory structure, place the native level-0 export file or files in:

<Snowflake Stage>/<Essbase Application>/<Essbase Cube>/data/

For the example used throughout this guide:

@CASABASE_CUBE_DEMO.SAMPLE_DATA.EPM_STAGE/ASOsamp/Sample/data/

The directory can contain one or more .txt or .gz native level-0 export files.

For example:

data/
├── data1.gz
├── data2.gz
└── data3.gz

Import the Data

Pass the stage directory containing the data files to IMPORT_ESSBASE_EXPORT:

CALL CUBE.IMPORT_ESSBASE_EXPORT(
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/ASOsamp/Sample/data/',
    'ASOSAMP',
    FALSE
);

Note

Unlike IMPORT_ESSBASE_OTL, which receives the path to a specific .otl file, IMPORT_ESSBASE_EXPORT receives the path to the data/ directory containing the native level-0 export files.

The procedure accepts:

Parameter	Description
STAGE_PATH	Snowflake stage directory containing the native level-0 .txt or .gz export files.
CUBE_NAME	Target Casabase Cube.
IGNORE_UNKNOWN_MEMBERS	Controls how rows containing members not recognized by the cube are handled.
SOURCE_TABLE	Optional intermediate source table used during the import process.

In the example above:

FALSE

is passed for IGNORE_UNKNOWN_MEMBERS, so unknown members are not silently ignored.

7. Validate the Cube

After importing the outline, rebuilding the dimensions, and loading the data, validate the resulting cube.

Casabase Cube provides:

CUBE.HEALTH_CHECK

for validating cube structure, data integrity, and configuration. The results identify checks as PASS, WARN, or FAIL.

For example:

CALL CUBE.HEALTH_CHECK('ASOSAMP');

Review any warnings or failures before considering the migration complete.

Also review the result returned by IMPORT_ESSBASE_OTL. The import can report compatibility-related changes, including member formulas removed from stored-data members.

Before functional validation, review Compatibility Notes for known differences in aggregation operators, dynamic formulas, stored-member formulas, and supported member functions.

You should also validate the migrated application against the source Essbase application, including:

• Dimension counts
• Hierarchy structures
• Member counts
• Shared members
• Aliases
• Aggregation behavior
• Member formulas
• Native level-0 data totals
• Key business calculations
• Representative query results

Caution

Formulas and complex application logic should be reviewed and tested after migration. Automated migration can reproduce and translate supported application structures, but validation against the source Essbase application remains an important part of the migration process.

8. Query the Migrated Cube

After validation, query the cube using the Query Builder or SQL.

The primary SQL query interface is:

CUBE.QUERY_CUBE

QUERY_CUBE retrieves aggregated cube data based on a Point of View (POV), with member formulas applied automatically.

See Querying for detailed query syntax and examples.

Complete SQL Example

The following example shows the core SQL workflow for migrating an Essbase application to Casabase Cube using the recommended stage structure.

USE DATABASE CASABASE_CUBE;
USE SCHEMA SHARED_DATA;

-- ============================================================
-- GRANT ACCESS TO MIGRATION STAGE
-- ============================================================

GRANT USAGE ON DATABASE CASABASE_CUBE_DEMO
  TO APPLICATION CASABASE_CUBE;

GRANT USAGE ON SCHEMA CASABASE_CUBE_DEMO.SAMPLE_DATA
  TO APPLICATION CASABASE_CUBE;

GRANT READ ON STAGE CASABASE_CUBE_DEMO.SAMPLE_DATA.EPM_STAGE
  TO APPLICATION CASABASE_CUBE;


-- ============================================================
-- CUBE BUILD
-- ============================================================

CALL CUBE.IMPORT_ESSBASE_OTL(
    'ASOsamp',
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/ASOsamp/Sample/otl/Sample.otl'
);

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


-- ============================================================
-- DATA LOAD
-- ============================================================

CALL CUBE.IMPORT_ESSBASE_EXPORT(
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/ASOsamp/Sample/data/',
    'ASOSAMP',
    FALSE
);


-- ============================================================
-- VALIDATION
-- ============================================================

CALL CUBE.HEALTH_CHECK('ASOSAMP');

Replacing an Existing Cube

During development or testing, you may want to completely remove an existing cube before rebuilding it.

Casabase Cube provides:

CUBE.DROP_CUBE

DROP_CUBE permanently removes a cube and its associated objects and requires a confirmation flag.

For example:

CALL CUBE.DROP_CUBE(
    'ASOSAMP',
    TRUE
);

Danger

DROP_CUBE permanently removes the cube and its associated objects. Do not include this step in a production migration or refresh process unless deleting the existing cube is intentional.

For ongoing updates to an existing cube, use the appropriate reimport and rebuild workflows rather than dropping and recreating the cube.

Ongoing Production Refreshes

The initial migration and ongoing production refresh processes are related but not necessarily identical.

For recurring production operations:

1. Export the updated .otl and/or native level-0 data from Oracle Essbase.
2. Place the updated artifacts in the appropriate otl/ and data/ directories in the configured Snowflake stage.
3. Execute the appropriate Casabase Cube stored procedures through SQL.
4. Rebuild affected dimensions as required.
5. Import the refreshed native level-0 data.
6. Validate the cube.
7. Monitor the automated process and handle any errors.

Casabase Cube also exposes REIMPORT_CUBE_SOURCE, which reimports a cube from its original source definition and can optionally rebuild the cube afterward.

The appropriate refresh strategy depends on whether metadata, data, or both have changed and how frequently your Essbase environment is refreshed.

Next Steps

• Migrating from Oracle Cloud EPM
• Compatibility Notes
• Querying
• Formulas
• Administration
• Troubleshooting