Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

MDX Tips from SQLCat

with 2 comments

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.

Written by Chris Webb

October 16, 2006 at 9:33 am

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  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.

    Jon

    Jon

    October 19, 2006 at 9:34 pm

  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  http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm
     

    chtepa

    October 23, 2006 at 4:58 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: