Crosstab Reports and High Memory Consumption

Issue

A customer reported that while creating an Ad Hoc crosstab report using a table with only 150,000 records they encountered and OutOfMemory exception with a 4 GB heap. Checking in the database they found that the table consumed only 6.5 MB of data, so what went wrong?

Resolution

When you use a high-cardinality field (a field with a large number of unique values) in a crosstab as a group, there is the possibility of using a lot of memory. The large amount of memory is not storing the actual data values -- it's used for data structures that take part in crosstab calculations. This data is only generated when you use the field for grouping. By default, numeric fields are added to the crosstab as measures, which will not incur extra memory, but you can change any measure to a field (menu option "use as field") so that it can be used as a group. However, if you have a high-cardinality field of string type, it can be added as a group directly, and you may see this problem.

Here are a number of things that can be done to reduce huge memory usage:

• Don't include high-cardinality string fields in the domain if they're not really needed; working with huge value lists can be cumbersome so you may want to examine the need for working with these particular fields.

• Use domain security at the column level so that only experienced users can get access to the fields.

• Find another way to get the results from fields with high cardinality, perhaps by defining a calculated field based on the high-cardinality field.

• Change the configuration for baseCategorizer bean to reduce maxMembers. In applicationContext-catFactory.xml the baseCategorizer bean limits the size of a dimension, currently set to 100000. If the number of values exceeds this, the rest of the values are grouped in a node called "Other". By decreasing this value, the user could get some protection against enormous crosstabs.

  <bean id="baseCategorizer" abstract="true" class="com.jaspersoft.commons.dimengine.Categorizer"> 
       <property name="maxMembers" value="100000"/>
  </bean>