OBIEE 11g - Exceeded configured maximum number of allowed output prompts, sections, rows or columns

There are various error messages produced in OBIEE 11g around "Exceeded Configured Maximum Number of ...". In this case we are looking at Output Prompts, Sections, Rows or Columns; which is not very specific.
There are now maximum limits set on the number of columns/rows/etc in a report. Why Oracle have done this I do not know; I have not noticed any change in performance by lowering these limits. The limits need to be applied separately for the different view objects available (i.e. <Table>, <Pivot>, etc).
These limits are set in the instanceconfig.xml file used by the Presentation Services.

 Follow the Linux commands below to locate the file.

Linux Command Prompt
> cd /opt/app/obiee/fmw_home1/instances/instance1 > cd config/OracleBIPresentationServicesComponent/coreapplication_obips1 > cp instanceconfig.xml instanceconfig.xml.original > vi instanceconfig.xml

As demonstrated above I would take a backup of the original, or current version, of the file before making any changes. The default settings are shown below. In the file it is commented that these settings are managed by the Enterprise Manager - ignore these comments.

instanceconfig.xml

... </ODBC> <Views> <Pivot> <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery> <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload> <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><DisableAutoPreview>false</DisableAutoPreview> </Pivot> <Table> <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery> <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload> </Table> </Views> <Dashboard> ...

Notice that each of the objects has its own subsection within the <Views> section. There are various settings that are applied to each object type. In the update document below I have replaced this entire section. I describe each configuration item below.

After Modify instanceconfig.xml file...

instanceconfig.xml

... </ODBC> <views>   <Cube>     <CubeMaxRecords>10000000</CubeMaxRecords>     <CubeMaxPopulatedCells>100000000</CubeMaxPopulatedCells>   </Cube>   <Table>     <MaxCells>10000</MaxCells>     <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>     <MaxVisiblePages>1000</MaxVisiblePages>     <MaxVisibleRows>500</MaxVisibleRows>     <MaxVisibleSections>25</MaxVisibleSections>     <DefaultRowsDisplayed>30</DefaultRowsDisplayed>     <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>     <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>   </Table>   <Pivot>     <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>     <MaxVisibleColumns>300</MaxVisibleColumns>     <MaxVisiblePages>1000</MaxVisiblePages>     <MaxVisibleRows>500</MaxVisibleRows>     <MaxVisibleSections>25</MaxVisibleSections>     <DefaultRowsDisplayed>30</DefaultRowsDisplayed>     <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>     <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>   </Pivot>   <Charts>     <MaxVisibleColumns>2000</MaxVisibleColumns>     <MaxVisiblePages>1000</MaxVisiblePages>     <MaxVisibleRows>2000</MaxVisibleRows>     <MaxVisibleSections>25</MaxVisibleSections>     <JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>   </Charts>   <Narrative>     <MaxRecords>200000</MaxRecords>     <DefaultRowsDisplayed>30</DefaultRowsDisplayed>   </Narrative> </Views></Views><Dashboard> ...

Cube settings affect the display and processing of data in pivot tables and charts. Cube settings are listed within their own element tags, cube. In this way they are different to other tags that are largely reused between different elements.

<CubeMaxRecords> - specifies the maximum number of records returned for a view to then process. The default is 40,000 records.

<CubeMaxPopulatedCells> - specifies the maximum number of data cells that can be populated in a view; the default is 120,000 cells.

<MaxCells> - specifies the maximum number of cells to be displayed in a view; it must be specified separately for each view type and is applicable to Table and Pivot views. The default is 50,000. The MaxCells should be equal to MaxVisibleColumns (in a pivot view) multiplied by the MaxVisibleRows.

<MaxPagesToRollOutInDelivery> - specifies the maximum number of pages included in a view when it is exposed via a Dashboard; the default is 1000.

<MaxRecords> - Applicable to the narrative view, or ticker, this element specifies the maximum number of records that can be processed to create a view; the default is 40,000.

<MaxVisiblePages> - specifies the maximum number of view prompts (or pages in a PDF) to be displayed for a Chart, Table or Pivot; the default is 1000 Pages.

<MaxVisibleRows> - specifies the maximum number of rows to be displayed in a chart, pivot or table; the default is 500.

<MaxVisibleSections> - specifies the maximum number of sections to be displayed in a chart, pivot or table; the default is 25 sections.

<MaxVisibleColumns> - specifies the maximum number of columns displayed in a chart of pivot table; the default is 300.

<DefaultRowsDisplayed> - specifies the default number of rows to display in a view. It can be applied to a Narrative, Pivot, Table or Trellis; the default is 25. This number should not exceen MaxVisibleRows.

<DefaultRowsDisplayedInDelivery> - specifies the maximum number of rows to display in a view when it is exposed via a Dashboard. Applicable to a Pivot or Table, the default is 100.

<DefaultRowsDisplayedInDownload> - specifies the maximum number of rows included in a view when it is exported, such as a PDF or Excel export. It is applicable to a Pivot or Table and the default is 65,000.

<JavaHostReadLimitInDB> - specifies the maximum amount of data sent to the browser for a chart. It is not relevant to any other view object.

After Modifications must need to restart the presentation services.

Disply previous 3month names

Hi All,

This is Commen requirement, need to disply all last 3months name.

Find bellow steps :

1.Here current month is September, select DATE,Month Names Column.
2. Add Filter convert on SQL on DATE Column
Use bellow query on filter Tab

Time.Date <= cast(current_date as date) and Time.Date >= TIMESTAMPADD(SQL_TSI_MONTH, -2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Here Time.Date is my Dimession Column

3.save and see result.

Finaly Result like this

OBIEE 11g - upgrade 11.1.1.5.x & 6.0 to 11.1.1.7.0

Hi All,

Follow bellow word document steps.

https://docs.google.com/file/d/0BxBUO3DvOkSPd1N6ajdNbkkzMGs/edit


Thx,