1. Using Oracle Essbase
  2. Optimize Cubes Using Cube Designer

16 Optimize Cubes Using Cube Designer

The cube designer Optimize Cube option provides a set of utilities to help you build and optimize cubes.

You can use these utilities with hybrid mode cubes, or aggregate storage cubes. Doing so helps you to understand where there are opportunities for optimizing the following processes: building and loading the cube, calculating or aggregating data, running queries, and exporting data.

Create Optimized Hybrid Cubes

The Baseline, Calc Cache, Solve Order, and Data Distribution Optimize Cube utilities help you fine tune your cubes for better performance.

Create optimized hybrid cubes using these four Optimize Cube utilities:
Utility Data Returned
Baseline Cube performance metrics
Solve Order Solve order of the members in the cube
Calc Cache Data to help you choose the best calculator cache value for the cube
Data Distribution Data to help you choose which dimensions to make sparse and which to make dense

Optimize Baseline Metrics on a Hybrid Cube

The metrics tracked by the Baseline utility show how the system is performing. Use these metrics to determine the baseline performance, and then to measure the benefits of the subsequent optimizations that you make.

Before using this utility, you first create an application workbook, including the outline, configuration settings, calculation scripts and queries you want to include in the cube.

When you run the utility, it builds the cube, loads the selected data files, executes the selected calculation scripts, and runs the queries contained in the application workbook. It is important to have a representative sample of queries from your users.

The baseline utility creates a dashboard of the application and operational processes, which can help you to design and optimize the cube. As you implement changes and rebuild the cube, the baseline helps you compare iterations of cube modifications. On the Essbase.Stats.Baseline tab of the application workbook, the baseline utility appends new tables with the latest data for each iteration.

Prepare to run the Optimize Cube Baseline Utility on a Hybrid Cube

Complete these tasks before running the baseline utility:

  1. Design and create your application workbook.

    To create an application workbook, you can download a sample application workbook and then modify it to suit your needs. See Explore the Gallery Templates.

  2. Clear the query sheets in the application workbook of Smart View metadata:
    1. Go to the Smart View ribbon.
    2. Choose Sheet Info and click Delete.

    If the query sheets have metadata from a different server, cube designer displays a warning and pauses processing until you respond.

  3. Modify the Cube.Settings worksheet with the following Application Configuration settings:
    Setting Value
    ASODYNAMICAGGINBSO FULL
    HYBRIDBSOINCALCSCRIPT NONE
    INDEXCACHESIZE 100M
    DATACACHESIZE 100M
    ASODEFAULTCACHESIZE 100
    MAXFORMULACACHESIZE 102400
    INPLACEDATAWRITEMARGINPERCENT 20
    CALCCACHEDEFAULT 200000
    LONGQUERYTIMETHRESHOLD -1

Run the Optimize Cube Baseline Utility on a Hybrid Cube

The Baseline utility identifies dense and sparse dimensions, data size (PAG and IND file sizes), block size, and the data, index, and calculator cache sizes. Additionally, it provides metrics for the data load, calculation, and query.

