MicroStrategy ONE
Limiting the Number of Elements Displayed and Cached at a Time
Incremental element fetching reduces the amount of memory Intelligence Server uses to retrieve elements from the data warehouse and improves the efficiency of Intelligence Server's element caching. You can set the maximum number of elements to display in the interface per element request in the Project Configuration Editor, by using the Maximum number of elements to display setting in the Project definition: Advanced category. The default value is 1,000 for Developer and 15 for Web.
Attribute element requests can be quite large (sometimes exceeding 100,000 elements). Requests of this size take a large amount of memory and time to pull into Intelligence Server and typically force many of the smaller element caches out of the element cache pool. Caching such large element lists is often unnecessary because users rarely page through extremely large element lists; they do a search instead.
When the incremental element fetching is used, an additional pass of SQL is added to each element request. This pass of SQL determines the total number of elements that exist for a given request. This number helps users decide how to browse a given attributes element list. This additional pass of SQL generates a SELECT COUNT DISTINCT
on the lookup table of the attribute followed by a second SELECT
statement (using an ORDER BY
) on the same table. From the result of the first query, Intelligence Server determines if it should cache all of the elements or only an incremental set.
The incremental retrieval limit is four times the incremental fetch size. For example, if your MicroStrategy Web product is configured to retrieve 50 elements at a time, 200 elements along with the distinct count value are placed in the element cache. The user must click the next option four times to introduce another SELECT
pass, which retrieves another 200 records in this example. Because the SELECT COUNT DISTINCT
value was cached, this would not be issued a second time the SELECT
statement is issued.
To optimize the incremental element caching feature (if you have large element fetch limits or small element cache pool sizes), Intelligence Server uses only 10 percent of the element cache on any single cache request. For example, if 200 elements use 20 percent of the cache pool, Intelligence Server caches only 100 elements, which is 10 percent of the available memory for element caches.
The number of elements retrieved per element cache can be set for Developer users at the project level, MicroStrategy Web product users, a hierarchy, or an attribute. Each is discussed below.
To Limit the Number of Elements Displayed for a Project (Affects Only Developer Users)
- In Developer, log into a project. You must log in with a user account that has administrative privileges.
- From the Administration menu, point to Projects, and then select Project Configuration.
- Expand Project definition, then select Advanced.
- Type the limit in the Maximum number of elements to display box.
To Limit the Number of Elements Displayed for MicroStrategy Web Product Users
- In MicroStrategy Web, log in to a project as a user with the Web Administration privilege.
- Click the MicroStrategy icon, then click Preferences.
- Select Project Defaults in the Preferences Level category.
- Select General in the Preferences category.
- Type the limit for the Maximum number of attribute elements per block setting in the Incremental Fetch subcategory.
To Limit the Number of Elements Displayed on a Hierarchy
- Open the Hierarchy editor, right-click the attribute and select Element Display from the shortcut menu, and then select Limit.
- Type a number in the Limit box.
To Limit the Number of Elements Displayed for an Attribute
- Open the Attribute Editor.
- Select the Display tab.
- In the Element Display category, select the Limit option and type a number in the box.
The element display limit set for hierarchies and attributes may further limit the number of elements set in the project properties or Web preferences. For example, if you set 1,000 for the project, 500 for the attribute, and 100 for the hierarchy, Intelligence Server retrieves only 100 elements.
Optimizing Element Requests
You may find the incremental element fetching feature's additional SELECT COUNT DISTINCT
query to be costly on your data warehouse. In some cases, this additional query adds minutes to the element browse time, making this performance unacceptable for production environments.
To make this more efficient, you can set a VLDB option to control how the total rows are calculated. The default is to use the SELECT COUNT DISTINCT
. The other option is to have Intelligence Server loop through the table after the initial SELECT
pass, eventually getting to the end of the table and determining the total number of records. You must decide whether to have the database or Intelligence Server determine the number of element records. MicroStrategy recommends that you use Intelligence Server if your data warehouse is heavily used, or if the SELECT COUNT DISTINCT
query itself adds minutes to the element browsing time.
Using Intelligence Server to determine the total number of element records results in more traffic between Intelligence Server and the data warehouse.
Either option uses significantly less memory than what is used without incremental element fetching enabled. Using the count distinct option, Intelligence Server retrieves four times the incremental element size. Using the Intelligence Server option retrieves four times the incremental element size, plus additional resources needed to loop through the table. Compare this to returning the complete result table (which may be as large as 100,000 elements) and you will see that the memory use is much less.
The setting is called Attribute Element Number Count Method.
To Configure Attribute Element Number Count Method
- In the Database Instance manager, select the database instance.
- From the Administration menu, select VLDB Properties.
- Under Query Optimizations, select Attribute Element Number Count Method and on the right-hand side, select one of the options:
- To have the data warehouse calculate the count, select Use Count(Attribute@ID) to calculate total element number (will use count distinct if necessary) - Default.
- To have Intelligence Server calculate the count, select Use ODBC cursor to calculate total element number.
- Click Save and Close.