Making Financial Reporting Reports More Efficient

Poorly designed Financial Reporting reports can generate several MDX requests or Essbase queries leading to the consumption of significant Oracle Enterprise Performance Management Cloud resources. Excessive resource consumption results in performance degradation when concurrent users access such reports.

The presence of several segments in the report is the key reason for generating a large number of MDX requests. This section explains how to make Financial Reporting reports more efficient by reducing the number of segments.

Redesigning Reports: a Use Case

The Original Report

The following illustration depicts the original report design:
Illustration of the original report design

This report illustration shows these design elements:
  1. Multiple rows for the each Entity member 100, 200, 403, and 500.
  2. Each Entity member has 8 rows each for different accounts.

The following table presents a high level view of the original report design and the optimized design:

Original Report Design Optimized Design

Multiple rows for each Entity member:


Combines Entity members into one segment:

100, 200, 403, 500

Each Entity member has 8 rows each for different accounts. Example for member 100:

100 = Children of 1100
100 = 1100
100= Children of 1200
100 = Children of 1300
100 = 1300
100 =Children of 1400
100 = 1400

Combines all segments for all members into one segment:

Entity members 100,200,403,500 = Children of 1100, 1100, Children of 1200, 1200, Children of 1300, 1300, Children of 1400, 1400

The Optimized Report

The following illustration depicts the optimized report design, which reduces the number of segments. Reducing the number of segments makes the report run faster by reducing the number of MDX requests:
Illustration of the redesigned report structure

Other Important Report Design Considerations

  • If possible, avoid relational-type reports (reports with multiple row dimensions expanded using functions) with a large combination of members. Big reports may take a significant amount of time to execute (or may not execute). A report is considered big when the number of cells exceeds ten thousand. This is similar to treating Financial Reporting as a large scale data extraction tool, which it is not.

  • Avoid reports with large number of cells with text functions (CellText, PlanningAnnotations and ListOfCellDocuments, etc.) that retrieve additional metadata from the data source.

  • Use current POV, prompts or books instead of Page dimension; all Page members are retrieved at one time upon executing the report.

  • Consider and test the impact of Conditional Formatting and Conditional Suppression, which can affect performance depending on the size of the report. Performance is contingent on the type of criteria and frequency with which they are used within the report. Criteria that are part of metadata or data query, for example, data value, member name, and member alias or description, are rendered fast. With large reports, minimize the use of criteria that are not part of the regular metadata or data query. Examples of such criteria include generation, level, account type, and attribute value.

Review Recent Application Changes

Identify if recent changes to the application causes report generation to slow down. You can do this by comparing the information in the Application Size table in the current Activity Report with the information in the Activity Report from a previous date when the report was working well. Also review any recent changes to the report design and usage to verify that such changes have not impacted the report.

Getting Help

After optimizing the report to reduce the number of MDX requests, if you do not see performance improvements, seek help from Oracle:

  • Use the Provide Feedback utility to gather the information that Oracle Support needs to identify and fix your problem. Make sure that you consent to submitting the snapshot to Oracle. See Creating a Provide Feedback Submission

  • Submit a service request indicating the reference number that the Provide Feedback utility created. See Submitting a Service Request.

    In the Service request, answer these questions:

    1. When was the issue first observed? (required)

    2. Was there any recent application or usage change that could have caused this issue? (optional)

    Provide the following to Oracle along with the service request:

    • Report name
    • All POVs
    • User and substitution variables in use
    • Expected and actual report generation times

See Getting Help From Oracle.