DEFINING LIMITS FOR THE TABLE OR PIVOT FROM INSTANCECONFIG OR EM

It is rare that our OBIEE reports fetch less number of rows. In most of the cases, built reports fetch huge data set. Add to this scenario where Adhoc Reporting users fire their own queries that fetch thousands of rows.

What we should do?

We apply either Query Limit in RPD or make changes in em or make changes inInstanceConfig.xml files to limit number of rows downloaded or exported or shown by default. Let me explain the second and third option in this blog. :)

THROUGH EM
To Customize number of rows that can be exported,

EM => Business Intelligence => coreapplication => CapacityManagement => Performance => Lock and Edit =>Maximum Number of Rows to Download

Please note that Reducing the maximum number of rows that can be downloaded can improve performance where exports are common

THROUGH INSTANCECONFIG.XML

1. Navigate to
<Middleware>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml
and take Backup Instanceconfig.xml file

2. Change instanceconfig.xml file as follows

3. Locate the below tags (if present) else add the ones (as per applicability) between <Table> and </Table> or between <Pivot> and </Pivot>

<DefaultRowsDisplayed>60000</DefaultRowsDisplayed>
<MaxVisibleColumns>4000</MaxVisibleColumns>
<MaxVisiblePages>2000</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>

4. Restart Presentation Server from EM. Our changes are ready to be tested.

Some common used tags

MaxCells:
Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows
BiCoach Tip:
If MAX Cells is not set, then following error

View Display Error
Maximum total number of cells exceeded (Configured Limit: 50000).
*Error Details*
*Error Codes: EY692ZW9*

MaxRecords
Specifies the maximum number of records that can be included in the view.

MaxVisibleColumns
Specifies the maximum number of columns to be displayed in a view.

MaxVisibleRows
Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value.

MaxVisiblePages
Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.

MaxVisibleSections
Specifies the maximum number of sections to be displayed in a view. This element does not apply when a slider is in place for a graph.

Till Next post, Happy Exploring :)

Keep posting your comments and your queries.