Optimize BSO Cubes

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

• Replacing zero blocks with #missing blocks
• Removing #missing blocks to reduce the database size
• Using Never as the consolidation operator for members with data type of SmartList, Date, Text, and Percentage

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:

1. 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.
2. 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.

Removing #Missing Blocks from Cubes

Remove #missing blocks to reduce BSO database size. Use one of the following two methods to clear #missing blocks:

Method 1

Perform an explicit dense restructure

In Planning, this process can be run manually or through a job. Database restructures can also be performed using EPM Automate and REST APIs.

To execute a database restructure using Calculation Manager:

1. Open Calculation Manager. From the Navigator, select Rules from Create and Manage.
2. Click Actions and then select Database Properties.
3. In Enterprise View, expand the database containing the cube that you want to restructure.
4. Right-click the cube that you want to restructure and then select Restructure Database.

Information Sources:

• restructureCube command in Working with EPM Automate for Oracle Enterprise Performance Management Cloud
• Restructure Cube in REST API for Oracle Enterprise Performance Management Cloud

Method 2

Clear all data. Reload all data from files that you previously uploaded to the environment.

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.