Optimize BSO Cubes

Optimizing BSO cubes reduces the size of the database; it involves the following processes:

Run an explicit restructure.
Run the Essbase Block Analysis report to identify repeating values and zeros in the BSO cube.
Replace zero blocks with #missing blocks.
Use Never as the consolidation operator for members with data type of SmartList, Date, Text, and Percentage.

Perform an Explicit Cube Restructure

In Planning, you can run an explicit cube restructure manually or through a job. You can also restructure cubes using the restructureCube EPM Automate command or the Restructure Cube REST API.

Note:

This is not a database refresh, but an explicit dense restructure of the cube.
An explicit restructure removes #missing blocks to reduce the BSO database size.

To restructure the cube using Calculation Manager:

In the Navigator, select Rules under Create and Manage to open Calculation Manager.
Select Actions and then Database Properties.
In Enterprise View, expand the database containing the cube that you want to restructure.
Right-click the cube that you want to restructure and then select Restructure Database.

Analyzing Data in a Cube

Start by generating the Essbase Block Analysis report. See these information sources:

essbaseBlockAnalysisReport in Working with EPM Automate
Essbase Block Analysis Report in the REST APIs for Oracle Fusion Cloud EPM guide

Note:

The cube is placed in read only mode when you export the cube data to the export file that is used as the source for generating the Essbase Block Analysis report. Depending on the size of the cube, the export could take some time. Therefore, it is recommended that you clone your instance to a test instance in order to carry out this analysis.

The Essbase Block Analysis report provides information on these three areas:

Percentage of blocks with only Zero, which shows the blocks that contain only zeros as a percentage of all the blocks contained in the export file.
Percentage of cells with only Near Zero Values, which displays the percentage of cells with near-zero values (such as 0.000000001604) in blocks
Top 10 Repeated Numerical Cell Values By Percentage of Numerical Cells, which shows the top 10 repeated values as a percentage of all the values in the export file.
Top 100 Dense Member Combinations with Repeated Values, which shows the top 100 dense combinations with repeated values in the cube. The Cell Value column shows a value for each member, in the order it appears in the hierarchy, as a different column. For example, if Period is across the column, there will be a different column for January, February, and so on. Other dense dimension(s) appear in the rows. This should help you identify the locations of the repeated values.

Converting Near-Zeros Values in a BSO Cube to Zero

To remove near zero or very small values in the sparse dimensions in the BSO cube, use the @round or the @truncate function, which turns them into zeros. These zero values can then be removed, replacing them with #missing blocks. See Removing Zeros in a BSO Cube.

To convert near-zero values to zero:

Create a business rule to round or truncate near zero or very small values to zero.
Configure the business rule environment to optimize this rule. This rule is currently set to run at level 0. After near zero values are converted to zero and cleared from the cube (see next section), those values will also be cleared from upper-level members during the next aggregation if SET AGGMISSG is enabled.
```
SET UPDATECALC OFF;
SET CREATENONMISSINGBLK OFF;
SET CREATEBLOCKONEQ OFF;
FIXPARALLEL(NumberThreads, @RELATIVE("SparseDim",0))
FIX on all level 0 sparse dimension @RELATIVE(SparseDim,0)
```
Note:
- Although UPDATECALC, CREATENONMISSINGBLK and CREATEBLOCKONEQ are, by default, disabled, Oracle recommends explicitly turning them off in the business rule.
- Be sure to replace "SparseDim" with the names of the sparse dimensions in which zero blocks are to be replaced.
- Use FIXPARALLEL to help improve performance, for example, FIXPARALLEL(4, @RELATIVE("Scenario",0)).
- Be sure to specify ENDFIXPARALLEL.
Use one of the following optimized calculation designs to change near-zero values to zero values. Select the calculation design that performs better depending on your dimension design, data patterns, and processes.
Calculation Design 1 (for Dense or Sparse Calculation)

This calculation changes the near zero values to zero.
```
"DenseMbr" = @round("DenseMbr",<Insert Decimal Place>);
"SparseMbr" = @round("SparseMbr ",<Insert Decimal Place>);
```
Calculation Design 2 (for Dense Members in Block Mode Only)
```
"DenseBlockHeader" (
    @CALCMODE(BLOCK);
       "DenseMbr" =@round("DenseMbr",<Insert Decimal Place>);
)
```
Note:
Replace DenseMbr, SparseMbr, and DenseBlockHeader with actual names.
After running this rule, remove the resulting zeros See Removing Zeros in a BSO Cube for instructions.

Removing Zeros in a BSO Cube

Begin by removing zero blocks in the sparse dimensions in the cube by replacing them with #missing blocks.

To replace zero blocks with #missing blocks:

Create a business rule to replace zero blocks with #missing blocks.
Set business rule environment to optimize this business rule:
```
SET UPDATECALC OFF;
SET CREATENONMISSINGBLK OFF;
SET CREATEBLOCKONEQ OFF;
FIXPARALLEL(NumberThreads, @RELATIVE("SparseDim",0))
FIX on all level 0 sparse dimension @RELATIVE(SparseDim,0)
```
Note:
- Although UPDATECALC, CREATENONMISSINGBLK and CREATEBLOCKONEQ are, by default, disabled. Oracle recommends explicitly turning them off in the business rule.
- Be sure to replace "SparseDim" with the names of the sparse dimensions in which zero blocks are to be replaced.
- Use FIXPARALLEL to help improve performance, for example, FIXPARALLEL(4, @RELATIVE("Scenario",0)).
- Be sure to specify ENDFIXPARALLEL.
Use one of the following optimized calculation designs to change zero blocks to #missing blocks. Select the calculation design that performs better depending on your dimension design, data patterns, and processes.
Calculation Design 1 (for Dense or Sparse Calculation)

This calculation results in the original value and changes zero values to #missing.
```
"DenseMbr" = "DenseMbr" * "DenseMbr" / "DenseMbr";
"SparseMbr" = "SparseMbr" * "SparseMbr" / "SparseMbr";
```
Calculation Design 2 (for Dense Members in Block Mode Only)
```
"DenseBlockHeader" (
    @CALCMODE(BLOCK);
       IF ("DenseMbr" == 0) 
           "DenseMbr" = #Missing;
       ENDIF
```
Note:
Replace DenseMbr and SparseMbr, and DenseBlockHeader with actual names.

Using Never as the Consolidation Operator for Members with SmartList, Date, Text, and Percentage Data Types

Dimension members that have a data type of SmartList, Date, Text, and Percentage should use Never as the consolidation operator. Using the Addition consolidation operator increases the cube size without adding any value. Using Ignore as the consolidation operator will only stop addition within the dimension of the member; it will not stop the rolling up of values from other dimensions, which potentially can create unneeded blocks.