SQL Server Developer Center > SQL Server Forums > SQL Server Analysis Services > MDX date diff average query optimization
Ask a questionAsk a question
 

QuestionMDX date diff average query optimization

  • Wednesday, November 04, 2009 4:12 PMPoleB Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    I have a query to get the average of the differences between two dates, but I don't know if there is a more efficient query syntax.  Anyone have any ideas?

    with
    member [Measures].[AvgShipTurn] as
    sum(
     filter(
      nonempty([Sales Summary Order Details].[Sales Order Number].[Sales Order Number].members
       * [Ship Date].[Calendar].[DATE]
       * [Delivery Date].[Calendar].[DATE]
      ,
       [Ship Date].[Calendar].currentmember
      )
     ,
      [Ship Date].[Calendar].membervalue <= [Delivery Date].[Calendar].membervalue
     )

     datediff("d",[Ship Date].[Calendar].membervalue, [Delivery Date].[Calendar].membervalue)
    )
    /
    sum(
     filter(
      nonempty([Sales Summary Order Details].[Sales Order Number].[Sales Order Number].members
       * [Ship Date].[Calendar].[DATE]
       * [Delivery Date].[Calendar].[DATE]
      ,
       [Ship Date].[Calendar].currentmember
      )
     ,
      [Ship Date].[Calendar].membervalue <= [Delivery Date].[Calendar].membervalue
     )

     [Measures].[Order Count]
    )
    , NON_EMPTY_BEHAVIOR = [Measures].[Order Count]
    SELECT { [Measures].[AvgShipTurn] } on 0,
    non empty
    [Ship Date].[Calendar].[Month]
     on 1
    FROM [Adventure Works]

All Replies

  • Wednesday, November 04, 2009 10:31 PMPoleB Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     

    I changed the query to use the avg function and that works.  I must have been using different date hierarchies during testing. 
    I still don't know if there is a better way though:
    with
    member [Measures].[AvgShipTurn] as
    avg(
     filter(
      nonempty([Sales Summary Order Details].[Sales Order Number].[Sales Order Number].members
       * [Ship Date].[Calendar].[DATE]
       * [Delivery Date].[Calendar].[DATE]
      ,
       [Ship Date].[Calendar].currentmember
      )
     ,
      [Ship Date].[Calendar].membervalue <= [Delivery Date].[Calendar].membervalue
     )

     datediff("d",[Ship Date].[Calendar].membervalue, [Delivery Date].[Calendar].membervalue)
     / [Measures].[Order Count]
    )
    , NON_EMPTY_BEHAVIOR = [Measures].[Order Count]
    SELECT { [Measures].[AvgShipTurn] } on 0,
    non empty
    [Ship Date].[Calendar].[Month]
     on 1
    FROM [Adventure Works]

    When I run it through:
    http://mdx.mosha.com/default.aspx
    I get the following.  I just can't see a way to implement those changes without breaking the query.
    Anyone know?

    Line Col Message Link
    4 3 Function 'Filter' was used inside aggregation function - this disables block computation mode More Info
    5 85 This set causes the expression to evaluate to the same value over different coordinates. Consider redirecting to coordinate with non-varying attribute to take advantage of cache More Info
    9 28 MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect More Info
    12 69 MDX function 'MemberValue' is not optimized for block computation mode More Info
    12 27 MDX function 'MemberValue' is not optimized for block computation mode More Info
    15 79 MDX function 'MemberValue' is not optimized for block computation mode More Info
    15 39 MDX function 'MemberValue' is not optimized for block computation mode More Info
    18 4 AS2008 only: Consider removing NON_EMPTY_BEHAVIOR property for '[Measures].[AvgShipTurn]'