1. Using Oracle Essbase
  2. Integrate Essbase with Autonomous Database Using Federated Partitions
  3. Federated Partition Cube Maintenance and Troubleshooting

Federated Partition Cube Maintenance and Troubleshooting

Use the following guidelines to maintain or troubleshoot Essbase cubes with federated partitions.

This topic assumes you have created a federated partition and reviewed the information detailed in the preceding topics.

Model and Test Federated Partition Cubes

When designing a federated partition cube, follow these testing guidelines if the creation takes too long. These guidelines can be useful for taking a phased approach to troubleshoot or monitor performance.

  • Begin the federated partition project on a test environment.

  • Start with cube models that have the following characteristics:

    • not many levels

    • not many shared members or attributes

  1. When creating a federated partition, schedule offline operations when queries are not allowed against the instance.

  2. 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 Data Warehouse. If you disable commands in the application, remember to re-enable commands after creating thefederated partition.

  3. 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.
  4. Create the federated partition.
  5. Revert the timeout adjustments in step 3.
  6. Enable users back onto the system using alter application enable commands and/or connects, if these were disabled previously.
  7. For reports on an Essbase cube with a federated partition, 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 Data Warehouse.
  8. After development environment testing and tuning are completed, then use the above steps 1 through 7 to add the federated partition into 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 partition is ready for use. If it does not validate in SQL Developer, then the model needs to be fixed and timeout tuning is needed as described above in step 3.

Metadata Precautions for Federated Partition Cubes

When Essbase has a federated partition, take care when editing the cube outline. If you add or rename members, ensure that the metadata changes are also represented in the fact table in Autonomous Data Warehouse.

If the Essbase outline becomes out of sync with the fact table in Autonomous Data Warehouse, the federated partition 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 partition 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 federated partition 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, thefederated partition 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:

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

    From the federated partition user schema in Autonomous Data Warehouse, manually delete any Essbase-generated tables and other objects that failed to be removed with the federated partition.

  2. Ensure that the outline changes are completed in the Essbase cube.

  3. Create the fact table again. See Create the Fact Table.

  4. Re-create the connection to Autonomous Data Warehouse. 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 Partitions.

  5. Re-create the federated partition, as described in Create a Federated Partition.

What to Do if the Database Connection Details Changed

If the Autonomous Data Warehouse connection details that Essbase uses for a federated partition 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.

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

  • The Autonomous Data Warehouse 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 Partition 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 partition 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 federated partition user schema in Autonomous Data Warehouse, drop any Essbase-generated tables and other objects that failed to be removed with the federated partition.

  1. ssh to the Essbase Server host as the opc user. 

    ssh -i MPOCI_KEY.pem opc@100.xxx.xxx.xxx

  2. Change to oracle user (and go to their home directory). 

    sudo su - oracle

  3. Navigate to the applications directory.

    cd /u01/data/essbase/app

  4. 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. Count the number of characters (appx) in the application name.

      Example: appx = 6.

    3. 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

    4. Count the number of characters (dby) in the cube name.

      Example: dby = 5.

    5. Build the Prefix as: 

      ESSAV_<appx><AppName>_<dby><DbName>_

      Example:

      <Prefix> = ESSAV_6Sample_5Basic_

  5. Using SQL Developer or another tool, connect to Oracle Database as the user of the schema to which the Federated Partition is connected.

  6. Run a SELECT statement to create a list of objects associated with your federated partition application. 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>%';

    Example:

    SELECT * FROM user_OBJECTS WHERE OBJECT_NAME like 'ESSAV_6Sample_5Basic_%';

  7. 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_6Sample_5Basic_ with your Prefix. 

    SET SERVEROUTPUT ON;

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

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 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 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 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;
/

  8. Drop and update associated metadata-related tables. First, you need to get values for ESSBASE_INSTANCE_UNIQUE_ID and OTL_ID.

    1. ssh to the Essbase Server host as the opc user. 

      ssh -i MPOCI_KEY.pem opc@100.xxx.xxx.xxx

    2. Change to oracle user (and go to their home directory). 

      sudo su - oracle

    3. Search for the Essbase Agent process. 

      ps -ef | grep ESSS | grep -v "grep"

      The above command should return a process listing that begins with oracle followed by two process IDs; for example, 

      oracle   10769  19563 ...

      Consider the first process ID to be <PID>, which you will use in the next step.

    4. Use the strings command to capture the value of ESSBASE_INSTANCE_UNIQUE_ID. 

      strings /proc/<PID>/environ | grep ESSBASE_INSTANCE_UNIQUE_ID

      Example:

      strings /proc/10769/environ | grep ESSBASE_INSTANCE_UNIQUE_ID

      The above command should return the value of ESSBASE_INSTANCE_UNIQUE_ID; for example, 

      ESSBASE_INSTANCE_UNIQUE_ID=EWRnHFlQteCEzWUhF7P3TPKunf3bYs

    5. Using SQL Developer or another tool, connect to Oracle Database as the user of the schema to which the Federated Partition is connected.

    6. Run a SELECT statement to get the value of OTL_ID.

      The SELECT statement format is:

      SELECT OTL_ID FROM ESSAV_OTL_MTD_VERSION where APPNAME ='<AppName>' and "JAGENT_INSTANCE_ID"='<ESSBASE_INSTANCE_UNIQUE_ID>';

      Example

      Replace ESSAV_6Sample_5Basic with your AppName, and replace 'EWRnHFlQteCEzWUhF7P3TPKunf3bYs' with your ESSBASE_INSTANCE_UNIQUE_ID. 

      SELECT OTL_ID FROM ESSAV_OTL_MTD_VERSION where APPNAME ='ESSAV_6Sample_5Basic' and "JAGENT_INSTANCE_ID"='EWRnHFlQteCEzWUhF7P3TPKunf3bYs';

    7. The above query should return the value of OTL_ID; for example, 

      62

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

      Example

      Replace 62 with your OTL_ID. 

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

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

      Example

      Replace ESSAV_6Sample_5Basic with your AppName, and replace EWRnHFlQteCEzWUhF7P3TPKunf3bYs with your ESSBASE_INSTANCE_UNIQUE_ID. 

      UPDATE "ESSAV_OTL_MTD_VERSION" SET  "OTL_STATUS" = 'INACTIVE'  where APPNAME ='ESSAV_6Sample_5Basic' and "JAGENT_INSTANCE_ID"='EWRnHFlQteCEzWUhF7P3TPKunf3bYs';
commit;

Recreate the Connection and Federated Partition

  1. Re-create the connection to Autonomous Data Warehouse. 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.

  2. Re-create the federated partition, as described in Create a Federated Partition.

  3. 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 Partition Application

Federated partitions are not migrated with Essbase applications. When preparing to move your application and 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 partition cube,

  1. 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 LCM export (or the Export LCM job in the Essbase web interface).

  2. Back up the fact table. See Backing Up and Restoring Autonomous Database.

  3. Delete the federated partition definition from the cube, following the steps in Remove a Federated Partition.

To restore your federated partition cube from backup,

  1. Re-create the application, using LcmImport: Restore Cube Files (or the Import LCM job in the Essbase web interface).

  2. If necessary, restore the fact table on Autonomous Data Warehouse.

  3. Re-create the connection to Autonomous Data Warehouse. It is recommended to use a new connection name to avoid encountering errors.

  4. Re-create the federated partition.