Essbase - Designing an Outline to Optimize Query or Calculations Performance

Essbase Overview


The relative locations of dimensions in an outline can affect performance times :

Indeed, although they contain the same dimensions, the outline examples below :

  • for Optimized Query Times
  • and for Optimized Calculation Times

are different. See meeting_the_needs_of_both_calculation_and_retrieval at the end of the article.

The structure defined in the Essbase - Outline (Database Outline) determines how data is stored in the database.

Rules of thumb

To optimize attribute calculation and retrieval performance, consider the following design tips :

  • Position attribute dimensions at the end of the outline.
  • Locate sparse dimensions after dense dimensions in the outline.
  • Place the most-queried dimensions at the beginning of the sparse dimensions and attribute dimensions at the end of the outline. In most situations, base dimensions are queried most.

To optimize attribute calculation and retrieval performance, consider the following :

To maximize attribute retrieval performance, use any of the following techniques:

  • Ensure that Essbase - Attribute dimensions are the only sparse Essbase - Dynamic Calculations dimensions in the outline.
  • Drill down to the lowest level of base dimensions before retrieving data. For example, in Essbase - Spreadsheet Add-in, turn on the Navigate Without Data feature, drill down to the lowest level of the base dimensions included in the report, and then retrieve data.
  • When the members of a base dimension are associated with several attribute dimensions, consider grouping the members of the base dimension according to their attributes. For example, in the Sample.Basic database, you can group all 8-ounce products.


Use the following topics to understand performance optimization basics.

Optimizing Query - Retrieval Performance

To optimize query performance, use the following guidelines when you design an outline:

  • If the outline contains Essbase - Attribute dimensions, ensure that the attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.
  • In the outline, place the more-queried sparse dimensions before the less-queried sparse dimensions.

The outline below is designed for optimum query performance:

Outline For Optimized Query Times

Optimizing Calculation Performance

To optimize Essbase - Calculations performance, order the sparse dimensions in the outline by their number of members, starting with the dimension that contains the fewest.

See Designing for Calculation Performance.

The outline in the Figure below is designed for optimum calculation performance:

  • The smallest standard dimension that is sparse, Market, is the first of the sparse dimensions in the outline.
  • The largest standard dimension that is sparse, Product, is immediately above the first attribute dimension. If the outline did not contain attribute dimensions, the Product dimension would be at the end of the outline.

Outline For Optimized Calculation Times

Meeting the Needs of Both Calculation and Retrieval

To determine the best outline sequence for a situation, prioritize the data retrieval requirements of the users against the time needed to run calculations on the database. How often do you expect to update and recalculate the database? What is the nature of user queries? What is the expected volume of user queries?

A possible workaround is initially to position the dimensions in the outline to optimize calculation. After you run the calculations, you can manually resequence the dimensions to optimize retrieval. When you save the outline after you reposition its dimensions, choose to restructure the database by index only. Before you run calculations again, resequence the dimensions in the outline to optimize calculation.

Discover More
Essbase Overview
Essbase - ASO/BSO Storage

Essbase support two storage type : block storage (BSO) and aggregate storage (ASO) as a persistence mechanism for multidimensional databases. How the data are internally stored is determined by...
Essbase Outline
Essbase - Outline (Database Outline)

An outline (also known as database outline) define the database design in a tree structure which define : dimension member consolidations formulas and calculations scripts aliases, properties,...
Essbase Overview
Essbase - Outline Creation and Management using Outline Editor

This article provides an overview of creating outline using Outline Editor. For information Outline Editor, see the Oracle Essbase Administration Services Online Help. All examples in this chapter are...
Essbase Short Cycle
Essbase - The OLAP Design Cycle (to create an optimized database)

A contains two types of values : the values that you enter or load (input data) and the values that are calculated from the input data (calculated data) Then a typical conforms to the following...

Share this page:
Follow us:
Task Runner