The SAS OLAP Server implements a NONEMPTY CROSSJOIN optimization that can greatly improve performance in queries where the OLAP cube data is sparse. The optimization removes empty crossings so that future subqueries go against a smaller number of members. The remaining members are added to the WHERE clause for SQL when a ROLAP cube is queried.

However, when a cube has dense data, the NONEMPTY CROSSJOIN optimization does not remove many rows. All the members that are not dropped are added to the SQL WHERE clause. This can result in the WHERE condition containing an extremely long IN clause.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Once the fix is in place, you have access to two new OLAP Server options: NECJPERCENTTOTAL and NECJWHEREMAX. These options enable you to specify when the NONEMPTY CROSSJOIN optimization should be bypassed due to dense data.

These two new options can be used separately or in combination. If both options are set, then the optimization is aborted only if both conditions are met.

The NECJPERCENTTOTAL Option

The NECJPERCENTTOTAL option is used to abort the NONEMPTY CROSSJOIN optimization when the number of members removed after the initial NONEMPTY subquery is greater than or equal to the percent defined by this option.

For example, if NECJPERCENTTOTAL=90 and the initial members in two sets to be crossjoined are 30 and 2730 members, SAS first does an initial subquery on each of these two sets to remove the empty members. In this example, the first set remains with 30 NONEMPTY members and the second set has 2460 NONEMPTY members. The code then performs a calculation to determine if the percent of NONEMPTY members is greater than or equal to the percent specified by this option.

In this case, the calculation is ((30 * 2460) / (30 * 2730)) = 90.1%. Since 90.1% is greater than or equal to 90%, the NONEMPTY CROSSJOIN optimization is aborted.

When the NECJMULTISETOPT has also been set, an initial query is performed that crosses all the members together. If the number of members in the result set divided by the initial set size is greater than or equal to this percent, then the NONEMPTY CROSSJOIN optimization will also be aborted. The default value for this option is 100.

The NECJWHEREMAX Option

The NECJWHEREMAX option indicates the maximum number of members that are acceptable on the WHERE clause of the SQL statement. Referring to the previous example, assume that NECJWHEREMAX=500. After the initial subqueries execute to remove empty members, there are 2490 members remaining, or 30 + 2460 NONEMPTY members. This value is greater than or equal 500, so the NONEMPTY CROSSJOIN optimization is aborted. The default value for this option is set to the value of MAXSETSIZE, which defaults to 1 million members. This option is ignored if the NECJMULTISETOPT option is set.

Operating System and Release Information

A fix for this issue for Base SAS 9.21_M3 is available at: