Problem Note 45263: Multiple queries might be generated for SAS® OLAP Server queries using the NONEMPTY CROSSJOIN optimization
 |  |  |  |
Consider the following query, which uses the NONEMPTY CROSSJOIN optimization for the SAS OLAP Server:
SELECT
MEASURES ON COLUMNS,
NON EMPTY CROSSJOIN(
{ [TIME].[All TIME].[1999].[Q4, 1999].[Oct,1999]} ,
{ [PRODUCT].[brand].MEMBERS} ) ON ROWS
FROM [sales]
This query creates the following four subqueries:
- to determine the non-emptiness of the [Time] set.
- to determine the non-emptiness of the [Product] set.
- to determine the non-emptiness of the combined [Time] * [Product] sets within the context determined by them.
- to retrieve the final data in the result set.
If you have a high cardinality in either of these sets, you might also notice that there are two long lists of members in the WHERE clause in the third and fourth queries. If you are at the top level of a hierarchy, those lists are simple IN() lists. If you are not at the top level, then the WHERE clause uses AND and OR.
Executing multiple queries, especially in constructing the long lists for the WHERE clause, could increase the response time to complete the queries.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS OLAP Server | z/OS | 9.1 TS1M2 | 9.4 TS1M0 |
| OpenVMS Alpha | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Tru64 UNIX | 9.1 TS1M2 | 9.4 TS1M0 | ||
| HP-UX IPF | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Linux | 9.1 TS1M2 | 9.4 TS1M0 | ||
| 64-bit Enabled Solaris | 9.1 TS1M2 | 9.4 TS1M0 | ||
| 64-bit Enabled AIX | 9.1 TS1M2 | 9.4 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows XP Professional | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows NT Workstation | 9.1 TS1M2 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M2 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M2 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M2 | |||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M2 | |||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M2 | 9.4 TS1M0 | ||
| Type: | Problem Note |
| Priority: | high |
| Topic: | Query and Reporting ==> OLAP (Online Analytical Processing) |
| Date Modified: | 2012-01-03 14:21:21 |
| Date Created: | 2012-01-03 11:02:51 |