A custom group of attribute elements is a means of grouping elements so that this group of sets of elements can be used in a Strategy reporting object to perform analysis that is meaningful over those defined groups.
Because custom groups may be based on varied criteria, typically SQL must be generated to retrieve the metric results for each individual set in the custom group. This also means that if more than one custom group is used on a template, SQL must be generated to retrieve the results of each combination of group of elements. This can quickly lead to a large number of SQL passes to be generated even in a simple case.
For example, consider a report with three custom groups. If Custom Group A has six elements, Custom Group B has five elements and Custom Group C has eight elements, the number of SQL passes generated would be 6 x 5 x 8 = 240.
If the report definition of a report with multiple custom groups is fairly complex, with many metrics and filtering criteria, the resulting complexity and size of the generated SQL would also be large. Since SQL is generated for each individual pass independently, no single pass may be larger than the Single Allocation Limit (if set for the Strategy Intelligence Server) but the size of the sum of all the passes and a large amount of memory may be used for report SQL generation.
The performance monitor extract below shows about 170 MB used for SQL generation for a report containing three custom groups on the template. The values shown on the vertical axis of the graph are in 10's of MB. The total number of SQL passes generated was 1760, as shown below:
Users should consider reducing the number of custom groups on a report template wherever possible or reducing the number of elements in each custom group so as to reduce the overall footprint for report execution.
A way to limit the Memory Consumption During SQL Generation is discussed in the following Strategy Knowledge base Technical Note:
KB19317: What is the Memory Consumption During SQL Generation setting in the MicroStrategy Engine?
