SPARSE

Tells Essbase to use the sparse data extraction method, which optimizes performance when a high proportion of the reported data rows are #MISSING. Essbase cannot use the sparse data retrieval optimization method on Dynamic Calc.

If you have at least one sparse row dimension in your report, Essbase uses the sparse data extraction method in two cases:

• Case 1: You use SUPMISSINGROWS in your report script to suppress #MISSING values, and Essbase estimates that a very high proportion of the requested data rows are #MISSING. In this case, Essbase implicitly uses the sparse method to optimize performance.

• Case 2: You explicitly use the SPARSE command in your report script. This forces Essbase to use the sparse method. If you use the SPARSE command in a report, and you have not used SUPMISSINGROWS, Essbase automatically turns on SUPMISSINGROWS for the report containing SPARSE. Essbase also turns on SUPMISSINGROWS for all following reports in your report script, unless you specify INCMISSINGROWS in a subsequent report.

Note:

If your report does not contain at least one sparse row dimension, Essbase cannot use the sparse method, and reverts to the regular method. Essbase displays a message to tell you that it cannot use the sparse method.

When Essbase uses the sparse method, it displays the following message: Report Writer Sparse Extractor method will be executed.

If you have at least one sparse row dimension in your report, the report is very large, and a very high proportion of the reported data rows are #MISSING, you may want to use the SPARSE command. You can then assess if this improves your report script performance.

If your report requests a small number of cells (#MISSING and non-missing), the sparse data extraction method is less efficient than the regular method. In this case, Essbase uses the regular method, unless you have at least one sparse row dimension in your report, and you explicitly use the SPARSE command.

SPARSE method: When Essbase uses the sparse data extraction method, Essbase first selects the row member combinations you have requested in your report script. Essbase looks at only the non-missing data blocks for these row member combinations. If your database is very sparse, this method is very efficient.

Regular method: By contrast, when Essbase uses the regular data extraction method, it cycles through every possible member combination requested by the report script. It then reports only those rows that are not#MISSING.

For example, suppose that only 1 in 10,000 data cells exist in a cube. The remaining cells are #MISSING. On this database, you run a report script that requests 100% of the data, and uses SUPMISSINGROWS to suppress the #MISSING values.

If Essbase uses the regular method of data extraction, it cycles through all the requested member combinations.

If Essbase uses the sparse extraction method, it looks only at the non-missing data blocks for the row member combinations requested. As this cube is very sparse, the number of data blocks is probably low. The sparse method produces the report much faster.

To exclude the sparse data extraction method from being used, use the <SPARSEOFF command. For example, you might want to use this command when reporting on data that includes Dynamic Calc members.

Syntax

<SPARSE

<SPARSEOFF

Notes

• The sparse extraction method cannot be used if the report contains attribute dimensions.

• When you include multiple logical reports separated by a ! within one report script, include the format commands/Headings for each logical report.