Saturday, February 23, 2008

OBIEE 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.




Till next Time

16 comments:

Anonymous said...

thanks for the post, helped me ........

Mirela said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.


Sarah

http://adoptpet.info

Anonymous said...

Thanks John

Could you show me where to put the 'pivotview' part in the original file content?
Like between which two lines I should insert the pivotview part?

Thanks

John Minkjan said...

They should be between the serverinstance tags.

regards
John

Anonymous said...

Thanks john..

I only see one serverinstance tag in the file which is the third line of the file content

So do you mean above serverinstance after webconfig, or below serverinstance and above DSN where I should insert pivotview part?

I am new in OBIEE, so I am a little confused..

Thanks

John Minkjan said...

There should a closing tag also. It's between those tags. Have a look a this document:
http://knowledge.ciber.nl/weblog/OBIEE/OBIEE10340CONFIGURATIONTAGS.pdf
to see where each tag should go

regards

John

Anonymous said...

Thanks John.. This article was excellent. The document you provided is invaluable.


Rama

Hari said...

Hi

I have few questions about the CubeMaxRecords and CubeMaxPopulatedCells. If we need to change these parameters, does it impact the performance, Please let me know. For our business, we need to change these parameters drastically from 200000 to 1 million. Please let me know what impact do we have on the performance.

Thanks in advance,
Hari

John Minkjan said...

@Hari,

Of course it will influecene performance, if the BI-server has take 5 times a much cells into memory you migth run into physical memory problemns. Next to that rendering a milions cells in HTML will seriously influence perfromance of th presentation server. Consider using cubes on the DB or using aggregate tables.

Regards

john

Nawneet said...

Hi
I tried the same and pasted

-----------------------------------

500
2500
25
3000


---------------


in the file , then restarted the BI server and BI presentation server. But still the report in Dashboard for pivot table show all rows.

Have i need to do some other changes.


Nawneet.

Zoek de Haas said...

What is actually the default value for [CubeMaxRecords] ? You mentioned all the others but skipped this one, and I am very curious about this one in specific.

Thx in advance :)

Ghost said...

Hi John,

I am facing the "Exceeded configured maximum number of allowed output prompts, sections, rows, or columns" in one of the environment (say ST) but not not in my development environment.
I checked the instanceconfig.xml in both the environments and there is no difference in the file. Both the environment are pointing to the same database.

do you think I am missing something here?? ..

Thanks

Mak

nir said...

What is the equivalent process in OBIEE 11g (11.1.1.5)?

Can you please specify the max allowed values in each parameter?

I tried configure the instanceconfig.xml, but keep getting this error.

Uma Nagarajan said...

Excllent Article John.

-Uma

Anonymous said...

Hi,

I using table instead the pivot table but this error up:
Error
View Display Error

Exceeded configured maximum number of allowed output prompts, sections, rows, or columns.
Error Details
Error Codes: IRVLJWTA:OI2DL65P
Location: saw.views.dashboard, saw.subsystem.portal.pagesImpl, saw.subsystem.portal, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
SQL Issued: 34167~t0dlri931mrkcgffselhi5qcrm

Please help

Thank you

Anonymous said...

Hi John -

In 11g, I am getting the error when putting the column in the pivot section. I tried reducing the report output to limit to the default. Now, with 28 distinct values in section and 239 total number of table rows, I still get the error when I put the values in the section.

Can you please let me know the solution if any !


Thanks !