• Print |
• E-mail  |
• Bookmark

The SAS^® OLAP Server can provide optimized queries for the results when a NON EMPTY CROSSJOIN is used by internally using a NONEMPTYCROSSJOIN function. The server will examine the query and determine which approach is better suited to the result set.

In order for the server to perform the optimization, the query must have the following characteristics:

1. The MDX query must have a CrossJoin() on an axis with "NON EMPTY" specified. This includes CrossJoin()s in NamedSets which are directly referenced by an axis in the SELECT statement. This is a common case when using SAS Web Report Studio or other clients using information maps to access the cube.
2. No calculated members which access other dimensions specified in the query are referenced in the query. This condition is due to the internal processing of NonEmptyCrossJoin() which cannot re-use dimensions across axes. Here is an example of such a query:

   Note the use of cars in the calculated measure and in the CrossJoin()

   WITH member [Measures].[CalcMeas] as 'iif(cars.currentmember.parent is cars.[all cars].ford, 100,null)' SELECT [Measures].allmembers on columns, NON EMPTY { crossjoin( { dealers.[all dealers].finch.al }, cars.members ) } on rows FROM mddbcars

The optimization can be enabled or disabled through the use of "Optimize NON EMPTY CROSSJOIN" option available in SAS Management Console. The default is to enable this optimization.

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

Operating System and Release Information

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :