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.

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’

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.

Expand ‘Provider Servers’ in EAS, you should see the Hyperion Provider Server you added. Right click on the Provider Server and select Edit->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’.

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

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 11.1.2.1 onwards. This option may also be available in prior releases.