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

[Account].[Account].[Account].MEMBERS*
[Date].[Date].[Date].MEMBERS
,10
,[Measures].[Amount]
)
)
MEMBER [Measures].TEST1 AS
[Measures].[Internet Sales Amount] / [Measures].Denominator
MEMBER [Measures].TEST2 AS
[Measures].[Internet Tax Amount] / [Measures].Denominator
SELECT
{
[Measures].TEST1
,[Measures].TEST2
} ON 0
,[Customer].[Gender].[Gender].MEMBERS ON 1
FROM [Adventure Works]
The query runs much faster, simply because instead of evaluating the Count
twice for each of the two visible calculated measures we evaluate it once, cache the
result in the calculated measure Denominator and then reference this in the other
calculated measures.
It's also possible to find situations where you can rewrite code to avoid
evaluating a calculation that always returns the same result over different
cells in the multidimensional space of the cube. This is much more difficult
to do effectively though; the following blog entry describes how to do it in
detail: http://tinyurl.com/fecache .
Tuning the implementation of MDX
Like just about any other software product, Analysis Services is able to do some
things more efficiently than others. It's possible to write the same query or
calculation using the same algorithm but using different MDX functions and see a
big difference in performance; as a result we need to know which are the functions
we should use and which ones we should avoid. Which ones are these though? The
trouble is that this changes from version to version of Analysis Services, even from
cumulative update to cumulative update, and what constitutes good practice in one
scenario might not work in another. The only advice we can offer is to read as many
blog posts on MDX query performance as you can, and if you identify a problem in
your code, search the Web to see if someone has experienced something similar and
already solved the problem.