none
MDX performance issue

    Question

  • Hi All,

    I have one report's parameter, the query is below:

    WITH MEMBER [Measures].[ParameterCaption] AS [DD DATE].[PK Date].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [DD DATE].[PK Date].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [DD DATE].[PK Date].CURRENTMEMBER.LEVEL.ORDINAL

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]}

    ON COLUMNS ,

    Order(FILTER([DD DATE].[PK Date].ALLMEMBERS,
    [Measures].[FACT CONTRACT Count]>0),[DD DATE].[PK Date].CURRENTMEMBER.UNIQUENAME, desc)

     ON ROWS FROM [ContractSchedule]

    Which take more than 10 minutes to execute, could anyone show some ideas to optimize the performance? Thanks in advance.

    Saturday, February 02, 2013 3:12 AM

Answers

  • Hi,

    please go through these link

    http://www.mssqltips.com/sqlservertip/2565/ssas--best-practices-and-performance-optimization--part-1-of-4/

    http://msdn.microsoft.com/en-us/library/bb934106(v=sql.105).aspx

    HTH


    Best Regards,
    Silna
    Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

    • Proposed as answer by RichardLees Monday, February 04, 2013 4:56 AM
    • Marked as answer by Jeddd Tuesday, February 05, 2013 1:34 AM
    Saturday, February 02, 2013 4:22 PM
  • Those tips should help you.  However, I might be able to offer some shortcuts.

    1. nonempty is generally faster than a filter.  So you might try nonempty(..., Measures.[Fact Contract Count]).  If [Fact Contract Count] has an aggregation of sum, and there are facts with a value of zero, then nonempty will not filter the zero records out.  Hopefully, you don't have zero, or the aggregation is Count, in which case happy days.  If you do use zero values in the fact table, you can still employ the nonempty() function inside the filter.
    2. Do you need to Order() the rows, as you are selecting dates, and they should already be ordered in the dimension.   Unlike SQL Server, SSAS won't disrupt the order simply because you have a filter (or nonempty).  Perhaps you could leave them in ascending sequence and have the report default with the last one.  That's quite easy in RS with another dataset for the default.

    Hope that helps,

    http://RichardLees.blogspot.com


    Richard

    • Proposed as answer by RichardLees Monday, February 04, 2013 4:55 AM
    • Marked as answer by Jeddd Tuesday, February 05, 2013 1:34 AM
    Monday, February 04, 2013 4:55 AM

All replies

  • Hi,

    please go through these link

    http://www.mssqltips.com/sqlservertip/2565/ssas--best-practices-and-performance-optimization--part-1-of-4/

    http://msdn.microsoft.com/en-us/library/bb934106(v=sql.105).aspx

    HTH


    Best Regards,
    Silna
    Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

    • Proposed as answer by RichardLees Monday, February 04, 2013 4:56 AM
    • Marked as answer by Jeddd Tuesday, February 05, 2013 1:34 AM
    Saturday, February 02, 2013 4:22 PM
  • Those tips should help you.  However, I might be able to offer some shortcuts.

    1. nonempty is generally faster than a filter.  So you might try nonempty(..., Measures.[Fact Contract Count]).  If [Fact Contract Count] has an aggregation of sum, and there are facts with a value of zero, then nonempty will not filter the zero records out.  Hopefully, you don't have zero, or the aggregation is Count, in which case happy days.  If you do use zero values in the fact table, you can still employ the nonempty() function inside the filter.
    2. Do you need to Order() the rows, as you are selecting dates, and they should already be ordered in the dimension.   Unlike SQL Server, SSAS won't disrupt the order simply because you have a filter (or nonempty).  Perhaps you could leave them in ascending sequence and have the report default with the last one.  That's quite easy in RS with another dataset for the default.

    Hope that helps,

    http://RichardLees.blogspot.com


    Richard

    • Proposed as answer by RichardLees Monday, February 04, 2013 4:55 AM
    • Marked as answer by Jeddd Tuesday, February 05, 2013 1:34 AM
    Monday, February 04, 2013 4:55 AM