locked
Use of NONEMPTY by default causing performance issues with SSAS 2008 RRS feed

  • Question

  • We are about to release an SSAS 2008 cube for access via ProClarity 6.3 SP3 Web professional, but are experiencing very poor performance on simple queries where slicers are involved.

    This is due to the issue discussed in the thread http://social.technet.microsoft.com/Forums/en-US/proclarity/thread/8f18d453-8461-4bd9-a701-0eacacb6068f. However, with our cases it is not an issue about displaying the blank rows (although we feel that the choice should DEFINITELY be there for the user and not forced), but the severe performance degradation that occurs if you have this NONEMPTY function in your query due to nested dimensions, and then add a large slicer either by selecting all the members of an attribute or by using the "Select Descendants" option on the hierarchy (this latter option uses the DESCENDANTS function and so even if the level you want on the slice is small, if the lower levels are large, they are all evaluated too).

    It appears to happen because the NONEMPTY function is evaluating all the possible results for the members in the slicer as well whatever is on the grid.

    This is causing us to reevaluate our live deployment, as the performance on thse types of queries is much slower than the equivalent in our current, AS 2000, cubes, where the addition of a slicer is instant, however many members are in it, as the query only evaluates the member selected via the WHERE clause.

    It looks as though this functionality was introduced in SP2, so we are considering rolling back to vanilla 6.3, but we know that this has at least one bug in it when using with SSAS 2008 (incorrect totals, as fixed in this hotfix http://support.microsoft.com/kb/956363/en-gb).

    Is there any prospect of this behaviour being fixed? - if the Options/NonEmptyCrossjoin box actually worked and was blank be default, then if you wanted this behaviour you could select it - as it is, you are stuck with it!

     

    Thanks

     

    Wednesday, September 8, 2010 3:25 PM

All replies

  • Hi Rachel,

    The product group is actively working on a fix for this.  I'll post back here once we have more info.

    -Sean


    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 9, 2010 1:43 AM
  • Hi Sean

    That's great news - any idea on timescales?

    Thursday, September 9, 2010 10:47 AM
  • There is now a public hotfix fix available for the forced NONEMPTY against 2008 cubes.  Removing the NONEMPTY also alleviates another issue where all possible slicer values are added on an axis with a join present.  After installing the hotfix, you will still need to go and uncheck the 'use nonempty crossjoin' box and save the view.  The difference is that now you actually can uncheck the box.

    http://support.microsoft.com/kb/2450921

    -Sean



    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 31, 2011 6:19 AM