Optimizing Your Reports

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

This chapter describes ways to optimize the time required to generate your reports. It discusses the following topics:

If you are migrating from a previous version of Hyperion Essbase, see the Hyperion Essbase Start Here booklet for important information about system changes and enhancements.

Setting Configurable Variables

Configurable variables specify the size of the internal server buffers used for storing and sorting data extracted by a report. The following report variables are used in the conditional retrieval and data sorting commands:

Retrieval Buffer Size setting
Retrieval Sort Buffer Size setting
NUMERICPRECISION setting

For more information about these settings, see the online Technical Reference in the DOCS directory. For information about the Report Extractor's process of retrieving data, see Quick Start to Report Scripts.

Setting the Retrieval Buffer Size

The Retrieval Buffer Size setting specifies the size, in kilobytes, of the server buffer that holds extracted row data cells before they are evaluated by the RESTRICT, TOP, or BOTTOM commands. If this buffer is full, the rows are processed, and the buffer is reused. Both the Spreadsheet Retrieval Wizard and the Report Writer use this buffer to process retrievals.

You can adjust the buffer size on a per database basis. The default buffer size is set to 10 kilobytes. If you are increasing the size of the buffer, it is recommended that you do not exceed 100 kilobytes, although the size limit is set at 100,000 kilobytes. Larger buffer sizes can create problems when concurrent users generate reports at the same time.

To set the retrieval buffer size:

From the Application Manager menu, choose Database > Settings.
Select the General page.
Type the number, in kilobytes, of the required buffer size in the Retrieval Buffer Size box.
Figure 40-1: Setting the Retrieval Buffer Size

Note: To determine the best buffer size for your report processing needs, test a report script with different settings.

In Hyperion Essbase Version 4 this variable was called REPTKBYTE and was set in the ESSBASE.CFG file. You can no longer set the variable in the ESSBASE.CFG file.

You can also use alter database in MaxL or the SETDBSTATEITEM command in ESSCMD to set the retrieval buffer size. For more information, see the online Technical Reference in the DOCS directory.

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 an Hyperion Essbase Spreadsheet Add-in or Application Manager Report Writer retrieval. If the sorting buffer is full, Hyperion Essbase posts an error message.

You can adjust the buffer size on a per-database basis. The default buffer size is set to 10 kilobytes.

To set the retrieval sort buffer size:

From the Application Manager menu, choose Database > Settings.
Select the General page.
Type the number, in kilobytes, of the required sort buffer size in the Retrieval Sort Buffer Size box.
Figure 40-2: Setting the Retrieval Sort Buffer Size

Note: To determine the best buffer size for your report processing needs, test a report script with different settings.

In Hyperion Essbase Version 4, this variable was called REPTKBYTESORTBUF and was set in the ESSBASE.CFG file.

You can also use alter database in MaxL or the SETDBSTATEITEM command in ESSCMD to set the retrieval sort buffer size. For more information, see the online Technical Reference in the DOCS directory.

Setting the NumericPrecision Variable

The NUMERICPRECISION setting, used by the RESTRICT command, defines the number of precision digits the internal numerical comparison considers in the Report Extractor.

To set the NUMERICPRECISION variable:

Open the ESSBASE.CFG server file.

Note: The ESSBASE.CFG file is created manually. Consult your Database Administrator if the file is not available.

Find the following line in the file:

NUMERICPRECISION X

where X is a numeric value representing the number of precision digits that are used by the Report Writer for numerical comparison.

Note: If the line does not exist, type in the variable information using the example above. See the online Technical Reference in the DOCS directory for a syntax description and details about the NUMERICPRECISION setting, as well as more information about adding information to the ESSBASE.CFG file.

Generating Symmetric Reports

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

Figure 40-3: Symmetric Report Member Combinations

Figure 40-4: Asymmetric Report Member Combinations

For more information about how the Report Extractor retrieves data, see Quick Start to Report Scripts.

Organizing Members to Optimize Data Extraction

Report Extractor extracts data in a certain order. If you do not require a formatted report, you can reduce the time required to generate the report by:

Structuring the report script in the same order as Report Extractor extracts data
Grouping dense dimensions in columns
Grouping sparse dimensions in rows

This is particularly useful when creating 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 then proceeding from the innermost row member (right) to the outermost row member (left). In Figure 40-5, the report is read in numerical sequence.

Figure 40-5: How the Report Extractor Examines Data

In Figure 40-5, the column members come from dense dimensions, and the row members come from sparse dimensions. To optimize the time needed to extract data, group dense dimensions first, then group sparse dimensions in the same sequence as they are displayed in your outline.

By nesting dense dimensions in the report columns, Report Extractor examines each block only once, thus improving the performance time. Sparse dimensions follow the dense dimensions in the outline.

Note: Attributes are sparse dimensions and are dynamically calculated. Hence, Hyperion Hyperion Essbase cannot use the sparse data extraction method when a report contains attribute dimensions. For more information on the SPARSE command, see the online Technical Reference in the DOCS directory.

Working with Database Outlines

Report scripts extract data based on members identified in the database outline. This section discusses time implications when generating reports.

Accessing Outlines that Contain Dynamic or Transparent Members

If you generate a report that accesses a database outline containing 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 containing Dynamic Calc or Dynamic Time Series members, Hyperion Essbase calculates the outline member every time a report is generated, which increases the reporting time.

For more information about dynamic members, see Dynamically Calculating Data Values.

If you run a report that contains transparent members, the report takes longer to generate, as it must access more than one server to retrieve the required data.

Ensuring that Members in the Report Script Synchronize with the Database Outline

When you run a report, it is important to ensure that the member names referenced in the report script match the members in the database outline. An error displays every time the Report Extractor cannot find a matching member name, and you must correct the name in the report script before the report continues processing.