Setting Data Query Optimization Options

When working with grids, you can set data query optimization options to optimize the efficiency of your data queries.

Because data queries extract values from a multidimensional database connection, it is beneficial to speed up the data query process using the optimization settings.

Because data queries extract one or more values from a multidimensional database connection, it is beneficial to speed up this process using the data query optimization settings.

For example, you can specify suppression options on your servers and include or exclude the sparse command, which is used to address the low probability that data exists for every combination of dimension members. A dimension is sparse when intersections of data are empty.

Note:

When applying optimization options, you may use MDX (Multidimensional expression) to query Oracle Essbase data sources on a grid basis or report script.

To set data query optimization options:

  1. In a report that contains a grid, do one of the following:

    • Select Task, and then Data Query Optimization Settings.

    • Click the top left cell in the grid to select the grid, and then right-click and select Data Query Optimization Settings.

    The options that display in the Data Query Optimization Setting dialog box, depend on the database connection. For example, the Essbase Sparse Command option is only displayed when Essbase is the database connection.

  2. Select whether to suppress rows on the Database Connection Server or the Report Server.
  3. Essbase Only: Specify whether to query a report using MDX (Multidimensional Expression) or a report script.:

    • Clear Essbase Queries Use MDX to use report scripts to query the Essbase database.

      This disables the Show Drill Through setting on the grid. fIn order to use Essbase Drill Though, the report has to use MDX query.

    • Select Essbase Queries Use MDX to use MDX to query the Essbase database, then select how to run the query:

      • Combine queries using with members (this is the default). This method combines the MDX queries into a single query using the MDX with membersyntax. By minimizing the number of queries, performance should be improved.

      • User separate queries. This method issues separate MDX queries for each row which has a member selection function (for example, "Descendants of" or "Children of").

  4. Essbase Only: In Sparse Command is, select Included or Excluded to specify whether to include or exclude the Essbase sparse command.

    The Sparse Command option is only displayed when the database connection uses an Essbase a data source.

  5. Optional: Click Set to Default to use the settings defined here as the default data query optimization settings.
  6. Click OK to save the settings.