locked
Using NON EMPTY for Measures RRS feed

  • Question

  • Hi All,

    I have a MDX query which needs to be optimized. The query is like this :

    Select [Measures].[Measures1],

              [Measures].[Measures2],

              [Measures].[CalculatedMeasure] ON COLUMNS,

    {

    [DimTable].[AttrbuteName].[CalculatedMember1],

    [DimTable].[AttrbuteName].[CalculatedMember2],

    [DimTable].[AttrbuteName].[CalculatedMember3],

    .

    .

    .

    [DimTable].[AttrbuteName].[CalculatedMember101 ],

    } ON ROWS

    FROM ((parameter1) on COLUMNS

    from ((parameter2) on columns,

    ...

    from ((parameter6) on columns,

    from [CubeName]))

     

    I used NON EMPTY keyword against measures and now the query has shown great amount of performance improvement.

    The NEW query is as such :

    Select NON EMPTY [Measures].[Measures1],

              [Measures].[Measures2],

              [Measures].[CalculatedMeasure] ON COLUMNS,....

    I would like to know, if this approach is correct. Can we use NON EMPTY against measures.

    Thanks..


    -Nitin Pawar
    Wednesday, June 8, 2011 4:29 AM

Answers

  • I would like to know, if this approach is correct. Can we use NON EMPTY against measures.

    Yes, you can use NON EMPTY against measures (you can put it on any of the visible axis) but if often has little effect against measures. It's also very unlikely to account for a large change in performance. The NON EMPTY clause on the axis is evaluated very late in the process of returning the query results, the main performance change you would see is possibly less rows or columns being transferred from the server to the client. If you are seeing the same amount of rows and columns then the performance is more likely due to the data being cached on the server than the NON EMPTY clause. Try taking it out and see if the performance drops again.

     


    http://darren.gosbell.com - please mark correct answers
    • Proposed as answer by Jerry Nee Saturday, June 11, 2011 4:57 AM
    • Marked as answer by Jerry Nee Saturday, June 25, 2011 6:14 AM
    Wednesday, June 8, 2011 7:28 AM