Essbase - Designing an Outline to Optimize Query or Calculations Performance
About
The relative locations of dimensions in an outline can affect performance times :
- either Essbase - Calculations
- or retrieval .
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.
Articles Related
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 :
- The calculation order for Essbase - Attribute calculations is the same as for Essbase - Dynamic Calculations. For an outline, see Calculation Order for Dynamic Calculation.
- Because Essbase calculates attribute data dynamically at retrieval time, Essbase - Attribute calculations do not affect the performance of the overall (batch) database calculation.
- Tagging base-dimension members as Dynamic Calc may increase retrieval time.
- When a query includes the Sum member and an attribute-dimension member whose associated base member is tagged as two-pass, retrieval time may be slow.
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.
Optimizing
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:
- Because the outline contains Essbase - Attribute dimensions, the storage property for Essbase - Standard dimensions and all standard dimensions members is set as store data.
- As the most-queried sparse dimension, the Product dimension is the first of the sparse dimensions. Base dimensions are typically queried more than other dimensions.
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.
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.