Optimizing Reports and Other Types of Retrieval

In This Section:

Changing Buffer Size

Setting Numeric Precision

Generating Symmetric Reports

Improving Retrieval Performance on Large Dimensions

Organizing Members to Optimize Data Extraction

Understanding Reports for Outlines That Contain Dynamic or Transparent Members

Limiting LRO File Sizes

Changing Buffer Size

The time required to generate a report varies depending upon factors such as the size of the database you are reporting from, the number of queries included in the script, and the size of the report buffer.

Configurable variables specify the size of the buffers used for storing and sorting data extracted by retrievals. The buffer should be large enough to prevent unnecessary read and write activities. See Report Extractor.

The following report variables are used in the conditional retrieval and data sorting commands.

Setting the Retrieval Buffer Size

The database retrieval buffer is a server buffer, per database, that holds extracted row data cells before they are evaluated. Retrieval tools such as the Essbase Retrieval Wizard and the Report Writer use this buffer.

When the retrieval buffer is full, the rows are processed and the buffer is reused. If this buffer is too small, frequent reuse of the area can increase retrieval times. If this buffer is too large, too much memory may be used when concurrent users perform queries, also increasing retrieval times.

The following sections describe ways you can manage retrieval buffer sizes.

Manually Setting the Retrieval Buffer Size

Each database has a retrieval buffer setting, in kilobytes, that you can change. The default buffer size is 10 KB for 32-bit platforms and 20 KB for 64-bit platforms. If you are increasing the size of the buffer, Oracle recommends that you do not exceed 100 KB, although the size limit is set at 100,000 KB.

  To manually set the retrieval buffer size, use a tool:

Tool

Topic

Location

Administration Services

Setting the Size of Retrieval Buffers

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM

Oracle Essbase Technical Reference

Note:

If an outline does not include Dynamic Calc, Dynamic Times Series, or attribute members, using the VLBREPORT configuration parameter to dynamically determine the size of the retrieval buffer overrides the database retrieval buffer size setting. See Enabling Dynamic Retrieval Buffer Sizing.

Enabling Dynamic Retrieval Buffer Sizing

If a database has large block size and retrievals include a large percentage of cells from each block across several blocks, consider setting the VLBREPORT option to TRUE in the Essbase configuration file essbase.cfg.

When the VLBREPORT setting is TRUE, Essbase internally determines an optimized retrieval buffer size for reports that access more than 20% of the cells in each block across several blocks. This setting takes effect only if the outline does not include Dynamic Calc, Dynamic Times Series, or attribute members. The VLBREPORT configuration setting overrides the manually set retrieval buffer size.

Setting VLBREPORT to TRUE can result in faster query response times for concurrent and serial queries at the cost of a slight increase in memory required for each query.

Setting the Retrieval Sort Buffer Size

The retrieval sort buffer size setting specifies the size, in kilobytes, of the server buffer that holds the data to be sorted during a Report Writer retrieval. If the sort buffer is full, Essbase posts an error message.

Essbase requires a larger retrieval sort buffer size on 64-bit platforms than on 32-bit platforms. If you encounter an error indicating that the retrieval sort buffer limit has been exceeded, increase the setting by a factor of two.

You can adjust the buffer size on a per-database basis. The default buffer size is set to 10 KB on 32-bit platforms and to 20 KB on 64-bit platforms.

  To set the retrieval sort buffer size, use a tool:

Tool

Topic

Location

Administration Services

Setting the Size of Retrieval Buffers

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM

Oracle Essbase Technical Reference

Setting Numeric Precision

The NUMERICPRECISION configuration setting, used by the RESTRICT command, defines the number of precision digits the internal numerical comparison considers in the Report Extractor. If a precision setting is greater than necessary for the data, retrieval is slower than it could be. Identify the correct precision level and adjust NUMERICPRECISION accordingly. See the Oracle Essbase Technical Reference.

