Data Load Options for Federated Cubes

Depending on how you selected to manage your federated data, you can load data using Essbase data load jobs, or, you can use Autonomous AI Database data load tools.

From an Essbase federated cube, you can load data to your fact table in Autonomous AI Lakehouse using Essbase data load methods, or methods available in Autonomous AI Database Data Studio (refer to Autonomous AI Database Data Studio Features).

Loading Data to Federated Cubes through Essbase

Before performing a data load through Essbase to the fact table, Oracle recommends you upload the data file to the Essbase Server. Client side data load is supported, but takes longer.

Loading Essbase-formatted data export files into federated cubes can be time consuming. To optimize data loads, use a DBMS formatted source file. You can make one using the DATAEXPORT calculation command with DataExportCSVFormat option. CSV formatted files can be loaded faster because they are in accordance with DBMS_CLOUD package format options for source files.

Pivot Dimension in Fact Table and Data Load Input Source

The pivot dimension used in data load input files must be the same as the pivot dimension of the fact table.

For example, in the following fact table, the pivot dimension is the Measures dimension (Sales, COGS, Margin, etc).


Fact table of denormalized data and metadata shown in SQL Developer, with column headers "Year","Product","Market","Scenario","Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions", and "Ending Inventory."

An acceptable data load input file for this fact table has a similar shape, because it has the same pivot dimension. Example (truncated):

"Year","Product","Market","Scenario","Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions","Ending Inventory"
"Jan","100-10","New York","Actual",678,271,407,94,51,0,145,262,2101,644,2067
"Feb","100-10","New York","Actual",645,258,387,90,51,1,142,245,2067,619,2041
"Mar","100-10","New York","Actual",675,270,405,94,51,1,146,259,2041,742,2108

If the input file pivot dimension differs from the fact table pivot dimension, the load might take more time.

SQL-to-SQL Data Load Optimization

When an Essbase data load rule uses a Datasource to the same Autonomous AI Database server and pluggable database (PDB) where the fact table resides, Essbase can use SQL-to-SQL data load optimization. The optimized path is faster because there is no need to load data into Essbase and move it to object storage.

The optimized path applies when all of the following conditions are met:

  • The load rule source type is Datasource.

  • The Datasource and the fact table are in the same PDB. The source table and fact table can be in the same or different schemas, provided that the required privileges and SQL qualification are correct.

  • The SQL query defined in the Datasource is valid, and the configured connection has access to all referenced objects.

  • The query defined in the load rule is valid, and its result columns align with the load rule field mapping.

  • Any required data transformations are expressed in the Datasource or load rule SQL query. Transformations performed outside the SQL query are not part of optimized-path eligibility.

In the Datasource and in the load rule, any valid Oracle SQL queries are supported using the optimized path. For example, you can use expressions such as:

'P' || PROD_ID AS "Products"

If the source table is not in the federated cube schema, grant SELECT privilege to the Database schema user. For example,

GRANT SELECT ON <datasource_table_name_schema>.<data_table_name> TO ADB_USER;

Note:

If you are optimizing data loads on Essbase on Autonomous AI Lakehouse, grant the SELECT privilege to "ESSBASE$ONBOARD".

To verify that the optimized path was activated, check the <appname>_ODL.log file for the following message: 

AV-DL: Data load optimization is activated

If the message does not appear, verify the load rule source type, database and PDB locations, object privileges, SQL qualification, and load rule field mapping.

Multiple Import Files in MaxL not Supported

Importing data from multiple files in parallel using a MaxL import statement with wildcard characters is not supported for federated cubes.

Loading Data directly to Federated Cubes without using Essbase Data Load

Caution:

This information applies only if you select to manage your own data in Autonomous AI Lakehouse. Do not use non-Essbase data load tools for Essbase-managed federated cubes.

If you do not need to load data through Essbase to Autonomous AI Lakehouse, you can use Data Tools in Autonomous AI Database to load data to the fact table and perform other management tasks. However, ensure that the cube outline and fact table do not get out of sync – see Metadata Precautions for Federated Cubes.

Oracle does not recommend loading an Essbase-managed fact table with non-Essbase tools.

If you load data directly to the fact table without using Essbase tools, take the following precautions:

  • Do not load duplicate rows to the fact table.

  • Intersections must be for stored data.

  • The data in your source file must use the exact member names (or qualified member names) that are used in Essbase. Do not load using aliases. For duplicate-member enabled cubes, the correct qualified member name format is required. The syntax is described in Duplicate Member Names in Outlines.

  • To load data into Essbase-managed federated cubes, the rows in your source data must contain member IDs rather than member names.

Refer to Autonomous AI Database Data Studio Features for information about loading data into Autonomous AI Database using Data Studio.

Refer to the Oracle Autonomous AI Database 15 Minute Quick Start Livelab for an overview of how to deploy Autonomous AI Database on OCI.