To run the Baseline utility:

  1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
  2. (Optional) Click Customize to choose which baseline operations to run.
    • Build cube - Build the cube defined in the application workbook and load the data in the data sheets.
    • Run calc scripts - Run the calculation scripts defined in each of the calculation sheets in the application workbook.

      Calculation worksheets run in the order they appear in the application workbook. Optimize Cube ignores the Execute Calc property on the calculation sheets.

      Only calculation scripts that can be run from Jobs are supported with Optimize Cube. You cannot run calculation scripts that depend on the current Smart View grid context (for example, calculations defined using the @GRIDTUPLES function, or those that use runtime substitution variables defined with <svLaunch> tags).

    • Run queries - Run the queries on the Query sheets.
    • Export all - Export all the data in the cube to the cube directory. After the export time and file size are recorded, the export file is deleted automatically.
  3. Click Create Baseline.

    If you don’t have a data sheet in the application workbook, you’ll be prompted to select data and rule files from the catalog. It is a good practice to store the data and rule files in a shared directory in the catalog so that the files won’t be lost when you rebuild the cube.

    It will take some time to build the cube.

    Essbase generates the Essbase.Stats.Baseline sheet and adds it to the workbook.

  4. View the Essbase.Stats.Baseline sheet in the application workbook.
    • The first table on the sheet displays the size of the data load files, the number of data load cells, block size, and cache sizes.
      Image of the first table in the Essbase.Stats.Baseline sheet, showing database metrics.

    • The colors in the baseline table identify the storage type for each dimension:
      • Green - dense dimension
      • Red - sparse dimension with at least one dynamic formula
      • Blue - sparse dimension with aggregations and without all dynamic parents and formulas
      • Gold - other sparse dimension

      Image of the second table in the Essbase.Stats.Baseline sheet, showing dense and sparse dimensions, total and stored members.

    • Under Load and Calc, the individual “Script:” rows identify which calculation script takes the longest to complete and thus might need optimizing.
      Image of the third table in the Essbase.Stats.Baseline sheet, showing load and calc times, blocks, data and index file size created.

    • Under Query, Blocks Read, it shows the amount of data requested by the query.

      Changing a dynamic dimension to stored reduces that amount.

    • Under Query, Formulas, it shows the number of formulas executed in the query.

      Review the solve orders of calculated members and make changes to reduce the number of formula executions and improve performance, or consider storing a calculated member containing formulas to reduce the number of formula executions and improve performance.


      Image of the fourth table in the Essbase.Stats.Baseline sheet, showing query time, blocks read, and formulas executed.

    • The last table on the sheet displays the export time and file size.
      Image of the fifth table in the Essbase.Stats.Baseline sheet, showing export time and file size.

Optimize the Solve Order on a Hybrid Cube

The Solve Order utility gives you a visual representation of the solve order flow used in the application. This can help diagnose query performance problems relating to formulas.

To run the Optimize Cube Solve Order utility:

  1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
  2. Click Solve Order.
  3. View the Essbase.Stats.SolveOrder sheet of the application workbook.

Use the information in the Essbase.Stats.SolveOrder sheet to adjust the solve order to optimize query performance. See Optimize the Cube for Hybrid Mode and Solve Order in Hybrid Mode.

Optimize the Calculator Cache on a Hybrid Cube

The Calc Cache utility recommends the optimal calculator cache setting for the cube.

Using the correct calculator cache setting can be an important performance enhancement when calculating entire sparse dimensions in a calculation script. Calculating an entire sparse dimension is a technique for reducing the number of blocks required by a query.

The default value of the calculator cache is 200,000 bytes. The maximum value is 20,000,000 bytes.

The calculator cache should be set to just large enough to contain the sparse dimensions that are calculated in the calculation script. Setting the calculator cache to larger than it needs to be has a negative impact on performance.

To optimize the calculator cache using the Calc Cache utility:
  1. In order to reduce the amount of data requested by the query, calculate and store one or more dimensions using a calculation script.

    The best choice is usually the largest dimension.

  2. Move that dimension to be the first sparse dimension in the outline.

    The calculator cache algorithm selects the sparse dimensions to place in the cache, beginning with the first sparse dimension.

  3. Build the cube without loading data.

    The cube must be built for the Calc Cache utility to work.

  4. Run the Calc Cache utility.

    The utility displays the correct cache setting next to each dimension up to 20 MB. Beyond 20 MB, it shows N/A. Generally, settings above a couple of MB are not needed.

    1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
    2. Click Calc Cache.
    3. View the Essbase.Stats.CalcCache sheet of the application workbook. You can view the recommended calculator cache settings in the Essbase.Stats.CalcCache worksheet, in the Calc Cache column.
      Image of the Essbase.Stats.CalcCache sheet in an optimize cube type application workbook.

  5. Find the Calc Cache setting in the Essbase.Stats.CalcCache sheet, next to the sparse dimension(s) you calculated and stored in step 1.
  6. If you calculated one dimension in step 1, set the calculator cache default to that value. If you calculated more than one dimension in step 1, choose the highest Calc Cache value from among the values you calculated.

    Add this value to the Application Settings section of the Cube.Settings worksheet. Alternatively, you can set the value in the application configuration settings in the Essbase web interface. It is a good practice to round up, in order to allow a little more room.

