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 Oracle's Hyperion® Essbase® Spreadsheet Toolkit or 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 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.

      Table 121 lists the setting that you specify in essbase.cfg on the server to set the NUMERICPRECISION. If you change an essbase.cfg setting, restart Essbase Server to apply the change.

      Table 121. Setting Messages in the Server Using essbase.cfg

      Setting

      Definition

      For More Information

      NUMERICPRECISION

      An essbase.cfg setting that determines the number of precision digits used by Report Extractor

      Oracle Essbase Technical Reference

      Generating Symmetric Reports

      If report processing time is of primary importance, and you are using Report Writer, consider making all reports symmetric. Symmetric reports provide better processing performance than asymmetric reports, because the Report Extractor, in one pass, composes the member list based on all possible member combinations. With asymmetric reports, the Extractor must retrieve and process each block of possible member combinations separately.

      Figure 160. Symmetric Report Member Combinations Supporting One Pass

      Symmetric Report Member Combinations Supporting One Pass

      Figure 161. Asymmetric Report Member Combinations Requiring Multiple Passes

      Asymmetric Report Member Combinations Requiring Multiple Passes

      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 grouping 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). Figure 162, How Report Extractor Examines Data illustrates the sequence in which the report is read.

      Figure 162. How Report Extractor Examines Data

      How Report Extractor Examines Data

      The column members come from dense dimensions and the row members from sparse dimensions. 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 more than one server 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.