Friday, May 6, 2016

Essbase.cfg setting QUERYRESULTLIMIT Follow-up

I had a couple of questions that nagged me a bit from my original write-up on QUERYRESULTLIMIT.

1. What happens when you set the value to zero?

In some Essbase config file settings a zero value indicates an "unlimited" setting. In this case when I tested a zero value it actually set the query result limit to 0 cells. So I don't think there's a way to set it to unlimited. I'm not quite sure why anyone would set the limit to 0 but it's there for you if you need it.

2. Does Essbase run the query and then quit or just figure out that the query is too big and never bother running it?

From the minimal testing I did on this, it seems like Essbase figures out how large the query is going to be and throws an error if it is too big, never actually asking the database for any data. I tested this by creating two identical cubes with different QUERYRESULTLIMIT settings. Then I fired an MDX statement that took about 7 seconds on the cube with a larger setting. The cube with the smaller setting returned the error immediately. More extensive testing on this might be necessary.

Sunday, May 1, 2016

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


Add-in



Please feel free to share with me any results you get that can add to this analysis.