Integrate Essbase with Autonomous Database Using Federated Partitions

18 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 partition means that the data for your cube is stored in Autonomous Data Warehouse.

In order to implement this feature, Essbase and Oracle Autonomous Database Serverless (with Autonomous Data Warehouse workload type) need to be deployed together in a shared Oracle Cloud Infrastructure tenancy, with Autonomous Data Warehouse serving as the repository database that holds RCU schemas for the Essbase stack deployed on OCI from Marketplace.

Essbase federated partition cubes have some key functional differences from non-federated block storage (BSO) and aggregate storage (ASO) cubes.

Compare the differences between ASO and BSO cubes to help you decide whether federated partition is the right choice.

Table 18-1 Differences between Aggregate Storage, Block Storage, and Federated Cubes

Aggregate Storage (ASO) Block Storage (BSO) Federated Partition Cube

	Aggregate Storage (ASO)	Block Storage (BSO)	Federated Partition Cube
Data storage model	Data is stored in Essbase.	Data is stored in Essbase.	Data is stored in a relational table in Autonomous Data Warehouse. Elsewhere in the documentation, it is referred to as the fact table.
How it works	Number of dimensions can be very high, containing millions of members, but the cube has relatively sparse data slices (many dimensional intersections contain no data). Data is input at level 0 only. Cubes are optimized for rapid aggregation.	The number and scale of dimensions are typically smaller as compared with ASO. BSO accomodates dense data sets. Some of the dimensions are defined as dense, with data at most intersections, and others are defined as sparse. This helps Essbase store data efficiently and optimize dependency analysis (so as not to overcalculate). Data can be input at any level.	The Essbase outline is mapped to the fact table, allowing data storage to remain in Autonomous Data Warehouse, while being accessible for analysis using the logic you build into your Essbase application. The analytical capabilities of your Essbase outline enable you to analyze the flat relational table as hierarchies, employing whatever complex procedural math you may need for your multidimensional analysis. Calculations and aggregations, when possible, are converted by Essbase into SQL and pushed to Autonomous Data Warehouse, so that processing occurs closer to where data is stored. You can find the SQL Essbase writes in the platform log, located in `<DOMAIN_HOME>/servers/essbase_server1/logs/essbase`.
Typical use cases	ASO cubes are commonly used for highly aggregational analytics, custom calculations, and allocations. Data loads can be broken into slices for frequent, highly parallelized updates.	BSO cubes are commonly used for financial and operational planning, and interactive reporting on aggregate data relative to the source. BSO cubes are designed for complex analytical requirements requiring formulas/ math, and frequent procedural calculations.	Data does not leave Autonomous Data Warehouse, eliminating the need for refreshing and restructuring in Essbase. Since you create the federated partition over an existing ASO or BSO cube, you can use either of those Essbase options and benefit from its style of calculations and queries, without ever having to load the data into Essbase or restructure the outline. If your organization already has a fact table stored in Autonomous Data Warehouse, federated partition enables you to use Essbase functionality such as: Querying tables using Smart View in Excel Powerful calculation and query capabilities including financial and time intelligence "What-if" modeling and forecasting Writeback capabilities If your organization already uses Essbase, federated partition enables you to access these benefits of storing data in Autonomous Data Warehouse: Elimination of data latency caused by Essbase data load processes Ability to handle larger volumes of data than possible in Essbase Other operational benefits of Autonomous Data Warehouse, including auto-scaling and automated backups

Data storage model

Data is stored in Essbase.

Data is stored in a relational table in Autonomous Data Warehouse.

Elsewhere in the documentation, it is referred to as the fact table.

How it works

Number of dimensions can be very high, containing millions of members, but the cube has relatively sparse data slices (many dimensional intersections contain no data).

Data is input at level 0 only. Cubes are optimized for rapid aggregation.

The number and scale of dimensions are typically smaller as compared with ASO.

BSO accomodates dense data sets. Some of the dimensions are defined as dense, with data at most intersections, and others are defined as sparse. This helps Essbase store data efficiently and optimize dependency analysis (so as not to overcalculate).

Data can be input at any level.

The Essbase outline is mapped to the fact table, allowing data storage to remain in Autonomous Data Warehouse, while being accessible for analysis using the logic you build into your Essbase application.

The analytical capabilities of your Essbase outline enable you to analyze the flat relational table as hierarchies, employing whatever complex procedural math you may need for your multidimensional analysis.

Calculations and aggregations, when possible, are converted by Essbase into SQL and pushed to Autonomous Data Warehouse, so that processing occurs closer to where data is stored.

You can find the SQL Essbase writes in the platform log, located in <DOMAIN_HOME>/servers/essbase_server1/logs/essbase.

Typical use cases

ASO cubes are commonly used for highly aggregational analytics, custom calculations, and allocations.

Data loads can be broken into slices for frequent, highly parallelized updates.

BSO cubes are commonly used for financial and operational planning, and interactive reporting on aggregate data relative to the source.

BSO cubes are designed for complex analytical requirements requiring formulas/ math, and frequent procedural calculations.

Data does not leave Autonomous Data Warehouse, eliminating the need for refreshing and restructuring in Essbase. Since you create the federated partition over an existing ASO or BSO cube, you can use either of those Essbase options and benefit from its style of calculations and queries, without ever having to load the data into Essbase or restructure the outline.

If your organization already has a fact table stored in Autonomous Data Warehouse, federated partition enables you to use Essbase functionality such as:

Querying tables using Smart View in Excel
Powerful calculation and query capabilities including financial and time intelligence
"What-if" modeling and forecasting
Writeback capabilities

If your organization already uses Essbase, federated partition enables you to access these benefits of storing data in Autonomous Data Warehouse:

Elimination of data latency caused by Essbase data load processes
Ability to handle larger volumes of data than possible in Essbase
Other operational benefits of Autonomous Data Warehouse, including auto-scaling and automated backups

With federated partitions, you can bypass the process of loading data into an Essbase cube before performing aggregations and queries. Data processing occurs within Autonomous Data Warehouse, to take advantage of the benefits of Autonomous Database and also of Essbase's analytical features.

Bypassing regular data loads from relational data sources to Essbase can save you operational costs surrounding the extract, transform, load (ETL) pipeline (using rule files or other data load processes), and eliminates the need for outline restructuring.

With Autonomous Database, the database configuration, tuning, object storage, backups, and updates are all Oracle managed, so you can use Essbase in a federated cloud environment without spending time on infrastructure management.

Writeback is supported through Essbase to stored intersections. For example, the data values you submit using Smart View (or MDX Insert) are updated in the fact table on Autonomous Data Warehouse.

You can also perform Essbase calculations and data loads, and Essbase will write SQL to update the fact table Autonomous Data Warehouse.