19 Integrate Essbase with Autonomous Database Using Federated Partitions

Federated partitions enable you to integrate Essbase cubes with Autonomous Data Warehouse, to combine Essbase's analytical power with the benefits of Autonomous Database.

Essbase integration with Autonomous Data Warehouse via federated partitions is enabled out-of-the-box when you use the Marketplace listing to deploy Essbase as a stack on your Oracle Cloud Infrastructure (OCI) tenancy.

With federated partitions, you bypass the process of loading data into an Essbase cube before performing aggregations and queries. You can perform data processing directly within Autonomous Database, to take advantage of its benefits and also of Essbase's analytical engine.

Bypassing regular data loads to Essbase saves you operational costs surrounding the extract, transform, load (ETL) pipeline.

Writeback is supported through Essbase to stored intersections. The data values you submit using Smart View (or MDX Insert) are updated in the fact table.

Topics:

Prerequisites for Federated Partitions

The following requirements must be met for Essbase cubes to have federated partition to Autonomous Data Warehouse on Shared Infrastructure:

  • You have access to Autonomous Data Warehouse on Shared Infrastructure.

  • You have an Essbase stack, deployed using the Marketplace listing, to a tenancy on Oracle Cloud Infrastructure.

  • During deployment, for relational database configuration, you selected the Use existing database option to deploy to your instance of Autonomous Data Warehouse. In other words, this instance of Autonomous Data Warehouse is your repository database for Essbase.

  • Set up the Oracle user who controls the schema in Autonomous Data Warehouse with privileges as follows: GRANT RESOURCE, CONNECT, CREATE TABLE, DWROLE to <USER>;

  • You have created a fact table in the Autonomous Data Warehouse database schema.

  • You have created an Essbase cube outline. For this requirement, you can import a cube from an application workbook, without loading any data.

  • A connection has been defined enabling Essbase to access the schema on Autonomous Data Warehouse.

  • Hybrid mode is enabled for the Essbase cube (this is the default).

If you still need to meet any of these requirements, proceed to Federated Partitions Workflow.

Federated Partitions Workflow

Federated partitions are a type of partition that enables Essbase to directly query Autonomous Data Warehouse on Shared Infrastructure, removing the need to load data into the Essbase cube.

The workflow to use federated partitions is:

  1. Acquire access to an instance of Autonomous Data Warehouse on Shared Infrastructure.

    Note:

    Elsewhere in this federated partitions documentation, all references to Autonomous Data Warehouse should be considered to mean Autonomous Data Warehouse on Shared Infrastructure.
  2. Deploy the Essbase stack to a tenancy on Oracle Cloud Infrastructure, using the Marketplace listing.

    During deployment, for the relational database configuration, select Use existing database option to deploy to your instance of Autonomous Data Warehouse. This instance of Autonomous Data Warehouse will then become the repository database for your Essbase instance.

  3. On your Autonomous Data Warehouse database, set up a schema controlled by a database user who is granted at least the privileges shown in the following statement: GRANT RESOURCE, CONNECT, CREATE TABLE, DWROLE to <USER>;

  4. Create a fact table in the schema. The fact table stores the numeric data values that will be referenced by your Essbase cube when you integrate Essbase with Autonomous Data Warehouse by creating a federated partition.

    For guidelines on fact tables (and pivot dimensions), see Fact Tables, Pivot Dimensions, and Essbase.

  5. Create an Essbase application and cube. For this requirement, you can import a cube from an application workbook. You do not need to load any data. Hybrid query mode must be enabled for the cube (this is already the default for newly created cubes).

  6. Define a connection that enables Essbase to access the schema on Autonomous Data Warehouse, as shown in Create a Connection for Federated Partitions.

    To create a global connection, you need to have the service administrator role. To create an application level connection, you need to have user role, plus application manager permission on the application.

  7. Log in to the Essbase web interface and create the federated partition, as described in Create a Federated Partition.

  8. Learn about maintaining and troubleshooting the federated partition. See Federated Cube Maintenance and Troubleshooting.

This release has partial/limited functionality, with restrictions noted.

Guidelines for Creating and Using Federated Partitions

