This setting started to appear in version 11.1.2.4.007 and has been making an appearance in a few releases, PSUs, or both for Essbase server and APS, let's take a look
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.007
Defects Fixed in this Patch:
21881863 - When running large MDX queries against an Aggregate Storage Database can result in running out of memory. In this case, use this essbase.cfg setting:
Name of setting: QUERYRESULTLIMIT
Syntax: QUERYRESULTLIMIT [appname [dbname]] n
Where n is an integer value specifying the maximum number of query result cells.
See the "QUERYRESULTLIMIT Configuration Setting" topic in the "Documentation Updates in this Patch" section in this Readme.
>>>
Documentation Updates in this Patch
QUERYRESULTLIMIT Configuration Setting
Sets the maximum number of cells returned by an MDX query. This configuration setting applies to block storage, aggregate storage and hybrid aggregation databases.
Syntax
QUERYRESULTLIMIT [appname [dbname]] n
QUERYRESULTLIMIT specifies the maximum number of result cells that an MDX query can retrieve before Essbase Server terminates that query. You can apply this setting to an entire server, to all the databases in a single application, or to a single database.
If no limit is defined in essbase.cfg, there is no results limit.
When the number of returned cells for a query exceeds the result limit, an error message is returned.
Use QUERYRESULTLIMIT to limit the result volume of MDX queries and prevent a query from freezing when a very large number of result cells are returned.
Examples
QUERYRESULTLIMIT Sample Basic 100000
Sets 100,000 cells as the maximum number of results cells returned in a query to the Basic database for the Sample application.
QUERYRESULTLIMIT 150000
Sets 150,000 cells as the maximum number of cells that a query can return before being terminated. The query result limit applies to all applications and databases on Essbase Server that correspond to the essbase.cfg file containing this setting.
>>>
Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.008
Defects Fixed in this Patch
22822213 - Provider Services support for Essbase Server update for QUERYRESULTLIMIT configuration setting in the essbase.cfg file.
See the Essbase 11.1.2.3.508_22314799 PSE Readme for more information.
22976584 - Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated in this release. The results of the grid are now controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.
See the "Deprecated Essbase Properties in the essbase.properties File" topic in the "Documentation Updates in this Patch" section in this Readme.
>>>
Documentation Updates in this Patch
Deprecated Essbase Properties in the essbase.properties File
Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated.
These properties, if defined in essbase.properties, do not have any effect on the grid result.
The results of the grid are controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.
With these changes, existing use cases that expect an error for the previous lower row and column limits set in JAPI will not get an error unless the QUERYRESULTLIMIT limit is exceeded.
With these changes, Provider Services JAPI does not apply any limits against a previous version of Essbase. Essbase has to be upgraded to use the QUERYRESULTLIMIT configuration setting.
>>>
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.009
Known Issues in this Patch
QUERYRESULTLIMIT configuration setting does not honor all values in this patch.
This version of the QUERYRESULTLIMIT configuration setting documentation replaces the content in the Essbase 11.1.2.4.008 Readme. The default value is 1,000,000 and can be increased to 100,000,000 but no other value will be honored.
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.010
Defects Fixed in this Patch:
22953962, 22999617 - QUERYRESULTLIMIT setting has an upper limit of 2^31 and when the limit is set to a greater value it is treated as the value 0.
22863123, 22861985 - When using QUERYRESULTLIMIT setting and pivoting a member from column to row or when you have particular spreadsheet layout while staying within the limit setting can result in a terminated query.
Patch Set Updates for Hyperion Essbase 11.1.2.4.011 – NONE
Patch Set Updates for Hyperion Essbase 11.1.2.4.012 – NONE
Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.013
Defects Fixed in this Patch:
23666602, 23149351 - Drill Through reports can exceed the limit of the QueryResultLimit Function and an error message is returned:
Error executing report \'EEGL'\'DT_REports'\'EEPL' in the Essbase Studio, message: Runtime error. Line =671.
Patch Set Update for Oracle Hyperion Essbase 11.1.2.4.014 – NONE
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.007
Defects Fixed in this Patch:
21881863 - When running large MDX queries against an Aggregate Storage Database can result in running out of memory. In this case, use this essbase.cfg setting:
Name of setting: QUERYRESULTLIMIT
Syntax: QUERYRESULTLIMIT [appname [dbname]] n
Where n is an integer value specifying the maximum number of query result cells.
See the "QUERYRESULTLIMIT Configuration Setting" topic in the "Documentation Updates in this Patch" section in this Readme.
>>>
Documentation Updates in this Patch
QUERYRESULTLIMIT Configuration Setting
Sets the maximum number of cells returned by an MDX query. This configuration setting applies to block storage, aggregate storage and hybrid aggregation databases.
Syntax
QUERYRESULTLIMIT [appname [dbname]] n
- appname—Optional. Applies the query result limit to the application specified. If you specify appname, you must also specify a value for n, or Essbase Server ignores QUERYRESULTLIMIT. If you do not specify an application, you cannot specify a database, and the query result limit applies to all applications and databases on the server. If you specify a value for appname and do not specify a value for dbname, the query time limit applies to all databases in the specified application.
- dbname—Optional. Must be used with appname and n, or the server ignores QUERYRESULTLIMIT. If you specify dbname, appname, and n, the query result limit is applied only to the specified database.
- n—Integer value of n specifies the number of query result cells that the server allows a query to return. You must specify this parameter or the server ignores QUERYRESULTLIMIT. If you do not specify appname or dbname, the query result limit applies to the entire server.
QUERYRESULTLIMIT specifies the maximum number of result cells that an MDX query can retrieve before Essbase Server terminates that query. You can apply this setting to an entire server, to all the databases in a single application, or to a single database.
If no limit is defined in essbase.cfg, there is no results limit.
When the number of returned cells for a query exceeds the result limit, an error message is returned.
Use QUERYRESULTLIMIT to limit the result volume of MDX queries and prevent a query from freezing when a very large number of result cells are returned.
Examples
QUERYRESULTLIMIT Sample Basic 100000
Sets 100,000 cells as the maximum number of results cells returned in a query to the Basic database for the Sample application.
QUERYRESULTLIMIT 150000
Sets 150,000 cells as the maximum number of cells that a query can return before being terminated. The query result limit applies to all applications and databases on Essbase Server that correspond to the essbase.cfg file containing this setting.
>>>
Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.008
Defects Fixed in this Patch
22822213 - Provider Services support for Essbase Server update for QUERYRESULTLIMIT configuration setting in the essbase.cfg file.
See the Essbase 11.1.2.3.508_22314799 PSE Readme for more information.
22976584 - Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated in this release. The results of the grid are now controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.
See the "Deprecated Essbase Properties in the essbase.properties File" topic in the "Documentation Updates in this Patch" section in this Readme.
>>>
Documentation Updates in this Patch
Deprecated Essbase Properties in the essbase.properties File
Essbase properties service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns in the essbase.properties file are deprecated.
These properties, if defined in essbase.properties, do not have any effect on the grid result.
The results of the grid are controlled by the QUERYRESULTLIMIT configuration setting in the essbase.cfg file on Essbase Server.
With these changes, existing use cases that expect an error for the previous lower row and column limits set in JAPI will not get an error unless the QUERYRESULTLIMIT limit is exceeded.
With these changes, Provider Services JAPI does not apply any limits against a previous version of Essbase. Essbase has to be upgraded to use the QUERYRESULTLIMIT configuration setting.
>>>
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.009
Known Issues in this Patch
QUERYRESULTLIMIT configuration setting does not honor all values in this patch.
This version of the QUERYRESULTLIMIT configuration setting documentation replaces the content in the Essbase 11.1.2.4.008 Readme. The default value is 1,000,000 and can be increased to 100,000,000 but no other value will be honored.
Oracle Essbase - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.010
Defects Fixed in this Patch:
22953962, 22999617 - QUERYRESULTLIMIT setting has an upper limit of 2^31 and when the limit is set to a greater value it is treated as the value 0.
22863123, 22861985 - When using QUERYRESULTLIMIT setting and pivoting a member from column to row or when you have particular spreadsheet layout while staying within the limit setting can result in a terminated query.
Patch Set Updates for Hyperion Essbase 11.1.2.4.011 – NONE
Patch Set Updates for Hyperion Essbase 11.1.2.4.012 – NONE
Oracle Hyperion Provider Services - Release 11.1.2.4.000 Patch Set Update (PSU): 11.1.2.4.013
Defects Fixed in this Patch:
23666602, 23149351 - Drill Through reports can exceed the limit of the QueryResultLimit Function and an error message is returned:
Error executing report \'EEGL'\'DT_REports'\'EEPL' in the Essbase Studio, message: Runtime error. Line =671.
Patch Set Update for Oracle Hyperion Essbase 11.1.2.4.014 – NONE
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
AND, with all of that I think that there might be a few other points to note.
- Default value is 1000000
- The maximum value of 2^31 is equal to 2147483648 (or 2,147,483,648) that is over 2 billion, why?! Really, WHY?!?! What do you do with that many cells of data in Excel anyway?! Please don't share as I already know the answer, it starts with PI and ends with VOT table, please stop the insanity!
- QUERYRESULTLIMIT does NOT have an 'unlimited' setting
- Make sure that your Smart View client is updated to version 11.1.2.5.610 :)
- 'unpublished bug' - huh?!? BUG 16005347 (MULTIPLE MEMBER SELECTION- ZOOM IN CAUSES "ESSBASE ERROR (1013295)" IN SMARTVIEW [UNPUBLISHED & INTERNAL]) ... but glad to hear it was fixed here with
- Essbase 11.1.2.4.013, Patch 24589660
- Smart View v11.1.2.5.610, Patch 24711736
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
No comments:
Post a Comment