Create a Schema for Federated Cubes

After you have provisioned an instance of Autonomous AI Lakehouse Serverless, create a schema in the database for the fact table you need to work with Essbase federated cubes.

Note:

Oracle recommends using a dedicated schema for federated cubes.

The schema you need to create for a federated cube, including its fact table, is independent from the Essbase RCU schemas. However, it does need to be in the same Autonomous AI Lakehouse Serverless database, which is also known as the repository database.

Note:

If you are letting Essbase manage the fact table, Essbase creates the fact table for you. See Data Load Options for Federated Cubes.

  1. Log in to Autonomous AI Lakehouse as the Autonomous AI Database administrator.

  2. Create a schema / Database user (for example, ADB_USER) with sufficient privileges to work with federated cubes. 

    CREATE USER ADB_USER identified by schemapass DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
grant CREATE ANALYTIC VIEW, CREATE HIERARCHY, CREATE TABLE, CREATE ATTRIBUTE DIMENSION, CREATE SESSION, CREATE VIEW, RESOURCE, CONNECT to ADB_USER;
grant execute on dbms_cloud to ADB_USER;
grant execute on dbms_cloud_oci_obs_object_storage to ADB_USER;
ALTER USER ADB_USER DEFAULT ROLE RESOURCE;
ALTER USER ADB_USER QUOTA UNLIMITED ON DATA;
commit;

  3. Optional: If you plan to create more than one federated cube, you must make a choice: You can use a single schema for all federated cubes, or create multiple schemas (typically one schema per application containing a federated cube).

    Note:

    Regardless of the number of schemas you create, you must maintain the following:

    • Each Essbase cube has only one federated partition
    • Each federated cube uses only one fact table.
    • Unlike other partition types used in Essbase, the data is not in two locations. Your federated cube's fact table must contain all the cube's data.

    For a comprehensive list, refer to Restrictions for Federated Cubes.

  4. Now that you have created a schema for the fact table, you can move on to Identify the Pivot Dimension and Set up the Fact Table.

Note:

You will also need to set up cloud credentials to enable Essbase to push data and metadata to Autonomous AI Lakehouse. Refer to Set up Credentials for Federated Cubes.