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:

  1. In the Navigator, select Rules under Create and Manage to open Calculation Manager.
  2. Select Actions and then 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.

Analyzing Data in a Cube

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

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:

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

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.

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.