I started learning MDX in 1999 and after so many years of using it and teaching it to other people, I still discover something new every day. Not only because I use it in strange ways (well, this doesn’t happen every day, at least!) but because there are other interesting information to read. Jeffrey Wang just published another interesting blog about data prefetching in MDX, which explains very well some strange behavior that sometime I observed in some customer cube. I never had a clear explanation about why this happen like in his excellent blog post.
Jeffrey explains how the Formula Engine makes a decision about pre-fetching data from Storage Engine and why this request might slower the query (especially in large cubes) instead of improving performances. Reading this post, I feel always more the need of a tool like Plan Explorer in order to better understand what happens in a query. Yes, I know it’s hard just to imagine how to graphically represents a query plan for MDX, but a tool that let you navigate in Profiler events with some hints would be helpful. A good start would be extending and improving MDX Studio.