When creating and using federated partitions, follow these guidelines:

  • Begin the federated partition project on a development environment.
  • Start with models that have the following characteristics:
    • They should have a small number of levels.
    • They should not have many shared members or attributes.
  1. When creating a federated partition, schedule offline operations when queries are not allowed against the instance.
  2. Gracefully bring users off the system. See Alter Application (especially enable/disable) and Alter System (especially logoff/ kill). If you use disable commands, you must reverse them with enable commands after creating the federated 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:
      • Check the value of olap.server.netSocketTimeOut in the Essbase web interface. On the Console page, 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=XXX/5 = 1800. In the Essbase web interface, on the Console page, select Configuration, and set olap.server.netRetryCount to 9000.
  4. Create the federated partition.
  5. Revert the timeout adjustments in step 3.
  6. Gracefully bring users back onto the system, see Alter Application (especially enable/disable) and Alter System (especially logoff/ kill). If you use disable commands, you must reverse them with enable commands after creating the federated partition.
  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.
  8. After development environment testing and tuning is 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 Developers then the federation 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.

Fact Tables, Pivot Dimensions, and Essbase

When you federate an Essbase cube to Autonomous Data Warehouse on Shared Infrastructure, the data is not stored in Essbase, but rather, in Autonomous Data Warehouse.

The fact table in Autonomous Data Warehouse stores the numeric data values of the Essbase cube. If you don't have a fact table in Autonomous Data Warehouse, you must create one.

About the Fact Table

Before building (or selecting) a fact table to use for the federated partition, think about which columns in your Autonomous Data Warehouse database hold the stored, numeric data values needed for your Essbase application.

If you already have a fact table in Autonomous Data Warehouse, note which columns contain numeric data values. These columns will be from the dimension in Essbase that you will designate as the pivot dimension when you create a federated partition in Essbase.

Similar to a column export from an Essbase cube, the fact table includes

  • one column for each (non-attribute) dimension of the outline

  • one column for each stored member of the pivot dimension

The following is an example of a fact table in which the measures dimension is the pivot dimension. The numeric data is in columns SALES, COGS, MARKETING, PAYROLL, MISC, INTITIAL_INVENTORY, and ADDITIONS.


Fact table with denormalized data and metadata

Additional guidelines for selecting a fact table include:

  • The fact table must have fewer than 1000 columns.

  • Do not include columns that will map in Essbase to attribute dimensions, or base dimensions with associated attributes.

Part of the fact table can be considered the pivot dimension. A pivot dimension is a dimension you designate from the Essbase cube outline. All stored members of the pivot dimension must map to the fact table columns that represent your numeric data values in Autonomous Data Warehouse.

  • The pivot dimension does not have to be measures/accounts, but it may be.

  • The pivot dimension should have fairly static member names. Reason: Changing the pivot dimension in the Essbase cube outline (for example, by adding or renaming stored members) necessitates corresponding, manual updates to the fact table in Autonomous Data Warehouse.

  • You provide your selected pivot dimension at the time of creating a federated partition.

  • Essbase dimensions that include members requiring complex, dynamic formulas (such as "Opening Inventory" and "Ending Inventory," using Sample Basic as an example) should not be selected as the pivot dimension.

Fact Table Creation

If you need to create a fact table in Autonomous Data Warehouse, use SQL. Include in the fact table:

  • a column for each non-attribute dimension (except for the pivot dimension)

  • a column for each stored member of the pivot dimension

For example,

