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.

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.

FAQ: MDX Crossjoin()

This is a post to address one of the most frequently asked MDX related questions or issues. Someone will ask: why isn't this CrossJoin function working?

CrossJoin({[Actual],[Budget]},{[Account_123]},{[Jan]})

The reason the above won't work is that the function is being fed three arguments and the CrossJoin function will only accept two. The CrossJoin function will take two sets and expand them into all possible combinations of tuples. In order to expand more than two dimensions, one needs to use more than one CrossJoin. The above can be re-written correctly as:

CrossJoin(
    CrossJoin({[Actual],[Budget]},{[Account_123]}),
    {[Jan],[Feb]})

And will yield a set containing the following tuples:

([Actual],[Account_123],[Jan])
([Actual],[Account_123],[Feb])
([Budget],[Account_123],[Jan])
([Budget],[Account_123],[Feb])

You can nest as many CrossJoins as you like, adding one additional function for each new dimension you wish to expand.

On a related note, I also see people asking a similar question that I'd give a slightly different answer to. Why isn't this CrossJoin function working?

CrossJoin({[Actual]},{[Account_123]},{[Jan]})

The same answer still applies here that too many arguments are being sent to the CrossJoin function. However, in this case, no CrossJoin function is needed as there is only a single member from each dimension. Simply using a tuple here will be sufficient. That would be written as follows:

([Actual],[Account_123],[Jan])

Patching Essbase 11.1.2.4.x Recommendation

I'm sure everyone who patches their Essbase server reads all of the documentation thoroughly but just in case... Please note that the following recommendation is included in the Essbase 11.1.2.4.x patches:

Recommendation: After you apply a patch within the same release codeline, Oracle recommends as a best practice that you export the data from your databases, clear the data from the databases, and then reload the data.

For customers with a handful of databases this probably isn't a big deal. But for shops with 500 databases covering many terabytes of disk this could be a huge task literally requiring months of effort.

My best guess is that they've changed the format of files being written to disk but I'm not really sure. Also note that it's a recommendation. But not following those can come back to bite you when you're trying to fix a production down issue in the middle of the night and the level 1 support technician asks very casually, "did you do an export, clear and load after you patched this server?" You've been warned.