If you change an essbase.cfg setting, restart Essbase Server to apply the change.

Generating Symmetric Reports

When using Report Writer, if report processing time is of primary importance, consider making all reports symmetric. Symmetric reports provide better processing performance than asymmetric reports.

With a symmetric report, the Report Extractor uses one pass to compose the member list based on all possible member combinations. In the following example, the data values (Actual Jan and Feb, and Budget Jan and Feb) are retrieved in one pass.

Symmetric Report Member Combinations Supporting One Pass

                        Sales South

                 Actual              Budget
              Jan       Feb       Jan       Feb
         ========  ========  ========  ========
100–10        757       773       930       950
100–20        450       487       550       590
100–30   #MISSING  #MISSING  #MISSING  #MISSING
  100       1,207     1,260     1,480     1,540

With an asymmetric report, the Report Extractor must retrieve and process each block of possible member combinations separately. In the following example, the data values (Actual Jan and Budget Jan) are retrieved in two passes.

Asymmetric Report Member Combinations Requiring Multiple Passes

              Sales South

           Actual    Budget
              Jan       Jan
         ========  ========
100–10        757       950
100–20        450       590
100–30   #MISSING  #MISSING
  100       1,207     1,540

See Report Extractor.

Improving Retrieval Performance on Large Dimensions

Queries on large dimensions often have large resource requirements. However, these queries typically are sparse, meaning that the number of nonempty values returned is small compared to the size of the input query. For these large but sparse queries, we suggest using the following special MDX and Report Writer functions to help Essbase more efficiently use memory and processor resources. These functions optimize retrieval performance by attempting to handle only nonempty combinations.

  • Leaves() MDX function

  • NonEmptySubset() MDX function

  • MDX optimization properties: NONEMPTYMEMBER and NONEMPTYTUPLE

  • Leaves Report Writer Command

  • Generation or level specification in Descendants and Idescendants Report Writer commands (when used within Link command)

Organizing Members to Optimize Data Extraction

Report Extractor extracts data in a certain order for Report Writer. If you do not require a formatted report and you are using Report Writer, you can reduce the time required to generate the report by using either of these strategies:

  • Creating the report script in the same order as Report Extractor extracts data

  • Grouping dense dimensions in columns and sparse dimensions in rows

These strategies save the most time if used to create large production reports.

Report Extractor looks at data from bottom to top and right to left, starting from the bottom column member to the top column member and proceeding from the innermost row member (right) to the outermost row member (left).

In the following example, the column members come from dense dimensions and the row members from sparse dimensions. The sequence in which the report is read is represented by the numbers 1–3, which are in parentheses; for example, (1):

     Sales South

       (3) Actual    Budget (2)
              Jan       Jan (1)
         ========  ========
100–10        757       950
100–20        450       590
100–30   #MISSING  #MISSING
  100       1,207     1,540

To reduce the time to extract data, group dense dimensions first, then group sparse dimensions in the sequence in which they are displayed in the outline.

When dense dimensions are nested in the report columns, Report Extractor examines each data block only once, improving performance time.

Because attributes are sparse dimensions and are dynamically calculated, Essbase cannot use the sparse data extraction method when a report contains attribute dimensions.

Understanding Reports for Outlines That Contain Dynamic or Transparent Members

If you generate a report that accesses a database outline that contains Dynamic Calc and Store members, the first time that you generate the report takes longer than subsequent retrievals that access the same data block.

If you generate a report that accesses a database outline that contains Dynamic Calc or Dynamic Time Series members, Essbase calculates the member every time a report is generated, increasing the reporting time.

See Dynamically Calculating Data Values.

If you run a report that contains transparent members, the report takes longer to generate, because it must access multiple servers to retrieve the required data.

Limiting LRO File Sizes

You can limit the size of files that users can link to a database. Limiting the size prevents users from taking up too much of the server resources by storing extremely large objects. See Limiting LRO File Sizes for Storage Conservation.