Migrating from Oracle Cloud EPM

Casabase Cube can migrate an existing Oracle Cloud EPM application to Snowflake using the native Essbase artifacts contained within the Cloud EPM environment.

A typical migration requires:

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

The outline is retrieved from the Oracle Cloud EPM Artifact Snapshot, while the native level-0 data is exported separately using Oracle EPM Automate.

Once these artifacts have been obtained, the Casabase Cube import process is the same core process used for an Oracle Essbase migration.

Migration Workflow

The migration process consists of the following steps:

1. Download the Cloud EPM Artifact Snapshot.
2. Extract the native Essbase outline (.otl) for the plan type.
3. Create and download a native level-0 export.
4. Prepare the native level-0 data files for Casabase Cube.
5. Upload the migration artifacts to Snowflake.
6. Import the outline.
7. Rebuild the cube dimensions.
8. Import the native level-0 data.
9. Review outline-import messages and Compatibility Notes.
10. Validate the migrated cube.
11. 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.
• Access to the source Oracle Cloud EPM environment.
• Oracle EPM Automate installed and configured.
• Sufficient Cloud EPM privileges to download the Artifact Snapshot and export Essbase data.
• A Snowflake stage accessible to Casabase Cube.
• Enough local storage to download the required Oracle Cloud EPM export files.

Caution

Casabase Cube uses the native Essbase outline (.otl) contained in the Cloud EPM Artifact Snapshot. Dimension CSV metadata exports are not required by the current Casabase Cube migration process.

1. Download the Artifact Snapshot

The native Essbase outline for each Oracle Cloud EPM plan type is contained within the application’s Artifact Snapshot.

Use Oracle EPM Automate to download the snapshot.

epmautomate login <username> <password> https://<Oracle_Cloud_EPM_Url>/HyperionPlanning/

epmautomate downloadFile "Artifact Snapshot"

epmautomate logout

This downloads the Cloud EPM Artifact Snapshot to the local EPM Automate working directory.

Note

EPM Automate is an Oracle product and is not part of Casabase Cube. Authentication methods, command syntax, and Cloud EPM behavior are controlled by Oracle.

2. Extract the Native Essbase Outline

The Artifact Snapshot contains the native Essbase .otl files used by the application’s plan types.

You do not need to extract the entire Artifact Snapshot. A single .otl file can be extracted directly from the ZIP archive.

The path differs depending on whether the plan type is a BSO or ASO cube.

BSO Plan Types

For a BSO plan type, the outline is located at:

HP-<Cloud EPM Application Name>/
└── resource/
    └── Essbase Data/
        └── <Plan Type Name>/
            └── cube/
                └── <Plan Type Name>.otl

For example, on Windows you can extract only the .otl file using:

tar -xf "C:\Artifact Snapshot.zip" -C "C:\" "HP-<Cloud EPM Application Name>/resource/Essbase Data/<Plan Type Name>/cube/<Plan Type Name>.otl"

ASO Plan Types

For an ASO plan type, the outline is located at:

HP-<Cloud EPM Application Name>/
└── resource/
    └── Essbase Data/
        └── <Plan Type Name>/
            └── <Plan Type Name>/
                └── <Plan Type Name>.otl

Extract the file using:

tar -xf "C:\Artifact Snapshot.zip" -C "C:\" "HP-<Cloud EPM Application Name>/resource/Essbase Data/<Plan Type Name>/<Plan Type Name>/<Plan Type Name>.otl"

The extracted .otl file is the same native Essbase outline format used by Casabase Cube for Oracle Essbase migrations.

For example:

Plan1.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

The BSO and ASO Artifact Snapshot paths are different. Use the path that corresponds to the storage type of the Cloud EPM plan type you are migrating.

3. Create a Native Level-0 Export

Use Oracle EPM Automate to generate a native level-0 export for the plan type.

epmautomate login <username> <password> <EPM_URL>

epmautomate exportEssbaseData <PlanType> <ExportFileName>.zip

epmautomate downloadFile outbox/<ExportFileName>.zip

epmautomate logout

For example:

epmautomate login john.doe@example.com password.epw https://example.oraclecloud.com/HyperionPlanning/

epmautomate exportEssbaseData Plan1 Plan1_Level0.zip

epmautomate downloadFile outbox/Plan1_Level0.zip

epmautomate logout

The key parameters are:

Parameter	Description
<username>	Oracle Cloud EPM username.
<password>	Password or supported encrypted password-file reference.
<EPM_URL>	URL of the Oracle Cloud EPM environment.
<PlanType>	Cloud EPM plan type to export.
<ExportFileName>	Name assigned to the exported ZIP file.

The native export contains the bottom-level data from the Essbase database associated with the plan type. The older Cloud EPM documentation also used exportEssbaseData for this native export process.

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

4. Prepare the Native Level-0 Data Files

