In This Section:
Improving Retrieval Performance on Large Dimensions
Organizing Members to Optimize Data Extraction
Understanding Reports for Outlines That Contain Dynamic or Transparent Members
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.
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.
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:
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 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 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.
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.
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,540With 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,540See Report Extractor.
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)
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:
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,540To 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.
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.
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.