04 Jun 2010 “Maximum total number of cells in Pivot Table exceeded” error

Pivot Tables are a great feature of Oracle BI. It is arguably one of the most used views when designing reports, and offers immediate hindsight on data with drilldown capabilities on both rows and columns.
Such intelligence power comes with a few limitations though.
Pivot Tables aren’t made to visualise large amounts of data (data dumps are best managed through normal tables or even better, direct download to Excel/flat files).

In order to optimize the performance of pivot views, Oracle BI establishes a limit of data that pivot tables can work with.
When a report retrieves a larger set of data, the following error is displayed:

Although we suggest to keep Oracle’s settings (after all, if you have a Pivot Table report retrieving millions of records at a time, maybe you should rethink your report layout or design altogether), altering the error threshold is possible.

The procedure is fairly simple and relies on the modification of the Oracle BI Presentation Services configuration file (instanceconfig.xml).
It is located in …\OracleBIData\web\config path.

Open the instanceconfig.xml file and look for a Pivot View section, where you should find some parameters of maximum values defined.
In case the Pivot View section does not exist, the behaviour of the pivot report is driven by the default values established by Oracle.

You can find an example of the instanceconfig file below:

In the table below you can find the parameters for pivot tables to be used in the instanceconfig file, with the description and the default values for version 10 (in older versions the default value might be different, and usually lower than the value specified below).
Default values, as already mentioned, come to work in case no specific values are specified in the file.

NOTE: Modifying the instanceconfig.xml file requires a restart of the ‘Oracle BI Presentation’.