Optimize Data Distribution on a Hybrid Cube

The data distribution utility helps you better understand the data in an application, enabling you to make important decisions about how to optimize your cube.

Understanding the data helps you determine the following:
  • Which dimensions to make dense and which to make sparse.

    Dense dimensions define the blocks in a block storage application. Ideally, a block should contain dimensions with the most data and represent the predominant query layout for that application. For financial reporting applications, this usually means the Time and Account dimensions should be dense.

  • Which dimensions to calculate and store using a calculation script.

    One of the factors that affects query performance is the number of blocks requested by the query. If the number of blocks requested is too high, the query performance suffers. To reduce the number of blocks requested, pre-calculate upper level members of one or more sparse dimensions. First, set the dimension storage attribute of the upper members to a stored attribute (Store or Never Share), and then run a calculation script that aggregates that dimension using either CALC DIM or AGG.

  • Which dimensions to use as the task dimension in the FIXPARALLEL command.

    To optimize the calculation script used to aggregate the stored sparse dimensions, use the FIXPARALLEL command. It is important to select the correct task dimensions. A task dimension is the one that determines how the calculation is split into threads and executed in parallel. One or more sparse dimensions should contain the most data to reduce empty tasks, and ideally, that data should be evenly distributed.

To run the Data Distribution utility:
  1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
  2. Select Data Distribution.

    This process can take a long time to run, especially on larger models.

  3. View the Essbase.Stats.DataDist worksheet.

Image of the Essbase.Stats.DataDist tab in an optimize cube workbook, showing data distribution stats.


Image of the Essbase.Stats.DataDist tab in an optimize cube workbook, showing blocks and cells per block.

Create Optimized Aggregate Storage Cubes

Create optimized aggregate storage cubes using these two Optimize Cube utilities:

Utility Data Returned
Baseline Cube performance metrics
Solve Order Solve order of the members in the cube

Optimize Baseline Metrics on an Aggregate Storage Cube

The metrics tracked by the Baseline utility show how the system is performing. Use these metrics to determine the baseline performance, and then to measure the benefits of the subsequent optimizations that you make.

Before using this utility, you first create an application workbook, including the outline, configuration settings, and queries you want to include in the cube.

When you run the utility, it builds the cube, loads the selected data files, builds either a default aggregation or a query based aggregation (if enabled in Customize), and runs the queries contained in the application workbook. It is important to have a representative sample of queries from your users.

The baseline utility creates a dashboard of the application and operational processes, which can help you to design and optimize the cube. As you implement changes and rebuild the cube, the baseline helps you compare iterations of cube modifications. On the Essbase.Stats.Baseline tab of the application workbook, the baseline utility appends new tables with the latest data for each iteration.

Prepare to Run the Optimize Cube Baseline Utility on an Aggregate Storage Cube

Complete these tasks before running the baseline utility:

  1. Design and create your application workbook.

    To create an application workbook, you can download a sample application workbook and then modify it to suit your needs. See Explore the Gallery Templates.

  2. Clear the query sheets in the application workbook of Smart View metadata:
    1. Go to the Smart View ribbon.
    2. Choose Sheet Info and click Delete.

    If the query sheets have metadata from a different server, cube designer displays a warning and pauses processing until you respond.

  3. Modify the Cube.Settings worksheet with the following Application Configuration settings:
    Setting Value
    ASODEFAULTCACHESIZE 100

    Designates the default size for the aggregate storage cache.

    100 is the default. Start with 100 and adjust if indicated by the output of QUERYTRACE.
    LONGQUERYTIMETHRESHOLD -1

    This setting lets you specify the lowest query-time length, in seconds, for which you want to capture statistical information. Oracle recommends setting LONGQUERYTIMETHRESHOLD when using this utility.
    QUERYTRACE -1

    Sets a query calculation flow trace to be run and the results to be printed out to a file. Setting QUERYTRACE provides a more in depth analysis.

Run the Optimize Cube Baseline Utility on an Aggregate Storage Cube

On aggregate storage cubes, the Baseline utility identifies dynamic, stored, and multiple hierarchy enabled dimensions. Additionally, it provides metrics for loading data, building aggregations, and performing queries.

