Chris Webb's BI Blog

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

Calculated members are better than assignments to real members (at least sometimes)

with 6 comments

Earlier this year I blogged about how, in my experience, using real members and overwriting them with MDX Script assignments sometimes seemed to perform worse than using calculated members. See here for the full posting:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1598.entry

Recently I came across this problem again but was able to get together a proper repro and open a support case with Microsoft. What was happening was that I had a time utility dimension with three members – let’s call them member A, member B and member C. A was the default member and had no calculation, it just showed the real values from the rest of the cube; B was a straightforward YTD calculation; C was a really nasty, untuned, same period previous year calculation with lots of special logic. I had a query that included A and B but not C and which was performing really badly, and I found that when I commented out the calculation for C the query returned instantly. So I guessed that for some reason the calculation for C was being evaluated when the query ran, even though C wasn’t actually requested in the query and this was confirmed by Microsoft.

Unfortunately I was also told this behaviour was ‘by design’, something to do with prefetching and sonar. Thankfully it doesn’t happen for all queries or all cubes (I tried and failed to repro it on Adventure Works) but in my opinion there should never be a situation where the formula engine evaluates calculations that aren’t needed – precisely because in some cases it will mean the query runs much longer than it ever should. Probably the worst thing about all this is that you have no idea when exactly it’s happening with the tools we’ve got available at the moment. I suspect that many people out there have poorly-performing cubes because of this issue…

One of the workarounds was to use calculated members instead of real members, as I recommended in my original post, but as I also said in that post there are some cases where calculated members are not a good alternative to real members. In my particular case I was able to tune the calculation associated with member C so either the problem didn’t happen or C evaluated so quickly it didn’t matter – I’m not sure which. Anyway, I stand by what I said in my original posting: use calculated members rather than real members overwritten by Script assignments wherever you can!

Written by Chris Webb

October 20, 2008 at 5:00 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  1. Chris,
     
    I have to disagree.  The scenario you lay out above does make a compelling argument based on performance of a particular situation (and I aggree, with a bug in the software).  Also, if you take a relational approach to cube architecture, where you only have calculated measures, I would agree with you.
     
    But, if you have a robust analytical system and are using calculations along more than just measures; real members and assignments is the way to go.  In a complex environment, calculated members are a blunt instrument.  You run into collissions across the calced members in different dimensions (the edge calc problem) and end up reordering the calced members (moving them in the calc script or solve order or even worse both) over and over. 
     
    Real members with scope and assignments is a surgical tool that gives you exactly the right control over the calculations. 
     
    Dan

    Dan

    October 21, 2008 at 3:28 am

  2. I agree that calculated members have a lot of drawbacks (which I\’d like to see fixed – see here: http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1877.entry) but I suppose it all depends on whether you get good performance or not: if your queries take minutes with real members and seconds with calculated members then whatever the manageability overhead you\’re going to go with calculated members.
     
    Can you be a bit more specific about the \’calc edge\’ problem? I don\’t really understand how using real members and assignments gives you any benefit here over calculated members, since both are subject to solve order. Apart from all the issues of ordering and placing of calculated members on a hierarchy, from an MDX point of view there\’s nothing I can do with a real member than I can\’t do with a calculated member; using Custom Member Formulas is perhaps the exception because of the closest pass wins rule. 
     
    The other issue to take into account here is whether you want the results of your calculation to be aggregated up through the rest of your cube. Sometimes you do, and sometimes you don\’t – and when you don\’t then calculated members make life a lot easier.

    Chris

    October 21, 2008 at 10:06 am

  3. > from an MDX point of view there\’s nothing I can do with a real member than I can\’t do with a calculated member
     
    I think there are more differences:
     
    1. Autoexist. It is natural with real members because they come from dimension table, but calculated members are defined in hierarchy, not in dimension, and relation to other attributes is unclear.
    2. Dimension security – you can secure real members, but not calculated members.

    3. Dimension members can have member properties, both custom ones and builtin ones (like Key etc).4. Differences in treatment in multiselect (both in WHERE and subselect), drillthrough, SQL queries, attribute actions etc
    5. Probably I missed few more

    Mosha

    October 30, 2008 at 5:14 pm

  4. Chris

    Did you ever get any feedback on this issue? I am having a similar problem. My cube has a large number of real member values that are overwritten by MDX Script assignments and I get the following performance anomalies with queries:

    1. A query for a member that IS overwritten comes back in 1 second.
    2. A query for a member that is NOT overwritten comes back in 10 seconds.

    Although the second query does not have anything to do with the assignment parts of the script its performance is directly affected by their presence in the script. If I take all of the assignments out of the script the 2nd query runs in 1 second.

    More confusingly, when the assignments are in the script, if I take the ‘NON EMPTY’ keyword off the columns and rows parts then the 2nd query (member NOT overwritten by assignments) comes back in 1 second as well.

    SQL Profiler has not been very helpful in understanding what is happening.

    I’ve tried changing all of the assignments to calculated members but that appears to cause the same problem.

    Any suggestions?

    John

    John

    March 23, 2012 at 12:10 am

    • If you get the same problem with calculated members as assignments, that suggests to me that you aren’t having the same problem as is described here: it sounds like some of your assignments are doing things you might not be expecting them to. It’s very easy to write assignments that affect parts of the cube that you don’t want them to affect, and very difficult to work out when this is happening. I suggest you comment out all your calcs, run your query on a cold cache, get a timing, then uncomment each calc one by one and take timings again to try to isolate what the specific problem might be (if there is one).

      Chris Webb

      March 23, 2012 at 9:25 am

      • I’ve tried taking out the assignments and progressively reinstating them and:

        - the performance for any query for a member that is overwritten by an assignment stays the same
        - the performance for any query for a member that is not overwritten degrades roughly in proportion to the number of individual assignments put back in the cube script.

        John

        March 23, 2012 at 11:24 am


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 2,866 other followers

%d bloggers like this: