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.

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 ADW. 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 Partition 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.
  • You must load data using the exact member names or fully qualified member names that are used in Essbase -- do not load using aliases.

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.