Calculate and Query Federated Cubes

When you have a federated partition, Essbase calculations and queries, whenever possible, are converted by Essbase into SQL and pushed to Autonomous Data Warehouse, so that processing occurs where the data is stored.

The Essbase cube outline contains the metadata (dimension and member names). Autonomous Data Warehouse holds the data associated with the metadata. Data is stored in a fact table.

Because Essbase pushes calculation processing to where the data is stored, it helps solve data latency problems. This functionality is new with Essbase 21.5.

If you are new to Essbase, start with reviewing Calculate Cubes to learn about calculation in general.

How calculation works with federated partitions depends on the type of Essbase cube you started with to create the federated partition: block storage (BSO), or aggregate storage (ASO).

Calculation Comparison Notes

For BSO-based federated partition cubes, when you calculate and query, Essbase analyzes dependencies and writes SQL to process the results using Analytic Views in Autonomous Data Warehouse.

For ASO-based federated partition cubes, Essbase Server processes custom calculations and allocations, and then pushes the results to Autonomous Data Warehouse.

Notes / Limitations on Federated Partition Calculations

Essbase block storage (BSO) calculation functions may be applied in BSO outline formulas, with their results impacting queries from Smart View, MDX, and other grid clients. The same functions, along with the calculation commands, can be used to write procedural calculation scripts.

Calculation Functions that Process in Autonomous Data Warehouse

When federated partitions are used, the following Essbase calculation functions are translated to SQL and processed in Autonomous Data Warehouse. Other functions not listed here are processed in Essbase.

@ABS
@ALLANCESTORS
@ANCEST
@ANCESTORS
@AVG (with SKIPMISSING option only)
@AVGRANGE
@CHILDREN
@CURRMBR
@DESCENDANTS
@EXP
@FACTORIAL
@GENMBRS
@IALLANCESTORS
@IANCESTORS
@ICHILDREN
@IDESCENDANTS
@INT
@IRDESCENDANTS
@ISANCEST
@ISCHILD
@ISDESC
@ISGEN
@ISIANCEST
@ISIBLINGS
@ISICHILD
@ISIDESC
@ISIPARENT
@ISISIBLING
@ISLEV
@ISMBR (when argument is only one member name)
@ISPARENT
@ISSAMEGEN
@ISSAMELEV
@ISSIBLING
@LEVMBRS
@LN
@LOG
@LOG10
@LSIBLINGS
@MAX
@MAXRANGE (exception: no XrangeList argument)
@MAXS
@MAXSRANGE (exception: no XrangeList argument)
@MBRPARENT
@MEDIAN (exception: no XrangeList argument)
@MEMBERAT
@MIN
@MINRANGE (exception: no XrangeList argument)
@MINS
@MINSRANGE (exception: no XrangeList argument)
@MOD
@PARENT
@POWER
@RDESCENDANTS
@RELATIVE
@REMAINDER
@ROUND
@RSIBLINGS
@SIBLINGS
@SUM
@SUMRANGE (exception: no XrangeList argument)
@TRUNCATE

Calculation Commands that Process in Autonomous Data Warehouse

When federated partitions are used, the following Essbase calculation commands are translated to SQL and processed in Autonomous Data Warehouse.

AGG (except when aggregating Dynamic Calc members or members using non additive consolidation operator)
CLEARDATA
CLEARBLOCK (exception: no NONINPUT nor DYNAMIC keywords)

DATAEXPORT (exception: only with the following data export options)

DATAEXPORTLEVEL ALL
DATAEXPORTCSVFORMAT
DATAEXPORTOVERWRITEFILE
DATAEXPORTDECIMAL

IF...ENDIF
ELSE...ELSEIF (expressions with multiple, nested IF / ELSE statements may have slower performance)
EXCLUDE...ENDEXCLUDE
LOOP...ENDLOOP
DATACOPY
FIX statement assignments with expressions containing mathematical operations, IF / ELSE statements, cross references, and supported @ functions listed on this page.

Commands ARRAY and VAR, as well as dynamic formulas processed in CALC DIM or CALC ALL, are processed in Essbase and may have slower performance.

Some calculation commands are not supported for federated partition cubes, and return an error if used. See Restrictions for Federated Partitions.

If you need to run Essbase block storage (BSO) calculation scripts, select a dense dimension as the pivot dimension. Calculation scripts are not supported for federated partitions if the pivot dimension is sparse.

Block calculation mode (enabled when Essbase configuration setting CALCMODE is set to BLOCK) is not applicable for federated partition cubes. Calculation processing is pushed to Autonomous Data Warehouse. If an exception exists and the calculation is processed on the Essbase Server instead, then solve order determines the dependency analysis.

When performing custom allocations on an aggregate storage cube with a federated partition, you can only override existing values. You cannot add to, nor subtract from, existing values.

Other Limitations

See Restrictions for Federated Partitions.

Precision Digits in Query Results

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

Calculation Order

Similarly to hybrid BSO and ASO cubes, the calculation priority of members in federated partition cubes follow a defined solve order that you set on the Essbase outline.

Ability to Run Essbase Calculations and Data Load Jobs

The Essbase configuration setting FEDERATEDAVCALC is implicitly set to TRUE by default for any block storage (BSO) applications that have federated partitions. This enables users to run Essbase BSO calculations and to perform data loads through Essbase to update records in the Autonomous Data Warehouse fact table.