Just seen this interesting post on the SQLCat blog containing MDX tips, most of which I’d not heard about:
http://blogs.msdn.com/sqlcat/archive/2006/10/12/best-sql-server-2005-mdx-tips-and-tricks-part-1.aspx
I would be careful about taking them all as general rules though. I ran into the issue described about calculated members using ParallelPeriod vs hard-coded tuples earlier this year on a performanve tuning job, but couldn’t repro it in AdventureWorks or any other cube so I guessed it wasn’t as straightforward as it seemed and therefore didn’t blog about it; I suspect some of the others aren’t generally applicable either (I checked with higher authority and he agreed). That doesn’t mean they won’t be worth trying if you do have performance problems though.
Hmm … the tip about CurrentMember in calculations should definitely
be taken with a pretty huge bucket of salt. Whilst I agree
wholeheartedly with the suggestion to "Avoid unnecessary .CurrentMember
in calculations", it\’s not for reasons of performance at all.
As touched on recently here, strong hierarchies and attribute
overwriting means that CurrentMember is non-idempotent in AS2k5 – big
change from AS2k – and when deciding whether or not to include
CurrentMember explicitly in a tuple it is, IMHO, purely the semantics
you should be worrying about. Who cares what happens to the execution
plan if you\’re not getting correct results for your calculation!! In a
nutshell: to be semantically correct sometimes calculations require
explicit .CurrentMember, and sometimes they require an absence of
explicit .CurrentMember. Performance doesn\’t come into it. I would
strongly discourage anyone from removing .CurrentMember from working
calculations in an effort to improve performance, unless they
thoroughly understand the implications on attribute overwriting in
their particular scenario.
Jon
It souns funny "I checked with higher authority and he agreed". Why not hightest? Lets about pure things:
Avoid unnecessary .CurrentMember in calculations. Comments are thoughtless. To use or not to use the .CurrentMember is not only Performance question. The result of query depends on it. Look at Richard Tkachuk\’s blog http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm