Federated Cube Maintenance and Troubleshooting

Model and Test Federated Cubes

When designing an Essbase federated cube, follow these design and testing guidelines, for a phased approach to troubleshoot or monitor performance.

Begin the federated cube project on a test environment.
Start with Essbase ASO or BSO cube models that have the following characteristics:
- not many levels
- not many shared members or attributes

When creating a federated partition, schedule offline operations when queries are not allowed against the instance.
Gradually disconnect active Essbase user sessions, using MaxL alter application disable commands and/or disable connects (to prevent any new user activity), followed by alter system logout session and/or kill request (if you need to terminate any active sessions that don't need to complete). Note that MaxL cannot terminate any requests that may be running in Autonomous AI Lakehouse. If you disable commands in the application, remember to re-enable commands after creating the federated partition.

To terminate requests that are running as SQL sessions in Autonomous AI Lakehouse, additional steps are required. See Cancel Long Running SQL on Federated Cubes.
Perform timeout tuning:
- HTTPS proxy on customer network - adjust customer network timeouts
- Load balancer - increase LoadBalance timeout to 1260 seconds (21 minutes)
- Increase HTTPD timeouts to 21 minutes
```
/etc/httpd/conf.d/00_base.conf:ProxyTimeout 1260
```
```
/etc/httpd/conf.d/00_base.conf:Timeout 1260
```
- APS/JAPI timeout:
  - On the Console page in the Essbase web interface, select Configuration, and note the value of olap.server.netSocketTimeOut. A value of 200 ms means that every count of 5 for these properties gives 1 second of time-wait.
  - To set APS/JAPI timeout limit to 30 minutes, set olap.server.netRetryCount to 9000.
Create the federated partition.
Revert the timeout adjustments in step 3.
Enable users back onto the system using alter application enable commands and/or connects, if these were disabled previously.
For reports on a federated cube, tune QRYGOVEXECTIME to be larger than the expected time to execute queries against federated partitions. Note that QRYGOVEXECTIME cannot terminate any requests that may be running in Autonomous AI Lakehouse.
After development environment testing and tuning are completed, then use the above steps 1 through 7 to implement a production environment.

Note:

If you see a "Failed to save outline" error when creating the federated partition, wait for the sessions to complete, then refresh the browser. If the federated partition has been created, then validate it in SQL Developer. If it validates in SQL Developer then the federated cube is ready for use. Otherwise, the model needs to be fixed and timeout tuning is needed as described above in step 3.

Metadata Precautions for Federated Cubes

Take care when editing the outline for a non-Essbase managed, federated cube. If you add or rename members, ensure that the metadata changes are also represented in your fact table in Autonomous AI Lakehouse.

This topic does not apply for federated cubes with Essbase managed data storage.

If the Essbase outline becomes out of sync with the fact table in Autonomous AI Lakehouse, the federated cube will become invalid or not function correctly. To fix it, you will need to drop the federated partition, make changes to the outline and fact table, and then re-create the federated partition.

If a federated cube becomes invalid, you may encounter an error beginning with Essbase Error(1040235): Remote warning from federated partition.

The following types of Essbase outline changes will cause a (non-Essbase managed) federated cube to become invalid:

Adding, renaming, or removing dimensions
Adding, renaming, or removing stored members in the pivot dimension
Changing any member from stored to dynamic

For other types of Essbase outline changes not indicated above (for example, adding or renaming a non-pivot-dimension member), you should make the corresponding change to the affected data row in the fact table. Otherwise, the federated cube may not function correctly.

If you know in advance that Essbase outline metadata will change, it is better to remove the federated partition first, make the outline changes, update the fact table, and then recreate the federated partition.

However, if the Essbase metadata changed and caused the federated partition to become invalid, take the following action:

Remove the federated partition, and the connection associated with it (if otherwise unused), as described in Remove a Federated Partition.

From the user schema in Autonomous AI Lakehouse, manually delete any Essbase-generated tables and other objects that failed to be removed with the federated partition.
Ensure that the outline changes are completed in the Essbase cube.
Create the fact table again. See Create the Fact Table.
Re-create the connection to Autonomous AI Lakehouse. This may be a global connection (under the main Sources icon in Essbase web interface), or it may be in the Sources defined just for the application. Follow the instructions in Create a Connection for Federated Cubes.
Re-create the federated partition, as described in Create a Federated Partition.

Cancel Long Running SQL on Federated Cubes

You can cancel SQL sessions that are running on Essbase federated cubes. SQL cancellation may be necessary if an calculation is taking too long, or if the federated cube needs backup or recovery.

If Essbase calculations, data loads, or aggregations running on federated cubes generate long running SQL statements, it is possible to cancel the sessions, even though they are running on Autonomous Data Warehouse.

Before SQL session cancellation through Essbase can become available, a brief workflow of steps must be completed to enable SQL Session Governor in Essbase.

Users with following types of access are involved in the preparation steps.

User Type	Role within Workflow
DB Admin	Knows the administrator schema name and password for Autonomous AI Lakehouse. Can create stored PL/SQL procedures on Autonomous AI Lakehouse, and grant privileges to other users.
DB User Also referred to as federated partition user	Knows the Autonomous AI Lakehouse schema name and password -- the same schema and password that is used to create the Essbase connection to Oracle Database (a required prerequisite before creating the federated partition).
Essbase Application Manager	The manager/owner of an Essbase application used for a federated cube.

The following workflow of steps must be completed to enable SQL Session Governor:

DB Admin: Create the following PL/SQL stored procedures in Autonomous AI Lakehouse and grant EXECUTE privilege to the DB User:

Note:

The stored procedures are to be created on the Database user schema.

ESS_SESSION_SET_MODULE (jagent_id in VARCHAR2,app_name in VARCHAR2, db_name in VARCHAR2, req_id in VARCHAR2)

Use the following statement to create the procedure:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE "ESS_SESSION_SET_MODULE" (jagent_id in VARCHAR2, app_name in VARCHAR2, db_name in VARCHAR2, req_id in VARCHAR2) AS
    ess_jagentid VARCHAR2(256);
    ess_appname VARCHAR2(256);
    ess_dbname VARCHAR2(256);
    ess_reqid VARCHAR2(256);
    hash_str VARCHAR2(100);
BEGIN
    IF jagent_id is not NULL AND app_name is not NULL AND db_name is not NULL AND req_id is not NULL THEN
        -- add prefixes to identify essbase sessions
        ess_jagentid := CONCAT('ess_jagentid_', jagent_id);
        ess_appname := CONCAT('ess_appname_', app_name);
        ess_dbname := CONCAT('ess_dbname_', db_name);
        ess_reqid := CONCAT('ess_reqid_', req_id);

        -- ess_jagentid is stored in client_info
        dbms_application_info.set_client_info(ess_jagentid);

        -- create hash out of CONCAT(ess_jagentid, ess_appname, ess_dbname)
        -- the hash identifies the fed partition
        -- store the hash as the module
        -- store the ess_reqid as the action
        SELECT TO_CHAR(ORA_HASH(CONCAT(CONCAT(ess_jagentid, ess_appname), ess_dbname))) INTO hash_str from dual;
        dbms_application_info.set_module(module_name => hash_str, action_name => ess_reqid);
        
    ELSIF jagent_id is NULL AND app_name is NULL AND db_name is NULL AND req_id is NULL THEN
        -- reset session
        dbms_application_info.set_module(null,null);
        dbms_application_info.set_client_info(null);
    END IF;
END ESS_SESSION_SET_MODULE;

ESS_SESSION_CANCEL (jagent_id in VARCHAR2, app_name in VARCHAR2, db_name in VARCHAR2, req_id in VARCHAR2)

Use the following statement to create the procedure:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE "ESS_SESSION_CANCEL" (jagent_id in VARCHAR2, app_name in VARCHAR2, db_name in VARCHAR2, req_id in VARCHAR2) AS
    ess_jagentid VARCHAR2(256);
    ess_appname VARCHAR2(256);
    ess_dbname VARCHAR2(256);
    ess_reqid VARCHAR2(256);
    uname VARCHAR2(100);
    hash_str VARCHAR2(100);
    sessionid VARCHAR2(100);
    killcmd VARCHAR2(1000);
    XCP_MARKED_FOR_TERMINATION exception;
    pragma exception_init (XCP_MARKED_FOR_TERMINATION, -00031);
BEGIN
    -- add prefixes to identify Essbase sessions
    ess_jagentid := CONCAT('ess_jagentid_', jagent_id);
    ess_appname := CONCAT('ess_appname_', app_name);
    ess_dbname := CONCAT('ess_dbname_', db_name);
    ess_reqid := CONCAT('ess_reqid_', req_id);

    -- create hash out of CONCAT(ess_jagentid, ess_appname, ess_dbname)
    -- the hash identifies the federated partition
    SELECT TO_CHAR(ORA_HASH(CONCAT(CONCAT(ess_jagentid, ess_appname), ess_dbname))) INTO hash_str from dual;
    uname := SYS_CONTEXT('USERENV', 'SESSION_USER');

    IF jagent_id is not NULL AND app_name is not NULL AND db_name is not NULL AND req_id is not NULL THEN
        -- valid jagent_id, app_name, db_name, req_id
        -- kill sessions associated with the given federated cube and request id
        FOR r IN (select sid,serial# from v$session where username=uname AND module=hash_str AND action=ess_reqid)
        LOOP
            BEGIN
                sessionid := DBMS_ASSERT.ENQUOTE_LITERAL(r.sid || ',' || r.serial#);
                killcmd:='ALTER SYSTEM KILL SESSION ' || sessionid || ' IMMEDIATE';
                EXECUTE IMMEDIATE killcmd;
            EXCEPTION
                WHEN XCP_MARKED_FOR_TERMINATION THEN
                    -- ignore - the session could not be terminated immediately, but is marked for termination.
                    NULL;
            END;
        END LOOP;
    ELSIF jagent_id is not NULL AND app_name is not NULL AND db_name is not NULL AND req_id is NULL THEN
        -- null req_id
        -- kill all sessions for the given federated cube - invoked at app startup
        FOR r IN (select sid,serial# from v$session where username=uname AND module=hash_str)
        LOOP
            BEGIN
                sessionid := DBMS_ASSERT.ENQUOTE_LITERAL(r.sid || ',' || r.serial#);
                killcmd:='ALTER SYSTEM KILL SESSION ' || sessionid || ' IMMEDIATE';
                EXECUTE IMMEDIATE killcmd;
            EXCEPTION
                WHEN XCP_MARKED_FOR_TERMINATION THEN
                    -- ignore - the session could not be terminated immediately, but is marked for termination.
                    NULL;
            END;
        END LOOP;
    END IF;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE;
END ESS_SESSION_CANCEL;

Grant EXECUTE privileges for the stored procedures above to the DB User.

GRANT EXECUTE on ESS_SESSION_SET_MODULE TO "DB User";
GRANT EXECUTE on ESS_SESSION_CANCEL TO "DB User";
CREATE OR REPLACE SYNONYM "DB User".ESS_SESSION_SET_MODULE FOR ESS_SESSION_SET_MODULE;
CREATE OR REPLACE SYNONYM "DB User".ESS_SESSION_CANCEL FOR ESS_SESSION_CANCEL;

DB User: In your Autonomous AI Lakehouse schema, confirm that you have access to the following stored procedures under Synonyms:
- ESS_SESSION_SET_MODULE
- ESS_SESSION_CANCEL
With these prerequisites met, long running SQL requests on the federated cube can be cancelled the same way other Essbase requests can. Requests can be cancelled in the Essbase web interface from the Sessions area of the Console. If you are using MaxL, you can cancel requests using the statement alter system kill request. Review Model and Test Federated Partition Cubes for guidelines about session termination.

What to Do if the Database Connection Details Changed

If the Autonomous AI Lakehouse connection details that Essbase uses for a federated cube have changed, you will need to drop and re-create the federated partition, and clean up associated objects and metadata tables from the database schema.

This topic does not apply for federated cubes with Essbase managed data storage.

You will need to drop and re-create the federated partition if any of the following events occur after it was created:

The Autonomous AI Lakehouse port changes
The connection name changes
The connection uses a wallet, and you switch from one service name to another (to make performance or concurrency changes)
An outline update changes the member mapping to the fact table, causing the federated partition to become out of sync. See Metadata Precautions for Federated Cubes for details.

If you know in advance that the connection details will change, it is better to remove the federated partition before the change occurs, and create it again after. However, if the connection changed and caused the federated cube to become invalid, take the steps that follow.

Drop the Federated Partition

Remove the federated partition, and the connection associated with it (if otherwise unused), as described in Remove a Federated Partition.

Clean Up Federated Partition Related Objects and Metadata Tables

From the user schema in Autonomous AI Lakehouse, drop any Essbase-generated tables and other objects that failed to be removed with the federated partition.

ssh to the Essbase Server host as the opc user.
```
ssh -i MPOCI_KEY.pem opc@100.xxx.xxx.xxx
```
Change to oracle user (and go to their home directory).
```
sudo su - oracle
```
Navigate to the applications directory.
```
cd /u01/data/essbase/app
```
Using the Essbase application and cube name, identify the unique prefix associated with your federated partition objects and metadata.
1. Get the application name (AppName). The name is case sensitive, so capture the exact case. In this example, AppName = Sample.
```
ls
Sample
```
2. Navigate to the cube directory and get the cube name (DbName). The name is case sensitive, so capture the exact case. In this example, DbName = Basic.
```
cd /Sample
ls
Basic
```
3. Using SQL Developer or another tool, connect to Autonomous AI Database as the user of the schema to which the federated cube is (or was) connected.
4. In SQL Developer, run a SELECT statement to get the value of OTL_ID and OTL_STATUS.
  
  The SELECT statement format is:
```
SELECT OTL_ID, OTL_STATUS, TRC_HOST FROM ESSAV_OTL_MTD_VERSION where APPNAME ='<AppName>';
```
  Example
  
  Replace Sample with your AppName.
```
SELECT OTL_ID, OTL_STATUS, TRC_HOST FROM ESSAV_OTL_MTD_VERSION WHERE APPNAME='Sample';
```
  The above query should return the value of OTL_ID; for example,
```
987
```
  Note:
  If any rows are returned where OTL_STATUS is ACTIVE, you should attempt to drop the application from the TRC_HOST instance before you continue to the next step.
5. Build the Prefix as:
```
ESSAV_<OTL_ID>_
```
  Example:
```
<Prefix> = ESSAV_987_
```
Run a SELECT statement to create a list of objects associated with your federated cube. These are the objects you will clean up in the next step.

The SELECT statement format is:
```
SELECT * FROM user_OBJECTS WHERE OBJECT_NAME LIKE '<Prefix>%' ESCAPE '\';
```
Example:
```
SELECT * FROM user_OBJECTS WHERE OBJECT_NAME LIKE 'ESSAV_987_%' ESCAPE '\';
```

Run a stored PL/SQL procedure that cleans up all the analytic views, packages, hierarchies, tables, and other objects associated with the Prefix.

Example

Replace ESSAV_987_ with your Prefix.

SET SERVEROUTPUT ON;

declare
  prefix_str varchar2(70) := 'ESSAV_987_';

BEGIN

  FOR c IN ( SELECT ANALYTIC_VIEW_NAME FROM user_analytic_views WHERE ANALYTIC_VIEW_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP ANALYTIC VIEW "' || c.ANALYTIC_VIEW_NAME || '" ';
    DBMS_OUTPUT.PUT_LINE('ANALYTIC VIEW ' || c.ANALYTIC_VIEW_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct OBJECT_NAME FROM USER_PROCEDURES WHERE OBJECT_TYPE='PACKAGE' and OBJECT_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP PACKAGE "' || c.OBJECT_NAME || '" ';
    DBMS_OUTPUT.PUT_LINE('PACKAGE ' || c.OBJECT_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct HIER_NAME FROM USER_HIERARCHIES WHERE HIER_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP HIERARCHY "' || c.HIER_NAME || '" ';
    DBMS_OUTPUT.PUT_LINE('HIERARCHY ' || c.HIER_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct DIMENSION_NAME FROM USER_ATTRIBUTE_DIM_TABLES_AE WHERE DIMENSION_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP ATTRIBUTE DIMENSION "' || c.DIMENSION_NAME || '" ';
    DBMS_OUTPUT.PUT_LINE('ATTRIBUTE DIMENSION ' || c.DIMENSION_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct VIEW_NAME FROM USER_VIEWS WHERE VIEW_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP VIEW "' || c.VIEW_NAME || '" ';
    DBMS_OUTPUT.PUT_LINE('VIEW ' || c.VIEW_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE "' || c.TABLE_NAME || '" purge';
    DBMS_OUTPUT.PUT_LINE('TABLE ' || c.TABLE_NAME || ' dropped successfully.');
  END LOOP;

  FOR c IN ( SELECT distinct TYPE_NAME FROM USER_TYPES WHERE TYPE_NAME like prefix_str || '%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TYPE "' || c.TYPE_NAME || '" FORCE';
    DBMS_OUTPUT.PUT_LINE('TYPE ' || c.TYPE_NAME || ' dropped successfully.');
  END LOOP;

END;
/

Drop and update associated metadata-related tables.

Run a stored PL/SQL procedure to drop metadata-related tables associated with the OTL_ID.

Example

Replace 987 with your OTL_ID.

SET SERVEROUTPUT ON;
BEGIN
  FOR c IN ( SELECT distinct TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME like 'ESSAV_MTD_987_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE "' || c.TABLE_NAME || '" purge';
    DBMS_OUTPUT.PUT_LINE('TABLE ' || c.TABLE_NAME || ' dropped successfully.');
  END LOOP;
END;
/

Run an UPDATE statement to set the ESSAV_OTL_MTD_VERSION table to inactive status.

Example

Replace Sample with your AppName.
```
UPDATE "ESSAV_OTL_MTD_VERSION" SET  "OTL_STATUS" = 'INACTIVE'  where APPNAME ='Sample';
commit;
```

Note:

As only one Essbase instance should be using the Autonomous AI Database schema, you should not need to specify the ESSBASE_INSTANCE_UNIQUE_ID when you are performing the cleanup tasks. Refer to Restrictions for Federated Cubes.

Recreate the Connection and Federated Partition

Re-create the connection to Autonomous AI Lakehouse. This may be a global connection (under the main Sources icon in Essbase web interface), or it may be in the Sources defined just for the application. Follow the instructions in connection. Make sure to Test and Save the connection.
Re-create the federated partition, as described in Create a Federated Partition.
If you continue to see a connection error such as Essbase Error(1350012): Attempt to connect to OCI failed, check https://support.oracle.com/rs?type=doc&id=2925030.1.

Back up and Restore a Federated Cube

Federated partitions are not migrated with Essbase applications. When preparing to move your federated cube to another server or to migrate to another Essbase version, you need to delete the federated partition and recreate it in the new environment.

To back up your federated cube,

Back up the application, without the data, but including everything else you may need (such as configuration properties, filters, variables, calculation scripts, and other artifacts). To do this, use LcmExport (or the Export LCM job in the Essbase web interface).
Back up the fact table. See Backing Up and Restoring Autonomous Database.
Delete the federated partition definition from the cube, following the steps in Remove a Federated Partition.

To restore your federated cube from backup,

Re-create the application, using LcmImport (or the Import LCM job in the Essbase web interface).
If necessary, restore the fact table on Autonomous AI Lakehouse.
Re-create the connection to Autonomous AI Lakehouse. It is recommended to use a new connection name to avoid encountering errors.
Re-create the federated partition.