CREATE TABLE "ADMIN"."SAMP_FACT" 
   (	"DIMENSION_PRODUCT" NVARCHAR2(1024), 
	"DIMENSION_MARKET" NVARCHAR2(1024), 
	"DIMENSION_YEAR" NVARCHAR2(1024), 
	"DIMENSION_SCENARIO" NVARCHAR2(1024), 
	"SALES" NUMBER(38,0), 
	"COGS" NUMBER(38,0), 
	"MARKETING" NUMBER(38,0), 
	"PAYROLL" NUMBER(38,0), 
	"MISC" NUMBER(38,0), 
	"INITIAL_INVENTORY" NUMBER(38,0), 
	"ADDITIONS" NUMBER(38,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ADMIN" PARALLEL 4;

Notes

  • In the example above, the schema user is ADMIN and the fact table name is SAMP_FACT.

  • For best performance, all non-numeric columns in the fact table should be of type NVARCHAR2(1024), and all numeric columns should be of type NUMBER.

  • Oracle recommends enabling parallel creation of the index in Autonomous Data Warehouse, by adding PARALLEL 4.

  • When you create a fact table, do not create any null rows.

  • If you get the following validation error when creating the fact table, delete null rows.

    ORA-18265: fact table key column ("<DIM_NAME>") with value ('') not in
          dimension("<Name_of_Column") star table key column

Create a Connection for Federated Partitions

Define a connection between Essbase and Autonomous Data Warehouse on Shared Infrastructure, to work with federated partitions.

Federated partitions are supported only for Essbase deployment on Oracle Cloud Infrastructure.

Before you start defining the required connection, review these guidelines:

To create the required connection for federated partitions,

  1. In Essbase, on the Sources page, click Connections.
    To define the connection and Datasource at application level, instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources.
  2. Click Create Connection and select Oracle Database.
  3. Select Autonomous using the toggle switch.

    Image of the Create Connection dialog box, showing how to create a connection from Essbase to Oracle Autonomous Data Warehouse. Repository Database is checked, and the following path to the system wallet file in the repository database is displayed in the Wallet File field: /system/wallets/EssbaseADWS.

  4. Enter a connection name.
  5. Select a service name.
  6. Select the Repository Database option. This option is required to be selected for federated partition connections.

    Repository Database option selected

    You do not need to upload a wallet, because Essbase will use the wallet associated with the repository database. The path to the system wallet file in the repository database is displayed in the Wallet File field.

    If you are lacking a wallet in the repository database, do not upload it from the Create Connection page. Obtain a wallet by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure.

  7. Enter your Autonomous Data Warehouse username, password, and optionally, a description.
  8. Click Test to validate the connection, and if successful, click Create.

    If you get connection errors, you may need to expand Advanced Options to adjust the minimum and maximum connection pool sizes.


    Advanced Options with Min Pool Size 5, Max Pool Size 50

    See About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

  9. Verify that the connection was created successfully and appears in the list of connections.

    If you no longer need the federated partition, remove it, to ensure that associated tables are cleaned up in Autonomous Data Warehouse.

Create a Federated Partition

This topic shows you how to create a federated partition between Essbase and Autonomous Data Warehouse on Shared Infrastructure.

This topic assumes you have completed the prerequisites and reviewed the information detailed in the preceding topics.

  1. In the Essbase web interface, on the Applications page, expand the target application. In the row for the target cube, click the Actions menu, and click Inspect.

  2. Select the Partitions tab.

  3. Click Create >Federated.

  4. For Connection name, enter the connection to Autonomous Data Warehouse that was previously created by a service administrator, as shown in Create a Connection for Federated Partitions.

  5. For Schema name, enter the name of the database schema (user name that you entered when you created the connection).

  6. For Fact table name, enter the name of the fact table in Autonomous Data Warehouse on Shared Infrastructure that stores numeric values and keys.

  7. For Pivot dimension, enter the name of the pivot dimension you decided to use from the Essbase outline.

    If the column names in the fact table are the same as the dimensions and pivot member names in the outline, then the mapping is automatically populated in Essbase to column map. Any dimensions or members that cannot be mapped to a column in the fact table, you will need to map manually.

    Use the Update button if you need to manually map level 0 pivot dimension members to fact table columns.

    If a member of the pivot dimension (or a non-measures dimension name) includes a space, Oracle recommends replacing the space with an underscore character (_). Essbase automatically maps these member names to corresponding column names in the fact table with the space replaced by an underscore. For example, Essbase automatically makes the following mapping:

    Pivot dimension member name Fact table column name
    "Initial Inventory" "Initial_Inventory"
  8. Click Non measures to map the non numeric dimensions to columns in the fact table. They may map automatically if the fact table column names match the dimension names in the outline. Otherwise, map them manually.

  9. In the Areas tab, the partition target area is automatically defined based on the mapping. You do not need to edit the target area.

  10. Click Validate.

  11. If the validation succeeded, click Save and Close, and confirm that it is OK to restart the application.

    Saving or validating your federated partition may take a while to complete. Check the job status to see if partition creation is complete.

    The partition is created. This process also creates dimension helper tables in Autonomous Data Warehouse on Shared Infrastructure, which are linked (by keys) to the fact table.

Example


Federated partition definition with pivot member mapping shown in Essbase web interface


Federated partition definition with dimension column mapping shown in Essbase web interface

Federated Cube Maintenance and Troubleshooting

Use the following guidelines to maintain or troubleshoot an Essbase cube after it has been federated to Autonomous Data Warehouse.

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

  • When you use a federated partition, Autonomous Data Warehouse partially processes calculations and aggregations. Therefore, query results may have slightly different values if compared to the values obtained without the usage of a federated partition.

  • If you add a dimension, ensure the fact table has a column with a name identical to the name of the added dimension. Otherwise, you may encounter an error beginning with Essbase Error(1040235): Remote warning from federated partition when you restructure the outline, submit data, or perform queries.

  • If you add a stored member to the pivot dimension, ensure the fact table has a column with a name identical to the added member. Otherwise, you may encounter the error type listed above.

  • Federated partitions are not migrated, so when moving your application to another server or version, you need to delete the federated partition and recreate it in the new environment. See Remove a Federated Partition.

    To back up your federated cube,

    1. Back up the cube outline. To do this, you can use LCM export without data, or export to an application workbook without data.

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

  • To restore your federated cube from backup,

    1. Re-create the cube outline, using LcmImport: Restore Cube Files or an application workbook.

    2. Re-create the connection to Autonomous Data Warehouse.

    3. Re-create the federated partition.

  • If you change the outline of a federated cube, validate the partition to re-sync it.

  • Saving or validating your federated partition may take a while to complete. You can monitor the progress in Jobs.

  • If you no longer need the federated partition, remove it, to ensure that associated tables are cleaned up in Autonomous Data Warehouse.

Remove a Federated Partition

Federated partitions are not migrated, so when moving your application to another server or version, you need to delete the federated partition and recreate it in the new environment.

When you need to remove a federated partition between Essbase and Autonomous Data Warehouse on Shared Infrastructure, take the following actions to ensure that associated tables are cleaned up in Autonomous Data Warehouse.

  1. Delete the federated partition from the application. This action removes all of the Essbase tables from Autonomous Data Warehouse (but does not remove the fact table).

    1. Log in to the Essbase web interface as a database manager or higher.

    2. On the Applications page, expand the target application. In the row for the target cube, click the Actions menu, and click Inspect.

    3. Select the Partitions tab.

    4. Click the Actions menu to the right of the partition definition, and click Delete.

    5. Click Yes to confirm you want to delete the partition and allow the application to restart.

  2. Remove the connection that was created for the federated partition.

Restrictions for Federated Partitions

Some functionality is not supported for Essbase cubes with a federated partition.

  • Running calculation scripts against federated cubes (neither for block storage/hybrid, nor for aggregate storage aggregations)

  • Data load through Essbase to a federated cube (you can use Data Tools in Autonomous Database to load data to the fact table and perform other management tasks)

  • Exporting data from a federated cube

  • Exporting a federated cube to an application workbook (does not export the data nor the partition definition)

  • Lifecycle Management (LCM) import operations (and Migration Utility import) are not supported for migration of federated partitions. Federated partitions must be recreated manually on the target.

  • Scenario management

  • Transparent or replicated partitions against the federated cube

  • MaxL does not support creating or altering federated partitions (but you can use REST API)

  • MaxL statements and APIs for clearing/resetting data, clearing data regions, or clearing aggregates

  • Text lists (a.k.a smartlists)

  • Request termination

  • Varying attributes, and any default attribute calculation other than Sum

  • MDX Sub Select

  • Building aggregate views (MaxL statements execute aggregate process|build|selection)

  • Merging data regions/slices

  • Information returned from the MaxL statement query application APP-NAME list aggregate_storage storage_info (or equivalent API) is not complete/accurate

  • Currency cubes

  • Data audit trail

  • Triggers on cube events

  • Asymmetric queries have slower performance in this release.

  • Copying or renaming federated applications and cubes is not supported.

  • The following Essbase application or server configuration settings are ignored:

    • AUTOMERGE
    • AUTOMERGEMAXSLICENUMBER
    • DATACACHESIZE
    • CALCCACHE
    • CALCCACHEDEFAULT
    • CALCCACHEHIGH
    • CALCCACHELOW
    • CALCLOCKBLOCK
    • CALCMODE
    • CALCNOTICE
    • CALCOPTFRMLBOTTOMUP
    • CALCREUSEDYNCALCBLOCKS
    • CALCPARALLEL
    • CALCTASKDIMS
    • DATACACHESIZE
    • DYNCALCCACHEBLKRELEASE
    • DYNCALCCACHEBLKTIMEOUT
    • DYNCALCCACHECOMPRBLKBUFSIZE
    • DYNCALCCACHEMAXSIZE
    • DYNCALCCACHEONLY
    • DYNCALCCACHEWAITFORBLK
    • ENABLE_DIAG_TRANSPARENT_PARTITION
    • EXPORTTHREADS
    • FORCEGRIDEXPANSION
    • GRIDEXPANSION
    • GRIDEXPANSIONMESSAGES
    • INDEXCACHESIZE
    • INPLACEDATAWRITE
    • PARCALCMULTIPLEBITMAPMEMOPT
    • SSAUDIT
    • SSAUDITR
    • SSLOGUNKNOWN
    • SUPNA
    • TARGETASOOPT
    • TARGETTIMESERIESOPT