Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 231

SELECT
MEASURES.MYRANK ON 0
,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
This query runs very slowly, and the problem is that every time the calculation is
evaluated it has to evaluate the Order function to return the set of ordered dates. In
this particular situation though, you can probably see that the set returned will be
the same every time the calculation is called, so it makes no sense to do the ordering
more than once. Instead, we can create a named set hold the ordered set and refer to
that named set from within the calculated measure:
WITH
SET ORDEREDDATES AS
Order
(
[Date].[Date].[Date].MEMBERS
,[Measures].[Internet Sales Amount]
,BDESC
)
MEMBER MEASURES.MYRANK AS
Rank
(
[Date].[Date].CurrentMember
,ORDEREDDATES
)
SELECT
MEASURES.MYRANK ON 0
,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
This version of the query is many times faster, simply as a result of improving
the algorithm used; the problem is explored in more depth in this blog entry:
http://tinyurl.com/mosharank
Since normal named sets are only evaluated once they can be used to 'cache' set
expressions in some circumstances; however, the fact that they are static means
they can be too inflexible to be useful most of the time. Note that normal named sets
defined in the MDX Script are only evaluated once, when the MDX Script executes
and not in the context of any particular query, so it wouldn't be possible to change
the preceding example so that the set and calculated measure were defined on the
server. Even named sets defined in the WITH clause are evaluated only once, in the
context of the WHERE clause, so it wouldn't be possible to crossjoin another hierarchy
on columns and use this approach because for it to work, the set would have to be
reordered once for each column.