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.
This chapter describes ways to optimize the time required to generate your reports, and other retrieval optimization issues:
If you are migrating from a previous version of Essbase, see the Essbase Installation Guide for information about system changes and enhancements.
Configurable variables specify the size of the buffers used for storing and sorting data extracted by a report. The buffer should be large enough to prevent unnecessary read and write activities.
These report variables are used in the conditional retrieval and data sorting commands:
For more information about these settings, see the Technical Reference in the docs directory. For information about the Report Extractor process of retrieving data, see Quick Start to Report Scripts.
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:
To determine the best buffer size for your report processing needs, test a report script with different settings.
Use any of these alternate methods to set the retrieval buffer size:
Tool |
Instruction |
Additional Information |
---|---|---|
Note: In Essbase Version 4, retrieval size was set with a variable called REPTKBYTE and was set in the essbase.cfg configuration file. You can no longer set the variable in the essbase.cfg file.
The Retrieval Sort Buffer Size setting specifies the size, in kilobytes, of the server buffer that holds the data to be sorted during an Spreadsheet Add-in or Application Manager Report Writer retrieval. If the sorting buffer is full, 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:
To determine the best buffer size for your report processing needs, test a report script with different settings.
Use any of these alternate methods to set the retrieval sort buffer size:
Tool |
Instructions |
Additional Information |
---|---|---|
Note: In Essbase Version 4, retrieval sort buffer size was set with a variable called REPTKBYTESORTBUF and was set in the configuration file essbase.cfg.
The NUMERICPRECISION setting, used by the RESTRICT command, defines the number of precision digits the internal numerical comparison considers in the Report Extractor. If you have a precision setting greater than necessary for your data, your retrieval will be slower than it could be. Identify the correct level of precision and then adjust NUMERICPRECISION accordingly.
To set the NUMERICPRECISION variable:
Note: The essbase.cfg file is created manually. Consult your Database Administrator if the file is not available.
NUMERICPRECISION Xwhere 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 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.
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, as the Report Extractor composes the member list based on all possible member combinations. A list of this nature allows Report Extractor to create the list in one pass. With asymmetric reports, the Extractor must retrieve and process each block of possible member combinations separately
Figure 549: Symmetric Report Member Combinations Supporting One Pass
Figure 550: Asymmetric Report Member Combinations Requiring Multiple Passes
For more information about how the Report Extractor retrieves data, see Quick Start to Report Scripts.
Report Extractor extracts data in a certain order for the 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 any of these strategies:
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 then proceeding from the innermost row member (right) to the outermost row member (left). Figure 551 illustrates the sequence in which the report is read.
Figure 551: How Report Extractor Examines Data
The column members come from dense dimensions, and the row members come from sparse dimensions. To reduce 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.
When dense dimensions are nested in the report columns, Report Extractor examines each data block only once, thus improving performance time.
Attributes are sparse dimensions and are dynamically calculated. Hence, Essbase cannot use the sparse data extraction method when a report contains attribute dimensions. For more information on the SPARSE command, see the Technical Reference in the docs directory.
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, 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.
Because Essbase stores linked files in a repository on the server, you might want to limit the size of files that users can link. This would prevent a user from taking up too much of the server's resources by storing extremely large objects. You can set the maximum linked file size for each application. If a user attempts to link a file that is larger than the limit, an error message displays.
Note: The maximum file size setting applies only to linked files and does not affect cell notes or URLs. The maximum cell note length is fixed at 599 characters. The maximum URL string length is fixed at 512 characters.
To specify a maximum file size for an application (by default the size is unlimited), use this procedure:
Figure 552: Application Settings Dialog Box
To prevent users from attaching anything except very small files, enter 1. This lets users link only cell notes, URLs, and files less than 1 kilobyte in size.
Tip: You can set a limit on the size of a linked object without using Application Manager.
Tool |
Instructions |
For more information |
---|---|---|
Technical Reference in the docs directory Automating the Production Environment for information about ESSCMD. |
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |