Translate

Wednesday, 21 December 2011

OBIEE 11g / 12c Row Settings Table and pivot Instanceconfig.


OBIEE 11g / 12c Row Settings Table and pivot

When you have a large number of rows in a report Table or Pivot to be viewed .


To implement the solution, please do:

1. Change instanceconfig.xml file in <biee11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 as following

<Views>
<Pivot>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DisableAutoPreview>false</DisableAutoPreview>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxVisibleColumns>5000</MaxVisibleColumns>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Table>
</Table>
</Views>

3. Note the new elements added are the following to both <Pivot> and <Table>, the Pivot part controls

Pivot view, and Table part controls Table View, my testing was done with Table View.
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxCells>5000000</MaxCells>
4. Restart Presentation Server from EM, then run your request, do pdf export to verify.




Default rows displayed  = incremental button on dashboard

Max visible rows = Display max rows per page or goto end of report -- if stable number can be high

Set them to the same value to remove the Max rows per page down arrow



Element
Description
Default Value
Applicable Views
DefaultRowsDisplayed
Specifies the default number of rows to display in views in analyses and dashboards. This number should not exceed the number that is specified for the MaxVisible Rows element.
25
Narrative, Pivot Table, Table
MaxCells
Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows, which is what the system attempts to render.
50000
Pivot Table, Table
MaxRecords
Specifies the maximum number of records that can be included in the view.
40000
Narrative, Ticker
MaxVisibleColumns
Specifies the maximum number of columns to be displayed in a view.
300
Graph, Pivot Table
MaxVisibleRows
Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value.
For tables and pivot tables, specifies the following:
500
Graph, Pivot Table, Table
MaxVisiblePages
Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.
1000
Graph, Pivot Table, Table
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. The SectionSliderDefault and SectionSliderLimit elements apply to limit section values when a slider is in place. See Table 18-2.
25
Graph, Pivot Table, Table

 
If MAX Cells is not set
Error
View Display Error
Maximum total number of cells exceeded (Configured Limit: 50000).
Error Details
Error Codes: EY692ZW9
 
 

Tried and Tested

<Pivot>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxVisibleColumns>1000</MaxVisibleColumns>
<DefaultRowsDisplayed>10000</DefaultRowsDisplayed>
<MaxVisiblePages>500</MaxVisiblePages>
<MaxVisibleRows>100000</MaxVisibleRows>
<MaxVisibleSections>50</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>
<DefaultRowsDisplayed>10000</DefaultRowsDisplayed>
<MaxVisiblePages>100</MaxVisiblePages>
<MaxVisibleRows>100000</MaxVisibleRows>
<MaxVisibleSections>50</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Table>




OBIEE 12C Settings --- All out tried and tested




<Views>
<Pivot>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>9999999</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>165000</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxVisibleColumns>500000</MaxVisibleColumns>
<MaxVisiblePages>250000</MaxVisiblePages>
<MaxVisibleRows>5000000</MaxVisibleRows>
<MaxVisibleSections>500000</MaxVisibleSections>
<MaxCells>50000000</MaxCells>
</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>9999999</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>165000</DefaultRowsDisplayedInDownload>
<MaxVisiblePages>250000</MaxVisiblePages>
<MaxVisibleRows>5000000</MaxVisibleRows>
<MaxVisibleSections>500000</MaxVisibleSections>
<MaxCells>50000000</MaxCells>
</Table>
<Charts>
<MaxVisibleColumns>200000</MaxVisibleColumns>
<MaxVisiblePages>100000</MaxVisiblePages>
<MaxVisibleRows>200000</MaxVisibleRows>
<MaxVisibleSections>900000</MaxVisibleSections>
<JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>
</Charts>
<Cube>
<CubeMaxRecords>9999999</CubeMaxRecords>
<CubeMaxPopulatedCells>999999999</CubeMaxPopulatedCells>
</Cube>
</Views>


<Prompts>
<MaxDropDownValues>100000</MaxDropDownValues>
</Prompts>

1 comment:

  1. how to find the path for instanceconfig.xml file in \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

    ReplyDelete

Google Drive API Google Sheets Extract to CSV with SERVICE KEY.JSON Recent Client had some Google Sheets stored on their Goo...