Import & Migration

The procedures in this section support migration from Oracle Essbase and Oracle Cloud EPM. They import Oracle Essbase outline metadata from native OTL or XML outline exports, load native data exports, and translate individual Essbase formulas into Casabase Cube syntax.

IMPORT_ESSBASE_OTL

Minimum role: CUBE_ADMIN

IMPORT_ESSBASE_OTL(
    P_CUBE_NAME,
    P_STAGE_FILE
) -> VARIANT

Parses a binary Essbase outline (.otl) file, creates the cube’s dimension source tables, and registers the dimensions. This is typically the first application procedure used during an Oracle Essbase or Oracle Cloud EPM migration.

For an XML outline export, use IMPORT_ESSBASE_OUTLINE_XML.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name to create.
`P_STAGE_FILE`	`VARCHAR`	req	Stage path to the binary Essbase outline file.

Returns

Returns a VARIANT describing the import result.

The procedure creates one input table per dimension in SHARED_DATA, named <CUBE>_<DIMENSION>_OTL, and registers the dimensions.

After importing the outline, use a single REBUILD_DIMENSIONS call to build the cube structures, then use IMPORT_ALL_FORMULAS to import formulas. Per-dimension CREATE_DIMENSION calls are not required after an outline import.

CREATE_DIMENSION remains available when you want to build an individual dimension or create dimensions from your own source tables.

Source Tracking and Import Details

The import records the source path so REIMPORT_CUBE_SOURCE can re-run the import without requiring the stage path again.

The import also records details about outline content that could not be carried over unchanged, including:

Suppressed stored-member formulas
Coerced consolidation operators
Skipped dimensions
Parse integrity

These details provide a durable record of how the source outline was interpreted during migration.

Example

CALL CUBE.IMPORT_ESSBASE_OTL(
    'ASOSAMP',
    '@MY_STAGE/ASOSAMP.otl'
);

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

IMPORT_ESSBASE_OUTLINE_XML

Minimum role: CUBE_ADMIN

IMPORT_ESSBASE_OUTLINE_XML(
    P_CUBE_NAME,
    P_STAGE_FILE
) -> VARIANT

The XML counterpart to IMPORT_ESSBASE_OTL. Parses a MaxL or Planning XML outline export using the <application> / <Dimension> / <Member> structure, creates the cube’s dimension source tables, and registers the cube.

Use this procedure when the Oracle Essbase outline was exported as XML. Use IMPORT_ESSBASE_OTL for a binary .otl file.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name to create.
`P_STAGE_FILE`	`VARCHAR`	req	Stage path to the XML outline export.

Returns

Returns a VARIANT describing the import result.

The procedure creates one input table per dimension in SHARED_DATA, named <CUBE>_<DIMENSION>_XML. The table shape is the same as the dimension source tables created by the OTL importer, so CREATE_DIMENSION and VALIDATE_DIMENSION treat them the same way.

Attribute dimensions and their associations are read and registered, but are not built by the import procedure. Follow the import with REBUILD_DIMENSIONS, which builds the base dimensions and then their attribute dimensions.

The built-in Essbase ATTRIBUTE_CALCULATIONS dimension (Sum, Count, Min, Max, and Avg) is outline scaffolding rather than data. It is skipped and reported in skipped_dimensions.

The returned VARIANT includes additional information about how the outline was interpreted, including:

cube_type
alias_tables
dynamic_time_series
attribute_dimensions
stored_member_formulas_suppressed
formulas_kept_storage_unstated
operators_coerced
hybrid_authored_solve_orders
parse_integrity

A dimension count that disagrees with the outline is refused and nothing is written. Every accepted or refused run is recorded in the audit log with EVENT_TYPE = 'OUTLINE_IMPORT'.

Example

CALL CUBE.IMPORT_ESSBASE_OUTLINE_XML(
    'ASOSAMP',
    '@MY_STAGE/ASOsamp/Sample/xml/Sample.xml'
);

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

IMPORT_ESSBASE_EXPORT

Minimum role: CUBE_ADMIN

IMPORT_ESSBASE_EXPORT(
    STAGE_PATH,
    CUBE_NAME,
    IGNORE_UNKNOWN_MEMBERS,
    SOURCE_TABLE
) -> VARCHAR

Loads data from Oracle Essbase or Oracle Cloud EPM native export files into a cube’s fact table.

Supported input file types include .txt and .gz.

Parameters

Parameter	Type	Req/Opt	Description
`STAGE_PATH`	`VARCHAR`	req	Full stage path to the data file.
`CUBE_NAME`	`VARCHAR`	req	Cube name. Determines member-to-dimension mapping and the target fact table.
`IGNORE_UNKNOWN_MEMBERS`	`BOOLEAN`	opt	`TRUE` silently skips unrecognized members. `FALSE` reports them as errors.
`SOURCE_TABLE`	`VARCHAR`	opt	Preloaded table to use instead of scanning the stage. Expected columns are `file_name`, `line_num`, and `raw_line`.

Example

CALL CUBE.IMPORT_ESSBASE_EXPORT(
    '@MY_STAGE/data.txt',
    'FINANCE',
    FALSE,
    NULL
);

IMPORT_ESSBASE_FORMULA

Minimum role: CUBE_ADMIN

IMPORT_ESSBASE_FORMULA(
    P_CUBE_NAME,
    P_FORMULA_NAME,
    P_ESSBASE_FORMULA,
    P_DEFAULT_DIMENSION,
    P_HIERARCHY
) -> VARIANT

Translates a single Essbase calc-script formula into Casabase Cube syntax.

Use this procedure to translate formulas individually during a migration or to inspect how a particular Essbase construct will convert.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_FORMULA_NAME`	`VARCHAR`	req	Name for the translated formula.
`P_ESSBASE_FORMULA`	`VARCHAR`	req	Essbase expression to translate.
`P_DEFAULT_DIMENSION`	`VARCHAR`	opt	Default dimension for unqualified member references.
`P_HIERARCHY`	`VARCHAR`	opt	Target dimension. This parameter retains the legacy `P_HIERARCHY` name.

Returns

Returns the translated formula together with any warnings.

Some Essbase functions do not have an equivalent Casabase Cube construct. Those functions are reported as requiring manual translation.

Example

CALL CUBE.IMPORT_ESSBASE_FORMULA(
    'FINANCE',
    'Test',
    '@SUMRANGE("Periodic","Jan":@CURRMBR("Period"))',
    'MEASURES',
    'VIEW'
);