Chris Webb's BI Blog

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

Calculated members vs assignments to real members

with 5 comments

One design technique I see used quite frequently on my travels, usually in financial applications and usually by people who have experience with OLAP tools other than AS, is the creation of members on a dimension which have no real data associated with them in the fact table and whose value is later overwritten with an assignment in the MDX Script. Imagine the following scenario, for example: you have a chart of accounts dimension which has a member Total Sales, which represents the total sales of all of the operating units in your company; you also want another member, Sales Plus Tax, which is the Total Sales value multiplied by a constant representing the rate of tax. To implement this you could either create a calculated member on the Account dimension which did the calculation, or in your Account dimension table create an extra real member and then in the MDX Script use a scoped assignment to write the value of the calculation to that member.

Creating the calculated member is the simplest option, so why not do that? Well, the ‘real member’ option has several advantages, such as:

  • You have absolute control over where the member appears in the hierarchy, unlike with a calculated member
  • Real members can have children, calculated members can’t
  • Real members can have member properties associated with them, calculated members can’t
  • It’s easier to manage all of your dimension members in one place, ie in the dimension table itself
  • It’s easier to write scoped assignments which cover only real members; writing scoped assignments that cover multiple calculated members can be a pain (see my blog entry here for example).
  • Some client tools (ok, Excel 2007) have problems with displaying and selecting calculated members on non-measures dimensions. For another practical example of the ‘real member’ technique, see Marco Russo’s blog entry on using it to create a time utility dimension here to get around exactly this issue.

Hmm, so that’s great, are there any disadvantages to using it? I’m going to have to be vague here, unfortunately, because I don’t understand what’s going on inside AS to be able to say for sure, but in many cases where I’ve seen the ‘real member’ technique used it’s been associated with poor query performance. Even where the calculation involved has been trivial and there has been no further aggregation of the resulting value involved, I’ve seen situations where queries are slow and Profiler has gone nuts. It certainly doesn’t happen for every calculation and every query, but when it has and I’ve created a calculated member that contains the same calculation performance has been much better. I wonder what’s going on here? Maybe someone from Microsoft can comment? As you can probably guess, I’m writing this while onsite with a customer experiencing exactly this problem and I’m hoping to provoke some discussion on this topic…

In the meantime, all I can say is that be wary of using the ‘real member’ technique and test to see if a calculated member performs better. And hopefully in Katmai+1 we’ll see some of the limitations of calculated members addressed so the provide a stronger alternative.

Written by Chris Webb

February 23, 2008 at 11:04 pm

Posted in MDX

5 Responses

Subscribe to comments with RSS.

  1. You have pointed at a problem that I also have met in financial SSAS2005 solutions. You can also easily recreate this problem by creating a calculated member on a single dimension member, like a city or a single customer.  Try first to slice on the single member and it is really fast. Try the same with the calculated member and SSAS2005 will go on forever.
     
    Good to know that there is no way to solve this but to create your own calculation members in the dimension table.

    Thomas

    February 25, 2008 at 8:22 pm

  2. had a look,finally ,didnt understand@_@

    Cherry

    February 26, 2008 at 4:50 am

  3. Jörg – thanks, that\’s interesting. I thought of custom member formulas but didn\’t try them, although I will now.
    Thomas – you\’ve got the wrong end of the stick, I think! It\’s the calculated members that perform better than overwriting the real members, although of course I\’m sure there are scenarios where the calculated members perform badly as you say.

    Chris

    February 27, 2008 at 6:18 pm

  4. Chris, perhaps the scenarios do not match but I have both seen the simple case of recreating a dimension member as a calculated member and when you try to slice on the calculated member the query will go on for ever. Utility dimensions like with a YTD expression will work fairly well as long as you keep them on an axis. If you slice on them they will be slow but not as bad as the first scenario.  Changing the first scenario to a MDX script assignement will not improve much either.
     
    When I read your text more carefully I can see this difference in what we are discussing.

    Thomas

    February 27, 2008 at 6:40 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,309 other followers

%d bloggers like this: