MicroStrategy ONE

Results Processing

When Intelligence Server processes results that are returned from the data warehouse, several factors determine how much of the machine's resources are used. These factors include:

Intelligence Server Thread Balancing

By default, threads within Intelligence Server process tasks in the order that they are received. You can configure Intelligence Server to allocate threads to processes, such as object serving, element serving, SQL generation, and so forth, that need them most, while less loaded processes can return threads to the available pool.

To enable thread balancing for Intelligence Server, in the Intelligence Server Configuration Editor, in the Server Definition: Advanced category, select the Balance MicroStrategy Server threads check box.

Limiting the Maximum Report Size

A report instance is the version of the report results that Intelligence Server holds in memory for cache and working set results. The size of the report instance is proportional to the size of the report results, that is, the row size multiplied by the number of rows.

The row size depends on the data types of the attributes and metrics on the report. Dates are the largest data type. Text strings, such as descriptions and names, are next in size, unless the description is unusually long, in which case they may be larger than dates. Numbers, such as IDs, totals, and metric values, are the smallest.

The easiest way to estimate the amount of memory that a report uses is to view the size of the cache files using the Cache Monitor in Developer. The Cache Monitor shows the size of the report results in binary format, which from testing has proven to be 30 to 50 percent of the actual size of the report instance in memory. For instructions on how to use the Cache Monitor to view the size of a cache, see Monitoring Result Caches.

Intelligence Server allows you to govern the size of a report or request in the following ways:

Like all requests, large report instances are also governed by the Memory Contract Manager settings. For more information about Memory Contract Manager, see Governing Intelligence Server Memory Use with Memory Contract Manager.

Limiting the Number of Report Result Rows

Reports with a large number of result rows can take up a great deal of memory at run time. For example, your data warehouse may contain daily sales data for thousands of items over several years. If a user attempts to build a report that lists the revenue from every item for every day in the data warehouse, the report may use all available Intelligence Server memory.

You can limit a report's size in Intelligence Server by setting a maximum limit on the number of rows that a report can contain. This setting is applied by the Query Engine when retrieving the results from the database. If the report exceeds this limit, the report is not executed and an error message is displayed.

To set the maximum number of result rows for all reports, data marts, and Intelligent Cubes in a project, in the Project Configuration Editor, expand the Governing Rules: Default: Result Sets category, and type the maximum number in the appropriate Final Result Rows field. You can set different limits for standard reports, Intelligent Cubes, and data marts.

You can also set the result row limit for a specific report in that report's VLDB properties. The VLDB properties limit for a report overrides the project limit. For example, if you set the project limit at 10,000 rows, but set the limit to 20,000 rows for a specific report that usually returns more than 10,000 rows, users are able to see that report without any errors.

To Set the Result Set Limit for a Specific Report

  1. In Developer, right-click the report to set the limit for and select Edit.
  2. From the Data menu, select VLDB properties.
  3. Expand the Governing settings, then select Results Set Row Limit.
  4. Make sure the Use default inherited value check box is cleared.
  5. In the Results Set Row Limit field, type the limit.
  6. Click Save and Close.

Limiting the Number of Element Rows

Another way that you can limit the size of a request is to limit the number of element rows returned at a time. Element rows are returned when a user accesses a report prompt, and when using the Data Explorer feature in Developer.

Element rows are incrementally fetched, that is, returned in small batches, from the data warehouse to Intelligence Server. The size of the increment depends on the maximum number of element rows specified in the client. Intelligence Server incrementally fetches four times the number for each element request.

For more information about element requests, such as how they are created, how incremental fetch works, and the caches that store the results, see Element Caches.

MicroStrategy recommends that you set the element row limit to be larger than the maximum number of attribute element rows that you expect users to browse. For example, if the Product table in the data warehouse has 10,000 rows that users want to browse and the Order table has 200,000 rows that you do not expect users to browse, you should set this limit to 11,000. Intelligence Server incrementally fetches the element rows. If the element rows limit is reached, the user sees an error message and cannot view the prompt or the data.

To set the maximum number of element rows returned for all element requests in a project in Developer, in the Project Configuration Editor for that project, expand the Governing Rules: Default: Result Sets category and type the number in the All element browsing result rows field.

To Set the Number of Objects Returned for Requests in MicroStrategy Web

  1. In MicroStrategy Web, log in to a project as a user with the Web Administration privilege.
  2. Click the MicroStrategy icon, then select Preferences.
  3. Select Project defaults, and then select the General category.
  4. In the Incremental Fetch section, specify the values in the Maximum number of attribute elements per block and Maximum number of report objects per block fields.
  5. Click OK.

Limiting the Number of Intermediate Rows

You can limit a report's size on Intelligence Server by setting a maximum number of intermediate result rows that are allowed in Intelligence Server. This limit does not apply to the rows in intermediate or temporary tables created in the data warehouse. Rather, it controls the number of rows held in memory in the Analytical Engine processing unit of Intelligence Server for analytic calculations that cannot be done on the database. Lowering this setting reduces the amount of memory consumed for large reports. If the limit is reached, the user sees an error message and cannot view the report. For example, this may happen when you add a complex subtotal to a large report or when you pivot a large report.

To specify this limit for all reports in a project, in the Project Configuration Editor, select the Governing Rules: Default: Result Sets category and type the number in the All intermediate result rows box.

You can also set the intermediate row limit for a specific report in that report's VLDB properties. The VLDB properties limit for the report overrides the project limit. For example, if you set the project limit at 10,000 rows but set the limit to 20,000 rows for a specific report that usually returns more than 10,000 rows, users are able to see that report without any errors.

To Set the Intermediate Row Limit for a Specific Report

  1. In Developer, right-click the report to set the limit for and select Edit.
  2. From the Data menu, select VLDB properties.
  3. Expand the Governing settings, then select Intermediate Row Limit.
  4. Make sure the Use default inherited value check box is cleared.
  5. In the Intermediate Row Limit field, type the limit.
  6. Click Save and Close.

Limiting the Size and Number of Intelligent Cubes

If you have purchased OLAP Services licenses from MicroStrategy, your report designers can create Intelligent Cube reports. These Intelligent Cubes must be stored in Intelligence Server memory for reports to access their data. This may cause a shortage of memory for other processes on the Intelligence Server machine.

You can govern the amount of resources used by Intelligent Cubes by limiting the amount of memory used by Intelligent Cubes and by limiting the number of Intelligent Cubes that can be loaded into memory.

To specify these settings, in the Project Configuration Editor for the project, select the Cubes: General category and type the new values in the Maximum RAM usage (MBytes) and Maximum number of cubes fields. For detailed information on governing Intelligent Cube memory usage, see Defining Memory Limits for Intelligent Cubes.

Limiting the Memory Used During Data Fetching

Certain MicroStrategy features enable you to fetch data from external data sources, such as web services, MDX cubes, or Excel spreadsheets. When data is fetched from one of these data sources, it is temporarily stored in Intelligence Server memory while being converted to a report. This can cause a shortage of memory for other processes on the Intelligence Server machine.

You can govern the amount of memory used for an individual data fetch in the Project Configuration Editor. Select the Governing Rules: Default: Result Sets category, and type the new value in the Memory consumption during data fetching (MB) field. The default value is -1, indicating no limit.