OBIEE 10G/11G Controling Pivot View behavior

OBIEE 10G/11G   Controling Pivot View behavior

One of the more powerful tools in OBIEE is the pivot view. If you have large amounts of data you sometime run out of rows and or columns or even worse out of cells

You can control these values by altering the instanceconfig.xml.
This file is usually found in ..\OracleBIData\web\config
Good info can be found in the Oracle® Business Intelligence Presentation
Services Administration Guide (b31766.pdf)

If you open instanceconfig.xml in notepad you get something like these parameters

The behavior of the pivot view can be altered with the following parameters:

• [MaxVisibleColumns] Sets the maximum number of columns to be displayed in a Pivot View default{1000}


• [MaxVisiblePages] Sets the maximum number of page choices (or pages in PDF) to be displayed in a Pivot View default{1000}


• [MaxVisibleRows] Sets the maximum number of rows to be displayed in a Pivot View. Default{100000}


• [MaxVisibleSections] Sets the maximum number of sections to be displayed in a Pivot View. Default{1000}

Altered in the instance config it would look like this:




If you still run out of cells try this:

• [CubeMaxPopulatedCells] The maximum number of populated cells in the Pivot Table. If this maximum is exceeded, the user receives an error when rendering the pivot table. Default {150000 }


• [CubeMaxRecords ] The maximum number of records returned by a query for the Pivot Table engine to process. This roughly governs the maximum number of cells that can be populated in a pivot table (unpopulated cells in a sparse pivot table do not count). If this maximum is exceeded, the user receives an error when rendering the pivot table.

Method2:

Presentation Server level

You can also limit the number of rows at the Presentation Server level.
Configuring the Maximum Number of Rows in an Answers Table View You can override the maximum number of rows that can appear in a Table view by modifying the Oracle BI Presentation Services configuration file (instanceconfig.xml) to add the following entry. The internal default is 65000. If the user exceeds this value, the server returns an error message when the table view is rendered. NOTE: This entry applies to the Table view, not the Pivot Table view. The following entry is an example: 95000
The ResultRowLimit entry controls the maximum value that can be set for :

• DefaultRowsDisplayed,
• DefaultRowsDisplayedInDelivery,
• and DefaultRowsDisplayedInDownload entries.

To set :

• DefaultRowsDisplayed,
• DefaultRowsDisplayedInDelivery,
• or DefaultRowsDisplayedInDownload

to a value that exceeds the current value of ResultRowLimit, you also must increase the value of ResultRowLimit to equal or exceed the value that you want to set.
In instanceconfig.xml, the node below limit the number of rows returned.

<ResultRowLimit>100</ResultRowLimit>

To display a message, set up the No result view in your report.
For the end user to limit the result set they would have to set a filter in the query

OBIEE 11G

 

To limit the Number of Rows in Pivot Table:

One of the more powerful tools in OBIEE is the pivot view. If you have large amounts of data you sometime run out of rows and or columns or even worse out of cells.

You can control these values by two ways:

1.       Limit rows from sever side

2.       Limit rows from presentation side

 

For limiting rows from server side you have to modify instanceconfig.xml file

 

This file is usually found in ....\OracleBI\instance\instance1\config\OracleBIPresentationServer

If you open instanceconfig.xml in notepad you get something like these

 

 

Now here pivot table is looking as follows:

<Pivot>

<DisableAutoPreview>false</DisableAutoPreview>

<MaxCells>1920000</MaxCells>

<MaxVisibleColumns>5000</MaxVisibleColumns>

<MaxVisiblePages>2500</MaxVisiblePages>

<MaxVisibleRows>25</MaxVisibleRows>

<MaxVisibleSections>5000</MaxVisibleSections>

<DefaultRowsDisplayed>50</DefaultRowsDisplayed>

</Pivot>

The behavior of the pivot view can be altered with the following parameters:

• [MaxVisibleColumns] Sets the maximum number of columns to be displayed in a Pivot View default{5000}
• [MaxVisiblePages] Sets the maximum number of page choices (or pages in PDF) to be displayed in a Pivot View default{25000}
• [MaxVisibleRows] Sets the maximum number of rows to be displayed in a Pivot View. Default{25}
• [MaxVisibleSections] Sets the maximum number of sections to be displayed in a Pivot View. Default{5000}

Here you can changes value as per your requirement or as u want to display on page & start BI services.

 

For limiting rows from Presentation side do following steps:

1.       Create an analysis using pivot table view.

2.       Click on Edit button of pivot table.

3.       Select pivot table property from top title bar:

4.       Select top page control from drop down list:

 

5.       Enter the rows as per your requirement:

 

6.       Click on OK & save analysis.

But it will work only for one analysis if u want same thing for multiple analysis then better option is do it from server side.

Thanks,

Satya Ranki Reddy