THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

I need your help or how to make multiselect work seamlessly in MDX

This post is unusual for me. In this post I am not going to give information to my readers. I am going to ask my readers to give information to me. For quite a some time now I have been looking into problem of interaction between MDX calculations and multiselect. Multiselect is a feature of pretty much every single OLAP client tool, where the user decides to slice not by single hierarchy member, but by several members. The way different client tool generate MDX for the multiselect differs (query calculated member with Aggregate, sets in WHERE clause, subselects etc) and each method has advantages and drawbacks, but this post isn't about how to write MDX SELECT statement to express multiselect in the best way. It is about the fact that each one of these methods breaks in one form or another when the query touches a cell using huge class of MDX calculation, specifically the ones which reference implicitly or explicitly CurrentMember property of the hierarchy by which multiselect is done. Sometimes it is possible to do something about it, and I even have written couple of blogs on the subject: "Writing multiselect friendly calculations" and "Multiselect friendly MDX for calculations looking at current coordinate". However, the methods I describe in these posts are cumbersome, not universal (i.e. don't solve all the scenarios) and hurt performance even when multiselect is used. Additionally, these methods don't address scenarios which are not pure multiselect, but in related functionality, such as Visual Totals and Custom Grouping. Lastly, most of these methods are useless when the queries are generated by Excel 2007 - which is a pity, because Excel 2007 compared to the previous versions of Excel is hands down the best OLAP browser, and according to OLAP Survey, Excel is the most popular client tool for Analysis Services.

This situation bothered me, and I was thinking about solution which would satisfy the following requirements:

1. Users should not change the existing calculations to work with multiselect, (or change very little). Ideally, the calculation author should not even think how his formula will work in the presence of multiselect - it should just work. The tricks with EXISTING & Co are too complex and unacceptable.

2. Performance should not be affected in the scenarios when there is a single member select. The performance in the scenarios with multiselect should be better or equal to the sum of performances for single member cases which compose the multiple member select.

3. The solution should work in any client tool regardless of how it generates MDX SELECT statement to implement multiselect. The results should be the same across all client tools.

4. The same solution should also address Visual Totals and Custom Grouping scenarios

These are not easy requirements to satisfy, but I believe it have found solution which would address all of them. I have checked all of the examples that I am aware of for calculations that fail today in the presence of multiselect, and all of them seem to be resolved with the solution I am thinking about. However, my collection of examples is far from complete, and this is something I need from the readers of my blog: Send me all the MDX calculations that you have which fail today with multiselect. You can either leave them in the comments of the blog, or send them me by email, or leave me your email address for me to contact you etc. I need as many different scenarios as I can get and in short amount of time to validate my approach. The sooner I can get more scenarios - the better. I really appreciate everybody's help on this. I have to apologize upfront, that I won't be able to share the progress of my investigation until I don't know when. At least several months if not more. But as soon as it will be OK to share the status - I will do it. Even if the status will be 'Sorry - nothing came out of it'. Thanks again for your help, and looking forward for your examples !

Published Wednesday, May 23, 2007 7:46 PM by mosha
Anonymous comments are disabled
Privacy Statement