Whenever I am asked “What is the single most important advice to write efficient MDX”, I always give same answer: The simpler MDX you write – typically the better performance you will get. The reasoning behind this is straightforward: If MDX is simple, it will be easier for the engine to understand and therefore to optimize it. I know that we all tend to think that we can do all kinds of tricks and outsmart engine, and sometimes it is true, but the engine becomes smarter and smarter with every release, and especially in AS2008. So the tricks which could’ve helped before, actually could be hurting more than helping.
And now, it seems that I am not the only one saying that ! Mark Whitehorn (my coauthor of “Fast Track to MDX” book) together with Keith Burns have just published a technical whitepaper “Best Practices for Data Warehousing with SQL Server 2008”. This seems to be a whitepaper focused specifically on the improvements in SQL 2008, and it includes section named “Write simpler MDX without worrying about performance”. Nice title, and in addition it has explanation of block computation mode.
I have really only couple of minor comments about it:
- Block computation mode was not introduced in AS2008, it existed in AS2005 (I have plenty of examples in my blogs). AS2008 though, extended applicability of block computation mode very significantly.
- Description of calculated member says that it is “running total of two consecutive years”, but the expression is not for running total, but rather for regular total, and also there is nothing specific about years in it. It will work with Month, Quarter, Day etc just as well.
- I didn’t really understand the following statement “In most hierarchical structures we know that if data is present for one cell in 2003, it will be there for all cells in 2003. The trip to see if there is data for the previous period only needs to be carried out once”. I think this statement is really misleading. If data is present for one cell in 2003 – there is absolutely no guarantee that it will be present for all (even for any other) cells in 2003. The diagrams just above this statement show that in fact most of the cells don’t have data. What I think authors tried to say was that MDX function PrevMember doesn’t need to be called for every cell, because .PrevMember will always result in , and AS2008 is smart enough to make this evaluation only once, and then compute the entire subcube of 2003 for non-empty cells only
- The example uses VISIBLE=2 property on calculated member. I wonder why…