Setting Data Query Optimization Options

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

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 Oracle Essbase sparse command, which is used to address the low probability that data exists for every combination of dimension members. For example, a dimension is sparse when intersections of data are empty.

You can also optimize certain types of Oracle Essbase and Workforce Planning Attribute dimension retrievals.

Note:

Using Workforce Planning, a dimension can have attribute associations that change across time periods or other dimensions. For example, an attribute dimension of “Status” associated with an “Employee” dimension allows for the change of employee status over time; In January, John Smith has the status of “Full Time”, in March his status changes to “Part Time”.

Note:

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

  To set data query optimization options:

  1. Open or create a report that contains a grid.

  2. Click Task, then Data Query Optimization Settings.

    Note:

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

    Note:

    The ability to suppress rows can be applied to any data source (Financial Management, Planning, SAP BW, and Oracle Essbase). All other options on this dialog box apply to Oracle Essbase only.

  3. To indicate the server on which you want to suppress rows, select one of the following options from the Suppress the Rows on the frame:

    • Database Connection Server

    • Report Server

  4. You can specify whether to query a report using MDX (Multidimensional Expression) or report script. For the check box, Essbase Queries Use MDX, perform one of the following:

    1. Clear the check box to use the report script queries. This will disable the “Show Drill Through“” setting on the grid.

    2. Select the check box to use MDX for querying the Essbase data source (this is the default), then select a method for running the query:

      1. 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.

      2. 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“””).

  5. To indicate if the Oracle Essbase sparse command is included or excluded, select one of the following options from the Sparse Command is frame:

    • Included

    • Excluded

      Note:

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

  6. Optional: Click Set to Default to use the default data query optimization settings.

  7. Click OK to accept the settings you indicated.

  8. Optional: Click Cancel to exit and return to the grid in the Report Designer.