Smart View - Query Govenors, Limits & Choices, Oh My!

This post is about Essbase, HFM & Planning queries run in Smart View.
Opinions on other data Providers in Smart View are protected by the innocent.

There used to be a simple solution to the way you were able to govern and or limit run-away queries and report grids in Smart View. As many of us know, users, all kinds of users, do not always know, how, when, from where or even sometimes why an application or cube was built in the first place. In that, the answer to finding data sometimes results in LARGE Smart View Ad-hoc queries-reports, etc.

With all that said, over time the settings and-or configurations that a Hyperion Administrator has to limit potential runaways from happening has changed. In many cases, in Essbase-Planning there was the simple ability to change the default setting of 5000 max rows (Office 2000-2003 to the Excel maximum row limit of 65,536 (sadly, I have that number memorized and here is the Wiki context, long version of why)) and in Excel 2007, 2010, 2013, & 2016 the maximum row limit is 1,048,576; no matter if you have a 32-bit or 64-bit version of Office.

For HFM, you are bound by time. Sounds profound! The setting for HFM is time bound (in minutes) and the default is set to 31 minutes (not sure why this was the precise number of minutes, maybe there was a time study done on the Finance functions of the world) on the server side, session timeout (web.xml).

For Essbase, it is no longer the case to simply limit rows & columns, as it is now deprecated, starting in Essbase version 11.1.2.4.008!

This means you can't simply change the APS setting anymore for Max rows & columns to let's say, 1M rows and 256 columns.

So, here are a few of your configuration options instead:

• SSPROCROWLIMIT: (Suppression REQUIRED! #Missing Rows) - this setting in Smart View applies to any grid or query (11.1.2.4 tech ref). Be careful with this setting as there are many 'notes' as to who wins when, why and how...

SSPROCROWLIMIT n

The default value is 250,000

• APS: (Smart View Only) - maxrows, maxcolumns only up until Essbase version 11.1.2.4.008 as mentioned above

• QUERYRESULTLIMIT: This applies to any MDX or Spreadsheet query
• QUERYGOVEXECTIME: Sets the maximum amount of time a query can use to retrieve and deliver information before the query is terminated (any query)

QRYGOVEXECTIME [appname [dbname]] n

• QUERYGOVEXECBLK:Sets the maximum number of blocks that a query can access before the query is terminated. This setting does not apply to aggregate storage databases (ASO), BSO only

 QRYGOVEXECBLK [appname [dbname]] n



Note: For the 'QUERY' settings, these place limits on queries, if any one of the limits are reached, it will prevent the query from being returned.

Lastly, if I didn't state this, I wouldn’t be able to sleep at night. Be careful kids, there are only a few settings out there for trying to set boundaries and limits for our runaways (queries that is). Always keep in mind, that some of the settings contradict or override others. Some settings only apply to certain database types. And there may, or may not, possibly be.. undocumented settings still lurking out there!