To run the Baseline utility:

  1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
  2. (Optional) Click Customize to choose which baseline operations to run.
    • Build cube - Build the cube defined in the application workbook and load the data in the data sheets.
    • Build aggregations – Aggregations are intermediate stored consolidations called aggregate views. Aggregate views store upper-level intersections, which support query performance by avoiding dynamic aggregations on the most commonly queried intersections in the cube. The term aggregation is used to refer to the aggregation process and the set of values stored as a result of the process.

      When you build an aggregation, Essbase selects aggregate views to be rolled up, aggregates them based on the outline hierarchy, and stores the cell values in the selected views. If an aggregation includes aggregate cells dependent on level 0 values that are changed through a data load, the higher-level values are automatically updated at the end of the data load process.

    • Run queries - Run the queries on the Query sheets.
    • Export all - Export all the data in the cube to the cube directory. After the export time and file size are recorded, the export file is deleted automatically
  3. Click Create Baseline.

    • Optionally, enter a non-zero value for Ratio to stop.
      Image of the build aggregations dialog box in cube designer optimize cube.

      Leaving ratio to stop at zero (the default) means there is no stopping ratio set.

      Consider setting this option to a non-zero value if there is no known common type of query executed by your cube’s users, and you want to improve performance by limiting the cube’s growth. Essbase aggregates the selected views, with the exception that the maximum growth of the aggregated cube must not exceed the given ratio. For example, if the size of a cube is 1 GB, specifying the total size as 1.2 means that the size of the resulting data cannot exceed 20% of 1 GB, for a total size of 1.2 GB

    • Check or clear the box for Based on query data.

      Essbase aggregates a selection of views that is defined based on analysis of user querying patterns. This is a good approach if similar types of queries are typically executed by the users of your cube. The utility runs the queries contained in the workbook first, then it creates the aggregate views based upon those queries.

  4. Select whether to Enable alternate rollups.

    Consider checking this box if your cube implements Alternate Hierarchies for shared members or attributes, and you want to include them in the aggregation.

  5. Click Okay.

    If you don’t have a data sheet in the application workbook, you’ll be prompted to select data and rule files from the catalog. It is a good practice to store the data and rule files in a shared directory in the catalog so that the files won’t be lost when you rebuild the cube.

    It will take some time to build the cube.

    Essbase generates the Essbase.Stats.Baseline sheet and adds it to the workbook.

  6. View the Essbase.Stats.Baseline sheet in the application workbook.
    • The first table on the sheet displays the number of cells loaded, the default aggregate storage cache size, whether to build aggregations, the ratio to stop, whether it is based on queries in the workbook, and whether alternate rollups are enabled.
      Image of the stats table on Essbase.Baseline.Stats for cube designer optimize cube on aso.

    • The colors in the Baseline table identify the hierarchy type for each dimension.
      • Green – multiple hierarchy dimension
      • Blue – stored hierarchy dimension
      • Gold – dynamic hierarchy dimension

      Image of the Baseline table on Essbase.Stats.Baseline for Excel tab for cube designer optimize cube on aso.

    • Under Load and Calc, the rows show load time, input level data size, and aggregate data size for the initial data load and after building aggregations.
      Image of the Load and Calc table on the Essbase.Stats.Baseline Excel tab for cube designer optimize cube on aso.

    • Under Query, the Formulas row shows the number of formulas executed in the query.

      Review the solve orders of calculated members and make changes to reduce the number of formula executions and improve performance, or consider storing a calculated member containing formulas to reduce the number of formula executions and improve performance.


      Image of the Query table on the baseline tab in Excel for cube designer optimize cube on aso.

    • The last table on the sheet displays the export time and file size.

Optimize the Solve Order on an Aggregate Storage Cube

The Solve Order utility gives you a visual representation of the solve order flow used in the application. This can help diagnose query performance problems relating to formulas.

To run the Optimize Cube Solve Order utility:
  1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
  2. Click Solve Order.
  3. View the Essbase.Stats.SolveOrder sheet of the application workbook.

Use the information in the Essbase.Stats.SolveOrder sheet to adjust the solve order to optimize query performance. See Calculation Order.