Parameter For Max Number Of Rows Not Taken Into Account

This is probably helpful for someone who migrated from OBIEE 10G to 11G and need to retain the same settings. I’m still wondering who needs to see reports with 500k rows, but still.

In OBIEE 10g the ResultRowLimit was working with a value of 5,000,000 (65,000 is the default value).

We have tried to do the same in 11g through EM > BI > Core application > Capacity Management >

Performance > Maximal number of rows in a tabular view set from 65,000 to 5,000,000.

The Servers have been restarted and the report is still not running.

The SQL query fetches less than 200,000 rows. i.e. one of our reports is 172,310 rows.

Impact on Business:
This problem impacts a lot of reports through the National Healthcare agency.
These reports used to work in 10g.

View Display Error

saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool, saw.threadpool, saw.threads
SQL Issued: 858~k1vsg0egoo7i60qhn13qv8lj52


Moreover, the same matter occurs in a report with 49220 lines fetched from Database.

Could it be linked to the fact that these reports use a pivot table to perform a second aggregation phase?

The final Pivot view is about 10 columns by 10 lines.
Cause
The Number of Rows setting was verified within OBIEE 11.1.1.5.

Capacity Management > Performance

Maximum Number of Rows Processed when Rendering a Table View

This setting limits how much data is retrieved from the BI Server and processed. The default value is 65000.

Reducing the maximum number of rows processed can significantly improve performance by reducing the system resources that can be consumed by a given user session.

Number of Rows 65000

It was found that the cause was the Pivot Table (which generally have more performance overheads when compared to a standard Table).
Solution
Within the instanceconfig.xml file in 10g you would have had to adjust the limits in your file for the parameters below.

In OBIEE 11g, these values below are discussed in the following Guides.

Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-02

18.3.1 Manually Configuring for Data in Views

If changes are made to the instanceconfig.xml file please carefully refer to the section stated below:
(Section 3.4, “Using a Text Editor to Update Configuration Settings.”)

When the following changes were made to the instanceconfig.xml file, this fixed the issue.





1000000
5000000





Oracle suggests that “These values were specific to this environment and the reports being run by that organisation. Other readers should note that increasing these values to too high a level may have an impact on performance (which is hardware dependent).”