The problem with this setting is the small number of rows allowed, as the default value is 250,000 and the maximum value is 500,000. That probably wasn't a bad limit when this setting was introduced in the very early days of Essbase. At that time, I was running a fast Pentium machine with 1 Mb of RAM and 2 1Gb SCSI drives. At that time, 500,000 rows was huge. Now, with Exalytics and 1 TB of RAM which is, if I did the math correctly, more that a million times more memory than my old Pentium, the same 500,000 row limit seems quite confining. At the same time, Essbase outlines keep growing. I have a customer who has 380,000 members in one dimension in a BSO database! If they tried to zoom in, suppress missing, on this and any other dimension, Essbase would need an SSPROCROWLIMIT of 760,000 rows if the second dimension had only 2 members. Further, the row limit enforced by this setting considers the number of rows before suppression. This means that it doesn't matter whether the query actually returns more than 500,000 rows or just a single row after suppression, the limit is used internally during the suppress missing processing.
I sent a quick email to the Essbase program manager after Open World and, like magic, he let me know that the latest Essbase version, 11.1.2.1.102, has removed the upper limit. Sure enough, I checked the readme.html for the new patch and here it is:
(11730559)
The Essbase.cfg setting SSPROCROWLIMIT no longer has a maximum value of 500,000. The maximum number of spreadsheet rows Essbase processes on a request can now be set to 16,384 or higher. It is not recommended to use a limit higher than 500,000.
I haven't tried it yet, but I am sure I will in the near future. I know a number of customers who will be excited about this change.
Posts
4 comments:
Thanks for this post Tim. Practically, do you know if is there any downside to using a really large value for SSPROCROWCOUNT? Like say a trillion? Sometimes users don't care how long the retrieval takes as long can they can come in the following morning and see a few numbers. Even with two large sparse dimensions, potential blocks can run into billions/trillions even if there are only a few hundred rows with numbers.
The downside is that allowing a larger SSPROCROWCOUNT takes up memory. If your server doesn't have enough memory, it could potentially bring down the server for all users.
Tim
Ok, thanks for that info. Do you know how much memory? And what determines how much memory? We only use Essbase and Foundation Services and our new servers have ample memory (128GB RAM) and rarely have more than 30 concurrent users at any given time.
As far as I know, Oracle doesn't publish any information about the memory requirements for expanded SSPROCROWLIMIT settings. That being said, you may be able to make a guess using the cartesian product of the number of possible member combinations that could be returned by your query times 80 bytes per member *plus* the number of possible rows times the number of columns in the query times 8 bytes (as each number is a double).
Tim
Post a Comment