Friday, October 20, 2006

Portlets with many rows

In a galaxy far, far away, during a time long, long back (in Bangalore, and on Aug 10 2005 actually), one of my first posts when I started the Oracle BI Blog was "Why You Should NOT Publish Large Worksheets to Portal".

This, then, can be seen as a followup post to that one.
Here, I show how it is possible to increase the number of rows that you want displayed in a Discoverer portlet in Oracle Portal.

As is my preference, I shall let the pictures say the thousand words (which, btw, if you were to try and apply to a data warehousing world, would be woefully inadequate... anyway - I digress).

I have a workbook called "Long Table", which has two worksheets. "200 Rows" returns only 200 rows, while "3300 Rows" returns surprise, surprise, 3300 rows (see the screenshot below).

So, if I publish both worksheets to an Oracle Portal page, I find that the "200 Rows" worksheet portlet displays without any problem. You can also notice the text next to the page drop down that says that you are viewing "Rows 1-25 of 200"'.
With the long table however, I have a problem. Since the default limit on the number of rows for a Discoverer portlet is 1000, there is an error text at the bottom of the portlet that says "Portlet data limits exceeded. Data shown may be incomplete".

The remedy is simple, though not something I would advise you pursue in haste.
If however you do want to do that, then, do this:
In the Oracle home where Discoverer has been installed - in this case it is "C:\ias", go to the discoverer/config folder, and locate the "configuration.xml" file.
Locate the lines that say maxDataRows='1000'.

Change it to some other value - in this case I changed it to 5000. Not advisable, I repeat, but the gun's in your hands, as are your feet, and I am only helping you point one in the direction of the other.
Restart your OC4J BI Forms service, reconnect to Oracle Portal, and refresh the portlet. I need to do that because when publishing the portlet I had specified that I never wanted to refresh the portlet.

While it is refreshing, and since it has to do the 'things' to process several thousand rows of data, it takes bit. If you see your task manager, you shall find that memory and cpu usage are not pretty sights. Therefore, another reason not to raise these limit values indiscriminately (have I said before that's not such a good idea?).
Anyway, once the portlet has refreshed, the long one mind you, you shall see that (i) it now says it is indeed capable of displaying the last row of data in the table (the 3300th row), and (ii) there is no error message at the bottom of the portlet saying that not all data was retrieved, because all data --was-- fetched.

And that's my last post for the week; I wish you a very happy Diwali, and see you in a bit.
Mike Durran shall be talking about and showing a very nice, useful, and frankly a very darn cool Discoverer feature that we have been working on for some time now. After his session is over (that would be next Thursday), I shall post more on that feature here.

Update: I forgot to add - thanks to Chandan for helping me with the information in this post.