Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile
• My SAS

• Print  |
• Feedback  |
• Share

  • E-mail
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

For MDX queries that use the MDX ORDER function with the ASC or DESC parameter, an unexpectedly large number of subqueries might be generated to satisfy the request. When ASC or DESC is specified, the ORDER function first arranges the members according to their position in the hierarchy, and then orders each level. This causes the OLAP Server to generate one subquery to obtain all the data, followed by individual subqueries to retrieve values cell by cell. If you have a large result set, this process could result in thousands of subqueries that might greatly impact the response time.

Following is a simple query:

select measures.quantitysum on 0,
   order(product.product_category.members ,measures.quantitysum,asc) on 1
from Starcube

This query generates the following subqueries to satisfy the result set:

select distinct PRODUCT_LINE, PRODUCT_CATEGORY, sum(QUANTITYSUM)
        from TOLAPSIO.CPLPC
        group by 001, 002
        having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
        from TOLAPSIO.YRP
        where ((PRODUCT_LINE eq 'Children'))
        group by 001
        having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
        from TOLAPSIO.YRP
        where ((PRODUCT_LINE eq 'Clothes & Shoes'))
        group by 001
        having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
        from TOLAPSIO.YRP
        where ((PRODUCT_LINE eq 'Outdoors'))
        group by 001
        having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
        from TOLAPSIO.YRP
        where ((PRODUCT_LINE eq 'Sports'))
        group by 001
        having count(*) gt 0
select distinct PRODUCT_LINE, PRODUCT_CATEGORY, sum(QUANTITYSUM)
        from TOLAPSIO.CPLPC
        group by 001, 002
        having count(*) gt 0

Click the Hot Fix tab in this note to access the hot fix for this issue. Once the hot fix is applied, the above four central subqueries will be replaced with the following:

select distinct PRODUCT_LINE, sum(QUANTITYSUM)
        from TOLAPSIO.YRP
        group by 001
        having count(*) gt 0

You can also circumvent this behavior by substituting BASC and BDESC, respectively, for ASC and BDESC. The BASC and BDESC parameters return the members in the result set without regard to the hierarchy.

Operating System and Release Information

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