In This Section:
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.
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.
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.
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.
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.
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,540
With 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,540
See 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:
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,540
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.
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.