Maximum number of rows exceeded errror in Smartview

When using Smartview on Essbase, you may get a maximum number of rows exceeded error specifying a number for the rows exceeded. This number is the one specified in the Oracle Provider Services maximum number of rows setting. The default setting is 5000 rows. When the Smartview query result exceeds 5000 rows (or the number specified in the Provider Services settings) , you get an error which looks like: Essbase Error (1020011): Maximum number of rows [number] exceeded.

Smartview Maximum Number of Rows [5000] Exceeded error

To fix this you need to increase the maximum number of rows setting for the Oracle Hyperion Provider Service (called as Analytic Provider Service in previous versions). This is the service that Smartview uses to connect to Essbase. Log on to EAS console. Expand Enterprise View, right click on ‘Provider Services’ and select ‘Add Provider Server’

Add Provider Services Server in EAS

In the ‘Add Provider Sever’ box, in the ‘Authenticating Essbase Server’ select the Essbase Server that you are authenticating against. From the ‘URL’ field, select the Hyperion Provider Services URL. Next, click in the ‘Provider Name’ field. It will prepopulate with the Analytic Server Name. You can change this name if needed. Click on OK.

Add Analytic Provider Services Server Details

Expand ‘Provider Servers’ in EAS, you should see the Hyperion Provider Server you added. Right click on the Provider Server and select Edit->Properties.

Edit Provider Services Properties

In the Provider Server Properties, you should see a setting called ‘Maximum Number of Rows’ with a default value of 5000. Increase this value to a value say 65000 and then click on ‘Apply’.

Update Maximum Number of Rows

You need to restart the Oracle Hyperion Provider Services services for the changes to take effect.

Oracle Hyperion Provider Services service

Please note that Smartview brings all the rows into memory before applying missing rows and zero rows suppression and then displays the query result in Excel. Say for example that that you have set the maximum rows setting to 10000. You fire an ad-hoc query on Essbase which will ultimately result in 2000 rows after missing and zero suppression. However if Smartview needs to fetch 11000 rows to ultimately display 2000 rows, you will still get the maximum number of rows exceeded error.

I have seen the option to update the maximum number of rows for Provider Services in EPM v onwards. This option may also be available in prior releases.

2 thoughts on “Maximum number of rows exceeded errror in Smartview

Leave a Comment