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 Essbase outline as either a native OTL (.otl) file or an XML (.xml) outline export
• A native level-0 export containing the source application data

Casabase Cube supports both OTL and XML representations of an Oracle Essbase outline. 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 Essbase outline as either an OTL (.otl) file or an XML (.xml) outline export.
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 outline as either an OTL (.otl) file or an XML (.xml) outline export.
• 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 or XML outline file must also be uploaded to a Snowflake stage accessible to Casabase Cube.

Note

For Oracle Essbase migrations, Casabase Cube accepts either the native OTL file or an XML representation of the outline. You only need one of these outline formats.

1. Obtain the Essbase Outline

Obtain the Essbase outline for the application you want to migrate. Casabase Cube accepts either:

• The native Essbase OTL file (.otl)
• An XML representation of the Essbase outline (.xml)

For example:

Sample.otl

or:

Sample.xml

Export the Outline to XML with MaxL

If you want to use XML instead of the native OTL file, use the following MaxL command:

export outline 'AppName'.'CubeName' all dimensions to xml_file 'C:\CubeName.xml';

Replace AppName, CubeName, and the output path with the values for your Essbase environment.

For example:

export outline 'ASOsamp'.'Sample' all dimensions to xml_file 'C:\Sample.xml';

The resulting XML file can be used in place of the OTL file during the Casabase Cube outline import.

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.

Export Level-0 Data with MaxL

To export level-0 data to a single file, use:

export database 'AppName'.'CubeName' level0 data to data_file 'C:\CubeName_Data.txt';

Replace AppName, CubeName, and the output path with the values for your Essbase environment.

For example:

export database 'ASOsamp'.'Sample' level0 data to data_file 'C:\Sample_Data.txt';

Parallel Export to Multiple Files

For larger cubes, Essbase can export the level-0 data in parallel to multiple files. Specify multiple output files in the same MaxL command:

export database 'AppName'.'CubeName' level0 data to data_file 'C:\CubeName_Data1.txt', 'C:\CubeName_Data2.txt', 'C:\CubeName_Data3.txt';

For example:

export database 'ASOsamp'.'Sample' level0 data to data_file 'C:\Sample_Data1.txt', 'C:\Sample_Data2.txt', 'C:\Sample_Data3.txt';

All files produced by the parallel export should be uploaded to the same data/ directory in the Snowflake stage. IMPORT_ESSBASE_EXPORT can import the directory containing the complete set of level-0 export files.

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>/
├── outline/
└── data/

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

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

The directories contain:

Directory	Contents
outline/	The Essbase outline for the cube as either an OTL (.otl) or XML (.xml) file.
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 or XML outline 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 or .xml 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 or XML outline file and using the SQL-based workflow.

4. Import the Outline

The stored procedure you use depends on the format of the Essbase outline:

Outline format	Stored procedure
Native Essbase OTL (.otl)	CUBE.IMPORT_ESSBASE_OTL
Essbase XML outline export (.xml)	CUBE.IMPORT_ESSBASE_OUTLINE_XML

Both procedures import the Essbase outline and create the Casabase Cube multidimensional structure. Use the procedure that matches the outline artifact you obtained in Step 1.

Import an OTL Outline

For a native Essbase OTL (.otl) file, use:

CUBE.IMPORT_ESSBASE_OTL

Using the recommended stage structure:

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

Import an XML Outline

For an Essbase XML (.xml) outline export, use:

CUBE.IMPORT_ESSBASE_OUTLINE_XML

Using the same recommended stage structure:

CALL CUBE.IMPORT_ESSBASE_OUTLINE_XML(
    'ASOSAMP',
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/ASOsamp/Sample/outline/Sample.xml'
);

The XML outline can be created from Oracle Essbase using the MaxL export outline ... to xml_file command described in Export the Outline to XML with MaxL.

Parameters

Both procedures require the cube name and the path to the specific outline file:

Parameter	Description
P_CUBE_NAME	Name of the Casabase Cube to create or update.
P_STAGE_FILE	Snowflake stage path to the specific outline file. Use an .otl file with IMPORT_ESSBASE_OTL or an .xml file with IMPORT_ESSBASE_OUTLINE_XML.

Note

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

The interactive Import Outline workflow handles the appropriate outline format automatically 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 the outline import procedure, which receives the path to a specific outline 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/outline/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 or XML outline and/or native level-0 data from Oracle Essbase.
2. Place the updated artifacts in the appropriate outline/ 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