MDX date diff average query optimization
- 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
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]'