Oracle Cloud EPM downloads the native Essbase data export as a ZIP archive.

The ZIP archive itself should not be uploaded as the Casabase Cube data source.

Extract the ZIP locally. The archive contains one or more native Essbase data files.

Casabase Cube accepts the extracted data in either of the following formats:

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

You can therefore:

• Upload the extracted .txt files directly, or
• Compress the extracted files with GZip and upload the resulting .gz files.

For large exports, .gz can reduce storage and transfer size.

5. Upload the Migration Artifacts to Snowflake

For production and automated migrations, upload the .otl and native level-0 data files to a Snowflake stage that Casabase Cube can access.

Casabase Software recommends organizing Cloud EPM migration artifacts using the same structure used for Oracle Essbase migrations:

<Snowflake Stage>/<Cloud EPM Application>/<Plan Type>/
├── otl/
└── data/

For example, for a Cloud EPM application named Financials with a plan type named Plan1:

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

The directories contain:

Directory	Contents
otl/	The native Essbase outline (.otl) extracted from the Artifact Snapshot.
data/	One or more extracted native level-0 data files in .txt or .gz format.

This structure keeps the outline and data for each Cloud EPM plan type together and provides predictable 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 extracted .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.

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

6. Import the Outline

The stored procedure for importing the native Essbase outline extracted from Cloud EPM is:

CUBE.IMPORT_ESSBASE_OTL

The procedure is the same one used for an Oracle Essbase migration.

Using the recommended stage structure:

CALL CUBE.IMPORT_ESSBASE_OTL(
    'PLAN1',
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/Financials/Plan1/otl/Plan1.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.

7. Rebuild Dimensions

After importing the outline, rebuild the cube dimensions.

CUBE.REBUILD_DIMENSIONS

For example:

CALL CUBE.REBUILD_DIMENSIONS(
    'PLAN1',
    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.

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

8. Import the Native Level-0 Data

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

The data files must be present in the data/ directory as .txt or .gz files.

For example:

@CASABASE_CUBE_DEMO.SAMPLE_DATA.EPM_STAGE/
└── Financials/
    └── Plan1/
        └── data/
            ├── data1.gz
            ├── data2.gz
            └── data3.gz

Use:

CUBE.IMPORT_ESSBASE_EXPORT

to import the data.

CALL CUBE.IMPORT_ESSBASE_EXPORT(
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/Financials/Plan1/data/',
    'PLAN1',
    FALSE
);

Note

IMPORT_ESSBASE_EXPORT receives the path to the data/ directory containing the native level-0 .txt or .gz files. It does not receive the original ZIP file downloaded from Oracle Cloud EPM.

The procedure accepts:

Parameter	Description
STAGE_PATH	Snowflake stage directory containing the native level-0 .txt or .gz 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.

9. Validate the Cube

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

Use:

CUBE.HEALTH_CHECK

For example:

CALL CUBE.HEALTH_CHECK('PLAN1');

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 Cloud EPM plan type, 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. Validation against the source Cloud EPM application remains an important part of the migration process.

10. Query the Migrated Cube

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

The primary SQL query interface is:

CUBE.QUERY_CUBE

See Querying for detailed query syntax and examples.

Complete SQL Example

Once the Cloud EPM artifacts have been extracted and staged, the Casabase Cube portion of the migration is straightforward.

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(
    'PLAN1',
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/Financials/Plan1/otl/Plan1.otl'
);

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


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

CALL CUBE.IMPORT_ESSBASE_EXPORT(
    '@"CASABASE_CUBE_DEMO"."SAMPLE_DATA"."EPM_STAGE"/Financials/Plan1/data/',
    'PLAN1',
    FALSE
);


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

CALL CUBE.HEALTH_CHECK('PLAN1');

Ongoing Production Refreshes

For recurring Cloud EPM migration or synchronization processes, the extraction and Casabase Cube import steps can be scripted.

A typical production workflow is:

1. Download the latest Cloud EPM Artifact Snapshot when metadata has changed.
2. Extract the plan type’s .otl file from the Artifact Snapshot.
3. Create a new native level-0 export using EPM Automate.
4. Download and extract the native level-0 export.
5. Place the .otl in the appropriate otl/ directory.
6. Place the .txt or .gz data files in the corresponding data/ directory.
7. Import or reimport the outline as required.
8. Rebuild affected dimensions.
9. Import the refreshed native level-0 data.
10. Review import messages and compatibility-related changes.
11. Validate the cube.
12. Monitor the automated process and handle any errors.

For a production implementation, the Oracle EPM Automate commands, file extraction, Snowflake stage uploads, and Casabase Cube stored procedure calls can all be incorporated into a repeatable automation process.

Next Steps

• Migrating from Oracle Essbase
• Compatibility Notes
• Querying
• Formulas
• Administration
• Troubleshooting