Everyday Essbase: Essbase.cfg setting QUERYRESULTLIMIT

I had to piece together information from a few readme files to figure out what happened with the QUERYRESULTLIMIT essbase.cfg setting. It looks like the most complete information is in the Readme for the Essbase 11.1.2.3.508_22314799 patch set which you can find here.

In a nutshell, this new Essbase configuration file setting now controls the maximum number of rows and columns that Essbase will return. This was formerly controlled by the essbase.properties APS file. The service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns settings are deprecated as of Essbase versions 11.1.2.3.508_22314799+ and 11.1.2.4.008+. I'm not sure why 11.1.2.4.008 since the setting is included in 11.1.2.4.007 -- possibly because the APS settings aren't yet deprecated in that version.

I think it is important to note that the setting specifies the maximum number of cells, not rows, to an MDX or spreadsheet query. It's interesting to me that now I don't think there's a way to limit users to 256 columns on their sheet.

Also note that patch .009 warns: The default value is 1,000,000 and can be increased to 100,000,000 but no other value will be honored. I don't like this wording -- it makes it seem like the only allowable values are 1,000,000 and 100,000,000 but that's not true. The original readme file specifies an upper limit for 2^31 which is just over 2 billion cells. I want to talk to the user who needs that much data. For now I'm assuming all values between 0 and 100,000,000 are acceptable.

Like most Essbase configuration settings, the setting can apply to either the entire server or you can set it on a database level.

Syntax

QUERYRESULTLIMIT [appname [dbname]] n

Some questions I had that I tested out on 11.1.2.4.009.

1. Do member names count toward the number of cells?
No, they do not.

2. Does the setting apply to Report Scripts?
I don't think so. I tested it out and never got an error. I found no mention of Report Scripts in the documentation.

3. Does the setting apply to the old Excel add-in?
Yes it does.

4. I don't understand the .009 documentation -- which values work?
I successfully tried the following settings using 11.1.2.4.009:
1,000
10,000
100,000
1,000,000
2,000,000

I couldn't get Smart View to give me back 100,000,000 cells so I didn't test those upper limits. I'm going to assume that the documentation means that any value entered above 100,000,000 will be treated as 100,000,000.

5. What happens when you hit the limit?

MDX:
MAXL> SELECT {[Jan],[Feb],[Mar],[Apr]} on columns,
2>
3> [Stores].members
4>
5> on rows
6>
7> from [ASOsamp.Sample];

ERROR - 1200766 - The number of query result cells exceeds the limit [1000] set by the QUERYRESULTLIMIT configuration setting.
ERROR - 1241101 - Unexpected Essbase error 1200766.

Smart View