High Cardinality Limits

SAS Visual Analytics imposes limits on the amount of data that can be displayed in an individual report object. The limits are designed to improve performance and minimize the risk of crashing the web browser or the Report Data service. If you exceed these limits, the behavior of the report object varies. For example, a list table might render only a portion of the results, but a crosstab might not render any data.

CAUTION: Making changes to any of the cardinality properties is done at your own risk, and it is strongly recommended that you test modifications in a non-production environment first. With the exception of MaxRowsLookup, these properties impact all report objects and could lead to unsatisfactory results. For example, performance might be significantly worse, or the Report Data service might crash. 

About the Properties

The following properties control the maximum number of rows that are returned for a given report object:

 

Property Name	Default Value	Pre-Defined or Supplemental	Description
MaxRowsLookup	Varies per report object	Pre-defined	Refer to the SAS Visual Analytics documentation for more information. This limit can be controlled either globally from SAS® Environment Manager, or on a per-report basis when you edit the report and select to Override the System Data Limit. This value is in rows.
defaultMaxCellsProduced	250000	Pre-defined	Client-side limitation. This value represents the maximum number of cells generated in a result set for a report object that the client will attempt to render.
midtierCellLimit	800000	Supplemental	Server-side limitation. This value represents the maximum number of cells generated in a result set for a report object that the Report Data service allows for processing.
midtierAllocationByteLimit	10000000	Supplemental	Server-side limitation. This value is in bytes and represents the total amount of space to allow for a single result set. It is calculated based on the length of a single row of data multiplied by the number of rows returned.
protobufSizeLimit	64000000	Supplemental	Server-side limitation. This value is in bytes, and is related to the length of all categorical data items in a single row of data in the result set. It is calculated based on this length multiplied by the number of rows returned.
categoryCardinalityServerLimit	50000	Supplemental	Server-side limitation. This value represents the maximum number of rows when categories are grouped together.

 

Calculating the Number of Rows and Cells

For most report objects, you can use the following methods to calculate the number of rows and cells a report object will generate:

To calculate the number of rows:

1. Concatenate all categories together.
2. Calculate the distinct count of this concatenation.

Note: For crosstabs, you must do these steps once for all categories used in the Column role, and once for all categories used in the Row role, and then multiply those two values together. 
 

To calculate the number of cells:

• Multiply the total number of rows for the object by the total number of data items used in the object.

Setting Properties

All of these properties can be defined for the Report Data service in SAS Environment Manager. The MaxRowsLookup value can also be set per report by editing the Override the Server Limit option for any report object.

To set the properties, you can follow these steps:

IMPORTANT: Do not use commas in any cardinality property values.

1. Log on to SAS Environment Manager as an Administrator.
2. Navigate to Configuration ► All Services ► Report Data Service.
3. Edit the sas.reportdata.properties section.
4. The defaultMaxCellsProduced and MaxRowsLookup properties are predefined and can be adjusted here. 
5. If you need to adjust any of the other cardinality properties, click the Add Property link under Supplemental Properties, and add the property name and value. 
   • Note: If you need to adjust the midtierAllocationByteLimit or protobufSizeLimit, first check to see if your CAS table is using VARCHAR types for character data items. VARCHAR lengths can be much higher than the actual values of data items, and it is recommended you first adjust these lengths before making any adjustments to the midtierAllocationByteLimit and protobufSizeLimit values. Otherwise, SAS recommends setting each to 100000000 as a starting point.
6. Restart the report data service to ensure the changes have been set. 
7. Close and re-open the report in SAS Visual Analytics.

If you sometime later delete these properties from the Report Data service, you must restart the Report Data service in order for those changes to be picked up. Otherwise, SAS Visual Analytics continues to use the previously defined values for the properties until the service is restarted.

Troubleshooting Cardinality Problems

• Use the Query Diagnostic Tool (SAS KB0036172) to re-execute the failing query, and review the BI Data log to see what properties might be limiting the number of rows returned. For example, a list table returns 22,000 rows instead of the expected 40,000 rows. In the BI Data log (shown on the Server Log tab in the diagnostics), look for a section near the bottom of the log like this:
  
  sas.reportdata.defaultMaxCellsProducedrow limit :62500 sas.reportdata.custom.protobufSizeLimitrow limit :56000 sas.reportdata.custom.expectedMaxCharsPerColumn sas.reportdata.custom.midtierAllocationByteLimit mid tier protect row limit:22000 sas.reportdata.custom.expectedMaxCharsPerColumn sas.reportdata.custom.midtierAllocationByteLimit aggregate estimate row limit:2300 sas.reportdata.custom.expectedMaxCharsPerColumn sas.reportdata.custom.midtierAllocationByteLimit summary estimate row limit:4700 sas.reportdata.custom.categoryCardinalityServerLimit row limit:50000
  
  This information tells you what the maximum number of rows are with respect to each property. Search this information for a match with the number of rows you are actually getting. For example, from the defaultMaxCellsProduced property, you see that the rows are limited to 62,500. From the protobufSizeLimit property, you see that the rows are limited to 56,000. From the midtierAllocationByteLimit property, you see that rows are limited to 22,000, which is a match for the number of rows that are being displayed in the list table. 
  
  Therefore, ​​​midtierAllocationByteLimit should be added to the Report Data service properties and increased.
   
• Sometimes, the BI Data log is not generated in the Query Diagnostic Tool. If that happens, you should enable debugging on the com.sas.bidata component for the Report Data service and replicate the problem. The reportdata microservice log will contain the same section that lists the properties and expected row limits. It also will contain additional errors that might provide a hint about where the problem is. To enable debugging, perform these steps:

1. Log on to SAS Environment Manager as an Administrator.
2. Navigate to Configuration ► All Services ► Report Data Service.
3. Edit the logging.level: com.sas.bidata section.
4. Set the level to DEBUG.
5. Click Save to save changes.
6. Wait 30 seconds to allow the Report Data service to pick up the changes.

• After you enable debugging, some common errors related to cardinality are:

  • 2018-10-16 09:54:23.466 DEBUG 7097 --- [o-auto-1-exec-3] com.sas.bidata.packager.PackageResult    : sasdemo(ef88449c) [5860492992037d1b] Reducing row count for transferred cell maximum.
    
    This exception indicates that the defaultMaxCellsProduced limit has been exceeded.
     

  • 2018-10-16 10:07:02.735 DEBUG 32732 --- [o-auto-1-exec-2] com.sas.bidata.error.DataErrorHandler    : sasdemo(ef88449c) [14199839754f47f8] dataTruncatedToNothing
    java.lang.Exception: Too much data it has been truncated.
    
    This error is fairly generic and is trickier to diagnose. Typically, this indicates that the report object's MaxRowsLookup value has been exceeded, and that value should be increased. This might require trial and error with the various cardinality properties to overcome. Start by increasing MaxRowsLookup. If that does not resolve the problem, then increase categoryCardinalityServerLimit.
     

  • 2018-10-15 12:27:08.379 DEBUG 29691 --- [o-auto-1-exec-9] com.sas.bidata.error.DataErrorHandler    : sasdemo(2463ed96) [3f7c58a2fcad5eae] dataTruncated
    java.lang.Exception: Too much data to normalize truncated to preserve heap space
    
    This exception indicates that the midtierCellLimit value has been exceeded.
     

  • com.sas.cas.CASException: The analytic server action stopped because the number of elements in the result set exceeds the specified limit (50000).
    
    This exception indicates that the MaxRowsLookup value or the categoryCardinalityServerLimit value has been exceeded.