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.