Sometimes Excel 2000/XP/2003 is hundred times or slower than other popular OLAP clients (ProClarity, NovaView…). Some queries can take 15 minutes in Excel, while they execute in couple of seconds in other clients. The problem is in fact that Excel does not use NON EMPTY keywords or NONEMPTYCROSSJOIN() function in the MDX query; so it filters the data on client side. As Excel does not show or allow us to modify the MDX query, we can’t do much about it. We can use other client tools, or maybe create our own report using Reporting Services.
—– Addendum 2/14/2005 ——
Seems like feedback to blogs is not working on our site at the moment 🙁 Therefore, I decided to add an important addendum to the original post.
I received an e-mail from Chris Webb. He kindly drew my attention to the fact that you can get the effect of the NONEMPTYCROSSJOIN() in Excel indirectly. He decribes the technique in his two blogs at
http://blogs.msdn.com/bi_systems/articles/162852.aspx and
http://blogs.msdn.com/bi_systems/articles/162856.aspx.
Thanks, Chris!
