KB30391: How does the MicroStrategy Intelligence Server 9.x determine the number of prompt elements to fetch and whether or not element caching will be used for the result set.

The technical note KB19581 "Element Browsing and Caching in the Strategy Intelligence Server 8.x" explains the various settings that affect the element caching and incremental fetch features in the Strategy Intelligence Server. The Intelligence Server must balance using memory efficiently while attempting to cache as many relevant prompt elements as possible, and these settings have an impact on the Intelligence Server behaviour in this regard.
 
The different settings that affect the behaviour are listed below, and are followed by an explanation of how the Intelligence Server uses these parameters:

1. The project level row level governing "All element browsing result rows" in Strategy 9.
2. The project level governing setting "Maximum RAM usage" for element caches.
3. The attribute level settings for "Limit" (limit on the number of elements of the attribute to cache) and the "Enable element caching" setting.
4. Strategy Desktop and Web incremental fetch settings for element requests - "Maximum number of elements to display" / "Maximum number of attribute elements per block".

When a user runs a report or document containing an element prompt, the Intelligence Server generates SQL for the element request to retrieve the elements from the data warehouse. If a sort order is specified for the attribute on one of the "Browse" forms, the Intelligence Server will specify the sort order in the SQL sent to the database server. If no sort order is specified for the attribute "Browse" forms, the Intelligence Server will request the results sorted by the ID form.
 
The Intelligence Server will run two SQL statements against the data warehouse, the first to retrieve the count of all the elements to be retrieved, and the second to actually retrieve the results as shown in the examples below for the Strategy Tutorial "Item" attribute:
 
Sql
select count(distinct a21.ITEM_ID)  WJXBFS1  from LU_ITEM a21 
 
select a11.ITEM_ID  ITEM_ID,   a11.ITEM_NAME  ITEM_NAME  from LU_ITEM a11  order by  2 asc , 1 asc 
 
Even before running this SQL the Intelligence Server will compute the limit on the number of elements to retrieve. The Intelligence Server computes four times the "User Limit" (Web / desktop setting (4) above), as the number of elements to retrieve, unless this value exceeds 10% of the cache size, in which case the number of elements to retrieve is reduced to 10% of the element cache size. This computed limit is then set to be the minimum of the value from the last step and the project level element row limit (1), but never less than the original User Limit requested.
 
The query engine is now informed of the limit, as well as the offset of the number of the row to start retrieving at, and is responsible for retrieving this attribute element data. Once it has executed the SQL and retrieved the results, the elements are stored in the element cache, and populated in the prompt presented to the user.
 
In the specific case that the number of rows returned from the query engine is greater than the attribute limit (3) - the result set will not be cached. Also if the attribute is set to not use element caching (3), the results will not be cached either - however the incremental fetch policy will still be used.
 
When the user requests a prompt element that is outside of the current "block" or not in the cache, the Intelligence Server will determine the offset of the currently cached rows and query the data warehouse to retrieve and cache the relevent set of data following this same procedure. This is the incremental fetch feature for element browsing in the Strategy Intelligence Server.