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:
- Multiple rows for the each
Entitymember 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
Combines all segments for all members into one segment:
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:
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.
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:
When was the issue first observed? (required)
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