MDX Tips from SQLCat

Just seen this interesting post on the SQLCat blog containing MDX tips, most of which I’d not heard about:

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.

2 thoughts on “MDX Tips from SQLCat

  1. 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.


  2. 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

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.