Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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 reports, and other retrieval optimization issues:
If you are migrating from a previous version of Analytic Services, see the Essbase Analytic Services 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 retrievals. 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 a description of the Report Extractor process of retrieving data, see Report Extractor.
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 Spreadsheet Add-in 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, resulting in increased retrieval times.
The following sections describe ways you can manage retrieval buffer sizes:
Each database has a retrieval buffer setting, in kilobytes, that you can change. 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.
To manually set the retrieval buffer size, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
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.
If a database has very 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.
The retrieval sort buffer size setting specifies the size, in kilobytes, of the server buffer that holds the data to be sorted during a Spreadsheet Add-in or Report Writer retrieval. If the sorting buffer is full, Analytic Services 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, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
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 the data, the retrieval is slower than it could be. Identify the correct level of precision and then adjust NUMERICPRECISION accordingly.
This table lists the setting that you specify in the essbase.cfg
file on the server to set the NUMERICPRECISION. If you change an essbase.cfg
setting, restart Analytic Server to apply the change.
Setting |
Definition |
For More Information |
---|---|---|
An |
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 245: Symmetric Report Member Combinations Supporting One Pass
Figure 246: Asymmetric Report Member Combinations Requiring Multiple Passes
For a description of how the Report Extractor retrieves data, see Report Extractor.
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 247 illustrates the sequence in which the report is read.
Figure 247: 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 the 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, Analytic Services cannot use the sparse data extraction method when a report contains attribute dimensions.
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, Analytic Services calculates the member every time a report is generated, which increases the reporting time.
For a comprehensive discussion of dynamic calculation, 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.
You can limit the size of files that users can link to a database. Limiting the size prevents a user from taking up too much of the server resources by storing extremely large objects. For more information, see Limiting LRO File Sizes for Storage Conservation.
![]() |